Business Overview
The client is a leading global Fast-Moving Consumer Goods (FMCG) company. They rely heavily on detailed market reports to understand product performance, sales trends, and consumer behavior. These reports, often sourced from data providers like WSP, are crucial for making strategic decisions about product placement, marketing campaigns, and inventory management.
Project Objective
The central objective of this project was to transform the client’s market analytics process from a time-consuming, manual workflow into an efficient, automated system. The team’s previous method of downloading and filtering reports from WSP was labor-intensive and delayed decision-making. The new automated system aimed to:
Ingest raw data and automatically create comprehensive market mappings.
Integrate multi-level product, period, and market data into a cohesive model.
Apply complex calculations to generate key sales metrics automatically.
Produce detailed, accurate reports with monthly, quarterly, year-to-date (YTD), and moving annual total (MAT) summaries in a single run.
Challenges & Solutions
Challenge 1: Time-Consuming Manual Process The manual process of downloading and filtering reports from WSP required a deep understanding of multiple market hierarchies and consumed significant hours of a data analyst’s time. This extensive manual effort was highly inefficient and a major bottleneck in the reporting cycle.
Solution: An automated process was implemented to ingest raw, flat CSV files directly from a designated location. This solution eliminated the need for manual data retrieval and filtering. By automating the entire data flow—from ingestion and transformation to final report generation—the time required to produce a complete report was drastically reduced from hours to mere minutes.
Challenge 2: Complex and Inconsistent Data Mappings The client’s market data involved intricate hierarchies and product mappings that varied across different reports and categories. Maintaining data accuracy and consistency was a constant struggle due to the complexity of manually aligning these disparate datasets.
Solution: The solution included an automated process for creating a detailed market mapping file. This process automatically derived and combined markets based on a pre-defined hierarchical structure (e.g., zones, states, cities), ensuring that all data was consistent and standardized. A robust data model was built to integrate product, period, and market hierarchies, which served as a single source of truth for all subsequent analysis and reporting.
Challenge 3: Diverse and Complex Metric Calculations The project required the calculation of a wide array of specific metrics, each with its own unique formula. These calculations had to be applied consistently across different levels—markets, time periods, and products—to provide meaningful insights.
Solution: The automated system was designed with a modular approach to handle distinct calculation logics. It applied a series of specific formulas for each metric and period (e.g., monthly, quarterly, YTD, MAT) on the integrated data model. This ensured that all metric-based outputs were calculated accurately and consistently, providing clear, reliable insights into market dynamics and performance trends.
Implementation and Outcomes
The solution was developed as a robust, executable Python script. It utilized the Pandas library for all data ingestion, modeling, and transformation tasks. The script was designed to read input data from CSV files, merge different DataFrames to create a unified data model, perform all necessary data transformations and calculations, and finally export the clean, processed data into a final CSV report.
The implementation delivered significant value and tangible benefits:
Drastically Reduced Time: The report preparation time was slashed from several hours to just a few minutes, enabling faster access to critical market insights.
Improved Accuracy and Consistency: By eliminating manual intervention, the automated system ensured that data was accurate and consistent across all reports, leading to more reliable analysis.
Actionable Insights: The final CSV reports provided a comprehensive view of product and market performance, complete with monthly, quarterly, and annual summaries, empowering stakeholders to make more informed, data-driven decisions.
Scalable Framework: The automated solution is highly scalable and adaptable, allowing it to easily accommodate new markets, products, or reporting requirements as the business evolves