SaaS KPI Assignments

Assignments mirror the monthly reporting pack a SaaS finance team actually ships.

Metrics focus:

• net revenue retention (NRR) by month
• gross MRR churn rate by month
• MRR by segment over time
• subscriptions-vs-invoices reconciliation

All dates are fixed (window: 2023-06 through 2024-05; latest observed month: 2024-05). Never use CURRENT_DATE.

The board asks one question before any other: "are existing customers worth more this month than last month?" That is NRR.

Exact definition (use this one, since NRR definitions vary across companies and graders only accept this one):

• 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.

Accounts that are new in month M are excluded by construction; so is the reactivated account in 2024-03, because it has no 2024-02 row and therefore is not in that cohort. The first window month (2023-06) has no prior month, so the output starts at 2023-07, giving 11 rows.

Return month_start, prior_mrr, retained_mrr, nrr, ordered by month.

NRR nets churn against expansion; finance also wants churn isolated. Gross MRR churn rate for month M:

• starting_mrr = total MRR of all accounts active in M-1.
• churned_mrr = the M-1 MRR of accounts with no row in M (downgrades do not count; only complete disappearance).
• gross_mrr_churn_rate = churned_mrr / starting_mrr, rounded to 4 decimals with a NULLIF guard.

Same month spine as Assignment 1, 11 rows (2023-07 through 2024-05). Note the 2024-01 number includes account 7's pause: at the monthly grain a pause is indistinguishable from churn until the account comes back.

Return month_start, starting_mrr, churned_mrr, gross_mrr_churn_rate, ordered by month.

Growth wants to know where the revenue mix is shifting: is Enterprise carrying the portfolio, or is SMB quietly compounding? Slice monthly MRR by account segment: a dimension join at full history depth.

Return one row per month per segment with a row in that month: month_start, segment, total_mrr, ordered by month then segment. The sum of total_mrr across your whole result must equal the sum of mrr in the fact. If it does not, your dimension join fanned out.

Finance closes the month by checking that what billing collected matches what the subscription system says it should have collected. Scope rules (read them carefully, they come straight from the data's billing conventions):

• Monthly-billed accounts only: exclude accounts 1 and 5, the two enterprise annual-prepay accounts. They paid 12 months up front, so their monthly invoiced amount is legitimately 0 and would flood the report with noise.
• Window: months from 2024-01 onward — earlier billing lived in a legacy system and was never migrated.
• Paid invoices only: a voided invoice never counts, and an open invoice has not been collected yet.
• Bucket invoices by calendar month of invoice_date (DATE_TRUNC), since some accounts are billed mid-month.

Flag every account-month where ABS(paid_invoiced - subscription_mrr) > 1.00 (a $1 tolerance absorbs rounding). Return account_id, account_name, month_start, subscription_mrr, paid_invoiced, difference (paid minus MRR), ordered by month then account. Done right, exactly two account-months surface — both in May, both still-open invoices.

Finish the track with:

• SaaS Case Study Capstone