learn-customer-retention-with-phoebe / Analyst session 8 of 8
Learn Customer Retention with Phoebe · Analyst track · Session 8 of 8

Capstone: the retention report

Seven sessions of parts become one machine. Today you build the deliverable sessions a1-a7 have been rehearsing: a stakeholder retention report on Online Retail II that names WHICH cohort changed, WHAT it changed against, and HOW MUCH money that is worth - with frozen history enforced in code, immature cells marked, one chart upstairs and the triangle in the appendix. Then you run it through a strict ship-or-not scorecard. No partial credit: a report either passes all eight rows or it does not leave the building.

🔴 Analyst track Data analysts · Python Capstone · ship it 60 min
0-3 · Welcome 3-15 · The spec 15-50 · Build the report 50-60 · Present & score
Part 0

Where this session sits

Everything converges here. a2's cohort triangle is the report's data spine, a3's gallery decides which chart goes upstairs, a4's segments explain who moved, a5's survival curves and a6's P(alive) price the lifetimes, and a7's risk scores name who to act on. Today they become ONE deliverable: the stakeholder one-pager plus its analyst appendix, built end to end on the real Online Retail II dataset in the companion notebook notebooks/a8_capstone.ipynb. This is the deliverable a1 defined - and the one you ship at work next week.

Live - presented in session Self-study - read after class ★ Build-along - run the code yourself Sources covered
★ What you walk out with today A finished retention report on Online Retail II: the three-questions one-pager, the frozen-history snapshot contract written as an assert that fails the build if completed cells restate, the one-chart rule applied (trend line upstairs, triangle in the appendix), and an 8-row ship-or-not scorecard you can run against any retention readout for the rest of your career.
Part 1 · covers the three questions + the one-chart rule

The spec: what a retention report must answer 10 min live

A retention report is not a chart dump. It is a one-page answer to three questions, backed by an appendix, bound by one contract. Write the spec before touching pandas - the spec is what stops the notebook from becoming forty cells of exploration with no verdict.

The one-pager wireframe: verdict, one chart, three questions, money, appendix Headline verdict - one sentence THE one chart: D30-by-cohort trend WHICH cohort · WHAT changed · vs WHICH comparison cohort $ money line appendix p.2 → The verdict leads. Nobody hunts for it. "Feb-May cohorts retain 4 pts better at month 3." One chart upstairs - NEVER the triangle. D30-by-cohort trend or the layer-cake (a3's rules). The raw triangle is analyst food: appendix only. The three questions, answered in writing. Which cohort. What changed vs the comparison. Denominator definition written down, not implied. Money = delta retention x cohort size x ARPU. No money line, no meeting. This is the whole point. Completed months on this page must match last quarter's report exactly - only the current partial month updates, marked.
🔍 Click to zoom - the one-pager: verdict on top, one chart, three questions, a money line, triangle exiled to the appendix
LiveThe three questions, the frozen contract, the one-chart rule8 min▶

A retention report answers exactly three questions, in this order:

  • WHICH cohort? Named precisely: "customers whose first purchase was Feb-May 2011", not "recent customers". The denominator definition (all signups vs activated; purchase-based for ecommerce) is written on the page.
  • WHAT changed, vs what? A number is meaningless without its comparison cohort: "month-3 retention 31% vs 27% for the Dec-Jan cohorts". Cohort ages compared, never calendar periods.
  • HOW MUCH money? Delta retention x cohort size x ARPU. Four points of month-3 retention on 4,000 customers at $30 monthly gross profit is a real number executives can rank against other projects.

And two standing rules bind the whole document:

  • The frozen-history contract: completed months are immutable - this quarter's report must show the SAME numbers for them as last quarter's report, provably. Only the current partial month updates, and it is visibly marked partial. A report whose history restates cannot be trusted, compared, or targeted against. In Part 2 this contract becomes an assert.
  • The one-chart rule: executives get ONE chart - the D30-by-cohort trend line ("are we improving?") or the layer-cake ("is revenue durable?"). The raw triangle, the KM curves, the SHAP beeswarm: appendix. Upstairs answers a question; the appendix proves you did the work.

Here is the upstairs chart itself: month-1 retention by join cohort, one line, with the two launches that moved it flagged on the axis.

The one chart upstairs: month-1 retention by join cohort 40% 45% 50% 55% 60% onboarding revamp loyalty launch 57% So what: each join cohort holds better at month 1 than the last. The two launches bend the line; this trend is the exec story. Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 2010 2011 M1 retention by join month, consistent with the repo dataset's improving trend - values ILLUSTRATIVE.
🔍 Click to zoom - one line, two flags, an annotated so-what: the whole exec page in a chart
Part 2 · covers the full pipeline on Online Retail II

Build it end to end ★ 25 min · everyone builds

Six steps from raw invoices to a shippable report. Steps 1-2 are a2's recipe verbatim - if any step feels new, that session is the reference. The new muscle today is steps 5 and 6: translating a finding into money, and writing the frozen-history contract as code that fails loudly.

The capstone pipeline: six steps, one report 1 · Clean drop C-invoices, no CustomerID, negative qty 2 · Table a2's recipe + masked heatmap, verbatim 3 · Find read columns down, check the diagonals 4 · Segment a4's RFM or behavior split, wholesalers out 5 · Money delta x size x ARPU, from a6's profit 6 · Snapshot dated CSV + assert history unchanged Steps 1-4 are sessions a2 and a4 replayed on real data. Steps 5-6 are today's new muscle: the money translation that earns the meeting, and the snapshot-assert that makes frozen history a build failure instead of a promise.
🔍 Click to zoom - clean, table, find, segment, money, snapshot: the whole course as one pipeline

Clean-up gate. Drop C-prefix invoices (cancellations with negative Quantity), rows missing CustomerID (~135k guest checkouts - despite the metadata claiming none missing), and any remaining negative quantities. Annotate the left-censored first cohort: December 2009 sweeps in pre-existing customers as if new.

Cohort table + masked heatmap. a2's recipe verbatim: stamp, age, count, divide - then the four heatmap choices: annot, mask the future, anchor vmin/vmax, single-hue safe cmap.

Pick the finding. Read down the columns for cohort-over-cohort improvement or deterioration; check the diagonals for calendar shocks (a December spike is seasonality, not genius). One finding. The report is a verdict, not a tour.

Segment the finding. a4's split: is the improvement everyone, or one RFM tier, one channel, one behavior? Wholesalers out first - a handful of weekly trade buyers will dominate any average.

Translate to money. Delta retention x cohort size x ARPU, with ARPU from a6's cumulative cohort gross profit, not a hopeful LTV formula. This line is why the meeting happens.

Snapshot. Write the frozen numbers to a dated CSV and assert that every completed cell matches the prior quarter's snapshot. History restating is now a crash, not a surprise.

Live★ Build-along: the three code moments (steps 2, 5, 6)12 min▶
★ Step 2 · a2's recipe verbatim, on the cleaned Online Retail II frameretail["order_month"] = retail["InvoiceDate"].dt.to_period("M") retail["cohort"] = (retail.groupby("Customer ID")["InvoiceDate"] .transform("min").dt.to_period("M")) retail["period"] = (retail["order_month"] - retail["cohort"]).apply(lambda d: d.n) counts = retail.pivot_table(index="cohort", columns="period", values="Customer ID", aggfunc="nunique") retention = counts.divide(counts.iloc[:, 0], axis=0) sns.heatmap(retention, annot=True, fmt=".0%", mask=retention.isnull(), vmin=0, vmax=0.5, cmap="Oranges") # the four choices, always
★ Step 5 · the money line: delta x size x ARPUimproved = retention.loc["2011-02":"2011-05", 3].mean() # month-3, new cohorts comparison = retention.loc["2010-12":"2011-01", 3].mean() # month-3, old cohorts delta = improved - comparison size = counts.loc["2011-02":"2011-05", 0].sum() # customers in improved cohorts arpu = 30.0 # monthly gross profit per retained customer, from a6's frame impact = delta * size * arpu * 6 # months left in the horizon print(f"{delta:+.1%} at month 3 x {size:,} customers -> ~${impact:,.0f}")
★ Step 6 · snapshot the frozen numbers for next quarter's proofretention.round(4).to_csv("reports/retention_snapshot_2011Q4.csv")

The snapshot is half the contract. The other half - the assert that reads it back next quarter - gets its own card below, because it is the single most transferable pattern on this page.

LiveThe snapshot-assert: the frozen-history contract as code6 min▶

Frozen history is the law (a2); today it becomes machinery. Before this quarter's report renders, re-read the PRIOR quarter's snapshot and assert that every cell whose window had already completed then is bit-identical now. If the assert fires, something upstream changed - a silent filter edit, a timezone shift, a redefined denominator - and the report must not ship until you know why.

★ The re-read assert - history restating becomes a build failureprior = pd.read_csv("reports/retention_snapshot_2011Q3.csv", index_col=0) prior.columns = prior.columns.astype(int) completed = [c for c in prior.columns if c < prior.shape[1] - 1] # elapsed windows rows = prior.index.intersection(retention.index.astype(str)) new = retention.copy(); new.index = new.index.astype(str) assert new.loc[rows, completed].round(4).equals( prior.loc[rows, completed].round(4)), \ "HISTORY RESTATED - completed cells changed since Q3. Do not ship."

Notice what this buys you politically, not just technically: when a stakeholder asks "why is January's number different from last quarter's deck?", your answer is "it cannot be - the build fails if it is". That sentence is worth more than any model behind the report.

The frozen-snapshot contract: completed cells never move, only the partial month may Q3 snapshot (frozen, on disk) m0 m1 m2 m3 2011-02 2011-03 2011-04 2011-05 100% 42% 34% 31% 100% 44% 35% - 100% 43% - - 100% - - - re-read + assert equal Q4 report (this quarter) m0 m1 m2 m3 m4 2011-02 2011-03 2011-04 2011-05 100%✓ 42%✓ 34%✓ 31%✓ 29% 100%✓ 44%✓ 35%✓ 32% 30% 100%✓ 43%✓ 36% 33% 30% 100%✓ 45% 34% 32% 31%* * window still open - the only cell allowed to move frozen - must equal the Q3 file exactly newly completed this quarter current month - marked partial If any frozen cell differs from the Q3 snapshot on disk, the assert fires and the report does not ship.
🔍 Click to zoom - this quarter's grid against last quarter's snapshot: frozen cells stamped, one partial cell may move
Part 3 · covers the ship gate + presenting the verdict

The ship-or-not scorecard 10 min live

A report that fails one row fails. There is no seven-out-of-eight: a missing denominator definition or a restated January quietly poisons every other number on the page. Run the scorecard before every send - on your own report today, and on every retention readout anyone hands you from now on.

LiveEight rows, pass or do-not-ship6 min▶
  1. Which-cohort named - the cohort under discussion is identified by anchor date and definition, not "recent users".
  2. Comparison cohort named - the delta has an explicit baseline cohort, compared at the same age.
  3. Money line present - delta x size x ARPU appears on the page, in currency.
  4. Denominator definition written - all signups vs activated; purchase-based for ecommerce; stated, not assumed.
  5. History matches prior snapshot - the assert ran and passed; completed cells identical to last quarter's report.
  6. Immature cells masked and marked - the current partial month is visibly partial; NaN never rendered as low retention.
  7. Benchmark carries year + source - "GameAnalytics 2025 median D7 ~3.4-3.9%", never a naked "industry says".
  8. One chart upstairs, triangle in appendix - the exec page has exactly one chart; the triangle and diagnostics live behind it.

Frame it exactly this way with your team: ship or do-not-ship. Partial credit is how restated dashboards and unmarked partial months get normalized.

The scorecard: eight stamps or it does not ship 1 · Which cohort - named, with anchor date + definition ✓ 2 · Comparison cohort - named, same age compared ✓ 3 · Money line - delta x size x ARPU, in currency ✓ 4 · Denominator definition - written on the page ✓ 5 · History matches prior snapshot - assert passed ✓ 6 · Immature cells masked AND marked partial ✓ 7 · Benchmark carries its year + source ✓ 8 · One chart upstairs, triangle in the appendix ✓ SHIP 8 of 8, or the stamp does not come out. One failed row fails the report - a missing denominator or a restated January poisons every other number.
🔍 Click to zoom - eight rows, eight stamps: ship or do-not-ship, no partial credit
Self-studyPresenting it: the verdict sentence and the two hard questions5 min read▶

Lead with the verdict sentence. Not the methodology, not the caveats: "February-May cohorts retain 4 points better at month 3; worth roughly $X by year end if it holds." Everything else on the page exists to survive the questions that sentence provokes.

  • Anticipate the mix-shift question (s4's trap). "Did retention improve, or did we just acquire stickier customers?" Blended retention can rise while every segment falls if acquisition shifts toward a stickier channel. Your step-4 segment split is the answer - have the by-segment view in the appendix before anyone asks.
  • Keep the answer to WHY honest. Cohort data shows WHEN and WHERE retention changed - it cannot prove why. The behavioral splits and a7's SHAP drivers are hypotheses, and hypotheses get tested with experiments: that handoff belongs to learn-experimentation-with-phoebe, and saying so out loud is what separates a report from a story.
Real world

This deliverable is the Duolingo discipline in miniature. Jorge Mazal's team (in Lenny's Newsletter) ranked candidate metrics by sensitivity analysis, found CURR - current-user retention rate - had roughly 5x the DAU impact of the next metric, made it the north star, and drove decisions from cohort movements against it: CURR +21% and ~4.5x DAU over about four years. One metric chosen deliberately, one chart watched, decisions traced to cohort movements - exactly the shape of the one-pager you just built, at a few thousand times the headcount.

Homework

Ship it for real - this month ◐ the real capstone

Sources covered

What this session draws on

The capstone invents nothing new - it enforces every discipline from a1-a7 on real data, under a ship gate:

✓a2's cohort recipe + heatmap craft, replayed verbatim on Online Retail IIPart 2 · steps 1-2
✓a3's exec-chart rules - one chart upstairs (trend or layer-cake), triangle in the appendixPart 1 · the one-chart rule
✓s4's stakeholder discipline - which cohort / what changed / money impact, mix-shift trapParts 1 and 3 · the three questions
✓The frozen-history law as a snapshot contract in code (dated CSV + re-read assert)Part 2 · step 6
◐Duolingo CURR case (Mazal, Lenny's Newsletter) - the deliverable pattern at scaleReal world callout · full case in s3
Check yourself

Three questions before you go 🎯 ◐ 90 seconds

1 · Which chart goes on the executive page, and where does the cohort triangle live?

The triangle is analyst food: it answers twenty questions at once, which is exactly why it answers none for an executive. Upstairs gets the single chart matched to the question at hand ("are we improving?" = trend line; "is revenue durable?" = layer-cake), with the so-what annotated on the chart itself. The triangle proves the work - in the appendix.

2 · What does the snapshot-assert in step 6 actually protect against?

Completed windows are immutable by the frozen-history law, so if a completed cell differs from the prior snapshot, something upstream changed - a filter, a timezone, a denominator - and every trend in the report is suspect. The assert turns that silent poison into a loud build failure, and turns "why did January change?" into "it cannot - the build fails if it does".

3 · Your report shows Feb-May cohorts retaining 4 points better after the new onboarding launched. What can the cohort data claim?

Cohort tables are observational: they localize the change precisely in time and segment, which is enormously useful - but the acquisition mix, seasonality, or pricing may have moved too. Behavioral splits and a7's SHAP drivers generate the WHY hypotheses; testing them is experiment design, handed off to learn-experimentation-with-phoebe.

The whole course on one card · pin this forever

The lawForward-looking cohort retention with frozen history: fix the base, look forward, completed windows never restate, current window marked partial.
The recipecohort = groupby(customer).transform("min") -> period = order_month - cohort -> pivot_table nunique -> divide by column 0.
Four heatmap choicesannot every cell · mask the future · anchor vmin/vmax · single-hue colorblind-safe cmap. Break one and the chart lies.
Three scansAcross a row = one cohort aging. Down a column = product improving? Diagonal = calendar shock (vertical stripe = lifecycle event).
Three curve shapesFlattens to an asymptote = durable base. Smile = resurrection, best case. Slopes to zero = leaky bucket, no PMF.
Three questionsWHICH cohort (named + denominator) · WHAT changed vs the comparison cohort · HOW MUCH money = delta x size x ARPU.
The scorecard8 rows: cohort named, comparison named, money line, denominator written, history matches snapshot, immature marked, benchmark dated, one chart upstairs. Ship or do-not-ship.
The handoffCohort data shows when and where, never why. Drivers and splits are hypotheses - experiments answer them: learn-experimentation-with-phoebe.