Y

Project · Intermediate · about 12 hours

Customer cohort retention analysis

Build monthly cohort retention tables in SQL from raw invoices and explain what they mean for a wholesaler.

Skills:Business acumen, Data visualisation and storytelling, SQL

Part of: Data Analyst

Data and licences

Brief

The wholesaler's leadership believes "customers do not come back". Test that belief with the order data from December 2010 to December 2011.

What to build

  1. In SQL (DuckDB, PostgreSQL or BigQuery), build one row per customer per active month, find each customer's first month, and compute retention by months since first purchase.

  2. Build a second table for revenue retention: revenue in month n as a share of the cohort's month 0 revenue.

  3. Pivot to a cohort by month matrix and draw a heatmap. Grey out cells that could not exist yet.

  4. Explain the December 2010 cohort's shape: many of those customers were buying before the data started (left censoring).

Check yourself: the December 2010 cohort has 885 customers, and 324 (36.6%) buy again in month 1.

Deliverables

  • cohorts.sql

  • retention_heatmap.png (or a BI page)

  • findings.md: two findings with numbers, one recommendation, caveats

How it is graded

Reviewers rerun the SQL and compare three cells with their own. Handling the edge effects honestly carries real weight: it is what separates a correct table from a misleading one.

Stretch

Split retention by country group (UK versus the rest) and discuss whether the difference is real.

Deliverables

  • SQL that builds customer and revenue retention tables
  • A heatmap of retention by cohort and month
  • A one-page write-up with two findings and one recommendation

How a reviewer grades it

CriterionWeightWhat good looks like
Cohort logic is correct (first month, distinct customers, month offsets)35%
Edge effects handled (left censoring, partial last month, cohorts with little time)20%
Visual is clear and correctly labelled20%
Findings are specific and tied to the business25%