📊 Pricing & Promotion Analytics Pipeline

An end-to-end pricing analytics database and reporting pipeline modeled on CooperVision's product portfolio, U.S. distributor network, contract structures, and manufacturer rebate programs. This repository simulates a production-grade pricing infrastructure to drive gross-to-net optimization, competitor benchmarking, channel concentration risk modeling, and promotion ROI analysis.

Important

All data is synthetic. Product names, wholesale prices, distributor market shares, and promotion parameters are simulated based on publicly available retail and rebate structures. No real CooperVision financial or proprietary data is used.

🔗 View the Interactive Dashboard Live →

---

🎯 Core Capabilities & Features

• Dimensional Modeling (Star Schema): Modeled on real enterprise data structures with 1 central transactional fact table and 5 descriptive dimension tables tracking products, distributors, promotions, contracts, and competitors.
• Transaction Simulator (52K+ Rows): A robust Python generator that models seasonal demand surges (back-to-school, Q4 insurance exhaustion), product-level margins, distributor-tier discounts, and realistic rebate redemption rates.
• Production-Grade SQL Layer: 10 analytics-driven queries modeling core pricing operations: pricing realization trends, Herfindahl-Hirschman Index (HHI) concentration, Q-over-Q price index changes, and promotion-driven ROI.
• Automated Analytical Reporting: Python pipeline (pandas & numpy) that performs ETL on CSV files, computes performance indices, runs automated pricing audits, and exports CSV summaries and consolidated JSON files.
• Interactive executive UI: A lightweight HTML5 dashboard utilizing Chart.js and Tailwind-like custom CSS, visualizing quarterly KPIs, product metrics, promo ROI, and distributor scorecards.

---

🏗 System Architecture & Data Flow

The project is structured as an end-to-end analytics stack, flowing from raw synthetic data generation to automated Python analytics, SQL query verification, and interactive web visualization.

graph TD
    %% Define Styles
    classDef generator fill:#e1f5fe,stroke:#0288d1,stroke-width:2px;
    classDef data fill:#efebe9,stroke:#5d4037,stroke-width:2px;
    classDef db fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px;
    classDef output fill:#fff3e0,stroke:#f57c00,stroke-width:2px;
    classDef ui fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px;

    A["01_data_generation.py<br/>(Synthetic Generator)"]:::generator -->|Star Schema Output| B[("data/ Directory<br/>(CSV Tables)")]:::data
    B -->|Pipeline Input| D["03_python_analysis.py<br/>(Python/Pandas Logic)"]:::generator
    B -->|Schema Input| C["02_sql_analysis.sql<br/>(T-SQL Analytics)"]:::db
    D -->|Generates Aggregates| E["outputs/ Directory<br/>(CSV Summary Tables)"]:::output
    D -->|Generates JSON| F["outputs/dashboard_data.json"]:::output
    F -->|Asynchronous Fetch| G["index.html<br/>(Interactive UI / Chart.js)"]:::ui
    G -->|Hosted On| H["GitHub Pages Live Dashboard"]:::ui

Loading

---

🗃 Relational Data Model (Star Schema)

The underlying data model mirrors enterprise ERP/CRM billing schemas, allowing direct implementation of relational business logic in SQL.

erDiagram
    fact_transactions ||--o{ dim_products : "product_id"
    fact_transactions ||--o{ dim_distributors : "distributor_id"
    fact_transactions ||--o{ dim_promotions : "rebate_promo_id"
    dim_distributors ||--o{ dim_contracts : "distributor_id"
    dim_products ||--o{ dim_competitors : "lens_type & replacement (Market comparison)"

    fact_transactions {
        string transaction_id PK
        datetime transaction_date
        string product_id FK
        string distributor_id FK
        string rebate_promo_id FK
        int quantity_boxes
        int lenses_per_box
        int total_lenses
        float list_price_per_lens
        float discount_pct
        float net_price_per_lens
        float cogs_per_lens
        float gross_revenue
        float net_revenue
        float total_cogs
        float gross_margin
        float margin_pct
        float rebate_amount
    }

    dim_products {
        string product_id PK
        string product_name
        string family
        string lens_type
        string replacement
        float list_price_per_lens
        float cogs_per_lens
    }

    dim_distributors {
        string distributor_id PK
        string distributor_name
        string tier
        string region
        float market_share
    }

    dim_promotions {
        string promo_id PK
        string promo_name
        list eligible_families
        string promo_type
        float reward_amount
        datetime start_date
        datetime end_date
    }

    dim_contracts {
        string contract_id PK
        string distributor_id FK
        string distributor_name
        datetime start_date
        datetime end_date
        string status
        float contracted_discount
        float annual_volume_commitment
    }

    dim_competitors {
        string product_name PK
        string manufacturer
        string lens_type
        string replacement
        float est_wholesale_price
    }

Loading

---

🛠 Technology Stack

• Data Manipulation: Python 3.8+, Pandas, NumPy, JSON, OS
• Database & Analysis: SQL (T-SQL / PostgreSQL compatible), CTEs, Window Functions, Aggregate Joins
• Web Frontend & Visualization: HTML5, CSS3, JavaScript (ES6+), Chart.js (asynchronous JSON data retrieval)
• Hosting & CI/CD: GitHub Pages (automatic deployment of the static dashboard)

---

📈 SQL Analytics Coverage

The script 02_sql_analysis.sql contains production-level queries modeled to solve commercial questions:

#	Analysis	SQL Operations Used	Business Insight Goal
1	Pricing KPI Summary	Aggregation, Conditional sums	Tracks ASP, discount depth, and margin quarterly
2	Product Family Pricing	Group by joins, Competitor benchmark subquery	Identifies price premium/discount vs competitor average
3	Monthly realized NSP Trend	Realized discount calculations, Date functions	Monitors whether price increases are holding or eroding
4	Distributor Scorecard	Window functions (SUM() OVER()), Outer joins	Prepares account managers with volume and margin metrics
5	Promotion ROI Analysis	Multi-table joins, ROI calculations	Determines incremental revenue vs rebate payout ratio
6	Discount Depth Distribution	Categorical Binning (CASE WHEN), Ratios	Analyzes channel discount compliance and leakage
7	Quarter-over-Quarter Growth	CTEs, Self-joins (LAG-like logic)	Measures QoQ product-level ASP and volume velocity
8	Distributor Concentration Risk	Analytical Window aggregates, HHI calculations	Flags market power risk using Herfindahl-Hirschman Index
9	MiSight Pricing Deep-Dive	Grouping, Filtering, Month trends	Detailed view of the high-growth myopia management category
10	Actionable Price Recs	Joins, Logic branching (CASE WHEN)	Flags targets for optimization based on margins/competitor gap

---

💡 Strategic Business Findings

By running the Python and SQL pipeline, several critical pricing insights are surfaced:

1. Price Headroom: clariti 1 day has a 39% price gap compared to competitor averages. A conservative 5% price adjustment is estimated to generate +$110K in incremental annual revenue with negligible volume risk.
2. Margin Leakage: The Avaira Vitality Trial promotion returned a net-negative 0.9x ROI, meaning rebate payouts exceeded incremental revenue. Recommendation: Discontinue or restrict campaign parameters.
3. Channel Concentration: The Herfindahl-Hirschman Index (HHI) for distributors stands at 2,126, signaling moderate concentration. ABB Optical holds 34% of the volume share. Recommendation: Balance the channel by incentivizing Tier 2/3 distributors (Walman, Henry Schein) with growth-focused rebates.
4. Redemption Friction: The MiSight pediatric program has the lowest rebate redemption rate in the portfolio (23%). Recommendation: Simplify the patient portal to reduce friction and improve new patient acquisition.

---

📂 Repository File Structure

pricing-analytics/
├── .gitignore                  # Git untracked file configurations
├── 01_data_generation.py       # Python transaction simulation (outputs CSVs to data/)
├── 02_sql_analysis.sql         # Core SQL analytics queries (CTEs, window functions)
├── 03_python_analysis.py       # Python pipeline (computes CSVs & aggregates dashboard JSON)
├── index.html                  # Interactive Dashboard (hosted via GitHub Pages)
├── README.md                   # Premium documentation
├── data/                       # STAR schema database tables (Generated)
│   ├── fact_transactions.csv   # ~52k synthetic transactional records
│   ├── dim_products.csv        # 15 SKU product master
│   ├── dim_distributors.csv    # 9 authorized distributors
│   ├── dim_promotions.csv      # 7 rebate promotion campaigns
│   ├── dim_contracts.csv       # Distributor contract terms
│   └── dim_competitors.csv     # 12 competitor product benchmarks
└── outputs/                    # Output aggregates (Generated by Python pipeline)
    ├── dashboard_data.json     # Consolidated metrics feeding index.html
    ├── distributor_scorecard.csv
    ├── monthly_trend.csv
    ├── pricing_recommendations.csv
    ├── product_family_analysis.csv
    ├── promotion_roi.csv
    └── quarterly_kpi_summary.csv

---

🚀 Installation & Local Execution

Follow these steps to run the pipeline and run the dashboard locally:

1. Clone the Repository

git clone https://github.com/rahul0443/pricing-analytics.git
cd pricing-analytics

2. Set Up Virtual Environment (Optional but Recommended)

# Create virtual environment
python3 -m venv venv

# Activate virtual environment
# On macOS/Linux:
source venv/bin/activate
# On Windows:
venv\Scripts\activate

3. Install Dependencies

This project uses standard library functions and pandas and numpy.

pip install pandas numpy

4. Run the Pipeline

Before running the dashboard, you must generate the data and compile the analytical outputs.

Note

The original python scripts contain absolute output directories. For local execution, run them directly to generate the CSV tables in your local directories.

# Step A: Generate the transactional star schema dataset
python 01_data_generation.py

# Step B: Run the analytical pipeline to compute metrics & generate dashboard JSON
python 03_python_analysis.py

5. Launch the Dashboard

Open the dashboard file in any standard web browser:

# On macOS:
open index.html

# On Windows/Linux:
# Double click index.html or open it within Chrome/Firefox/Safari

---

🧰 Technical & Business Skillsets Demonstrated

• Commercial Data Infrastructure: Star schema modeling, transaction simulation, and ETL engineering.
• Advanced Analytical SQL: Window aggregation, self-joins, Herfindahl-Hirschman Index (HHI) modeling, price indexes.
• Strategic Pricing: Gross-to-net (GTN) waterfall optimizations, competitor gap indexes, rebate and promo ROI modeling.
• Web Visualization: Asynchronous JavaScript, Chart.js integrations, dynamic DOM rendering, CSS flexbox/grid.

---

👤 Author

Rahul Muddhapuram
MS in Data Science, Analytics & Engineering — Arizona State University

• LinkedIn: linkedin.com/in/rahulmuddhapuram
• GitHub: github.com/rahul0443
• Email: rmuddhap@asu.edu