cohort-retention-analysis

1. Overview

I built this project because retention is the single most underrated lever in performance marketing: payback period and customer LTV are mathematically driven by the shape of the retention curve, not the headline conversion rate. A campaign that acquires customers cheaply but loses them by Month 3 is destroying budget; a campaign that costs more but compounds spend over a year is the one I want to scale. This analysis decomposes a real two-year transaction history into 25 monthly cohorts so I can see exactly which acquisition months produced compounding customers — and which ones did not.

2. Dataset

I used the Online Retail II dataset from the UCI Machine Learning Repository — real transaction-level data from a UK-based online retailer (Dec 2009 to Dec 2011) with a strong wholesale gift-ware skew.

• Source: UCI ML Repository
• Alternative download: Kaggle mirror
• Date range: 2009-12-01 to 2011-12-09 (25 calendar months)
• Raw rows: 1,067,371 · Cleaned rows: 779,425 · Cleaned customers: 5,878
• Total lifetime revenue (cleaned): £17.4M

Exclusions

I dropped the following before any cohort math, and the README is upfront about each one:

• Rows with null Customer ID (243,007 rows) — cannot be assigned to a cohort.
• Rows with Quantity ≤ 0 (18,744 rows) — returns and cancellations, so revenue is reported gross.
• Rows with Price ≤ 0 (71 rows) — manual adjustments and non-revenue lines.
• Exact duplicate rows (26,124 rows).

3. Methodology

Cleaning rules

• Loaded with utf-8 encoding; InvoiceDate parsed as datetime.
• Kept only the analysis columns: Invoice, StockCode, Description, Quantity, InvoiceDate, Price, Customer ID, Country.
• Dropped rows per the four exclusions listed above.
• Added Revenue = Quantity * Price.

Cohort definitions

• cohort_month = the calendar month (YYYY-MM) of a customer's first purchase in the cleaned dataset. Each customer belongs to exactly one cohort.
• order_month = the calendar month of each individual transaction.
• cohort_index = integer months elapsed between order_month and cohort_month. Index 0 is the acquisition month.

User retention formula

For each (cohort_month, cohort_index) cell:

• active_users(c, k) = count of distinct Customer ID from cohort c that transacted in cohort_index k.
• cohort_size(c) = active_users(c, 0).
• retention_pct(c, k) = active_users(c, k) / cohort_size(c) * 100.

By construction, retention_pct(c, 0) = 100 for every cohort.

Revenue retention formula

For each (cohort_month, cohort_index) cell:

• revenue(c, k) = sum of Revenue for cohort c at cohort_index k.
• revenue_retention_pct(c, k) = revenue(c, k) / revenue(c, 0) * 100.

Values can exceed 100% — surviving customers spending more in later months than the cohort spent at acquisition.

Tools

Python, Pandas, NumPy, Matplotlib.

4. Key Findings

• The Dec 2009 founder cohort alone contains 955 customers and generated £8.5M in lifetime revenue — roughly £8,917 per customer, which is ~6× the next-highest cohort.
• That single cohort carries 49% of total lifetime revenue across 25 months. Acquisition quality compounds.
• Average Month-12 user retention is 18.2%, while average Month-12 revenue retention is 25.4% — surviving customers are compounding spenders, not just repeat buyers.
• The Dec 2009 cohort hits a seasonal Month-11 peak: 49.5% user retention and 83.6% revenue retention — a clear pre-Christmas reorder spike I would plan campaigns around.
• One outlier cell (cohort 2011-05, Month 7) reads 329.7% revenue retention: a single large wholesale reorder inside a 111-customer cohort. I disclose it transparently rather than smoothing it away.

5. Strategic Recommendations

• Protect the Dec 2009 founder cohort. Dedicated account management; priority pre-Christmas outreach in September and October to lock in the Month-11 reorder spike.
• Re-segment acquisition toward wholesale-intent channels. Dec 2009-style buyers are ~6× more valuable per head than the average — the right move is to shift spend toward trade shows, B2B LinkedIn, and wholesale directories rather than chase volume.
• Reactivate the Month 4–6 dropoff. User retention bottoms in this window before the seasonal Month-11 spike — a structured email and call sequence here has the highest ROI of any retention play.
• Adopt revenue-based success metrics. I would add revenue retention to every dashboard alongside user retention, because this business compounds per-user spend and a user-only view understates the real picture.
• Investigate the 2011-05 premium cohort. £2,579 revenue per user from only 111 acquisitions points to a high-value channel I would want to identify and scale.

6. Files

• README.md — this file.
• cohort_retention_report.pdf — the polished two-page report I would hand to a hiring manager or stakeholder.
• p6_methodology.txt — full plain-text methodology and limitations note.
• data/cohort_retention_matrix.csv — 25 × 25 user retention matrix, rows = cohort_month, cols = cohort_index, values in %.
• data/revenue_retention_matrix.csv — same shape, values are revenue retention vs cohort_index 0.
• data/cohort_summary.csv — per-cohort size, total revenue, AOV, revenue per user, max observed index.
• charts/cohort_retention_heatmap.png — user retention heatmap (1200 dpi-ready PNG).
• charts/revenue_retention_heatmap.png — revenue retention heatmap.
• charts/cohort_quality.png — two-panel cohort quality chart (size + revenue per user).
• script/p6_cohort_analysis.py — reproducible analysis script that produces the three CSVs and the methodology file.
• script/p6_charts.py — chart generation script that produces the three PNGs.

7. Tools

Python, Pandas, NumPy, Matplotlib, GitHub.

8. How to Reproduce

1. Download the raw dataset (online_retail_II.csv) from UCI or the Kaggle mirror and place it in the project root.
2. Clone this repository:

   git clone https://github.com/<your-username>/cohort-retention-analysis.git
   cd cohort-retention-analysis
   

3. Install dependencies:

   pip install pandas numpy matplotlib
   

4. Run the analysis script to produce the cleaned dataset, the cohort matrices, the cohort summary, and the methodology file:

   python script/p6_cohort_analysis.py
   

5. Run the chart script to produce the three PNG visualisations:

   python script/p6_charts.py
   

9. Contact

• Email: ashwin.kumar.glbimr18@gmail.com
• LinkedIn: linkedin.com/in/ashwin-kumar-180816174