FMCG Category Analytics and Assortment Optimization

Project Overview

This project simulates the work of a Data Analytics Intern supporting an FMCG Category Management team. It integrates retail sales, price and calendar data to build a reproducible SKU–store–date analytical dataset, evaluate commercial performance, optimise the assortment and forecast future demand.

The analysis covers 3 California stores, 1,437 food SKUs and 365 historical days.

Business Questions

• Which stores, departments and SKUs drive sales performance?
• How concentrated is revenue across the assortment?
• Which products are core, volatile, growing or suitable for further review?
• Does a machine-learning model improve 28-day demand forecasting?
• How can the findings support replenishment and assortment decisions?

Tools

• Python: pandas, NumPy, matplotlib, scikit-learn and LightGBM
• SQL: joins, aggregation, window functions, ranking and validation
• Tableau: executive, assortment and forecast dashboards
• Kaggle: cloud-based data preparation and model execution
• GitHub: code, methodology and project documentation

Data Pipeline

1. Selected the FOODS category, three California stores and the latest 365 days.
2. Reshaped the original wide sales table into SKU–store–date format.
3. Joined calendar, event, SNAP and weekly price information.
4. Created estimated revenue and quality-control indicators.
5. Validated row counts, duplicate keys, date matching and price coverage.
6. Produced daily, weekly, SKU and store-level analytical tables.

Key Results

• Analysed 3,455,236 historical units and approximately 9,178,649.00 in estimated revenue.
• Segmented 1,437 food SKUs using an ABC-XYZ framework.
• Identified 436 core and variability-managed core SKUs, contributing approximately 68.63% of estimated revenue.
• Identified 81 growth opportunities, 11 high-value volatile SKUs, 239 stable niche SKUs and 4 potential rationalisation candidates.
• Built a 28-day forecasting pilot for the top 200 SKUs, covering 49.46% of historical estimated revenue.
• Machine Learning achieved the lowest validation WAPE at 36.52%.
• The machine-learning model reduced WAPE by 13.87% relative to the strongest simple baseline.
• Forecast approximately 117,232 units and 347,793.90 in estimated revenue over the next 28 days for the selected SKUs.

Analytical Framework

Performance Reporting

The project evaluates units sold, estimated revenue, revenue share, average selling price, active SKUs, zero-sales rate and price coverage at store, department and SKU levels.

ABC-XYZ Segmentation

• ABC measures estimated-revenue contribution.
• XYZ measures weekly demand stability using the coefficient of variation and active-week rate.
• Combined decision labels include Core SKU, Growth Opportunity, High-value Volatile SKU, Stable Niche SKU, Monitor and Potential Rationalization.

Demand Forecasting

Three models were compared using a time-based 28-day backtest:

• Seasonal Naive
• 28-day Moving Average
• Machine Learning (LightGBM)

The primary selection metric was WAPE, supported by MAE, RMSE and forecast bias.

Repository Structure

fmcg-category-analytics/
├── notebooks/
│   ├── 01_data_cleaning_and_validation.ipynb
│   ├── 02_eda_kpi_and_sku_analysis.ipynb
│   ├── 03_abc_xyz_assortment_optimization.ipynb
│   ├── 04_demand_forecasting.ipynb
│   └── 05_project_packaging_and_tableau_exports.ipynb
├── sql/
│   └── 01_bigquery_data_validation.sql
├── tableau_data/
│   ├── 01_executive_kpi.csv
│   ├── 04_weekly_trend.csv
│   ├── 05_sku_portfolio.csv
│   ├── 07_store_assortment.csv
│   ├── 08_model_metrics.csv
│   └── 12_future_28d_detail.csv
├── documents/
│   ├── resume_project_cn.txt
│   ├── resume_project_en.txt
│   ├── interview_pitch_cn.txt
│   ├── interview_pitch_en.txt
│   └── tableau_dashboard_guide_cn.md
└── README.md

Limitations

• Estimated revenue equals units sold multiplied by selling price and does not represent profit.
• Event/SNAP indicators are descriptive proxies rather than confirmed promotion variables.
• Inventory, stockout, cost, margin and lead-time data are unavailable.
• The forecasting pilot covers the top 200 SKUs rather than the complete assortment.
• The current evaluation uses one 28-day backtest window; rolling backtests would provide a stronger robustness assessment.

Business Interpretation

The project demonstrates that assortment decisions should combine revenue contribution, demand stability, recent performance and store-level differences. Low-revenue products should not be treated as one homogeneous group, and high-value volatile products require forecasting and inventory attention rather than automatic reduction.