Wear-Dated Report Builder: Case Study

This case study describes the development of the "Wear-Dated Report Builder," which created presentation-ready reports filled with customer relevant product analysis data to support the placement of carpet styles made with Wear-Dated branded fiber at targeted retail accounts.

Situation:
When I joined the Wear-Dated carpet division in August 2003, the business was transitioning from handshake-deals to a consultative, volume-based approach based on data. Although we had plenty of data, none of it was easily extractable for use in consistent and coherent reports that could be used in consultation with retail customers.

Problem Part I:

The only credible way to make product recommendations to retailers was based on insights obtained by integrating the data from three separate Oracle databases: a warranty database with information on individual carpet grades [weight, construction style and name], sales transaction data with volume information, and a retailer database. We needed a report that extracted the information relevant to a specific retailer, analyzed Wear-Dated product usage and recommended specific styles to fill product gaps.

Solution:

Starting in October 2003, I worked with IT to obtain limited extractions of all three databases [note: these were large files!] to start experimenting.

Using Access, I learned how to run multiple query iterations that I validated against existing reports. Once the queries calibrated and the results consistent, I presented the information to the 5 Wear-Dated regional managers for agreement to proceed and for confirmation on the types of charts they needed in the final output. I also asked them to identify their top 30 accounts [out of 40,000]. These would be the accounts I focused on.

The next three months were spent manually producing ~20 page PowerPoint presentations analyzing retail account Wear-Dated volume by weight and style, then recommending new carpet style introductions to fill product gaps for over 150 top tier retail accounts in time for Surfaces, the yearly flooring industry trade show that takes place in late January/early February.

Result:
This first ever in-depth product analysis tool for use as consultative up-selling tool with retailer accounts to preferentially sell branded products was a big success and enabled the regional managers to have more productive, consultative discussions with accounts. The reports had made for higher level and pro-active discussions about how to drive increased retail business and create the optimum product mix to maximize margins. They led to additional placement of product made with Wear-Dated carpet fiber.

Problem Part II:

After Surfaces, I debriefed with the regional managers to understand how they had used the reports, what feedback they had received and whether to continue. The response: a resounding yes!

Given that agreement, the problem now became how to produce the same reporting results with less effort. However useful, that level of focused effort requiring three full months of my time to analyze data and manually produce reports was not sustainable. Could the process be automated more efficiently and cost effectively? Could reports be generated more frequently?

Solution:

In Fall 2004, I met with Blue Lacuna, a vendor specializing in automating the process of getting multiple databases to speak to one another, extracting relevant information into presentation ready, useable output to discuss the project.

In addition to automating the process, I wanted to place into the hands of the end users [i.e., the regional managers], the ability to run reports when they needed to rather then when I was available, and as frequently as they required to support and grow business volume.

With Blue Lacuna, I scoped out the end report requirements and mapped out how to extract the correct information. A major issue was the size of the databases which could easily cripple the operating system of the average laptop. Part of the process, then, would require "normalizing" the data.

Once a workable program in place, we parallel-produced automated and manual reports based on 2004 data to calibrate results and ensure that the reports created for Surfaces 2005 retailer meetings were accurate. Half of the Surfaces 2005 reports were produced using the Wear-Dated Report Builder program, enabling more reports to be run and closer to the show date.

As soon as Surfaces 2005 over, we were ready to put in place the 2005 Wear-Dated Report Builder program which now included monthly and quarterly updates. Individual end users produced their own reports via a user-friendly control panel [see the image below of the 2008 user panel]. Data updates and retailer logos were delivered digitally and automatically.

Each individual report included a 4 year volume analysis in yards and pounds with a year-over-year analysis, breakdown by product style and weight, and by manufacturer, the top volume products and the top increasing/decreasing products. We also included national and regional benchmarking.

Each program year, from 2006 through 2008, we made additional enhancements based on user feedback adding:

- reports by retailer buying groups

- individual carpet mill reporting

- a specific carpet style suggestor tool to include specific carpet grades for recommendation
- customizable bundled reports

- and enhanced the look/feel of the presentation template to reflect changes in Wear-Dated marketing campaigns.

Result:
The final automated program generated best-in-class customized retailer reports, taking otherwise irrelevant and unrelated data and converting it into actionable information, all available in minutes to individual users vs. built manually over several months and at far higher cost. The automated report builder meant that reports could be generated for any one of the 40,000 customer retailer accounts at any time.

Wear-Dated Report Builder created professional presentation reports that formed the backdrop for high level and pro-active discussions about how to drive increased retail business and create the optimum product mix to maximize margins. They led to additional placement of product made with Wear-Dated carpet fiber.


Technorati Tags: Wear-Dated report builder DXT.report builder Blue Lacuna consultative selling simple marketing Del.icio.us Tags: Wear-Dated report builder DXT.report builder Blue Lacuna consultative selling simple marketing