E-Commerce ETL & Analytics Dashboard

Full-stack data pipeline project: Python ETL → SQLite → Next.js interactive dashboard.

Stack

Layer	Tech
Data Generation	Python · pandas · numpy
ETL Pipeline	Python · sqlite3 · logging
Database	SQLite
Dashboard	Next.js 15 · TypeScript · Recharts · Tailwind CSS v4
Deployment	Vercel

Project Structure

├── ecommerce-etl/        # Python ETL pipeline
│   ├── src/
│   │   ├── generate_api.py    # Simulate dirty API JSON (1200 customers, 8500 orders)
│   │   ├── etl_pipeline.py    # Extract → Validate → Transform → Load → SQLite
│   │   └── analytics.py       # SQL queries → results.json + matplotlib visuals
│   ├── data/                  # Raw API payload
│   ├── db/                    # SQLite database output
│   ├── logs/                  # ETL run logs
│   ├── model_output/          # results.json consumed by dashboard
│   └── visuals/               # Matplotlib charts
│
└── ecommerce-dashboard/   # Next.js dashboard (deployed on Vercel)
    └── app/
        ├── page.tsx           # Full dashboard — all charts & layout
        ├── layout.tsx
        └── data/results.json  # Exported by analytics.py

How to Run

1. Python ETL Pipeline

cd ecommerce-etl
pip install -r requirements.txt
python src/generate_api.py    # generates data/api_payload.json
python src/etl_pipeline.py    # validates, cleans → db/ecommerce.db
python src/analytics.py       # SQL queries → model_output/results.json

2. Update Dashboard Data

cp ecommerce-etl/model_output/results.json ecommerce-dashboard/app/data/results.json

3. Run Dashboard Locally

cd ecommerce-dashboard
npm install
npm run dev   # http://localhost:3000

ETL Flow

1. generate_api.py — creates 1,200 customers, 80 products, 8,500 orders with intentional dirty data (nulls, type mismatches, duplicates)
2. etl_pipeline.py — validates each entity, coerces types, skips orphans, loads 4 tables into SQLite
3. analytics.py — runs 10 SQL queries, exports results.json for the dashboard

Built by

Matteo Aslam · Data & Business Analyst · Microsoft PL-300
LinkedIn · GitHub