Thorogood is dedicated to supporting our customers during the Covid-19 pandemic.

Building a solution that lasts: Thorogood helps a prominent CPG firm reap dividends from a decades-old investment

Case study by Evelyn Heyes

When it comes to winning market share, understanding one’s competition is as important as understanding one’s own customers. Today’s successful businesses are those that were best able to anticipate and respond to yesterday’s competitive landscape.

More often than not, the secret to that success is the ability to process and analyze widely available data in a way that paints the most accurate and timely picture of market conditions.

Nearly two decades ago, well before the business world fully understood the potential of Big Data, consultants at Thorogood set out to build an analytics solution for a prominent CPG firm that would both place it ahead of the curve and keep it there throughout the future. Today, the application and its underlying software serve as a testament to the enduring value that an investment in analytics can have when it is made with the proper foresight.

A tool to grow on

Then as now, the client in question was competing in a hotly-contested and highly-regulated product space where brand recognition and price points were among the most significant variables in driving consumer behavior. The company’s Strategic Insights team envisioned a world in which it could follow the purchasing habits of consumers in as close to real time as possible. By monitoring the sales performance of its competitors’ products, the team would be able to anticipate changing market conditions and take steps to adapt to them.

To accomplish this, the CPG firm enlisted Thorogood to develop a tool that would aggregate sales data from each retailer in which its products were sold and enable its Strategic Insights team to analyze that data from a variety of points of view. Accomplishing these goals required a keen understanding of the capabilities of front end and back end technology and the nature of the client’s volatile business. It also required the foresight to build the tool in such a manner that would enable it to grow and scale up as the requirements shifted.

Using a collaborative, iterative design process that is the long-standing hallmark of Thorogood’s approach, the consultants developed an application that would be built atop a robust back-end architecture that would facilitate the aggregation of the company’s array of sales-related data coming from a wide number of external data providers. Not only would the tool automatically load and reconcile the requisite data sources, it would also include a component to allow users to recognize and match to its internal systems, any new products and stores that arrived with the data. This latter functionality was an example of the way solutions can be built with the future in mind. Our client didn’t just want a tool that would meet its present needs. It wanted one that was agile enough to adapt over time.

Building on the past

The fundamentals of the tool in question remain very much the same. The client’s main source of data comes from Information Resources, Inc., one of the authorities on market intelligence in the retail sector. But due to the nature of our client’s business, IRI’s data cannot capture everything. Filling in these gaps means relying on data from a number of smaller sources. To rectify the lack of uniformity between these sources, Thorogood designed to tool to utilize SQL Server Integration Services (SSIS) pipelines to automatically transform and load the pertinent Excel files into a SQL Server Data base. By including an attribution component, the tool allows users to manage the 20 to 25 files that are loaded into SQL each week.

User friendliness is a key point of emphasis when developing a solution that has lasting value. Our client needed a tool that gave it the ability to manage and structure data so that it could tailor toward specific analyses. The SQL Server Reporting Services (SSRS) reporting that the Strategic Insights team performs utilizes sales metrics such as volume, price and quantity sold to keep tabs on the flow of products in the marketplace. These analyses are crucial for any company hoping to keep pace with its competitors and adapt its offerings to changes in demand. For instance, our client might be experiencing a decrease in sales over a series of weeks. Using the Thorogood-developed tool, analysts can quickly and accurately pinpoint any factors that are contributing to the change. If each of its competitors are experiencing similar declines, one might conclude that the market itself – or the overall economy – is retracting. But the type of powerful analysis the tool provides can also reveal that a competitor has introduced a new product or reduced the price on an existing product, and that these changes are siphoning away sales volume from one of their own products. Users needed a tool that was capable of adapting to these changes.

Until we actually got to play with the tool, it was very hard to know what it was capable of.” – Strategic Insights team member, user of the tool.

A tool that grows with business

One of the reasons our client enlisted Thorogood in the initial project was our consultants’ long history of developing present-day solutions with an eye toward the future, thereby limiting the need for substantial perpetual reinvestment in new technology.

Over the last 17 years, Thorogood has worked diligently to expand the tool’s capabilities to respond to new technologies and business realities. That work has included the expansion of an SQL Server Analysis Services (SSAS) cube that gives users an ability to structure their reports to their liking. By building a scalable solution, Thorogood has enabled users to consistently expand the number of data sources it incorporates into its analyses. Recently, the tool underwent a complete facelift with a redefined user journey and a future ready architecture, along with constant additions to the number of data feeds reported. Future integration with Power BI will open up a new frontier in visualization capabilities.

The foundation of all of these things was a smart, forward-thinking implementation of back-end technology that continues to lead to the insights that lead to business success.

Find out more

Contact Evelyn Heyes. Evelyn is a Principal BI & Analytics Consultant and a member of the Executive Team at Thorogood.