Transforming 541K Raw Transactions into Business Intelligence
An end-to-end, validated analytical workflow transforming raw UCI Online Retail data into actionable insights. This project strictly follows an evidence-based framework, prioritizing data lineage, revenue reconciliation, and strategic recommendations.
Phase 1: Data Quality & Lineage Reconciliation
Rigorous Analytical Decisions
- ✓ Unknown CustomerID (24.93%): Retained for aggregate sales analysis but strictly excluded from RFM/Pareto to maintain behavioral integrity. Not assumed to be "guest checkouts".
- ✓ Extreme Outlier Isolation: A single transaction of 80,995 units (£168,469.60) was isolated, not deleted, preserving total revenue reconciliation while preventing statistical distortion.
- ✓ Operational Separation: £362,103.47 in service fees (POSTAGE, Manual, Bank Charges) across 2,149 rows were separated from physical merchandise rankings.
V1.1 Revenue Reconciliation Bridge
Phase 2: Exploratory Data Analysis (EDA)
Temporal Patterns: The November 2011 Spike
November 2011 generated £1,503,866.78 (highest observed month). Analysis reveals a dual-driver phenomenon: core product amplification combined with seasonal emergence.
Customer Segmentation (RFM)
4,338 known customers segmented by Recency, Frequency, and Monetary value. Note: 47.8% are classified as At Risk or Lost/Hibernating.
Geographic Revenue Concentration
Strong dependence on the UK market (84.6% of revenue). Smaller markets like EIRE and Netherlands show high revenue per known customer but require cautious interpretation due to small sample sizes.
Seasonal Lift Analysis (Scatter Plot)
Comparing November revenue against the average of other months. Points above the red diagonal line represent products with positive seasonal lift (e.g., RABBIT NIGHT LIGHT).
Known vs. Unknown CustomerID Revenue
Unknown CustomerID represents 16.5% overall, but spikes to ~23% in November, indicating a specific seasonal behavior for unidentified buyers.
Total Revenue Composition (Post-Cleaning)
Visualizing the separation of physical merchandise revenue from operational fees and the isolated extreme outlier.
Phase 3: Deep Dive & Product Analytics
Top 5 Products: November 2011 vs. Other Months
Products showing massive seasonal emergence in November compared to their baseline performance.
| Product Description | Nov Revenue | Other Months Avg |
|---|---|---|
| RABBIT NIGHT LIGHT | £34,478 | £2,069 |
| PAPER CHAIN KIT 50'S | £28,955 | £2,641 |
| POPCORN HOLDER | £14,188 | £1,334 |
| PAPER CHAIN VINTAGE | £12,929 | £1,559 |
| HOT WATER BOTTLE | £11,760 | £1,180 |
Bottom 5 Products (Catalog Review Candidates)
Low-revenue tail products. Treated as candidates for catalog review, not confirmed dead stock (no cost/inventory data).
| Product Description | Total Revenue | Invoices |
|---|---|---|
| MUG , DOTCOMGIFTSHOP.COM | £4.17 | 5 |
| SPOTS ON RED BOOKCOVER TAPE | £8.34 | 2 |
| VINTAGE JINGLE BELLS | £12.50 | 3 |
| SET 4 KNITTED BAUBLES | £15.00 | 4 |
| RED RETROSPOT OVEN GLOVE | £18.50 | 6 |
Management Implications & Recommendations
Targeted Retention Testing
The 1,188 "At Risk" customers represent a historical baseline of ~£689K. Design controlled A/B tests for reactivation campaigns rather than assuming guaranteed recovery.
Dual-Track Inventory Planning
Differentiate between core recurring products and seasonal emergent products (e.g., RABBIT NIGHT LIGHT) when forecasting for Q4 to prevent stockouts.
Geographic Risk Monitoring
With 84.6% of revenue and 90% of Champions in the UK, prioritize data-driven investigation into high-AOV niche markets (EIRE, Netherlands, Australia) for sustainable diversification.