Data visualization and monthly dealer report automation
for a captive finance company
Toyota Industries Commercial Finance (TICF) is the captive finance provider for Toyota Forklifts and Hino Trucks. As part of the Toyota Family, they have been a leading provider of financial solutions since 1985.
The problem: A growing dealer network created the need to better communicate individualized key metrics to the dealers on a routine basis.
The solution: Developed a process to export data monthly, visualize the data points using custom scripted specialized software, then generate hundreds of customized e-mails through an e-mail marketing platform for distribution.
The result: Dealer feedback exceeded expectations, and the customized reports have allowed Toyota Commercial Finance to work with individual dealers on goal acheivement and performance tracking over time.
“Rains | Birchard Marketing delivered an elegant solution, designed on-brand, integrated with our existing Mailchimp platform, and in a repeatable, automated manner serving our two distinct dealer groups. It’s the sort of creative problem solving that benefits our operations.”
– Bill Borris, Director – Marketing and Remarketing, Toyota Commercial Finance
The North American dealer networks for both Toyota Forklifts and Hino Trucks have been expanding. As a captive finance company, Toyota Commercial Finance is dedicated to helping these dealers achieve their goals. To demonstrate the role strategic financing can play in enhancing dealer operations and profitability, TICF knew they needed to better communicate individualized key metrics to the dealers on a routine basis. But, they had no existing systems in place to do this.
To remedy this, we first consulted with TICF to understand the problem and desired outcome. We proposed an initial thesis of how we might solve the problem. Sought to understanding the limitations of the existing databases and dealer communication channels, and then embarked on a research phase to determine if an off-the-shelf solution existed that could be applied to the problem. Exploratory work determined that a custom solution would be needed.
To begin, we designed a prototype report that could be delivered via e-mail. This allowed us to settle on how the existing TICF brand guidelines would apply to a new deliverable not specifically covered by the guidelines. Then we guided TICF on a database query from an internal system, with an export of the data set as a CSV file. Next, we implemented data visualization software, Tableau, running on an agency server to generate individual graphics for each of the dozen or so metrics we’d report on monthly. These were designed to be reassembled as variable data in an e-mail send. Mailchimp is the e-mail marketing platform used by TICF for dealer communications, thus our work needed to integrate with it.
Once the visualization work was done in Tableau, we needed to automatically name each graphic. The two dealer bodies, Toyota Forklifts and Hino Trucks, each have different metrics they track, and the number of metrics also varies. It was critically important that we only send the specific dealer their information. Thus, developing a nomenclature for graphics keyed to a dealer name, that would be repeatable, ensured the system would be durable. After solving for the creation and naming of the visuals, we faced the challenge of getting Mailchimp to properly assemble the individual e-mails based upon the graphics. Mailchimp doesn’t currently offer a bulk file upload function, and uploading over a thousand individual graphics to the media library would be impractical. Thus, we FTP the graphic files to a hidden directory on a TICF domain. We then scripted each e-mail for Toyota Forklifts and Hino Trucks to pull the graphics in via a variable data function. However, each e-mail includes some non-visualized variable data, namely the dealer’s name and contact information for TICF sales and support staff based upon regions and dealer.
Our Mailchimp solution also relies on two custom mailing lists, that include additional details in order for the data and image merge to work correctly. In testing we discovered that in some instances, a dealer may no have any data reported for a particular metric, and this would “break” the e-mail. Thus, we added additional logic to use a default graphic, or “n/a” graphic for some cases, and to simply not generate an e-mail if certain data is not provided. The entire system is triggered by a set of 6 CSV files TICF outputs on a set date to remain consistent. Processing and proofing requires some manual steps by the agency, but we’ve elected to keep it this way in order to have confidence in the output. After the initial launch, we worked with TICF to do additional customization to allow for more flexibility, such as changes in goal lines for graphics. This additional customization allows the system to remain mostly automated based upon the CSV files provided monthly by TICF.
Dealer feedback exceeded expectations, and the customized reports have allowed Toyota Commercial Finance, particularly regional sales managers, to work with individual dealers on goal achievement and performance tracking over time.