Case Study: SaaS Retention Analysis

Case: The board saw net revenue retention (NRR) dip below 100% twice in the last twelve months and wants to know what happened. You have one table, saas_subscriptions, with one row per account per active month, 2023-06 through 2024-05. A missing account-month means the account was not paying that month.

Your tasks:

1. Define NRR exactly, including the cohort and denominator rules.
2. Compute monthly NRR and identify the worst month.
3. Classify churn correctly: one account has a gap and then comes back.
4. State your denominator rules explicitly: what happens in the first month of the window, and which accounts are in scope each month.

• 5 min: write the NRR definition and denominator rules before any SQL
• 15 min: compute monthly NRR, find the worst month, explain the driver
• 5 min: churn classification edge cases and validation checks

NRR definitions vary across companies, so write one down and defend it. Use the same contract the SaaS practice track grades:

• The cohort for month M is every account with a subscription row in month M-1.
• prior_mrr = the cohort's total MRR in M-1.
• retained_mrr = the same cohort's total MRR in M (0 for cohort accounts with no row in M, which is the churn drag).
• nrr = retained_mrr / prior_mrr, rounded to 4 decimals with a NULLIF guard.

Denominator rules, stated explicitly:

• Month 0 has no denominator. The first window month (2023-06) has no prior month, so it produces no NRR row. The series starts at 2023-07, 11 rows.
• The denominator is prior-month cohort MRR only. Accounts that are new in month M are excluded by construction: they were not in the M-1 cohort, so they touch neither numerator nor denominator.
• Reactivations are excluded the month they return. The account that resumes in 2024-03 has no 2024-02 row, so it is not in the 2024-03 cohort. Crediting its returning MRR to retained_mrr would inflate 2024-03 NRR from 1.0233 to 1.0386: a definitional error, not a rounding one.

Expected output: 11 rows, 2023-07 through 2024-05. Most months sit at exactly 1.0000 (the steady accounts). The five months that move:

• 2023-11: 1.0443. Expansion (Cascade Analytics 500 → 750)
• 2024-01: 0.9688. The worst month: Harbor & Finch churns (-99) and Quill & Parcel pauses (-99) against a 6,347.00 prior-month base
• 2024-02: 1.0320. Expansion (+300) net of contraction (-100)
• 2024-03: 1.0233. Expansion (+150); the reactivation and the new 2,200/mo logo are both outside the cohort
• 2024-04: 0.9833. Bluegrain Foods churns (-149)

So the answer to the board: NRR went sub-100% in 2024-01 (0.9688) and 2024-04 (0.9833). January is the worst month, and half of its drag is a pause, not a true loss, which leads directly to the churn-classification question.

Quill & Parcel (account 7) has no rows in 2024-01 and 2024-02, then resumes in 2024-03. Handle it differently at two levels:

• Monthly MRR churn: in 2024-01 its disappearance correctly counts as churned MRR. At the monthly grain a pause is indistinguishable from churn until the account comes back.
• Logo churn: it is not a churned logo, because its rows resume. A churned logo is an account whose MAX(month_start) is earlier than the latest observed month (2024-05-01). Gap detection flags it wrongly; the max-month test does not.

The query returns exactly two churned logos: Harbor & Finch (last active 2023-12) and Bluegrain Foods (last active 2024-03), the same two accounts behind the sub-100% NRR months.

• NRR definition quality (35%), strong: states cohort = prior-month accounts, names the month-0 rule, and excludes new and reactivated accounts before writing SQL
• SQL correctness and cohort scoping (30%), strong: drives from the M-1 rows with a LEFT JOIN to M, so churned accounts contribute 0 instead of vanishing
• churn and reactivation handling (20%), strong: distinguishes monthly MRR churn from logo churn and keeps the reactivated account off the churned-logo list
• communication (15%), strong: leads with the two sub-100% months and the drivers, not with the query