The assignment: explain where the recurring revenue came from
A fictional software team celebrates rising recurring revenue. Customer success asks a different question: are the customers who were already paying staying and expanding? Your assignment is to reconcile January and February snapshots, compute a fixed opening-cohort view and write a recommendation that does not confuse acquisition with retention.
The inputs contain 35 subscription rows for seventeen account identities. A customer can hold multiple subscriptions. One annual price needs normalization, one account replaces its subscription identifier, one row is a retry, and the file includes trials, a non-USD customer, new customers and a reactivated account. Use the case’s explicit inclusion policy rather than infer missing facts from a familiar software platform.
Stripe’s billing documentation distinguishes recurring-revenue movements, customer-level subscribers and cohort retention, and notes that metric settings can change definitions. This exercise uses its own narrower policy: active USD subscriptions only, monthly-normalized amounts and an endpoint cohort of accounts positive in January. It is not a reproduction of every Stripe dashboard default.
Data and worked inputs
Sources[1]
Normalize billing intervals, then aggregate by customer
Remove only identical rows at the same month-and-subscription key. A contradictory repeated key should stop the calculation. Filter to the two specified months, USD and active status. Trials and the EUR account are outside the exercise’s included recurring-revenue measure; keep a count of those exclusions instead of converting them to guessed dollars.
Divide annual recurring amounts by twelve. A04’s $9,600 annual subscription contributes $800 of monthly recurring revenue. Do not interpret the normalized number as cash collected in February or as accounting revenue earned that month. Those require billing, service and collection information that this dataset does not contain.
Now sum subscriptions at account-and-month level. A02 has two subscriptions but is one customer. A06’s subscription identifier changes while its total remains $2,500; it has not churned and been reacquired under the case’s account-level definition. Without the account mapping, the appropriate response would be an unresolved identity question, not a confident migration assumption.
Classify movements inside the opening cohort
The twelve accounts with positive January MRR form the opening cohort. Their starting amount is $16,000. Two finish February at zero, taking $2,000 of opening MRR with them. Two surviving accounts contract by a combined $700. Four expand by a combined $1,300. The remaining accounts are unchanged.
| Movement | MRR |
|---|---|
| Opening cohort | $16,000 |
| Churn | −$2,000 |
| Contraction | −$700 |
| Expansion | +$1,300 |
| Ending opening cohort | $14,600 |
| New accounts outside cohort | +$2,000 |
| Reactivation outside cohort | +$600 |
| Ending total business | $17,200 |
The cohort’s ending amount is $16,000 − $2,000 − $700 + $1,300 = $14,600. Gross revenue retention, excluding expansion, is 83.125%. Net revenue retention is 91.25%. Ten of twelve opening customers remain positive, giving account retention of 83.33%. These are endpoint measures for this case; a cancellation followed by reactivation inside the month would require event-level analysis that the snapshots cannot provide.
Notice how the denominator stays fixed. The two new customers are not added to the opening cohort simply because they appear in February. A15 is also excluded: it was not positive in January and the history table identifies earlier positive USD activity. That makes its $600 a reactivation in the full-business bridge, not a retained January customer.
Write both the growth story and the retention problem
Total MRR grows by $1,200, or 7.5%. The opening cohort shrinks by $1,400, or 8.75%. Neither calculation is wrong. New and reactivated accounts contribute $2,600 and more than offset the cohort decline. A slide containing only total growth would conceal the deterioration in the existing base.
A useful next investigation is to review the two churned accounts and the contracting accounts separately. Their outcomes may reflect different problems. The table does not say why they changed, whether customer-success activity helped, or whether the product is failing. You can prioritize discovery without inventing the causal explanation.
Avoid replacing the cohort analysis with a single lifetime-value estimate. This short, constructed sample cannot support a stable long-run churn model. It can support an exact bridge under explicit definitions. That is already a useful analytical deliverable, especially when the original narrative relied on total growth alone.
Use a memo that separates arithmetic from diagnosis
Your answer should make it easy for a customer-success manager and a finance reader to use the same numbers without assuming they measure the same thing. State what is in scope and where the evidence ends.
Original fictional customer-success memo
Build a workbook or script with three inspectable layers
Keep one layer for raw rows, one for normalized account-month MRR and one for the movement bridge. In a spreadsheet, use a mapping table for interval divisors and status inclusion. In code, reject an unfamiliar interval instead of assuming that every amount is monthly. Check that the sum of account movements plus new and reactivated amounts equals the total change.
For a harder extension, add a second active subscription to an existing customer. Customer count should not rise, but that account’s MRR should. Change A06’s replacement subscription to a genuinely different account only when the account table supplies that mapping; explain how this changes your interpretation. A well-designed extension tests the identity policy rather than changing a number randomly.
Download the starter answer file before inspecting the reference output. A useful final package contains the normalized account table, the bridge, your memo and a short definitions note. Do not hide all intermediate logic in one formula or cell. A reviewer should be able to trace the $14,600 cohort result back to the twelve accounts.
Data and worked inputs
Use the optional SQL track to demonstrate the query itself
The optional SQL version first produces monthly_account, then account_bridge. That sequence matters: the final classification compares total included recurring revenue for an account, not each subscription independently. Inspect the account whose old subscription disappears and is replaced. A query that counts canceled subscription IDs as churned customers will misstate this case.
Run python run_sql.py to load the local CSVs into an in-memory SQLite database and execute analysis.sql. To write your own version, start with sql-starter.sql and supply its path to the same runner. The expected output is an exceptions view and a one-row report. The reference is separate from the Python calculation; you can compare the account-level intermediate results as well as the totals.
Change an annual subscription into an equivalent monthly amount and confirm the bridge is unchanged. Then add $100 to one continuing account and explain where it appears. New and reactivated revenue must remain outside the opening cohort’s retention numerator. Do not solve a join problem by applying DISTINCT to the final revenue total: two real accounts can have identical amounts.
Data and worked inputs
Sources[2]
Defend the denominator in an interview
A strong explanation begins with the distinction between the business’s total growth and the experience of its starting customer base. Then walk through one record that required judgment: annual billing, a multi-subscription customer or the migrated subscription. Those examples demonstrate more than memorizing a retention formula.
An accurate portfolio bullet is: “Built an account-level MRR bridge from synthetic subscription snapshots; separated 7.5% total growth from 91.25% opening-cohort revenue retention.” It is not “increased retention” or “grew recurring revenue.” The result belongs to the exercise; the skill belongs to you after you reproduce and understand it.
To extend the project, propose the fields needed to investigate a churn explanation: product usage, support context, contract dates or a documented customer reason. Explain why access and timing would matter. Do not fabricate those fields and then report the resulting correlation as an observed business finding.