Explored, cleaned, and analyzed a public e-commerce dataset in SQL (DuckDB), built an RFM customer segmentation (Recency, Frequency, Monetary), and produced a dashboard of the findings.
UCI Online Retail (public, no auth required): 541,909 transactions from a UK-based online retailer (2010–2011).
uv run python data/download_data.py # downloads xlsx -> parquet snapshot| Step | File | What it does |
|---|---|---|
| 1 | sql/01_clean.sql |
Loads raw parquet; drops credit notes, returns, zero prices, missing customer IDs, non-product lines |
| 2 | sql/02_rfm.sql |
Computes recency (days since last purchase vs reference date), frequency (distinct invoices), monetary (total spend); scores 1–5 (R: thresholds, F/M: NTILE); maps to 10 business segments |
Orchestrated by src/rfm.py (DuckDB) → renders reports/rfm_dashboard.png.
- Champions: 1,058 customers (24%) drive 64% of revenue (£5.67M) — the retention priority.
- 1,139 customers (26%) are dormant-but-valuable (avg recency 264–279 days): 532 Can't Lose Them, 329 Lost, 278 About to Sleep — together £671K of revenue at churn risk, the clearest reactivation opportunity.
- At Risk (311 customers, £355K) are still transacting but slipping (avg 129 days) — a win-back window exists.
- Need Attention (512 customers, £535K) mix moderate recency with lower frequency — candidates for engagement programs.
uv run pytest tests/ -q # synthetic end-to-end: scores, segments, recency semanticsThe tests caught and fixed a real scoring bug: NTILE(... ORDER BY ... DESC) assigns bucket 1 to
the highest value — the F/M scores must order ASC so 5 = best.
- Power BI version: same SQL model, connected to the parquet/xlsx, dashboard layout in Power BI
Desktop (
.pbix+ screenshots). - Extend with monthly cohort retention and monetary-value forecasting.
DuckDB (SQL), Python (pandas, pyarrow), matplotlib, pytest · CI: GitHub Actions