RETAIL KPI WORKBENCH + CUSTOMER COHORT RETENTION
Independent portfolio studies; analysis and code created for this portfolio in October 2026.
Dataset: Chen, D. (2015). Online Retail. UCI Machine Learning Repository.
DOI: https://doi.org/10.24432/C5BW33. License: CC BY 4.0.
Source download: https://archive.ics.uci.edu/static/public/352/online%2Bretail.zip

REPRODUCTION
1. Install requirements.txt with Python 3.11+.
2. Put online-retail.zip inside data/ next to analyze.py and queries.sql.
3. Run python analyze.py. Results are saved to output/ and SQL tables to data/retail.sqlite.

GRAIN + DEFINITIONS
Input grain: invoice/product line. 541,909 raw rows.
Analysis window: December 2010 through November 2011, twelve complete calendar months.
December 2011 is partial and excluded.
Positive invoice value: sum(quantity * unit_price), positive quantities/prices only, C-prefixed cancellations excluded.
This is NOT net revenue, net profit, or return-adjusted sales. Positive shipping/adjustment lines are retained.
Orders: distinct eligible invoice numbers. AOV: positive invoice value / those orders.
Exact-row duplicates removed as a modelling policy, not a proof of error. Retaining them adds GBP23,571.39 in this window.
Country fields are descriptive locations, not a causal sales driver.

COHORTS
Identified customers only. Cohort = first observed purchase month inside the window, not true acquisition month.
Month-N retention = distinct cohort customers purchasing in month N / initial observed cohort size.
Unobserved future months are blank, not zero. Weighted month-one retention includes cohorts with a full next month.
Repeat-customer percentage = customers with >1 distinct invoice / all identified customers, over the full window.
This differs from next-month retention. Left-censoring affects first-month cohorts and unknown IDs limit coverage.

VALIDATION
Monthly totals reconcile to line totals. Cohort counts independently reconcile between SQLite CTEs and pandas.
All cohort month-zero values equal 100%; observed retention is within 0..100%.
Recommendations are hypotheses for testing, not measured campaign uplift or commercial outcomes.

If downloading SQL from the website, queries.sql.txt is the same SQL source; rename to queries.sql or keep its .txt filename (the Python runner accepts both).