# UK Online Retail: Commercial Analytics **Analysing 1.07 million real transactions from a UK online retailer to find where revenue actually comes from, and which customers are quietly worth saving.** ![Commercial performance dashboard](dashboard/retail_dashboard.png) --- ## Executive summary Two years of transactions from a UK-based online giftware retailer: **£20.05M gross**, **£18.53M net of returns**, 40,077 orders, 5,878 identified customers, 43 countries. The business is far more concentrated than its customer count suggests: 1. **58 customers, the top 1%, generate 31.9% of all revenue.** The top 10% generate 64%. The bottom half of the customer base contributes 6.4%. 2. **RFM segmentation shows "Champions" are 22.1% of customers but 68.4% of revenue**, averaging £9,311 each. 3. **£1.01M of revenue sits in 226 "At Risk - High Value" customers** who have not ordered in an average of 341 days. That is 5.7% of revenue actively decaying, and it is addressable. 4. **The product range is equally top-heavy.** The top 5% of SKUs (235 of 4,707) drive 46% of revenue; the bottom 50% (2,354 SKUs) drive 4.6%. 5. **Guest checkouts average £915 per order versus £475 for identified customers**, nearly double. 13.3% of revenue comes from orders that cannot be attributed to any customer, so the true concentration is likely understated. --- ## Business / operational problem A commercial manager needs to know where revenue comes from, which customers to protect, which products carry the range, and where margin leaks. This project answers: - How concentrated is revenue across customers and products? - Which customer segments are most valuable, and which are decaying? - Where are cancellations concentrated? - How seasonal is the business? - Does repeat purchasing actually increase customer value? - Which markets justify investment beyond the UK? --- ## Dataset | | | |---|---| | **Source** | UCI Machine Learning Repository, [Online Retail II](https://archive.ics.uci.edu/dataset/502/online+retail+ii) (Dr Daqing Chen, London South Bank University) | | **Description** | Real transactions of a UK-based online giftware retailer selling to consumers and wholesalers | | **Period** | 1 Dec 2009 – 9 Dec 2011 (25 months) | | **Raw rows** | 1,067,371 across two workbook sheets | | **After cleaning** | 1,029,609 sales lines + 19,433 return lines | | **Customers / products / countries** | 5,878 / 4,707 / 43 | | **Licence** | CC BY 4.0 | --- ## Tools **SQL (SQLite)** · **Excel** · **Python (pandas)** · **RFM segmentation** --- ## Process ``` Online Retail II (.xlsx) → profile → 10 cleaning rules → star schema (SQLite) ↓ 12 analytical queries · RFM engine · dashboard ``` --- ## Data-quality considerations This dataset is genuinely messy, and most of the analytical value came from handling it correctly rather than from the aggregation itself. | Defect found | Volume | How it was handled | |---|---|---| | Exact duplicate rows | **12,133** | Removed. | | Missing `Customer ID` | **243,007 (22.8%)** | **Kept in revenue, excluded from RFM.** Deleting them would understate revenue by 13.3%; including them in customer analysis would invent customers. This split is the single most consequential decision in the project. | | Cancellations (`Invoice` prefixed `C`) | 19,494 rows | Split into a separate returns fact. Netted off revenue, never summed into gross sales. | | Negative quantities outside cancellations | 3,457 rows | Classified as stock adjustments/write-offs, excluded from sales. | | Non-product service codes | 6,093 rows | `POST`, `DOT`, `M` (Manual), `BANK CHARGES`, `AMAZONFEE`, `ADJUST`, `TEST001`, gift vouchers: flagged as non-merchandise. Left in, product analysis would put "postage" in the top products. | | Zero or negative unit price | 6,207 rows | Excluded from revenue: cannot contribute value. | | Missing descriptions | 4,382 rows | Backfilled from the stock code's modal description. | | One stock code, many descriptions | 1,214 codes | Conformed to the modal description so products don't fragment. | | Extreme quantities (±80,995) | n/a | Retained and verified: a genuine bulk order and its matching cancellation, not a data error. | **A validation check caught a real anomaly the cleaning rules missed:** invoice `C496350` is a cancellation carrying a *positive* £373.57 "Manual" adjustment line. Rather than silently editing it, the rule was scoped to merchandise (where the invariant genuinely holds) and a second check pins the anomaly at exactly one non-product line, so it fires if the data ever changes. --- ## Key findings ### 1. Revenue is extremely concentrated | Customer band | Customers | Revenue | Share | |---|---|---|---| | Top 1% | 58 | £5.63M | **31.9%** | | Top 10% | 587 | £11.31M | **64.0%** | | Top 20% | 1,175 | £13.67M | **77.3%** | | Bottom 50% | 2,939 | £1.13M | 6.4% | ### 2. RFM segmentation | Segment | Customers | % of customers | % of revenue | Avg value | Avg recency | |---|---|---|---|---|---| | Champions | 1,299 | 22.1% | **68.4%** | £9,311 | 20 days | | Loyal | 1,133 | 19.3% | 14.6% | £2,286 | 73 days | | **At Risk - High Value** | **226** | **3.8%** | **5.7%** | **£4,487** | **341 days** | | At Risk | 598 | 10.2% | 3.5% | £1,025 | 380 days | | Hibernating / Lost | 1,459 | 24.8% | 2.7% | £330 | 461 days | | Cannot Lose | 64 | 1.1% | 1.0% | £2,845 | 422 days | The commercially urgent cell is **At Risk - High Value**: 226 customers, £4,487 average lifetime value, nearly a year since their last order. ### 3. Value compounds steeply with repeat purchasing | Orders placed | Customers | Avg lifetime value | % of revenue | |---|---|---|---| | 1 | 1,623 | £348 | 3.2% | | 2 | 944 | £868 | 4.6% | | 3–5 | 1,510 | £1,400 | 12.0% | | 6–10 | 925 | £2,866 | 15.0% | | 11–20 | 579 | £5,979 | 19.6% | | 20+ | 297 | **£27,182** | **45.6%** | 297 customers who ordered 20+ times produce 45.6% of revenue. Customers with two orders are worth roughly **2.5×** those with one, on average: a strong association across the customer base, not proof that any specific win-back action causes that lift for an individual customer. ### 4. Seasonality is severe November 2011 was the peak month at £1.43M, roughly **2.8× a typical spring month**. September–November consistently carries the year. ### 5. The UK dominates, but export order values are higher The UK is **85.7%** of revenue. Among export markets, EIRE (£629k), the Netherlands (£550k) and Germany (£388k) lead. Several export markets show materially higher revenue per customer than the UK. --- ## Recommendations 1. **Put a named account process around ~600 customers.** The top 10% carry 64% of revenue; losing a handful is a material revenue event. This is small enough to manage individually. 2. **Run a win-back campaign on the 226 "At Risk - High Value" customers.** £1.01M of historic revenue, averaging £4,487 each, ~341 days silent: the largest single pool of addressable at-risk revenue this analysis identified. Actual return depends on the offer and response rate, which this dataset can't estimate. 3. **Drive the first repeat order.** Second orders correlate with a ~2.5× lift in lifetime value; 1,623 customers are sitting at exactly one order. 4. **Review the tail of the range.** 2,354 SKUs produce 4.6% of revenue, a rationalisation candidate, subject to checking they aren't basket-builders. 5. **Capture identity at checkout.** 13.3% of revenue is unattributable, and those orders are the *largest* ones (£915 vs £475). This is the biggest blind spot in the data. 6. **Plan cash and stock around Q4.** A 2.8× seasonal swing has obvious working-capital consequences. --- ## Technical implementation ### Star schema ``` dim_customer (5,878) fact_sales (1,029,609) dim_product (4,707) customer_id ◄───────────── customer_id stock_code R / F / M stock_code ──────────────► revenue rfm_score invoice_no revenue_rank segment line_revenue cumulative_revenue_pct recency_days invoice_month monetary country fact_returns (19,433) ``` ### RFM engine Quintile scoring (1–5) on Recency (reversed), Frequency and Monetary, against a snapshot date of **2011-12-10** (one day after the last transaction, so "recency" is meaningful rather than relative to today). Ties are broken with `rank(method="first")` so quintiles are evenly sized. Nine segments derived from R/F/M combinations. ### SQL **12 analytical queries** (450 lines across three files) using CTEs, `ROW_NUMBER`, `LAG`, windowed running totals for Pareto analysis, and correlated subqueries. **17 validation checks, all passing**: including cross-table reconciliation that `dim_customer.monetary` and `dim_product.revenue` both agree with `fact_sales` to within £0.05. ### Excel `excel/Retail_Commercial_Analysis.xlsx` has 8 sheets. Not a data dump: `Monthly_Trend` recalculates 25 months of gross revenue with `SUMIFS` against a 562-row month × country table, and `Segment_Summary` recalculates all 9 RFM segments with `SUMIFS`/`COUNTIFS`/`XLOOKUP` against the 5,878-row customer table. `QA_Reconciliation` compares both sets of Excel formulas against the SQL query output (`R01`, `R03`): 34/34 checks pass. Building this reconciliation caught a real pipeline bug: `monthly_summary.csv` had been aggregated from all cleaned sales lines instead of merchandise-only lines, so it disagreed with the SQL layer by 2 to 10% a month. Fixed at the source in `scripts/02_clean_transform.py`, not patched in Excel. --- ## Files / reproduction ```bash python scripts/01_ingest_profile.py # ingest .xlsx, cache parquet, profile python scripts/02_clean_transform.py # 10 cleaning rules + RFM python scripts/03_build_database.py # build + validate SQLite (17 checks) python scripts/04_run_analysis.py # execute 12 analytical queries python scripts/05_build_dashboard.py # render dashboard python scripts/06_build_excel.py # build Excel workbook + reconciliation ``` | Path | Contents | |---|---| | `data/raw/` | Source `.xlsx` + original `.zip` with SHA-256 | | `data/processed/` | Cleaned facts and dimensions | | `sql/` | `schema.sql`, `analysis.sql`, `validation.sql` | | `reports/query_outputs/` | CSV output of all 12 queries | | `excel/` | Excel workbook with reconciliation | | `dashboard/` | Dashboard PNG | | `PROJECT_EVIDENCE.json` | Every metric in this README, machine-derived | | `INTERVIEW_DEFENCE.md` | 15 interview questions with answers | > `data/processed/*.parquet` and `retail_analytics.db` are build artifacts (~229 MB) and are git-ignored. Run the pipeline to regenerate them.