A subscription sale can look healthy in Stripe or your CRM while your finance report tells a different story. A SaaS revenue waterfall separates recurring run rate, subscription revenue, billing, cash, and recognized amounts, so those differences remain visible.
SQL is a durable financial model for a subscription business model, not a fragile spreadsheet. You can trace every movement to a source record and rerun a prior month after corrections. That makes financial analysis and financial forecasting easier while supporting a clearer evaluation of financial performance.
Key Takeaways
- Define whether the waterfall measures MRR movements or GAAP revenue recognition before building it; these reports answer different questions.
- Use effective-dated subscription history, a calendar table, and a complete customer-month spine to make the MRR model traceable and reliable.
- Classify movements at the customer level and reconcile beginning MRR, movements, and ending MRR to a zero variance each month.
- Keep MRR, bookings, billings, cash, and recognized revenue in separate models, using a finance-approved revenue schedule for ASC 606 reporting.
- Treat backdated changes, plan migrations, pauses, refunds, currency conversion, and usage revenue as explicit data and accounting controls.
Start by choosing the waterfall you need
“SaaS revenue waterfall” can mean two related, but different, reports, so define the revenue waterfall model before using it. Mixing them creates misleading dashboards and tense finance reviews.
MRR waterfall tracks recurring run rate
A Monthly Recurring Revenue (MRR) waterfall explains how the recurring run rate changes between month-end snapshots. An annual contract’s recurring value may be expressed as Annual Recurring Revenue, while MRR normalizes it into a monthly run rate:
| Waterfall line | Meaning |
|---|---|
| Beginning MRR | Recurring run rate at the prior month-end |
| New or reactivated MRR | MRR from customers with no prior-month MRR |
| Expansion MRR | Net increase from an existing customer |
| Contraction MRR | Net decrease while the customer remains active |
| Churned MRR | MRR lost when a customer reaches zero |
| Ending MRR | Recurring run rate at the current month-end |
Expansion MRR and contraction MRR are run-rate movements, not necessarily expansion revenue or contraction revenue under GAAP. Expansion, contraction, and churn movements inform Net Revenue Retention, a downstream view of existing-customer performance.
The reconciliation is simple:
Ending MRR = Beginning MRR + New MRR + Expansion MRR + Contraction MRR + Churned MRR
Contraction and churn are negative values in this format. That makes the equation easy to audit.
A commercial MRR bridge differs from a bookings to revenue bridge, which reconciles signed business with accounting revenue over time.
GAAP revenue follows service delivery
MRR is an operating metric. It usually normalizes recurring contract value into a monthly amount. GAAP revenue follows the performance obligation and service period.
Under ASC 606, cash received before service delivery creates a contract liability, commonly called deferred revenue. The FASB Topic 606 update and IFRS 15 framework use the same core idea.
A customer that prepays an annual subscription may create:
- Stable MRR across the 12-month term.
- A large invoice and cash receipt at the start.
- A declining deferred revenue balance as recognized revenue is reported over the service period.
An MRR waterfall supports financial analysis of commercial momentum. A revenue recognition schedule supports financial reporting by showing what belongs on the income statement.
Build a small, reliable source schema
Start with versioned subscription records, not a monthly billing dashboard export. They preserve the history needed for a reliable revenue waterfall model and audit-ready financial reporting.
Use a calendar table for portable SQL
This example uses ANSI-style SQL that works with minor changes in PostgreSQL, Snowflake, BigQuery, and many warehouses. It relies on a dim_month calendar table instead of a database-specific date generator.
| Table | Required columns | Purpose |
|---|---|---|
dim_month | month_start, month_end | One row per reporting month |
subscription_version | subscription_id, customer_id, effective_from, effective_to, mrr_amount, status, recorded_at | Effective-dated subscription history |
invoice_line | invoice_date, amount, contract_line_id | Billing activity |
revenue_schedule | service_month, recognized_revenue, contract_line_id | Finance-approved recognition amounts |
The finance-approved schedule supports a deferred revenue view without mixing it into MRR. Keep bookings data distinct from invoices and recognition schedules when building a bookings to revenue analysis.
Store mrr_amount in a single reporting currency before aggregation. If you convert currencies, keep both the original amount and applied exchange rate for auditability. Treat currency normalization, effective dates, recorded timestamps, and non-overlapping versions as data controls, not implementation details.
Define the snapshot rule before writing SQL
The query below measures MRR as of the last day of each month. A subscription starting mid-month appears in that month’s ending MRR if it’s active on month-end.
For annual plans, load mrr_amount as the normalized recurring value your company uses for management reporting. A common rule divides annual recurring subscription value by 12. Don’t include implementation fees, one-time credits, or usage spikes unless your MRR policy explicitly includes them.
Also, enforce one rule in the source data: a subscription can’t have two overlapping active versions for the same effective period. Overlaps are a common cause of doubled MRR.
Build a SaaS revenue waterfall in SQL
This query implements a customer-level revenue waterfall model for Monthly Recurring Revenue (MRR). Customer-level classification matters because a customer might cancel one seat bundle while expanding another product in the same month. This prevents offsetting product changes from being double-counted.
SQL:
WITH active_subscriptions AS (
SELECT
d.month_start,
s.customer_id,
s.subscription_id,
SUM(s.mrr_amount) AS subscription_mrr
FROM dim_month d
JOIN subscription_version s
ON d.month_end >= s.effective_from
AND d.month_end < COALESCE(s.effective_to, DATE ‘9999-12-31’)
WHERE s.status = ‘active’
GROUP BY d.month_start, s.customer_id, s.subscription_id
),
customer_months AS (
SELECT
d.month_start,
c.customer_id
FROM dim_month d
CROSS JOIN (
SELECT DISTINCT customer_id
FROM subscription_version
) c
),
customer_balances AS (
SELECT
cm.month_start,
cm.customer_id,
COALESCE(SUM(a.subscription_mrr), 0) AS ending_mrr
FROM customer_months cm
LEFT JOIN active_subscriptions a
ON a.month_start = cm.month_start
AND a.customer_id = cm.customer_id
GROUP BY cm.month_start, cm.customer_id
),
with_prior_month AS (
SELECT
month_start,
customer_id,
ending_mrr,
COALESCE(LAG(ending_mrr) OVER (
PARTITION BY customer_id
ORDER BY month_start
), 0) AS beginning_mrr
FROM customer_balances
),
movements AS (
SELECT
month_start,
beginning_mrr,
ending_mrr,
CASE WHEN beginning_mrr = 0 AND ending_mrr > 0
THEN ending_mrr ELSE 0 END AS new_or_reactivated_mrr,
CASE WHEN beginning_mrr > 0 AND ending_mrr > beginning_mrr
THEN ending_mrr – beginning_mrr ELSE 0 END AS expansion_mrr,
CASE WHEN beginning_mrr > 0
AND ending_mrr > 0
AND ending_mrr < beginning_mrr
THEN ending_mrr – beginning_mrr ELSE 0 END AS contraction_mrr,
CASE WHEN beginning_mrr > 0 AND ending_mrr = 0
THEN -beginning_mrr ELSE 0 END AS churned_mrr
FROM with_prior_month
)
SELECT
month_start,
SUM(beginning_mrr) AS beginning_mrr,
SUM(new_or_reactivated_mrr) AS new_or_reactivated_mrr,
SUM(expansion_mrr) AS expansion_mrr,
SUM(contraction_mrr) AS contraction_mrr,
SUM(churned_mrr) AS churned_mrr,
SUM(ending_mrr) AS ending_mrr
FROM movements
GROUP BY month_start
ORDER BY month_start;
The first CTE finds subscriptions active at month-end. Next, customer_months creates a complete customer-month spine, so a customer with no active subscription gets an explicit zero instead of disappearing.
LAG() retrieves each customer’s prior month-end MRR. The final movement logic compares the two balances and assigns a single net category per customer. expansion_mrr and contraction_mrr are MRR movements. Use expansion revenue or contraction revenue only when your organization has explicitly defined them as revenue measures. The complete customer-month spine and LAG() comparison can feed Net Revenue Retention, but this query doesn’t calculate GAAP revenue.
Reconcile the result before sharing it
A reconciliation is the control that makes a revenue waterfall chart trustworthy. In the underlying revenue waterfall model, add a reconciliation column to the final query or calculate it in a reporting layer.
The monthly test is:
beginning_mrr + new_or_reactivated_mrr + expansion_mrr + contraction_mrr + churned_mrr = ending_mrr
A SQL-friendly variance expression is:
SUM(beginning_mrr) + SUM(new_or_reactivated_mrr) + SUM(expansion_mrr) + SUM(contraction_mrr) + SUM(churned_mrr) - SUM(ending_mrr) AS reconciliation_variance
Add reconciliation_variance as an expression in the final grouped SELECT, rather than pasting it as a standalone fragment. The latter produces invalid SQL.
Every completed period should return zero, allowing a small, clearly defined tolerance when currency conversion uses rounded decimals.
The check supports financial analysis and clearer financial performance reviews. The reconciled output can inform Net Revenue Retention, Average Revenue Per User, and customer lifetime value analysis, but it doesn’t define those metrics alone.
Keep customer movement separate from subscription movement
A product-level report can classify every subscription change. However, don’t blend gross product-level expansion and contraction revenue into a customer-level waterfall.
For example, a customer may reduce Product A and add Product B on the same date. The customer-level report should show the net change. Otherwise, the same commercial event can inflate both expansion and contraction.
If leadership wants gross retention metrics, publish a separate revenue bridge chart with clear labels and its own aggregation rules. Never blend it into the reconciled customer MRR bridge without documenting the rule.
Calculate recognized revenue in a separate model
Your MRR waterfall should connect to financial reporting, but it shouldn’t replace a separate revenue waterfall model for finance. ASC 606 uses a five-step framework for revenue recognition from customer contracts, outlined in Stripe’s ASC 606 and IFRS 15 guide.
Use a finance-approved revenue schedule
The cleanest approach stores one row per contract line and service month in revenue_schedule, a finance-approved source for revenue recognition. Finance reviews allocation, contract modifications, credits, and performance obligations against applicable accounting standards before dashboard use. That review supports ASC 606 compliance.
A monthly deferred revenue bridge follows this formula:
Closing contract liability = Opening contract liability + Current-period billings – recognized revenue
A bookings to revenue analysis keeps commercial bookings separate from invoice activity, billings, and cash collections. The deferred balance and amounts recognized for services belong in the accounting bridge.
For a basic summary query, aggregate invoice_line.amount by invoice month and revenue_schedule.recognized_revenue by service month. Then use a running sum to calculate the closing contract liability.
Do not infer recognized revenue by dividing every invoice by 12. That shortcut fails for multi-year terms, prorated starts, bundled services, discounts, and modified contracts.
Treat usage revenue with its own timing rules
Usage-based billing introduces another timing question, since these products are separate revenue streams with their own service-period and rating rules. Product events may occur in one month, invoices may post later, and credits may arrive after both.
Keep metered usage facts separate from recurring subscription MRR. Join them to a billing or accounting-approved schedule only after you define the service period, rating logic, and treatment of late events. The ASC 606 and IFRS 15 recognition steps are a useful reference when a contract has more than one promised service.
Stop common data problems before they distort MRR
Most waterfall failures start upstream. Clean SQL can’t fix unreliable effective dates, ambiguous status fields, or inconsistent customer definitions.
Handle backdated changes and plan migrations
Store both effective_from and recorded_at. The effective date changes historical MRR, while recorded_at preserves the audit trail.
When a backdated cancellation arrives, rerun the affected months and preserve a monthly snapshot of the published result. This gives finance and founders a clear explanation when last month’s number changes.
Plan migrations need the same care. End the old plan version before the new version begins. If both versions remain active at month-end, the model counts both.
Make pauses, refunds, and churn explicit
A pause isn’t always customer churn. Define a true cancellation as zero MRR, then distinguish it from a pause, refund, credit, or contract modification. Decide whether a paused subscription has zero MRR, reduced MRR, or remains contractually active, and apply that policy consistently.
Refunds also need their own accounting treatment. A refund may be a billing correction, a credit memo, or a contract modification. It can affect deferred revenue, but it doesn’t automatically reduce historical MRR.
Finally, compare your waterfall with CRM data, billing records, and the general ledger every month. For a bookings to revenue comparison, Salesforce opportunity amounts may reflect bookings or total contract value, not MRR or GAAP revenue. Use consistent customer definitions before calculating Net Revenue Retention, Average Revenue Per User, or customer lifetime value. These controls make financial analysis more reliable.
Frequently Asked Questions
What is a SaaS revenue waterfall?
A SaaS revenue waterfall explains how recurring revenue or recognized revenue changes from one period to the next. An MRR waterfall tracks commercial movements such as new, expansion, contraction, and churned MRR, while a GAAP revenue waterfall follows service delivery and revenue recognition rules.
How is an MRR waterfall different from a GAAP revenue waterfall?
An MRR waterfall measures normalized recurring run rate at month-end and categorizes changes between customer balances. A GAAP revenue waterfall records revenue as performance obligations are satisfied, which can differ from billing, cash collection, and MRR timing.
Why should the waterfall classify movements at the customer level?
Customer-level classification assigns one net movement category to each customer for the period. This prevents a customer that reduces one product and adds another from being counted as both gross contraction and gross expansion in the same MRR bridge.
How do you reconcile a SaaS revenue waterfall?
Add beginning MRR, new or reactivated MRR, expansion MRR, contraction MRR, and churned MRR, then compare the result with ending MRR. The reconciliation variance should be zero for each completed period, subject only to a documented tolerance for rounding.
Can you calculate recognized revenue by dividing invoices by 12?
No. That shortcut can fail for multi-year terms, prorated starts, bundled services, discounts, usage charges, and contract modifications. Use a finance-approved revenue schedule that assigns recognized revenue to the appropriate service period.
Final thoughts
A trustworthy SaaS revenue waterfall starts with effective-dated subscription history and ends with a zero-variance reconciliation. Once reconciled, the Monthly Recurring Revenue model supports financial analysis, Net Revenue Retention tracking, and a clearer view of financial performance.
Keep MRR movement and GAAP revenue recognition in separate models. Reconcile bookings to revenue, but keep bookings, billings, MRR, and recognized revenue distinct. That discipline turns a monthly chart into a report your team can use for planning and close.
