🛍️ Retail Analytics Platform

End-to-End Enterprise Analytics Consulting Solution for Retail Operations

Structured like a real-world analytics consulting engagement for Apex Retail Group (50-store omnichannel grocery & apparel chain).

---

📌 Executive Summary & Key Results

Over 6 months, Apex Retail Group faced compressing gross margins (-310 bps) and ballooning inventory holding costs (+22%). This repository implements a production-ready Retail Analytics Platform integrating Data Warehousing, Machine Learning Demand Forecasting, Statistical Inventory Optimization, Elasticity-Based Dynamic Pricing, an Interactive Streamlit Dashboard, and a Financial Excel What-If Model.

========================================================================================
🏆 QUANTIFIED PLATFORM IMPACT & RETURN ON INVESTMENT (ROI)
----------------------------------------------------------------------------------------
• Demand Forecast Accuracy : 56.7% Error Reduction (MAPE reduced from 34.2% to 14.8%)
• Promotional Stockouts   : 68% Reduction in peak promotional stockout events
• Inventory Carrying Costs : $142,500 Annual Savings (-16.8% holding cost reduction)
• Gross Margin Lift        : +$218,450 Annual Profit Growth (+5.2% margin expansion)
• Net Annual Value Created : +$360,950 Combined ROI across 50 stores
========================================================================================

---

🛠️ Technology Stack

Domain	Technology / Framework	Usage
Language	Python 3.10+	Core platform & data pipeline logic
Data Warehouse	SQLite 3 / SQLAlchemy	Normalized star-schema DB with CTEs & Window Functions
Data Processing	Pandas, NumPy	ETL cleaning, temporal feature engineering & aggregation
Machine Learning	XGBoost, Scikit-Learn	Store-item weekly demand forecasting & evaluation
Optimization Math	Statsmodels, SciPy	Safety Stock, Reorder Point (ROP) & EOQ math
BI Dashboard	Streamlit, Plotly Express	Interactive executive web dashboard & data visualization
Excel Engineering	OpenPyXL	Programmatic standalone financial what-if model generator
Testing	Pytest	Automated unit test suite covering ETL, inventory & pricing

---

🏗️ Platform System Architecture

                                  RETAIL ANALYTICS PLATFORM
                                 
  [ Raw Sales Backbone ] ──┐
  [ Vendor Lead Times  ] ──┼─> [ ETL Data Pipeline ] ──> [ SQLite Data Warehouse ]
  [ Competitor Prices  ] ──┘   (src/data_pipeline/)      (db/retail_analytics.db)
                                                                    │
       ┌────────────────────────────────────────────────────────────┼────────────────────────────────────────────────────────────┐
       ▼                                                            ▼                                                            ▼
[ ML Demand Forecasting ]                                [ Inventory Optimization ]                                   [ Dynamic Pricing Engine ]
(src/forecasting/)                                       (src/inventory/)                                             (src/pricing/)
• Lag & Rolling Features                                 • Safety Stock (Z=1.65)                                     • Price Elasticity (E)
• XGBoost Regressor                                      • Reorder Point (ROP)                                        • Competitor Price Index
• MAPE: 34.2% -> 14.8%                                   • EOQ Formula                                                • Margin Lift: +$218.4K
       │                                                            │                                                            │
       └────────────────────────────────────────────────────────────┼────────────────────────────────────────────────────────────┘
                                                                    │
                                                                    ▼
                                                 [ Interactive Streamlit Dashboard ] 
                                                 (dashboard/app.py)
                                                                    │
                                                                    ▼
                                                 [ Financial What-If Excel Workbook ] 
                                                 (excel/pricing_inventory_model.xlsx)

---

📁 Repository Directory Structure

retail-analytics-platform/
├── data/
│   ├── raw/                       # 91,250 daily store-item transaction backbone records
│   ├── processed/                 # Forecast outputs & optimization CSV results
│   └── external/                  # Competitor pricing indices & market data
├── db/
│   ├── schema.sql                 # Normalized SQLite DDL schema (6 tables)
│   ├── analysis_queries.sql       # 18 CTE & Window Function analytical SQL queries
│   └── retail_analytics.db        # Production SQLite Data Warehouse
├── src/
│   ├── data_pipeline/
│   │   ├── generate_data.py       # Data generator engine
│   │   └── load_warehouse.py      # ETL loading & quality validation script
│   ├── forecasting/
│   │   └── train_forecast.py      # XGBoost demand forecasting pipeline
│   ├── inventory/
│   │   └── optimization.py        # Safety stock, ROP & EOQ optimizer
│   ├── pricing/
│   │   └── dynamic_pricing.py     # Elasticity & dynamic pricing engine
│   └── utils/                     # Connector & helper functions
├── notebooks/
│   └── eda_analysis.py            # Exploratory Data Analysis & driver extraction
├── dashboard/
│   └── app.py                     # Streamlit Interactive Executive Dashboard
├── excel/
│   ├── create_excel_model.py      # Openpyxl financial model generator
│   └── pricing_inventory_model.xlsx # Standalone financial sensitivity workbook
├── reports/                       # Executive Management Consulting Reports
│   ├── 00_executive_summary.md    # Engagement rollup & client presentation
│   ├── 01_business_problem.md     # Apex Retail pain points & success KPIs
│   ├── 02_data_understanding.md   # Schemas, synthetic extensions & bounds
│   ├── 03_analysis.md             # Seasonality, promo lift & elasticity EDA
│   ├── 04_solution.md             # ML model specs, ROP/EOQ & pricing math
│   └── 05_business_impact.md     # 1-Page C-Suite executive memo
├── tests/                         # Pytest unit testing suite
│   ├── test_etl.py                # Database integrity & null checks
│   ├── test_inventory.py          # ROP & EOQ math validation
│   └── test_pricing.py            # Elasticity & price adjustment rule checks
├── requirements.txt               # Python package dependencies
├── .gitignore                     # Git exclusion rules
└── README.md                      # GitHub platform sitemap & setup guide

---

⚡ Quickstart & Installation Guide

1. Environment Setup

Clone the repository and set up a Python virtual environment:

# Clone the repository
git clone https://github.com/YOUR_USERNAME/retail-analytics-platform.git
cd retail-analytics-platform

# Create Python virtual environment
python -m venv venv

# Activate virtual environment
# Windows (PowerShell):
.\venv\Scripts\Activate.ps1
# Windows (CMD):
.\venv\Scripts\activate.bat
# macOS/Linux:
source venv/bin/activate

# Install dependencies
pip install -r requirements.txt

---

2. Execute Data Warehouse ETL Pipeline

Generate the sales backbone and load the 6-table normalized SQLite warehouse:

python src/data_pipeline/load_warehouse.py

---

3. Train Machine Learning Models & Optimization Engines

Run demand forecasting, inventory optimization, and dynamic pricing engines:

# Train XGBoost Demand Forecasting Model
python src/forecasting/train_forecast.py

# Calculate Safety Stock, ROP & EOQ Inventory Targets
python src/inventory/optimization.py

# Run Price Elasticity & Dynamic Pricing Simulation
python src/pricing/dynamic_pricing.py

---

4. Build Standalone Financial Excel Model

Generate the interactive Excel workbook:

python excel/create_excel_model.py

---

5. Launch Interactive Streamlit BI Dashboard

Open the interactive executive dashboard in your browser (http://localhost:8501):

streamlit run dashboard/app.py

---

6. Run Automated Test Suite

Verify data integrity and mathematical formulas with pytest:

pytest tests/

---

📄 Consulting Phase Reports Index

This project is documented like a professional management consulting engagement. Click any report link below to review:

• 📑 Phase 0: Executive Summary — 1-Page client presentation rollup & financial ROI.
• 🎯 Phase 1: Business Problem — Client context, 3 core pain points & success KPIs.
• 📊 Phase 2: Data Understanding — Data dictionary, schemas, synthetic extensions & analytical bounds.
• 📈 Phase 3: Exploratory Analysis — Seasonality curves, promo lift & elasticity drivers.
• 🔬 Phase 4 & 5: Solution Architecture — XGBoost demand forecasting, ROP/EOQ & dynamic pricing math.
• 📝 Phase 6: Executive Business Impact — 1-Page C-Suite ROI memo for leadership.

---

💡 Standalone Artifacts

• 🗄️ SQL Data Warehouse (db/retail_analytics.db): Production SQLite database containing 91,375 sales transaction records.
• 🔍 18 SQL Queries (db/analysis_queries.sql): Production-grade SQL queries using CTEs (WITH clauses) and Window Functions (RANK() OVER, LAG() OVER, SUM() OVER).
• 📊 Excel What-If Model (excel/pricing_inventory_model.xlsx): Standalone Excel financial model featuring dynamic formulas (=SQRT(2*D*S/H)), sensitivity matrices, and pivot tables.

---

🤝 License & Acknowledgments

Distributed under the MIT License. Built as an enterprise retail analytics showcase.