Turning 100,000 raw transactional records into 4 executive-level business decisions — using SQL, Python, and Tableau.
Small and mid-size e-commerce businesses sit on rich transactional data across multiple database tables — but lack the analytical layer to turn it into strategic action. Questions like "which customer segment should we fight to retain?" or "is our logistics hurting satisfaction in specific regions?" go unanswered — not because the data doesn't exist, but because no one has asked the right SQL questions.
This project provides a reusable analytical framework: 6 SQL query patterns that can be adapted to any e-commerce dataset with a similar schema (orders, customers, products, reviews, sellers), plus a Tableau dashboard that converts outputs into business decisions — not just charts.
Dataset: Olist Brazilian E-Commerce — 100,000 real orders, 8 relational tables, 2016–2018.
Each insight is linked to a specific business action — not just a number.
bed_bath_table drives the highest order volume (9,417 orders) but watches_gifts generates 2× more revenue per order despite 40% fewer transactions.
→ Action: Allocate paid acquisition budget toward premium AOV categories. Use high-volume categories for retention and repeat-purchase campaigns, not new customer acquisition.
Revenue grew 8× over 16 months (Jan 2017 → May 2018), with a +54% MoM Black Friday spike in Nov 2017. From March–July 2018, revenue flatlined within a tight band despite continued operations.
→ Action: Organic growth has stalled. The plateau persists across 5 consecutive months — not seasonal. New acquisition channels or category expansion required to break through.
Using a SQL-built RFM model (Recency, Frequency, Monetary scored via NTILE(4)):
- Champions (5,804 customers, ~6%) spend 4× more per lifetime than the Lost segment
- At Risk (22,672 customers) share the same historical spend profile as Champions but have not purchased recently
→ Action: Win-back campaigns targeting At Risk customers have the highest expected ROI — they have proven spend capacity, they just need re-engagement.
Northern states (AM, RR, AP) average 2× the national delivery time (12.4 days). But slow delivery alone does not always produce bad reviews — 6 specific states (RN, RJ, BA, MA, PA, AM) suffer from both slow delivery and below-average satisfaction simultaneously.
→ Action: Logistics investment prioritised by the intersection of slow delivery AND low satisfaction — not slow delivery alone. The 6 problem states are the intervention target, not all remote regions.
📊 View Live Interactive Dashboard on Tableau Public →
| Panel | Chart Type | Business Question Answered |
|---|---|---|
| KPI Row | Big Number (4 cards) | What is the scale of this business? |
| Top Left | Scatter Plot | Which categories are premium vs mass-market? |
| Top Right | Dual-axis Line + Bar | Is revenue trending up, growing, or stalling? |
| Bottom Left | Treemap | Which customer segments deserve marketing investment? |
| Bottom Right | Scatter Plot | Where does logistics hurt customer satisfaction most? |
| Layer | Tool | Purpose |
|---|---|---|
| Database | SQLite (.db file) |
8-table relational schema — portable, no server required |
| SQL | SQLite dialect | CTEs, LAG() window functions, NTILE(4), multi-table JOINs |
| Processing | Python (pandas, sqlite3) | Data loading, EDA, chart generation, CSV export pipeline |
| Visualisation | Tableau Public | Interactive BI dashboard (live link above) |
| Notebook viz | Matplotlib, Seaborn | In-notebook charts per business question |
| Version Control | GitHub | SQL files readable in browser without downloading |
Below is the relational schema for the Olist dataset, showing how the 8 tables connect.
| Skill | Implementation |
|---|---|
| Advanced SQL | CTEs, LAG() window functions, NTILE(4) quartile scoring, 4-table JOINs across 8 tables |
| Data Quality Audit | Pre-analysis null checks, partial month detection, documented exclusions (q0_data_quality.sql) |
| Cohort Analysis | Customer retention by first-purchase month using CTE chain + LEFT JOIN |
| RFM Segmentation | Quartile-based customer classification into 6 actionable marketing segments |
| Business Storytelling | Every chart titled as an insight; every finding linked to a specific decision |
| Python EDA | pandas data loading, matplotlib/seaborn visualisations, SQLite integration |
| Dashboard Design | KPI summary row, dual-axis charts, treemap, scatter with quadrant analysis |
olist-ecommerce-analysis/
│
├── data/
│ ├── olist.db # SQLite database — all 8 tables loaded and indexed
│ ├── dashboard_preview.png # Full dashboard screenshot (used in this README)
│ ├── schema_diagram.png # Entity relationship diagram — 8 tables, FK connections
│ ├── kpi_summary.csv # Single-row KPI aggregates for Tableau header cards
│ ├── q1_category_revenue.csv # Category revenue, order volume, and AOV
│ ├── q2_delivery_satisfaction.csv # State-level delivery time vs review score
│ ├── q3_monthly_revenue.csv # Monthly revenue with MoM% (partial months excluded)
│ ├── q4_cohort_retention.csv # Cohort retention rates by first-purchase month
│ └── q5_rfm_segments.csv # RFM segment distribution and average lifetime value
│
├── sql/
│ ├── q0_data_quality.sql # Pre-analysis null audit + partial month detection
│ ├── q1_category_revenue.sql # Revenue vs volume vs AOV by category (3-table JOIN)
│ ├── q2_delivery_satisfaction.sql # Delivery speed vs satisfaction by state
│ ├── q3_monthly_revenue.sql # Monthly trend using LAG() — partial months excluded
│ ├── q4_cohort_retention.sql # Lifetime cohort retention (CTE chain + LEFT JOIN)
│ ├── q5_rfm_segmentation.sql # NTILE(4) RFM scoring + 6-segment CASE WHEN
│ ├── q6_seller_quality.sql # High-volume, low-satisfaction seller identification
│ └── q7_kpi_summary.sql # Aggregated KPI metrics for dashboard header row
│
└── notebooks/
└── olist_eda.ipynb # Full pipeline: DB connect → SQL → charts → CSV export
Why SQLite over PostgreSQL?
SQLite stores the entire database as a single .db file. Anyone can clone this repo and run every query immediately — no server, no credentials, no configuration. The SQL dialect is standard: all queries (CTEs, window functions) transfer directly to PostgreSQL or BigQuery with zero changes.
Why exclude months 2016-09, 2016-12, and 2018-09 from trend analysis?
q0_data_quality.sql confirms these months have fewer than 5 days of order data. Including them produces -100% MoM artefacts. The exclusion is documented in the SQL comment and the raw data is untouched in olist.db.
Why lifetime retention in cohort analysis instead of a 90-day window? The dataset spans only 25 months. A 90-day window makes early cohorts (2016) appear artificially weak because the data ends before their full window closes. Lifetime retention maximises statistical reliability per cohort. A 90-day variant is documented in the SQL file comments.
Why require score 4 on all three dimensions for Champions?
Initial testing with >= 3 thresholds on all RFM dimensions classified 25% of customers as Champions — economically meaningless. Score 4 on all three (top quartile on recency, frequency, AND monetary simultaneously) yields ~6% Champions with 4× average lifetime spend — consistent with real-world RFM benchmarks.
Why a scatter plot for categories instead of a bar chart? A bar chart answers "which category has the most revenue?" — one dimension. A scatter with AOV on Y and order volume on X answers "which categories are premium vs mass-market — and should strategy differ?" — two dimensions, one decision. Same data, fundamentally different value.
Prerequisites: Python 3.8+, Jupyter Notebook, Tableau Public Desktop (free)
# 1. Clone the repository
git clone https://github.com/gandhiheer7/olist-ecommerce-analysis.git
cd olist-ecommerce-analysis
# 2. Install Python dependencies
pip install -r requirements.txt
# 3. Download the Olist dataset
# Visit: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce
# Download all 9 CSV files into the /data folder
# 4. Run the full pipeline
jupyter notebook notebooks/olist_eda.ipynb
# Kernel → Restart Kernel and Run All Cells
# Expected: 6 charts generated inline, 5 CSVs exported to /data
# Final cell output: "✅ Database connection closed."
# 5. View the Tableau dashboard
# Live version: link at top of this README
# To recreate locally: open Tableau Public Desktop
# → Connect to Text File → load each CSV from /dataExpected outputs after running the notebook:
data/olist.db— SQLite database with all 8 tablesdata/q1_category_revenue.csvthroughdata/kpi_summary.csv— 6 analysis exports- 6 inline Matplotlib/Seaborn charts (one per business question)
| Item | Detail |
|---|---|
| Dataset | Olist Brazilian E-Commerce |
| Size | ~100,000 orders, 8 relational tables, 2016–2018 |
| Licence | CC BY-NC-SA 4.0 |
| Privacy | Fully anonymised — customer and seller IDs are hashed |
Heer Gandhi B.Tech Computer Engineering — Sardar Patel Institute of Technology, Mumbai
The SQL patterns in this project are designed for reusability — adapt the queries to any e-commerce dataset with a similar schema (orders → customers → products → reviews → sellers).