E-Commerce Database Analytics & Customer RFM Segmentation | PostgreSQL & Window Functions

Project Overview:

A comprehensive relational database analytics project modeling 4,500+ commercial transactions across normalized relational tables (customers, orders, order_items). Utilizing advanced SQL queries, window functions, and CTEs, this project solves key operational business questions regarding revenue velocity, logistics SLAs, and customer retention.

Business Challenge:

Marketplace management lacked centralized database visibility into customer repurchase cycles, logistics delivery bottlenecks, and customer value distribution.

SQL Architecture & Query Implementations:

1. Month-over-Month (MoM) Growth Analysis: Built modular Common Table Expressions (CTEs) combined with LAG(total_revenue, 1) OVER (ORDER BY order_month) to calculate monthly revenue percentages and growth trends.

2. Advanced RFM Segmentation: Calculated Recency, Frequency, and Monetary scores per customer using NTILE(5) analytical windowing, segmenting the customer base into Champions/VIP, Loyalists, At Risk Spenders, and Lost Customers.

3. Logistics SLA & Review Score Correlation: Analyzed delivery latency thresholds against 1-to-5 star customer ratings.

Key Analytical Findings:

- Revenue Concentration: The top 'Champion / VIP' segment (9.4% of customer count) drove 31.8% of aggregate revenue.

- Delivery Sensitivity: On-time deliveries maintained an average rating of 4.7/5 stars; delays of 3+ days resulted in an immediate plunge to 1.6/5 stars.

Deliverables Included:

- Complete relational SQLite database: ecommerce_analytics.db

- Production SQL script: 02_sql_olist_ecommerce_analytics.sql

- Exported query output datasets: sql_mom_revenue_growth_results.csv & sql_rfm_customer_segments_results.csv