Manufacturing & Retail Company
Consolidating Sales Metrics.
This manufacturing company struggled to consolidate sales metrics across a variety of markets.
2nd Watch pulled our client’s raw data into Snowflake and wrote SQL scripts to recreate the data transformation we were seeing in their existing Excel workbooks. We then appropriately formatted the data for use in a Power BI report.
The documentation of the transformation process and SQL script gave this manufacturer’s BI team a better understanding of how raw sales data became consolidated market share information.
About the Business
This well-known client is an international retailer based in the U.S. They produce powerful, long-lasting batteries, including AA batteries, rechargeable batteries, coin button batteries, and more.
The Business Challenges
An international manufacturing company was struggling to consolidate sales metrics across a variety of markets. The process for generating reports was unclear to business intelligence (BI) teams and stakeholders, and it seemed that much of the work was done manually by one person. Transformations of the data were done in Excel, across multiple sheets and workbooks for each market, and then pasted into a final Market Share document for stakeholders to analyze. This process left our client’s teams with no ability to drill further into market level metrics and no way to understand what transformations had been done.
The company asked 2nd Watch to explore these Excel workbooks, understand the data transformation process, and recreate it in Snowflake for use in a Power BI report.
The 2nd Watch Solution
To start, the 2nd Watch development team pulled the raw sales data into our warehouse using Snowflake. From there, we wrote SQL scripts to recreate the transformation of the data we were seeing in the Excel workbooks. This was a combination of rewriting Excel formulas and appropriately formatting the data for analytical purposes.
We also created a dedicated currency conversion table to more quickly and more accurately convert sales metrics. The entire transformation process was recreated in Snowflake, only using one SQL script.
The Business Benefits
By recreating the transformation logic in Snowflake, 2nd Watch eliminated the need for multiple Excel workbooks and sheets per market, as well as a tedious manual process that needed to be done several times a year. We lowered the time it took for reports to be generated, and getting more accurate data into the hands of our client’s stakeholders enabled them to make decisions more quickly.
The documentation of the transformation process and SQL script gave this manufacturer’s BI team a better understanding of how raw sales data became consolidated market share information. A week’s worth of work by several people is now done in a few minutes, without the need for copy pasting data from one workbook to another.
The greatest impact is turning a manual task into an automated and documented process. If the employees completing that task were to leave the company, our client was at risk of losing the knowledge of how their international market share data was being calculated. Now, the entire process is documented in Snowflake and all that is needed to maintain their system is an experienced data engineer.