E-Commerce Customer Analytics & Insights Engine

This is a PostgreSQL-oriented SQL analytics project for customer segmentation, retention analysis, and executive e-commerce reporting.

Business Problem

The project addresses four core e-commerce questions:

• Which customers deliver the greatest value and how should they be segmented?
• Do customers return and purchase again after their first order?
• How are sales performance and revenue distributed across product categories?
• What is the distribution of completed, cancelled, and returned orders?

Analytical Approach

The analysis is organised into three complementary views:

1. RFM Customer Segmentation
2. Cohort Retention Analysis
3. Executive KPI Analysis

Customer-value and sales metrics use completed orders where appropriate.

Analyses

1. RFM Customer Segmentation

queries/01_rfm_analysis.sql calculates each customer's recency, frequency, and monetary value from completed orders. Recency measures days since the most recent completed order, frequency counts distinct completed orders, and monetary value sums completed-order amounts. The query uses NTILE(5) to assign RFM scores and applies the existing customer segments: Champions, Loyal Customers, At Risk / Need Attention, Hibernating / Lost, and Other.

The query uses the fixed reference date 2026-08-01 for recency calculations. It does not include or claim any segmentation results.

2. Cohort Retention Analysis

queries/02_cohort_retention.sql assigns each customer to the month of their first completed order and follows their completed-order activity in subsequent months. The output contains the cohort month, month number, and active-customer count.

The query does not calculate a retention percentage.

3. Executive KPI Analysis

queries/03_kpi_dashboard_queries.sql returns four independent result sets:

• Completed orders, revenue, and average order value (AOV).
• Monthly revenue, order volume, and month-over-month revenue growth.
• Product-category units, revenue, and revenue share.
• Order-status counts and percentages.

SQL Techniques Used

• Common table expressions (CTEs)
• NTILE()
• LAG()
• SUM() OVER()
• CASE
• WHERE
• DATE_TRUNC()
• EXTRACT()
• Aggregations
• Joins
• Distinct counts
• Percentage calculations

Business Use Cases

These queries can support customer prioritization, retention monitoring, sales trend reporting, category-performance analysis, and operational order-status analysis.

This repository currently contains no populated source dataset and no saved query outputs. The README therefore does not claim actual business findings or performance results.

Data Model

The schema contains four tables:

• customers stores customer attributes.
• products stores product details and categories.
• orders stores order-level transactions and links each order to a customer.
• order_items stores item-level order details and links orders to products.

Project Structure

.
├── README.md
├── schema/
│   └── 01_create_tables.sql
├── data/
│   └── 02_mock_data_insert.sql
└── queries/
    ├── 01_rfm_analysis.sql
    ├── 02_cohort_retention.sql
    └── 03_kpi_dashboard_queries.sql

How to Run

1. Create a PostgreSQL database.
2. Run schema/01_create_tables.sql to create the database tables.
3. Note that data/02_mock_data_insert.sql is currently empty and therefore does not populate the database.
4. Load compatible data into the four tables before running the analytical queries.
5. Run the SQL scripts under queries/ as needed:
   • 01_rfm_analysis.sql returns customer-level RFM results.
   • 02_cohort_retention.sql returns cohort activity results.
   • 03_kpi_dashboard_queries.sql returns four independent result sets.