How to Model SaaS MRR Without Double Counting Revenue

Last updated: 14 October 2026.

A founder asks why MRR doubled after the CRM joined the warehouse. No prices changed. No customers expanded. Each subscription simply matched two deals, so the query counted it twice.

The arithmetic can be correct while the data model is wrong. A trustworthy MRR model begins with the grain of the record and a written definition of eligibility.

This guide builds an operational recurring-value model for product and growth reporting. It does not define accounting revenue recognition or replace the finance team's reporting policy.

What exactly should your MRR number mean?

Write a metric contract before writing SQL.

Specify the reporting timestamp and timezone, included product types, eligible states, discount treatment, trial treatment, currency policy and handling of overdue subscriptions. Name the owner who approves changes.

Stripe's documented standard MRR includes monthly-normalised active and past-due subscriptions, excludes taxes and metered products, and applies specific trial rules. Some discount settings are configurable. Its documentation separately advertises a usage-MRR preview, so do not assume every account or report has the same usage treatment. Stripe Billing analytics

Your operational definition may differ, but the difference must be named. “Contracted recurring value”, “cash collected” and “MRR under our eligibility policy” should not share an unlabeled revenue column.

Our data warehouse and customer data work begins with these definitions because every later retention and growth calculation inherits them. Use metric contracts to make the model reviewable outside the data team.

Why should the grain be a subscription item?

A subscription can contain several products, quantities and billing intervals. One customer can hold several subscriptions.

Stripe exposes subscription items with their own IDs, prices, quantities and billing-period fields. Its mixed-interval subscriptions can combine different billing intervals, subject to the provider's billing-mode and API requirements. Subscription item reference, mixed-interval subscriptions

Build your recurring-value fact at this grain:

“One subscription item, for one effective interval, in one source account and environment.”

Include the source-account ID and live/test distinction in the business key where relevant. Provider object IDs alone should not silently merge records from different businesses or environments.

Then aggregate in stages:

1. Item to subscription.
2. Subscription to your canonical customer account.
3. Customer account to segment or company total.

Do not classify an account as churned because one add-on ended while its main subscription continued. The net revenue retention guide explains the customer-level cohort calculation that should consume this model.

Which source facts should remain separate?

Keep at least four distinct concepts:

• Subscription state: the current and historical commercial configuration used by the MRR model.
• Invoice lines: billed charges and adjustments for specified service periods.
• Payments and refunds: collection activity.
• CRM deals: sales commitments and pipeline records.

A fifth layer maps provider customers and subscriptions to your internal account IDs.

These facts can be reconciled, but their amounts should not be summed into one measure. An annual invoice and the monthly-normalised value of its subscription describe the same relationship through different lenses.

Maintain raw source records and transformation lineage. When a stakeholder challenges one account's MRR, the team should be able to show the contributing item, effective state, price, quantity and policy.

How do you normalise recurring prices?

For a simple licensed per-unit item, determine the period amount after the applicable quantity and discount rules, then divide by the number of months represented by that recurring interval.

Examples under an explicitly monthly-normalised policy:

• USD120 per month contributes USD120.
• USD360 every three months contributes USD120.
• USD1,440 per year contributes USD120.
• Three seats at USD20 per month contribute USD60.

Do not divide an annual price by the number of days remaining on the invoice. MRR is a run-rate snapshot under this model, not the value earned during the remainder of the service period.

Stripe's Price object distinguishes per-unit and tiered schemes and exposes recurring interval, currency and amount information. Its unit amount uses the currency's smallest unit; a general implementation must not assume every currency has two decimal places. Stripe Price object

For tiered, transformed-quantity or other complex prices, resolve the applicable recurring period amount with dedicated logic. A universal unit_amount * quantity expression is unsafe.

For weekly or daily plans, document a chosen annualisation convention and test it separately. The worked query below intentionally covers only monthly, quarterly and annual fixed recurring amounts.

How should discounts and adjustments enter the model?

Resolve discount scope and ordering before aggregation. A subscription-level fixed discount cannot be subtracted in full from every item without multiplying its effect.

If your reporting requires item-level attribution, define how that discount is allocated across eligible items and reconcile the allocation back to the subscription total. Keep the unallocated source value for audit.

A temporary credit is not automatically a permanent reduction in the subscription's ongoing value. Decide whether your metric includes one-time discounts and apply that policy consistently.

Proration lines are another common trap. Stripe can create debit and credit adjustments when subscriptions change, and those adjustments represent partial-period billing. Stripe proration documentation

For an MRR snapshot, use the effective recurring configuration after the change. Keep the proration in the invoice model. Counting the new subscription value plus its proration charge would mix a stock measure with a transaction.

How do you preserve historical MRR?

Store effective intervals for changes to price, quantity, eligible state, trial state and discount configuration.

Use half-open intervals: the version is valid from its start timestamp, inclusive, until its end timestamp, exclusive. An upgrade should close one version and open another without overlap.

Distinguish when a change became effective from when your pipeline learned about it. A late event can require correcting a historical snapshot. Keep an audit record of that correction instead of silently changing every previously published result.

If the connector only exposes current state, a snapshot can preserve changes from the time collection begins. It cannot prove historical values from before that point. Backfill history from suitable source evidence or label the gap.

Avoid using a future scheduled price as today's MRR. It belongs in a forecast until the policy's effective condition is met.

Open full diagram

What does a checked MRR fixture look like?

Use this illustrative USD-only fixture for an as-of date of 14 October 2026:

• Account A has an annual base item worth USD1,200 per year.
• Account A also has three monthly seats at USD20 each.
• Account B has a USD50 monthly item with a USD10 recurring discount.
• Account C has a cancelled USD25 monthly item.
• Account D has a trial item that the chosen policy excludes.
• Usage charges and a one-time implementation fee are kept outside this fixed-recurring fixture.

The expected MRR is USD100 plus USD60 plus USD40, or USD200. Account A contributes USD160 and Account B USD40.

The following query runs against an illustrative normalised history table. net_period_amount is already in major currency units after quantity and discount resolution; period_months is already validated; eligible is the result of the approved policy.

WITH item_history (
  item_id, account_id, currency, valid_from, valid_to,
  net_period_amount, period_months, eligible
) AS (
  VALUES
    ('base_A', 'A', 'USD', '2026-01-01', NULL, 1200.0, 12, 1),
    ('seats_A', 'A', 'USD', '2026-10-01', '2026-10-20', 60.0, 1, 1),
    ('seats_A', 'A', 'USD', '2026-10-20', NULL, 80.0, 1, 1),
    ('plan_B', 'B', 'USD', '2026-10-01', NULL, 40.0, 1, 1),
    ('plan_C', 'C', 'USD', '2026-10-01', NULL, 25.0, 1, 0),
    ('trial_D', 'D', 'USD', '2026-10-01', NULL, 100.0, 1, 0)
),
as_of_items AS (
  SELECT *
  FROM item_history
  WHERE valid_from <= '2026-10-14'
    AND (valid_to IS NULL OR '2026-10-14' < valid_to)
)
SELECT account_id, currency,
       SUM(net_period_amount / period_months) AS mrr
FROM as_of_items
WHERE eligible = 1
GROUP BY account_id, currency;

The fixture was executed locally and returned A: USD160 and B: USD40. At 20 October, the seat change makes A USD180 and the total USD220. This validates the example, not a production Stripe connector.

Use decimal arithmetic and properly typed timestamps in the production warehouse. The short query isolates the as-of and aggregation logic; it is not a complete extractor or universal pricing engine.

How do CRM joins create invisible duplicates?

Suppose Account A has two CRM deals and Account B has one. Joining the USD160 customer MRR directly to deal rows produces USD320 for A. Adding B yields USD360 instead of USD200.

The correction is not SUM(DISTINCT mrr). Two unrelated customers can legitimately have the same MRR, so distinct amounts can undercount.

Keep the MRR fact at its chosen grain. Join only a dimension with one row per matching key, or use a bridge with a declared allocation rule if a fact truly belongs to several reporting entities.

For example, choose one account owner as of the reporting date for an owner-level report. If the business wants to allocate MRR across several teams, store weights that sum to one and explain the allocation.

Use Stripe and CRM reconciliation to investigate mismatches without forcing deal totals to equal recurring value.

What validation should block publication?

Make failures visible before refreshing the executive dashboard.

Compare a small set of customer-level results with the provider's reports after aligning definitions and timezone. Stripe notes that its prebuilt reports use UTC while dashboard charts use the account timezone, which can create boundary differences. Stripe report definitions

Publish the model version with the number. A useful dashboard lets a founder understand why MRR moved and lets an analyst reproduce the answer from item-level evidence.