The Client
Our client is a leading pediatric nutrition company offering a wide range of formulas tailored to meet the diverse needs of infants. Their products are manufactured and sold in numerous countries across the globe.
Problem Statement:
- Client’s product marketing data was scattered across multiple vendors and stored in CSV/Excel files on a quarterly ad-hoc basis.
- Lack of a defined system to track vendor performance or measure campaign effectiveness.
- This created a “dark view” of marketing source performance.
- Ineffective tracking directly impacted product promotion and sales outcomes.
Solution Implemented:
- Developed a Power BI scorecard with predefined methodology to evaluate vendor performance using interaction-based scoring and flags.
- Categorized different interaction types to assign scores, providing a comprehensive view of each source’s effectiveness.
- Data ingestion is handled via Couch Drop SFTP, loading files into SQL staging tables on ad-hoc client requests.
- Applied complex scoring logic and activity flagging in SQL procedures, then moved cleaned data into transformation layer tables.
- In Power BI, modeled data with advanced DAX formulas to calculate average scores, segmenting vendors into high, medium, and low performance categories.
Results & Impact:
- Advanced source scoring model cleared the dust on actual performance
- Real effectiveness of sources at different time series finally unfolded
- Directly impacted promotion sells numbers after new scoring model