SaaS Case Study Capstone
Case brief:
The board meets next week. The CEO wants two artifacts from you: a month-by-month MRR bridge for the full reporting window (2023-06 through 2024-05) that proves the growth story arithmetically, and an at-risk account watchlist with a recommendation of who to call first.
Everything is as of the 2024-05 data snapshot. Never use CURRENT_DATE. Headline numbers you should be able to defend: the portfolio grew from 2,500 MRR in 2023-06 to 8,748 in 2024-05, two logos churned along the way, and one came back.
Task 1: The Full Monthly MRR Bridge #
Build the bridge for every month in the window, one row per month, comparing each month against the previous observed month at account grain:
starting_mrr: prior-month total (0 for 2023-06, the first window month)new_mrr: MRR of accounts whose first-ever subscription row is this monthreactivated_mrr: MRR of accounts with no prior-month row but at least one row before this month (unlike the drills, the bridge now separates the comeback from new business, and the board cares about the difference)expansion_mrr/contraction_mrr: increases / decreases for accounts present in both months (both as positive numbers)churned_mrr: prior-month MRR of accounts with no row this monthending_mrr: this month's total
The identity starting + new + reactivated + expansion - contraction - churned = ending must hold on all 12 rows; a grader checks it. Order by month.
Task 2: At-Risk Account Watchlist #
The CEO's second ask: "who might we lose next, and who do we call first?" Watchlist criteria, as of the 2024-05 snapshot: an account currently active (it has a 2024-05 subscription row) goes on the list if either:
- it has at least one open invoice (billed but not collected), or
- its current MRR is below its peak MRR in the window: a contraction it never recovered from.
Return account_id, account_name, segment, current_mrr, peak_mrr, open_invoice_amount (0 if none). Order deterministically: largest open exposure first, then largest gap below peak, then account_id. Three accounts should surface.
Findings: Who Do You Call First? #
Before the board meeting, write down, in two or three sentences, which watchlist account you would call first and why. Anchor the answer in numbers from your two result sets: open exposure relative to total MRR, tenure, and what the bridge says about how the account got here.
Model answer: Korvo Industries (account 12). Its open May invoice of 2,200 is by far the largest uncollected exposure (roughly a quarter of the entire 8,748 MRR base), and it is a three-month-old enterprise logo whose 2024-03 landing drove the biggest jump in the bridge; losing it would erase that quarter's growth story. Pinewheel HR's open 99 is routine collections, and Saltbox Studio's contraction (200 vs a 300 peak) is real but already three months stable.
Debrief #
Explain in one or two sentences for each task:
- why the bridge needed account grain even though the output is month grain
- how the new/reactivated split changes the story the board hears about 2024-03
- why "below peak MRR" and "open invoice" are leading indicators while churn is a lagging one
In production SaaS reporting, the bridge identity is your regression test: run it every month-close, and when it breaks, suspect the account-classification logic before the data.