Why this matters
The advanced growth level judged one lever at a time. A board asks two questions no single test answers: where will the active base be in a year, and why did it not move last quarter? The tutorial on building a growth model in a spreadsheet answers the first with cohort layers, and the growth accounting tutorial answers the second with a bridge. Leading the programme means running both on one definition of active: the model must reproduce the bridge before its forecast is trusted, and the bridge says which inputs have moved.
The guitar app and its flat quarter
Askevrinde Guitar is an invented subscription app that teaches guitar through video lessons, sold on a monthly plan. A subscriber is active in a month if they hold a paid subscription at its end. In July the app began paying guitar creators to promote it in their videos, and the same month the win-back emails to lapsed subscribers were switched off for a move to a new CRM. September brought the most new subscribers in the app's history; the board pack calls the quarter flat and asks for next year's forecast.
Askevrinde Guitar: the bridge, the sources and the cohort table
Summer bridge as exported from the subscriptions dashboard; active at end comes from billing. End of June: 11,970.
| Month | Active at start | New | Resurrected | Churned | Retained | Active at end |
|---|---|---|---|---|---|---|
| July | 11,970 | 1,450 | 140 | 1,650 | 10,320 | 11,910 |
| August | 11,910 | 1,680 | 140 | 1,730 | 10,180 | 11,940 |
| September | 11,940 | 1,780 | 140 | 1,860 | 10,080 | 12,000 |
| From billing | Q2 monthly average | July | August | September |
|---|---|---|---|---|
| New, organic (same months last year: 1,200, 1,080, 1,030) | 1,460 | 1,000 | 900 | 860 |
| New, creator sponsorships | 0 | 450 | 720 | 920 |
| Churned in first two paid months (of them sponsored) | 840 | 830 | 910 (290) | 1,030 (510) |
| Churned after three paid months or more | 810 | 820 | 820 | 830 |
| Resurrected | 200 | 140 | 140 | 140 |
- Activation, completing three lessons in the first fortnight: 65% of organic and 25% of sponsored new subscribers.
- Dormant pool, subscribers lapsed within the last year: about 12,400 in June and 12,800 in September; 1.6% returned each month in Q2, 1.1% from July.
- Retention by cohort age from 36 monthly cohorts, split by activation; the full table by month of age is in the sheet. Returners have kept paying at the activated rates from their first month back.
- A weekly lesson-rating score (1 to 5) is reported in the board pack; nobody has checked it against later cancellations.
| Age in months | 1 | 2 | 3 | 6 | 12 | 24 |
|---|---|---|---|---|---|---|
| Activated | 80% | 69% | 61% | 46% | 28% | 10% |
| Not activated | 20% | 10% | 7% | 3% | 2% | 0.5% |
- Plan to next December: organic 1,000 new subscribers a month (1,400 in January), sponsorships 900 a month, win-back off until a restart is agreed.
From cohorts to the active base
The guide's order holds, with three decisions an expert makes explicitly. Askevrinde's cohort table, built as the cohort analysis tutorial describes, follows paying subscribers and is split by activation, so the sheet applies the Activation RateThe share of new users or sign-ups who complete the activation event, the action marking first real value, within a set window. by source once and sends each part down its own curve; applying it again on top of a blended curve would count it twice. Sources differ here only through activation, an assumption the back-test has to confirm. And because returners keep paying after they come back, each month's resurrected subscribers become a layer of their own, drawn as a rate from the dormant pool rather than a fixed count. The bridge uses the same definition of active, and it is checked for balance before any movement in it is read.
Task
- Write the model's question, output, grain and horizon, and lay out the inputs, layers, outputs and checks.
- Build the layers by acquisition month and source, with activation applied once and returners as their own layers.
- Back-test the sheet against the last twelve months and record the gap that remains.
- Run scenarios for the realistic changes, a sensitivity table of one-input steps, and find the level where gains meet losses on the plan.
- Check that the summer bridge balances, find and fix what does not, then split it by source and tenure.
- Say what the bridge shows that the headline missed, give each movement an owner and write the board's forecast.
Worked answer
The question and the sheet. The output is active subscribers at month end, by month, to next December, a horizon inside the ages the cohort table has observed, so no tail is invented. Inputs sit in one block with source, date and owner; layers are formulas only; checks compare the model with billing and with the bridge.
The back-test. One activation share for every source is the trap: the sheet tracks billing until July, then runs high, because sponsored subscribers activate far less often. With activation by source and resurrection as a rate that drops when win-back stopped, the gap wanders around zero, and that remaining gap is the model's BaselineAn estimate of what a metric would have done without a given change, campaign or promotion, against which the effect is measured. error, printed beside every forecast. Leaving returners out shows how much of the base win-back work carries.
Askevrinde Guitar: back-test, scenarios and sensitivity
| Back-test variant | July | August | September |
|---|---|---|---|
| Activation 65% for every source | 0 | +110 | +280 |
| Resurrection at the Q2 rate throughout | +60 | +110 | +160 |
| Returners left out | −1,810 | −1,770 | −1,740 |
| By source, resurrection as a rate with the pause | +20 | −30 | +10 |
| Scenario from January | Active subscribers next December |
|---|---|
| Plan as written | 12,250 |
| Win-back restarted, return rate back to 1.6% | 12,730 |
| Sponsored onboarding lifts their activation to 45% | 13,090 |
| Both | 13,560 |
| Sponsorships stopped | 9,530 |
| One-input step from the plan | Change next December |
|---|---|
| Activation +1 point, both sources | +90 |
| Activated retention at age 1 +1 point, later ages in proportion | +120 |
| Organic new subscribers +100 a month | +550 |
| Return rate from the dormant pool +0.1 point | +100 |
On the plan, gains meet losses at about 12,500 across the year: the base is already within about 500 of the level its inputs can hold. With both fixes that level rises to about 14,900.
Reading the scenarios. The plan leaves the base roughly where it is, so any growth target must name the input that changes. Organic subscribers move the base most per unit but are not on offer at the same cost; the realistic moves are sponsored onboarding and a restarted win-back. Cutting the sponsorships would hurt: they hold the base up, and their weakness is activation, not volume.
The bridge. August fails the first identity while retained plus churned still equals the opening base, so the error sits in the gains, and the billing split by source gives fewer new subscribers than the export. July and September match that split, so the fault belongs to August alone. The data owner traces it to the query rewritten for the August export, which counted every started subscription, including those refunded before the month ended, instead of subscriptions active at month end. Fixed, the quarter balances.
Askevrinde Guitar: the bridge checked and read
August as exported: 11,910 + 1,680 + 140 − 1,730 = 12,000 against 11,940 in billing; organic 900 + sponsored 720 = 1,620. With 1,620 new: 11,910 + 1,620 + 140 − 1,730 = 11,940. Gains to losses: July 0.96, August 1.02, September 1.03. Quarter: 11,970 to 12,000, with 4,850 new and 420 resurrected against 5,240 churned. Churn in the first two paid months rose from 840 a month in Q2 to 1,030 in September; churn after that held between 810 and 830.
What the headline missed. The record month came from sponsorships outgrowing a fall in organic subscribers against last year. Sponsored subscribers rarely activate, so early churnShare of customers at the start of a period who left during it. rose with them, while churn after the second paid month did not move: the product's core is not the problem. Resurrection fell when win-back stopped. Gains matched losses every month, and every CohortA group of customers who share a starting event and period, tracked together over time. behaved as its source predicts.
Owners and the board's forecast. Sponsored onboarding goes to product and lifecycle, read on retention at age two; the win-back restart to lifecycle, judged against a holdout of lapsed subscribers; the organic decline to the acquisition lead; the definition of new to the data owner. The board receives the scenarios with the back-test band and the ceiling beside any target. The weekly lesson-rating score stays out of the model until the check on whether survey scores predict churn has been run on the app's own data.
Common mistakes
- Applying activation twice. A curve that already contains the early drop needs no second cut.
- One activation share for every source. A shift in mix then reads as a fall in retention.
- Resurrection as a fixed count, or left out. The pool grows with the base, and returners keep paying.
- Forecasting from the record month's growth rate. The layers show losses catching up; a trend line cannot.
- Reading a bridge that does not balance. Fix the definition first; every trend after it depends on it.
- Netting the quarter. A flat total hid rising acquisition, rising early churn and falling resurrection.
The module checkpoint puts these judgements to a meditation app with annual plans and asks whether a survey score has earned a place as an early warning.
