E-Commerce SQL Business Analysis

An end-to-end, portfolio-ready business intelligence and analytical engineering project demonstrating enterprise-grade SQL querying, relational database modeling, data quality auditing, cohort retention analysis, customer lifetime value (LTV) modeling, revenue concentration (Pareto distribution), independent Python validation, and executive Power BI dashboard design.

---

Table of Contents

1. Business Problem
2. Project Objectives
3. Database Architecture & ERD
4. Dataset Specifications
5. SQL Skills & Technical Highlights
6. Business Questions Answered
7. Key Business Findings
8. Power BI Executive Dashboards
9. Independent Python Validation
10. Actionable Business Recommendations
11. Project Repository Structure
12. How to Run & Reproduce
13. Limitations & Assumptions
14. Skills Demonstrated

---

Business Problem

Modern direct-to-consumer (D2C) and B2B e-commerce platforms generate massive volumes of relational transactional data spanning customer journeys, orders, catalog merchandise, fulfillment lifecycles, and financial settlement channels.

Without structured analytical modeling, management faces critical blind spots:

• Revenue Drivers vs. Product Vulnerabilities: Which products drive genuine enterprise revenue versus low-margin volume, and what is our exposure to product concentration?
• Customer Lifetime Value & Churn: Are top-line gains driven by sustainable, compounding repeat purchasers, or is the business reliant on expensive single-purchase customer acquisition?
• Retention Dynamics & Repurchase Cadence: How quickly do monthly customer acquisition cohorts decay, and what is the optimal inter-order interval for lifecycle marketing interventions?
• Geographic Penetration: Where are the high-AOV market strongholds, and where does acquisition headroom exist?

This project assumes the role of a Senior Data Analyst & SQL Specialist tasked with engineering a relational analytics warehouse, auditing data integrity, conducting advanced SQL business analysis, validating calculations with Python, and delivering actionable strategic insights to executive decision-makers.

---

Objectives

1. Relational Database Design: Design and implement a normalized 3NF relational schema enforcing primary keys, foreign keys, domain constraints, check constraints, and performance indexes.
2. Data Quality & Integrity Auditing: Build a comprehensive SQL audit script verifying zero orphan records, date sequence consistency, line item arithmetic accuracy, and payment reconciliation.
3. Executive KPI Calculation: Formulate verified core business KPIs (Gross Merchandise Value, Net Revenue, AOV, ARPU/LTV, Repeat Customer Rate, Purchase Frequency).
4. Advanced Business SQL Queries: Write performant, maintainable SQL queries using Common Table Expressions (CTEs), multi-level window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM OVER), recursive CTEs, and conditional aggregation.
5. Cohort & Retention Modeling: Construct a 24-month acquisition cohort matrix measuring retention percentage decay and cumulative LTV expansion over time.
6. Pareto Revenue Concentration: Evaluate revenue deciles and test whether catalog sales and customer value adhere to the 80/20 rule.
7. Cross-Technology Validation: Independently cross-validate all SQL aggregations against raw files using Python and Pandas.
8. Executive BI Reporting: Architect an enterprise 3-page Power BI dashboard suite backed by a Star Schema data model and production-grade DAX measures.

---

Database

Relational Schema & Entity Relationship Diagram (ERD)

=========================================================================================================
                                     RELATIONAL DATABASE ERD
=========================================================================================================

 +-----------------------+             +-----------------------+             +-----------------------+
 |        REGIONS        |             |       CUSTOMERS       |             |        ORDERS         |
 +-----------------------+             +-----------------------+             +-----------------------+
 | PK  region_id         | 1         * | PK  customer_id       | 1         * | PK  order_id          |
 |     region_name       +-------------+     first_name        +-------------+     customer_id (FK)  |
 |     country           |             |     last_name         |             |     order_date        |
 |     state_code        |             |     email (UNIQUE)    |             |     shipping_date     |
 |     market_size       |             |     phone             |             |     delivery_date     |
 +-----------------------+             |     sign_up_date      |             |     order_status      |
                                       |     region_id (FK)    |             |     shipping_cost     |
                                       |     customer_segment  |             |     discount_amount   |
                                       +-----------------------+             +-----------+-----------+
                                                                                         | 1
                                                                                         |
                                                                                         | *
 +-----------------------+             +-----------------------+             +-----------v-----------+
 |      CATEGORIES       |             |       PRODUCTS        |             |      ORDER_ITEMS      |
 +-----------------------+             +-----------------------+             +-----------------------+
 | PK  category_id       | 1         * | PK  product_id        | 1         * | PK  order_item_id    |
 |     category_name     +-------------+     product_name      +-------------+     order_id (FK)     |
 |     department        |             |     category_id (FK)  |             |     product_id (FK)   |
 |     description       |             |     cost_price        |             |     quantity          |
 +-----------------------+             |     list_price        |             |     unit_price        |
                                       |     launch_date       |             |     line_discount     |
                                       |     is_active         |             |     line_total        |
                                       +-----------------------+             +-----------------------+
                                                                                         |
                                                                                         | 1 (Parent Order)
                                                                                         v
                                                                             +-----------------------+
                                                                             |       PAYMENTS        |
                                                                             +-----------------------+
                                                                             | PK  payment_id        |
                                                                             |     order_id (FK)     |
                                                                             |     payment_date      |
                                                                             |     payment_method    |
                                                                             |     payment_status    |
                                                                             |     amount            |
                                                                             +-----------------------+
=========================================================================================================

Relational Table Summary

• regions (5 rows): Geographic sales territories (West, Northeast, Southeast, Midwest, Southwest).
• categories (6 rows): Merchandise departments (Laptops & Computers, Audio & Headphones, Smart Home & Office, Fitness & Wearables, Mobile Accessories, Gaming & VR).
• products (50 rows): Product catalog with manufacturing cost, list price, and active flags.
• customers (1,000 rows): Customer profiles across Consumer, Corporate, Small Business, and Home Office segments.
• orders (2,336 rows): Transaction headers recording order lifecycle statuses, dates, shipping, and discounts.
• order_items (4,178 rows): Granular line items linking products, quantities, prices, discounts, and line totals.
• payments (2,336 rows): Financial settlement ledger tracking tender methods and clearing status.

Full data definitions available in the Data Dictionary.

---

Dataset

• Source: Enterprise e-commerce transactional database designed to simulate realistic consumer buying habits, corporate procurement patterns, and fulfillment lifecycles.
• Time Horizon: 24 months of operations (2023-01-01 through 2024-12-31).
• Total Records: 10,911 relational records across 7 normalized tables.
• Data Integrity: Enforced foreign key constraints, zero orphan records, 100% price/quantity validity, and zero date inconsistencies.

---

SQL Skills

This repository emphasizes high-performance, readable, and production-grade SQL analytical engineering:

Technical Category	SQL Features Demonstrated	Repository Implementation
Data Quality Audits	Anti-joins (LEFT JOIN ... WHERE IS NULL), chronological constraint checks, delta reconciliation	sql/02_data_quality.sql
Window Functions	ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(4), NTILE(10), NTILE(100)	sql/05_product_analysis.sql, sql/09_revenue_concentration.sql
Offset Functions	LAG(), LEAD() with explicit partition boundaries	sql/04_revenue_analysis.sql, sql/07_retention_analysis.sql
Cumulative Windows	SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)	sql/04_revenue_analysis.sql, sql/08_cohort_analysis.sql
Rolling Averages	AVG(...) OVER (ORDER BY ... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)	sql/10_advanced_sql.sql
Recursive Queries	WITH RECURSIVE calendar date generation & gapless time-series backfilling	sql/10_advanced_sql.sql
Correlated Subqueries	EXISTS, NOT EXISTS for churn risk identification	sql/10_advanced_sql.sql
Market Basket Analysis	Self-joins (JOIN ... ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id)	sql/10_advanced_sql.sql
Pivoting & Matrixing	Conditional Aggregation (SUM(CASE WHEN ... THEN ... ELSE 0 END))	sql/08_cohort_analysis.sql, sql/10_advanced_sql.sql

---

Business Questions

The SQL queries in this project answer 28 core business questions categorized across 7 organizational domains:

1. Revenue Dynamics: Gross vs. Net Revenue, MoM sales velocity, quarterly cyclicality, YoY growth (+29.38%), and cumulative revenue trajectory.
2. Customer Analytics: Top lifetime value accounts, segment ARPU benchmarking, repeat vs. one-time buyer spend, and 4-tier RFM behavioral segmentation.
3. Product Performance: Top 10 revenue SKUs, volume drivers, 7 declining SKUs in 2024, category gross profit margins, and high-ticket anchor items.
4. Order Operations: Fulfillment status distribution (97.4% completion rate), AOV trajectory, and basket size metrics (2.23 units/order).
5. Retention & Cohorts: 24-month acquisition cohort retention matrix, MoM customer retention rates, and inter-order cadence (53.2-day mean).
6. Regional Performance: Geographic revenue distribution (West leads at 33.13%), regional penetration, and territory AOV comparison.
7. Revenue Concentration: Decile Pareto distribution (Top 10% customers generate 37.75% of revenue; Top 20% generate 58.10%).

Complete question documentation with SQL logic and interpretations is in reports/business-questions.md.

---

Key Findings

All figures below are directly extracted and verified from the database ledger:

========================================================================================
                           EXECUTIVE PERFORMANCE SCORECARD
========================================================================================
  Metric                                      2023           2024          Overall Total
----------------------------------------------------------------------------------------
  Net Completed Revenue                $872,340.71  $1,128,606.16          $2,000,946.87
  Completed Net Orders                         969          1,306                  2,275
  Delivered Units                            2,194          2,869                  5,063
  Average Order Value (AOV)                $900.25        $864.17                $879.54
  Average Revenue Per User (ARPU)        $1,425.39      $1,541.81              $2,029.36
  Repeat Customer Rate (Active)             44.20%         53.80%                 51.42%
  Gross Margin (Estimated)                   39.8%          40.5%                  40.2%
========================================================================================

1. Sustained Top-Line Expansion

• Annual net revenue expanded by +29.38% YoY, accelerating from $872.3k in 2023 to $1.13M in 2024.
• Completed order volume increased by +34.78% (969 orders in 2023 to 1,306 orders in 2024).

2. The Repeat Customer Growth Engine

• 51.42% of customers placed 2 or more orders (507 repeat accounts).
• Repeat customers accounted for $1,582,654.21 (79.09%) of total net revenue, spending an average of $3,123.87 per customer.
• Single-purchase buyers generated $418,292.66 (20.91%) with an average spend of $870.87.
• Inter-order purchase velocity accelerates with loyalty: Order 1 -> Order 2 takes 53.2 days, while Order 2 -> Order 3 narrows to 48.6 days.

3. Product & Category Revenue Concentration

• Laptops & Computers is the primary revenue engine, generating $1,213,903.76 (60.67% of company revenue) across 1,029 units.
• The top 3 flagship products account for 50.12% of total corporate sales:
  1. ProBook Max 16-inch M3: $523,047.57 (26.14% share)
  2. ProBook Ultralight 14-inch: $268,772.89 (13.43% share)
  3. WorkStation Ultra Tower: $211,119.23 (10.55% share)
• Declining Products: 7 SKUs contracted in 2024, led by HealthBand Continuous HR Tracker (-32.38% YoY drop).

4. Regional Performance

• West Region leads all markets with $662,870.39 (33.13% share) and highest AOV ($900.64).
• Northeast is second with $445,382.45 (22.26% share), followed by Southeast ($380.9k, 19.04%), Midwest ($278.0k, 13.90%), and Southwest ($233.8k, 11.68%).

Read the full analysis in reports/business-insights.md.

---

Power BI Dashboard

The project includes an enterprise 3-page Power BI dashboard suite designed with a clean, high data-to-ink ratio executive theme.

Page 1: Executive Overview

High-level operational summary featuring 5 core KPI cards, 24-month revenue trend with 3-month rolling moving average, category bar breakdown, regional market share donut chart, and top 10 revenue SKUs.

---

Page 2: Customer Analytics & Retention

In-depth customer lifetime dynamics featuring new vs. returning revenue area charts, order frequency distribution tiers, 24-month cohort retention heatmap matrix, and RFM behavioral segment revenue bars.

---

Page 3: Product Performance & Merchandising

Catalog intelligence displaying the BCG unit velocity vs. price scatter matrix (bubble size = revenue), category gross profit & margin % bars, YoY declining SKU variance bars, and top market basket cross-sell affinity pairs.

Specifications, dimensional data model, and DAX libraries:

• Dashboard Specifications
• Data Model Architecture
• DAX Measures Library

---

Python Validation

To uphold rigorous analytical standards, all primary SQL query calculations were independently cross-validated against raw CSV files using Python and Pandas in notebooks/sql_validation.ipynb.

# Sample validation test from Jupyter Notebook
assert abs(sql_net_revenue - pandas_net_revenue) < 0.01, "Revenue mismatch!"
assert sql_completed_orders == pandas_completed_orders, "Order count mismatch!"
assert sql_active_customers == pandas_active_customers, "Customer count mismatch!"
assert sql_units_sold == pandas_units_sold, "Units sold mismatch!"

Validation Audit Results

Validation Check	SQL Result	Pandas Result	Delta	Audit Status
Total Net Revenue	$2,000,946.87	$2,000,946.87	$0.00	MATCH (100.0%)
Completed Orders	2,275	2,275	0	MATCH (100.0%)
Active Purchasing Customers	986	986	0	MATCH (100.0%)
Total Units Delivered	5,063	5,063	0	MATCH (100.0%)
Average Order Value (AOV)	$879.54	$879.54	$0.00	MATCH (100.0%)
Average Revenue Per User (ARPU)	$2,029.36	$2,029.36	$0.00	MATCH (100.0%)
24 Monthly Time-Series Buckets	24 Months	24 Months	0	MATCH (100.0%)
Top 10 Product Rankings & Revenue	Top 10 SKUs	Top 10 SKUs	$0.00	MATCH (100.0%)
Repeat Customer Revenue	$1,582,654.21	$1,582,654.21	$0.00	MATCH (100.0%)

---

Business Recommendations

1. Capitalize on Cross-Sell Affinities with Curated Hardware Bundles

• Finding: Market basket analysis revealed strong joint buying patterns between ProBook Max 16-inch + Thunderbolt 4 Pro Dock, and ErgoDesk Standing Desk + MeshPro Executive Chair.
• Action: Implement "One-Click Executive Workspace" and "Power Creator" bundles at checkout with a 5% discount incentive to increase average basket size beyond 2.23 units.

2. Deploy Automated 40-Day Repurchase Email Workflows

• Finding: The average inter-purchase interval between Order 1 and Order 2 is 53.2 days.
• Action: Launch automated dynamic product recommendation email sequences at Day 40 post-first-purchase, showcasing complementary accessories (chargers, cases, audio gear) to convert single buyers before they reach the 90-day lapsing threshold.

3. Mitigate Flagship SKU Revenue Concentration

• Finding: 3 laptops drive 50.12% of total corporate revenue. Supply chain disruption or component shortages on these SKUs would severely impact cash flow.
• Action: Expand marketing spend on high-margin Smart Home & Office (46.8% gross margin) and Audio & Headphones (44.2% gross margin) lines to balance category mix.

4. Create Dedicated B2B / Corporate Account Concierge

• Finding: Corporate and Small Business customers generate an ARPU of $2,450+ compared to $1,850 for retail consumers.
• Action: Build a streamlined B2B portal offering tiered volume discounts, net-30 terms, and dedicated account managers.

5. Phase Out and Discount Contracting SKUs

• Finding: 7 SKUs contracted in 2024, led by HealthBand Continuous HR Tracker (-32.4% YoY).
• Action: Implement clearance markdowns on Gen-1 wearable and mobile accessories, reallocating inventory capital to fast-moving GPS smartwatches and GaN charging hubs.

---

Project Structure

sql-business-analysis/
├── data/
│   ├── raw/                           # Original CSV relational extracts
│   │   ├── categories.csv
│   │   ├── customers.csv
│   │   ├── order_items.csv
│   │   ├── orders.csv
│   │   ├── payments.csv
│   │   ├── products.csv
│   │   └── regions.csv
│   └── processed/                     # Cleaned, production-ready CSV tables
│       ├── categories.csv
│       ├── customers.csv
│       ├── order_items.csv
│       ├── orders.csv
│       ├── payments.csv
│       ├── products.csv
│       └── regions.csv
│
├── database/
│   ├── schema.sql                     # Complete PostgreSQL / ANSI DDL schema
│   ├── data_dictionary.md             # Comprehensive table & column definitions
│   ├── ecommerce.db                   # SQLite database instance
│   └── ecommerce.duckdb               # DuckDB analytical warehouse instance
│
├── sql/
│   ├── 00_create_database.sql         # Production DDL setup script
│   ├── 01_insert_or_load_data.sql     # Bulk COPY and batched INSERT scripts
│   ├── 02_data_quality.sql            # 10 exhaustive data quality audit queries
│   ├── 03_kpis.sql                    # Core executive business KPI calculations
│   ├── 04_revenue_analysis.sql        # 10 revenue & time-series analysis queries
│   ├── 05_product_analysis.sql        # 8 product velocity & margin queries
│   ├── 06_customer_analysis.sql       # 8 customer LTV & behavior queries
│   ├── 07_retention_analysis.sql      # 6 customer retention & inter-order queries
│   ├── 08_cohort_analysis.sql         # 24-month acquisition cohort matrix
│   ├── 09_revenue_concentration.sql   # Customer decile & Pareto 80/20 analysis
│   └── 10_advanced_sql.sql            # Multi-CTE RFM, Recursive calendar, Rolling MA
│
├── notebooks/
│   └── sql_validation.ipynb           # Executed Jupyter validation notebook
│
├── powerbi/
│   ├── dashboard-specification.md     # 3-page UI/UX layout specification
│   ├── data-model.md                  # Star schema architecture & relationships
│   └── measures.md                    # Complete production DAX measure library
│
├── reports/
│   ├── business-questions.md          # Catalog of 28 business questions answered
│   └── business-insights.md           # Deep executive analytical report
│
├── images/
│   ├── executive-overview.png         # Executive Overview dashboard mockup
│   ├── customer-analysis.png          # Customer Analytics dashboard mockup
│   └── product-analysis.png           # Product Performance dashboard mockup
│
├── README.md                          # Project documentation and summary
├── requirements.txt                   # Python environment dependencies
└── .gitignore                         # Git exclusion rules

---

How to Run

1. Database Setup (PostgreSQL / MySQL / DuckDB / SQLite)

Using PostgreSQL:

# Connect to your PostgreSQL instance
psql -U postgres

# Create database and execute DDL
CREATE DATABASE ecommerce_analytics;
\c ecommerce_analytics;
\i sql/00_create_database.sql;

# Load data via COPY commands (or run sql/01_insert_or_load_data.sql)
\copy regions FROM 'data/processed/regions.csv' WITH (FORMAT csv, HEADER true);
\copy categories FROM 'data/processed/categories.csv' WITH (FORMAT csv, HEADER true);
\copy products FROM 'data/processed/products.csv' WITH (FORMAT csv, HEADER true);
\copy customers FROM 'data/processed/customers.csv' WITH (FORMAT csv, HEADER true);
\copy orders FROM 'data/processed/orders.csv' WITH (FORMAT csv, HEADER true);
\copy order_items FROM 'data/processed/order_items.csv' WITH (FORMAT csv, HEADER true);
\copy payments FROM 'data/processed/payments.csv' WITH (FORMAT csv, HEADER true);

Using DuckDB (CLI / Python):

duckdb database/ecommerce.duckdb < sql/00_create_database.sql
duckdb database/ecommerce.duckdb < sql/01_insert_or_load_data.sql

2. Running SQL Analytical Scripts

Execute any analysis module directly in your SQL editor (DBeaver, pgAdmin, DataGrip, DuckDB CLI):

# Run Data Quality Audit
duckdb database/ecommerce.duckdb < sql/02_data_quality.sql

# Run Executive KPIs
duckdb database/ecommerce.duckdb < sql/03_kpis.sql

# Run Cohort Retention Matrix
duckdb database/ecommerce.duckdb < sql/08_cohort_analysis.sql

3. Running the Python Validation Notebook

# Create virtual environment & install requirements
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

# Launch Jupyter Notebook
jupyter notebook notebooks/sql_validation.ipynb

4. Connecting Power BI

1. Open Power BI Desktop.
2. Select Get Data -> PostgreSQL Database (or Text/CSV pointing to data/processed/).
3. Import the 7 relational tables.
4. Set up relationships according to powerbi/data-model.md.
5. Copy DAX measures from powerbi/measures.md.

---

Limitations

1. Non-Subscription Cadence: The dataset represents durable goods e-commerce rather than a SaaS subscription model; cohort retention reflects natural intermittent re-purchasing rather than recurring monthly billing.
2. Promotional Cost Modeling: Marketing acquisition expenses (CAC / ad spend) are not included in the transactional ledger; gross margin calculations reflect product COGS and discounts.
3. Geographic Coverage: Geographic distribution reflects North American sales territories across 5 regions.

---

Skills Demonstrated

• SQL & Relational Databases: PostgreSQL, MySQL, DuckDB, SQLite, ANSI SQL, 3NF Relational Schema Design, Foreign Keys, Check Constraints, Performance Indexing.
• Analytical SQL Techniques: Multi-level CTEs, Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM OVER, AVG OVER), Recursive CTEs, Self-Joins, Conditional Aggregation, Matrix Pivoting.
• Data Quality Engineering: Referential integrity validation, chronological anomaly detection, price/quantity constraint checks, financial reconciliation.
• Business Intelligence & Analytics: Customer Lifetime Value (LTV), Cohort Retention Analysis, RFM Segmentation, Basket Affinity Analysis, Pareto Concentration (80/20 Rule).
• Data Validation & Scripting: Python, Pandas DataFrames, Jupyter Notebooks, Automated assert Testing, Parity Auditing.
• Business Intelligence Design: Power BI Star Schema Modeling, DAX Time Intelligence, Visual Hierarchy, Executive UI/UX Design.
• Executive Communication: Strategic Insight Synthesis, Data Storytelling, Markdown Technical Documentation.

---

Author: Senior Data Analyst & SQL Specialist
Portfolio Project: Available for GitHub, Technical SQL Interviews, and BI Analytics Reviews.