Skip to content

README.md

Latest commit

 

History

History
156 lines (119 loc) · 7.94 KB

README.md

File metadata and controls

156 lines (119 loc) · 7.94 KB
Raw

🛒 E-Commerce Customer & Revenue Analytics (SQL)

📌 Project Overview

An end-to-end SQL analytics project based on the Olist Brazilian E-Commerce Dataset (~100k orders). The goal of this project is to analyze monthly revenue growth, identify top product categories, perform customer segmentation (RFM analysis) using SQL window functions and data quality validation (integrity checks and revenue reconciliation).

📊 Key Business Questions & SQL Insights

1. Monthly Revenue & Order Volume

Aggregates delivered orders per purchase month. Revenue is the sum of payment_value (includes shipping and vouchers); orders are counted with COUNT(DISTINCT order_id), because an order can have several payment rows.

SELECT 
    strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(SUM(p.payment_value), 2) AS total_revenue
FROM orders o
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'
GROUP BY order_month
ORDER BY order_month ASC;

2. Top-Performing Product Categories by Revenue

Ranks categories by the sum of item prices (order_items.price, excl. shipping) for delivered orders. Revenue is taken from order_items instead of payments: joining payments to order_items would count payment amounts once per item and overstate revenue for multi-item orders.

SELECT
	pr.product_category_name,
	COUNT(*) AS items_sold,
	COUNT(DISTINCT oi.order_id) AS num_orders,
	ROUND(SUM(oi.price), 2) AS category_revenue
FROM order_items oi
JOIN products pr ON oi.product_id = pr.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered'
  AND pr.product_category_name IS NOT NULL
GROUP BY pr.product_category_name
ORDER BY category_revenue DESC
LIMIT 5;

3. Customer RFM Segmentation

Segments customers by Recency, Frequency and Monetary value (RFM) to show which customers drive revenue and which are at risk of churning. Uses CTEs and the window function NTILE.

Method

  • Recency: days between a customer's last delivered order and the latest order in the dataset (the dataset ends in 2018, so "today" would label every customer as inactive). Scored 1–4 with NTILE(4), where 4 = most recent.
  • Frequency: number of delivered orders. Because ~97 % of customers ordered only once, quartiles are not meaningful here, so fixed tiers are used (1 order = 1, 2 orders = 2, 3+ orders = 3).
  • Monetary: total payment value per customer. Scored 1–4 with NTILE(4), where 4 = highest spend.
  • Segments are derived from the scores: Repeat customers, Top customers, Recent, low spend, At risk, high spend, Inactive.
-- Core of the scoring step (full query: queries/03_rfm_segmentation.sql)
NTILE(4) OVER (ORDER BY recency_days DESC) AS r_score,
CASE WHEN order_count >= 3 THEN 3
     WHEN order_count = 2  THEN 2
     ELSE 1 END                            AS f_score,
NTILE(4) OVER (ORDER BY total_spent ASC)   AS m_score

📊 Key Results

Scope: delivered orders only (96,477 orders, Oct 2016 – Aug 2018). All amounts in Brazilian reais (BRL). Full result tables are stored as CSV files in outputs/.

1. Monthly revenue & order volume

Month Orders Revenue (BRL)
Jan 2017 750 127,546
Nov 2017 (peak) 7,289 1,153,528
Jan 2018 7,069 1,078,607
Aug 2018 6,351 985,414
  • Monthly revenue grew roughly 8.5x between January 2017 and January 2018, then levelled off at about BRL 1.0–1.15 M per month during 2018.
  • November 2017 is the strongest month in both orders and revenue, which is consistent with Black Friday demand.
  • Oct–Dec 2016 contain very few orders (Dec 2016: a single order), so early months should not be read as a trend.

2. Top 5 product categories by revenue

Revenue = sum of item prices (excl. shipping).

Category Items sold Orders Revenue (BRL)
beleza_saude (Health & Beauty) 9,465 8,647 1,233,132
relogios_presentes (Watches & Gifts) 5,859 5,495 1,166,177
cama_mesa_banho (Bed, Bath & Table) 10,953 9,272 1,023,435
esporte_lazer (Sports & Leisure) 8,431 7,530 954,853
informatica_acessorios (Computer Accessories) 7,644 6,530 888,725
  • relogios_presentes ranks second in revenue with only 5,991 items sold: its revenue per item (~BRL 201) is about twice that of beleza_saude (~BRL 130).
  • cama_mesa_banho sells the most items but ranks third in revenue (~BRL 93 per item).

3. RFM customer segments

Segment Customers Avg. spend (BRL) Total revenue (BRL) Revenue share
Top customers 22,529 261.34 5,887,620 38.2 %
At risk, high spend 21,682 264.30 5,730,484 37.2 %
Inactive 23,745 63.84 1,515,988 9.8 %
Recent, low spend 22,600 63.01 1,424,012 9.2 %
Repeat customers 2,801 308.59 864,357 5.6 %
  • The two high-spend segments (about 47 % of customers) generate ~75 % of revenue.
  • "At risk, high spend" alone accounts for 37.2 % of revenue (21,682 customers who have not ordered recently): the most valuable target for retention campaigns.
  • Only 3 % of customers (2,801) ordered more than once, but they have the highest average spend (BRL 309).

Note: Revenue in queries 1 and 3 is based on payment_value (includes shipping and vouchers), while category revenue in query 2 uses item prices, so these figures are not directly comparable. Because NTILE creates equally sized groups, similar segment sizes reflect the method, not the business.

Data Quality Checks

Before analysing, the dataset was validated with SQL checks (queries/04_data_quality_checks.sql) and revenue was reconciled against item prices (queries/05_reconciliation.sql). Results are stored in outputs/.

Integrity and plausibility checks

Check Result Handling
Duplicate primary keys (orders, customers, products) 0 –
Order items without matching order / product 0 –
Delivered orders without items 0 –
Delivered orders without payment 1 Dropped by the inner join on payments (not counted in revenue)
Products without category 610 Excluded from the category ranking (IS NOT NULL)
Payments with value <= 0 9 Kept; negligible effect on revenue
Items with price <= 0 0 –
Delivered orders without delivery date 8 No impact, delivery dates are not used in this project

Reconciliation (delivered orders)

Total payments (BRL) Item prices + freight (BRL) Difference
15,422,461.77 15,419,630.29 +2,831.48 (+0.02 %)

246 orders deviate by more than BRL 1. Among the affected payment rows, 239 of 251 are credit card payments (avg. deviation +BRL 11.88), which is consistent with interest on installment plans. The remaining cases (debit card, voucher) are too few to interpret.

Findings: The dataset is structurally clean: keys are unique and every order item links to an existing order and product. The remaining issues are small (one delivered order without a payment record, 610 products without a category, 8 delivered orders without a delivery date) and are either excluded or have no impact on the analysis. Total payments match item prices plus freight within 0.02 %, so the revenue figures can be considered reliable.

🚀 How to Run Locally

  1. Download the dataset from Kaggle.
  2. Run python scripts/setup_database.py to populate ecommerce.db.
  3. Open ecommerce.db using DB Browser for SQLite or DBeaver and execute queries from /queries.

🛠️ Tech Stack & Database Setup

  • Database Engine: SQLite (ANSI SQL compatible)
  • Languages: SQL, Python (Data Pipeline)
  • GUI & Tools: DB Browser for SQLite, VS Code