Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Growth Marketing › Growth Economics

Building a Growth Model in a Spreadsheet

How to build a spreadsheet growth model: new customers by month, activation, retention by cohort age, resurrection, the active base, scenarios and sensitivity.
Tutorialadvanced13 min readUpdated

What a spreadsheet growth model is for

The growth fundamentals hub describes the growth model document: the model sentence, the loop drawing, the north star and its input tree, the economics summary and the assumptions the team knows to be shaky. That document says how growth works. The spreadsheet says how much, and when. It takes the same inputs and carries them forward month by month, so that a target can be checked against what the inputs can deliver before anyone commits to it.

Three questions justify the effort. First, what happens to the active base if the plan is followed exactly? Second, which input matters most: more acquisition, better activation, better retention at a given age, or more resurrection? Third, where does the base stop growing on current inputs? The third is the one a team rarely asks and most needs. Because customers already acquired keep leaving at the rates the cohort curve shows, the number lost each month grows with the base. When it reaches the number gained each month, the base stops rising, whatever the trend line drawn through last year suggested. The academy exercise on building a cohort growth model for a guitar lesson app practises answering all three for a board that wants next year's subscriber forecast after a flat quarter of record sign-ups.

The model is not a replacement for the observed tables. The cohort analysis tutorial builds the curves it uses, the retention and payback guide explains how to read their shapes, and the growth accounting tutorial gives the period-by-period movements it must reproduce. The sheet takes new customers as an input; turning a spend plan into new customers belongs to the response curves in the guide to marginal returns and forecasting, which flatten as spend rises. Keep that conversion there and paste its output into the model.

Before you start

  • A cohort table with at least a year of history, built to the cohort tutorial's definitions of acquired and active, with retention by age in months. The older cohorts tell you whether the curve flattens; the newer ones tell you whether it has shifted.
  • A growth accounting bridge for the same period and the same definition of active, so that resurrection can be measured rather than assumed and the back-test has monthly actuals to compare with.
  • An activation definition and its measured share, read by sign-up cohort, consistent with the activation rate the team already reports.
  • The acquisition plan in customers, not spend, by month and ideally by source, from the marketing plan or from a spend forecast that has already applied diminishing returns.
  • The north star definition and its input tree. The north star and OKRs framework covers choosing it; the KPI tree framework and the guide to KPI trees cover the arithmetic between its levels.
  • A spreadsheet and an hour with whoever owns the data, because every input will need a definition checked before it is trusted.

The steps

  1. Decide the question and the output the model must give

    Write down the decision the model is for before opening the sheet. "Set next year's north star target", "choose between an acquisition push and an onboarding project" and "tell finance when the base will reach a level that justifies a second support hire" each need a different output and a different horizon. Name the output measure (active customers at month end, the north star, or both), the grain (usually the calendar month, matching the cohort table) and the horizon. A horizon longer than the history behind the retention curve is a projection past the data, and the LTV and CAC guide explains why such projections should be labelled as projections.

  2. Lay out the sheet in inputs, cohort layers, outputs and checks

    Use four blocks and keep them apart. Inputs hold every assumption in one place, each cell labelled with its source and the date it was read: new customers by month, activation share, retention by age, resurrection by month and the north star conversion. Cohort layers hold one row per acquisition month and one column per calendar month, each cell computed from the inputs and never typed in. Outputs sum the layers into the active base and the north star. Checks compare the model with actuals and with the bridge. Colour input cells differently from formula cells, so that nobody overwrites a formula with a hopeful number.

  3. Enter new customers by month and source

    Fill the acquisition row with history for the back-test period and the plan for the forecast period. Split it by source where retention differs by source, because a plan that shifts the mix towards a channel with weaker retention will produce a smaller base than the same total from the current mix. The retention and payback guide shows how to read retention by segment and channel. If the plan is in spend, convert it to customers outside the sheet first, using the marginal returns guide, and record which spend scenario each acquisition row represents.

  4. Apply activation to each month's new customers

    Multiply each month's new customers by the activation share for that month and source to give the activated customers who enter the retention curve. Where the cohort table is built on paying customers rather than sign-ups, activation may already be inside the curve's early drop; decide which and apply it once, never twice. Keeping activation as its own input is worth the extra row: it is the lever onboarding work moves, and the sensitivity table later needs to vary it separately from retention.

  5. Apply retention by cohort age from your own cohort table

    For each cohort row, multiply the activated customers by the retention share at each age, read from the business's own cohort table, so that a cohort in its third month uses the third-month share and a cohort in its tenth month the tenth-month share. Use recent cohorts for the early ages and older cohorts for the later ages that recent ones have not reached, and note where the two are spliced. Beyond the oldest age observed, either hold the last observed share flat, if the curve has clearly flattened, or stop the projection; never invent a tail. The complement of retention is the churn rate at each age, and seeing both in the sheet makes the early drop hard to ignore.

  6. Add resurrected customers from your growth accounting

    Resurrected customers are not in any cohort layer, because the layers count customers who stay, so add them as their own output row. Read the monthly resurrected count from the growth accounting bridge, or, better, the rate at which the dormant pool returns, since the pool grows as the base grows and a fixed count will understate resurrection in a larger business. If the business is seasonal, resurrection follows the season, and the input should carry that shape rather than an average. Where the bridge shows resurrection is small, say so in the assumptions and keep the row anyway, so that a win-back plan has somewhere to land.

  7. Sum the cohort layers into the active base and the north star

    Add every cohort layer's surviving customers in each calendar month, plus that month's resurrected customers, to give the modelled active base. Then convert the base into the north star using the relationship the input tree describes, such as the share of active customers who complete the core action in the month, read from the business's own data. Add two derived rows: customers gained (new activated plus resurrected) and customers lost (the fall in every layer between months). Their difference is the month's change in the base, and it must equal the change in the active base row. If it does not, a formula is wrong.

  8. Back-test the sheet against the last year

    Run the sheet over the last year with actual new customers in the acquisition row and compare the modelled active base with the actual base month by month, and the modelled gains and losses with the growth accounting bridge. A model that cannot reproduce the year it was built from will not forecast the next one. Read the gap for direction as well as size: a gap that grows steadily in one direction usually means a retention curve taken from cohorts that behave differently from recent ones, a missing resurrection row, or an activation share applied twice. The missed forecast diagnosis splits a gap into volume, rate and timing, and the same split works here. Small monthly differences are read as the guide to reading small numbers advises, not chased. Record the remaining gap; it is the model's Baseline error, and it belongs beside every figure the sheet produces.

  9. Build scenarios and a sensitivity table

    Scenarios change several inputs together to describe a plausible future; the scenario planning framework covers choosing the uncertainties that matter and agreeing in advance the signals that show which future is arriving. Keep the scenarios few and name each by its story, such as "plan held", "acquisition budget raised" and "onboarding project lands", with every changed input listed. Then build a sensitivity table that changes one input at a time by a small, equal step (a point of activation, a point of retention at one age, a step in monthly acquisition) and records the effect on the output at the horizon. The scenarios say what might happen; the sensitivity table says which lever is strongest, which is the question the experiment backlog needs answered.

  10. Find the ceiling the inputs imply and write the assumptions down

    Run the plan scenario forward until the base stops growing, and read off the level and the month. The ceiling is pure arithmetic: when the customers lost each month equal the customers gained, the active base stops rising, and with acquisition held at the plan's level, losses grow with the base until they meet it. Show the ceiling next to any target. A target above it can only be met by changing an input, so the target should name the input: the north star framework describes writing targets as key results that say how anyone will know, and Google's re:Work guide to OKRs, as that framework reports it, has key results graded at the end of the period, which makes a model-backed key result easy to check. Finish with an assumptions block listing every input, its source, its date and the person who owns it, and link the sheet from the growth model document. Where the target also has a cash constraint, the payback reading in the retention and payback guide shows how long each new layer takes to repay its acquisition cost, the Payback Period that decides how fast the business can afford to fill the sheet's acquisition row.

Model inputs and where each comes from

InputWhat it controls in the sheetWhere it comes fromHow to vary itCommon error
New customers by month and sourceThe size of each cohort layerThe acquisition plan, converted from spend outside the sheetScenarios for budget and mixPasting spend and dividing by average acquisition cost
Activation shareHow many new customers enter the retention curveSign-up cohorts and the team's activation definitionOne point at a time in the sensitivity tableApplying it twice when the curve already contains it
Retention by cohort ageHow fast each layer decaysThe business's own cohort table, recent cohorts for early agesOne age at a time, especially the early dropUsing old cohorts' curves for a changed product or mix
ResurrectionCustomers added back from the dormant poolThe growth accounting bridgeAs a rate on the dormant pool, with its seasonLeaving it out, so the back-test runs low
North star conversionThe north star from the active baseThe input tree and the business's own usage dataOnly when the product changesAssuming a constant share without checking its trend
Back-test gapThe error band around every outputModelled against actual for the last yearNot varied; reportedNot recording it, so forecasts look precise
CeilingWhere gains and losses meetComputed by running the plan forwardMoves with every other inputReading it as a market size rather than as arithmetic

A model with these rows is small enough to explain in a meeting and specific enough to argue about. Each row has an owner, and the growth economics hub shows where the sheet sits beside the unit economics table. Where the base has already stalled and the question is why, the growth plateau diagnosis is the place to start; the model then shows what each fix would be worth. A business whose growth comes partly from a loop can add the loop's output as a source in the acquisition row, measured as the growth loops guide describes, rather than assuming it compounds. The same layered logic produces the lifetime value figure (LTV) when margin replaces customers in the cells, which is why the two should agree on retention. When the year's plan changes mid-way, the tutorial on reforecasting a marketing budget re-forecasts the spend, and the model's acquisition row is updated from it. The academy checkpoint on growth models, growth accounting and leading indicators tests where each input comes from, which input moves the result most and how a period's change splits into new, retained, resurrected and churned.

A sheet music subscription models its next year (illustrative)

Illustrative example — not a real result

Quorbisk Sheet Music finds its ceiling

Quorbisk Sheet Music, an invented subscription of printable piano scores for amateur pianists, sells a monthly plan. Its north star is subscribers who download at least one score in the month, which has run at 80% of paying subscribers for a year. In December it had 8,120 paying subscribers, a north star of about 6,500, and a plan for new subscribers to rise from 660 a month to 720 by June and hold there.

The growth lead builds the sheet from the business's own data. Activation, defined as downloading a first score in the first week, has been 70% of new subscribers. The cohort table shows activated subscribers retained at 72% in their second month, 63% in their third, 52% in their sixth and 44% in their twelfth, with the curve flattening after the eighth month. The growth accounting bridge shows about 40 resurrected subscribers a month, more in September and January when lessons restart.

Back-tested over the past year with actual new subscribers, the sheet gives 7,980 subscribers in December against the actual 8,120, a gap of 140, and the gap runs low in every spring month. Adding the seasonal shape to resurrection removes most of the drift, and the team records the remaining gap beside every output.

Run forward, the plan scenario flattens. Gains settle at about 760 a month (720 new, 40 resurrected), and losses climb with the base until they reach 760 at about 8,650 subscribers in October. After that the base is flat while acquisition holds at the plan.

ScenarioNew subscribers by DecemberActive subscribers next DecemberNorth star next December
Acquisition held at 6606608,2406,590
Plan: rising to 720 by June7208,6506,920
Budget raised: rising to 840 by December8408,9807,180
Plan, with second-month retention at 76%7209,4107,530

The sensitivity table shows each point of second-month retention moving next December's base by about 190 subscribers, while the raised budget, which needs a larger response curve than the paid channels have shown, adds 330. A four-point gain in second-month retention moves the north star further than any acquisition scenario. The team writes two key results: the north star at 7,500 downloading subscribers by December, and second-month retention of the June cohort at 76%. The onboarding tests in the backlog move to the top.

Common mistakes

  • A single growth rate instead of layers. A rate drawn through last year cannot show losses catching up with gains; cohort layers can.
  • Spend as the acquisition input. Spend turns into customers along a curve that flattens; convert it outside the sheet and paste customers in.
  • Borrowed retention curves. A curve from another business, or from a benchmark, describes someone else's customers. Use the business's own cohort table and say which cohorts fed which ages.
  • Inventing a tail. Projecting retention beyond the oldest observed age turns an assumption into a lifetime figure. Hold the last share flat only where the curve has clearly flattened.
  • Leaving out resurrection. The back-test runs low, the team blames retention, and win-back work has no row to show its value.
  • Skipping the back-test. A model that cannot reproduce last year has no claim on next year.
  • Treating the ceiling as a forecast of the market. It is arithmetic about the current inputs, and it moves as soon as any of them does.
  • Hard-coded hopes. A number typed into a formula cell to make a target work is the most common way a model stops being a model. Keep inputs in one block with sources and dates.

Frequently asked questions

Why build the model from cohort layers instead of a single growth rate?

Because a single growth rate hides the two things that decide where the base goes: how many customers arrive each month and how long each group stays. A cohort-layer sheet keeps every month's new customers as their own row and applies the retention the business has actually observed at each age, so old customers decay at the rate old customers decay and new ones at the rate new ones do. That is what lets the sheet show when losses catch up with gains, which a flat growth rate can never show.

How close should the back-test be before I trust the model?

There is no standard tolerance; the test is whether the gap is small relative to the decisions the model will inform and whether it runs consistently in one direction. A gap that wanders around zero is noise in the inputs. A gap that grows month after month in the same direction means an input is wrong, most often a retention curve taken from older cohorts that behave differently from recent ones, or resurrection left out. Fix the input, rerun the back-test and record the remaining gap next to every forecast the sheet produces.

Should acquisition spend be an input to the growth model?

Not directly. The growth model takes new customers by month as its input, and the step from spend to customers belongs to a response curve, which flattens as spend rises. Keep that curve in a separate forecast, convert each spend scenario into new customers there, and paste the result into the model's acquisition row. Mixing the two in one sheet tends to hide the diminishing returns and makes every extra pound look as productive as the average one.

Terms used on this page

Frameworks

Where this page is used

Part of these learning paths

Also related

Sources

  1. Guide: Set goals with OKRs — Google re:Work

Related