Billing Analytics & Revenue Metrics

Every SaaS company reports MRR, and remarkably few can explain how theirs is calculated. The number gets assembled in a spreadsheet, then in a dashboard tool, then in a board deck, and the three disagree — usually because each made a different silent decision about annual plans, taxes, credits, or the month a downgrade takes effect. Metrics built directly on billing data, with the movement between two months modelled explicitly, do not have this problem. For the surrounding context see Tax Compliance & Revenue Recognition.

The framing that makes this tractable is to stop treating MRR as a level and start treating it as the running total of movements. February’s MRR is January’s plus new plus expansion minus contraction minus churn. If those five components are stored as rows, every number in every report is traceable to the subscriptions that produced it, and the question “why did MRR drop in March?” is answered by a query rather than an investigation.

Prerequisites

Metrics are downstream of billing correctness. A metrics layer built on inconsistent subscription state produces confident, wrong numbers — which is worse than no numbers at all.

Billing metrics prerequisites Normalised recurring amounts, a subscription change history, an immutable ledger, and a fixed month boundary underpin the metrics layer. Movement log → MRR, churn, retention Normalised monthly amounts Subscription change history Immutable revenue ledger Fixed month boundary + zone One written definition per metric, versioned with the code
A metric is a contract — its inputs, boundary, and exclusions must be as fixed as the code that computes it.

Normalising to a Monthly Amount

Every recurring line has to reduce to one number: what it contributes per month. This sounds mechanical and is where most disagreements originate, because the normalisation rules encode judgement calls.

from decimal import Decimal, ROUND_HALF_UP

INTERVAL_MONTHS = {"month": 1, "quarter": 3, "semiannual": 6, "year": 12}

def monthly_amount_minor(unit_amount_minor: int, quantity: int,
                         interval: str, interval_count: int = 1,
                         discount_bps: int = 0) -> Decimal:
    """Normalise any recurring line to a monthly contribution, in minor units."""
    months = Decimal(INTERVAL_MONTHS[interval] * interval_count)
    gross = Decimal(unit_amount_minor) * Decimal(quantity)
    net = gross * (Decimal(10_000 - discount_bps) / Decimal(10_000))
    return (net / months).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

Four rules have to be decided explicitly and written down, because reasonable people choose differently:

  • Annual plans divide by twelve. A $1,200 annual subscription contributes $100 of MRR in every month of its term, not $1,200 in the month it was invoiced. Anything else makes MRR a cash chart with a seasonal spike.
  • Tax is excluded. MRR is a revenue measure, not a collections measure. Including VAT or GST makes the number vary by customer geography for reasons unrelated to the business.
  • Usage is included only if it recurs. Committed or reliably repeating metered spend belongs in a separate “usage MRR” component; a one-off overage spike does not belong in the recurring number at all. Report both, never blend them silently.
  • Discounts reduce MRR; credits do not. A 20% permanent discount is a lower recurring price. A one-off account credit for an outage is a concession against a specific invoice and leaves the recurring amount unchanged.

The fourth rule is the one that generates the most internal argument, and the deciding question is durability: does this change what the customer will pay next month if nothing else happens? A discount does. A credit does not.

The Movement Model

With normalisation settled, every subscription contributes one movement per month, and the movement’s type is a function of the previous month’s amount and this month’s.

MRR movement classification New, expansion, contraction, churn, and reactivation are each determined by comparing the prior month amount to the current month amount. New prior = 0 never billed before + full amount Expansion current > prior both non-zero + difference Contraction current < prior current > 0 − difference Churn current = 0 prior > 0 − prior amount Reactivation prior = 0 churned before + full amount
Five mutually exclusive types — every subscription lands in exactly one per month, which is what makes the totals reconcile.

The distinction between new and reactivation is not pedantry. Sales-driven growth and win-back are different motions with different costs, and blending them hides whether a recovery programme is working. Similarly, splitting contraction from churn keeps a downgrade from looking like a loss and a partial recovery from looking like a win.

CREATE TABLE mrr_movements (
  month           DATE NOT NULL,               -- first day of the month, UTC
  subscription_id UUID NOT NULL,
  account_id      UUID NOT NULL,
  prior_minor     BIGINT NOT NULL,
  current_minor   BIGINT NOT NULL,
  movement_type   TEXT NOT NULL,               -- new|expansion|contraction|churn|reactivation
  delta_minor     BIGINT NOT NULL,             -- current − prior, signed
  currency        CHAR(3) NOT NULL,
  computed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (month, subscription_id)
);

-- MRR for any month, and the bridge that explains the change from the prior one.
SELECT month,
       sum(current_minor) FILTER (WHERE current_minor > 0)          AS mrr_minor,
       sum(delta_minor) FILTER (WHERE movement_type = 'new')        AS new_minor,
       sum(delta_minor) FILTER (WHERE movement_type = 'expansion')  AS expansion_minor,
       sum(delta_minor) FILTER (WHERE movement_type = 'contraction') AS contraction_minor,
       sum(delta_minor) FILTER (WHERE movement_type = 'churn')      AS churn_minor,
       sum(delta_minor) FILTER (WHERE movement_type = 'reactivation') AS reactivation_minor
  FROM mrr_movements
 GROUP BY month
 ORDER BY month;

The composite primary key on (month, subscription_id) is the guardrail. It makes the computation idempotent — rerunning a month upserts rather than duplicates — and it makes the invariant checkable: the number of movement rows in a month must equal the number of subscriptions that were active in either that month or the previous one.

Retention, Cohorts, and the Denominators That Matter

Net revenue retention is the movement model’s most useful derivative, and its definition is where comparisons between companies quietly break down. The version most investors mean is: take the cohort of accounts that existed at the start of the period, sum what they pay at the end of the period, and divide by what they paid at the start. New accounts acquired during the period are excluded from both sides.

-- Net revenue retention for the cohort active 12 months ago.
WITH base AS (
  SELECT account_id, sum(current_minor) AS start_minor
    FROM mrr_movements
   WHERE month = date_trunc('month', now()) - INTERVAL '12 months'
     AND current_minor > 0
   GROUP BY account_id
), latest AS (
  SELECT account_id, sum(current_minor) AS end_minor
    FROM mrr_movements
   WHERE month = date_trunc('month', now())
   GROUP BY account_id
)
SELECT round(100.0 * sum(coalesce(l.end_minor, 0)) / nullif(sum(b.start_minor), 0), 1) AS nrr_pct
  FROM base b LEFT JOIN latest l USING (account_id);

Two traps live in that query. The LEFT JOIN is essential: an account that churned entirely must contribute zero to the numerator, not disappear from the calculation — an inner join silently reports retention among survivors, which is always flattering and always wrong. And the cohort is defined by accounts, not subscriptions, because an account that cancels one subscription and starts a larger one has expanded, not churned.

Logo churn deserves its own definition and its own denominator. Revenue churn weighted by account size tells you about the money; logo churn tells you about product-market fit at the small end. A month where one large account leaves and forty small ones stay looks catastrophic on revenue churn and healthy on logo churn, and both readings are true.

Reconciling Metrics Against the Ledger

A metrics layer that never gets checked against accounting will drift, usually in the direction of optimism. The reconciliation is straightforward: recurring revenue recognised in a month, from the double-entry billing ledger, should approximate that month’s MRR. It will not match exactly, and the gaps are informative rather than alarming — but every gap should have a named cause.

MRR to recognised revenue bridge Mid-month starts, one-off charges, credits, and usage overage explain the difference between the MRR level and revenue recognised in the ledger. MRR level movement log − mid-month starts and stops + one-off charges and setup fees − credit notes and concessions Recognised revenue every residual over the threshold gets a named cause before the month closes
The bridge is the control — an unexplained residual is a bug in one of the two systems, and it is usually the metric.

Run the bridge monthly, alert when the unexplained residual exceeds a threshold — half a percent of MRR is a reasonable starting point — and require a named cause before the month is signed off. The discipline pays for itself the first time it catches a subscription whose currency was misclassified or a plan whose amount was recorded in major units in one place and minor units in another.

The Definitions That Cause Arguments

Most disputes about SaaS metrics are not arithmetic disputes; they are definitional ones that nobody wrote down. Five recur in every company, and settling them explicitly — in a document versioned next to the code — removes more confusion than any dashboard.

When does a cancellation count as churn? Two defensible answers exist. Churn on request date makes the metric responsive and gives the retention team fast feedback, but it books a loss for a customer who is still paying and still using the product, and who may reverse the decision. Churn on service end date matches the money but delays the signal by up to a year on annual plans. Most companies choose service end for revenue reporting and track cancellation requests as a separate leading indicator, which is the arrangement that satisfies both finance and the people trying to save the account.

Is a failed payment churn? No, until dunning gives up. A subscription in retry is still a subscription, and counting a card decline as churn produces a metric that recovers a few days later and makes the series unusable for trend analysis. Track involuntary churn separately, dated to the moment grace-period retry logic exhausts, and report it next to voluntary churn — the two have entirely different remedies, and blending them hides which lever to pull.

What happens on a currency change? A customer moving from USD to EUR pricing has not expanded or contracted, but a naive movement calculation will report both a churn and a new sale. Detect same-account currency changes and classify them as a migration with a zero net delta, or the month a pricing rollout ships will show a spike in both directions that takes a week to explain.

Do trials count? Not until they convert to a paying subscription. A trial with a card on file contributes nothing to MRR because no recurring commitment exists yet. The moment of conversion is a new movement, and the trial period itself belongs in a funnel metric rather than a revenue one — see trial period management for why the conversion boundary is worth modelling precisely.

How are multi-product accounts handled? An account with three subscriptions is one logo and three revenue streams. Logo churn requires all three to end; revenue churn is computed per stream and aggregated. Getting this backwards — counting a logo as churned when one of three products ends — produces a churn rate that no operator recognises and that the sales team will correctly reject.

Write the answers down, put them next to the query that implements them, and change them only with a restatement. A metric whose definition drifts silently is worse than one that is slightly wrong in a known direction, because the second can be corrected and the first cannot even be compared with itself.

Instrumenting the Movement Job

The computation itself is a monthly batch, and the operational requirements are unusually strict for a reporting job because its output ends up in board decks and investor updates. Three properties matter.

It must be idempotent. Rerunning a month must produce identical rows, which the (month, subscription_id) primary key and an upsert give you. Any use of now() inside the computation breaks this, because a rerun in March of February’s numbers would classify movements against a different clock. Pass the month boundary in as a parameter and forbid wall-clock reads inside the job.

It must be explainable. Store the inputs alongside the output: prior amount, current amount, the subscription’s status at each boundary, and the currency. When someone asks why a specific account shows contraction, the answer should be visible in one row rather than requiring the job to be re-run with logging.

It must be auditable after close. Once a month is signed off, its rows are frozen. Late-arriving corrections — a backdated cancellation, a mis-entered quantity fixed in April for a February subscription — become an explicit restatement row rather than an in-place edit, so the number reported in March and the number in the database in June agree or visibly differ for a recorded reason.

def compute_month(db, month_start, month_end) -> int:
    """Deterministic: every input is a parameter, no wall-clock reads."""
    rows = db.subscription_amounts_at(month_start, month_end)   # prior + current per subscription
    written = 0
    for r in rows:
        movement = classify(r.prior_minor, r.current_minor, r.ever_churned_before)
        db.upsert_movement(
            month=month_start, subscription_id=r.subscription_id,
            account_id=r.account_id, prior_minor=r.prior_minor,
            current_minor=r.current_minor, movement_type=movement,
            delta_minor=r.current_minor - r.prior_minor, currency=r.currency,
        )
        written += 1
    return written

The invariant worth asserting at the end of every run is that the sum of delta_minor across a month equals that month’s MRR minus the prior month’s MRR. If it does not, a subscription was missed or double-counted, and the job should fail loudly rather than publish a bridge that does not balance.

Performance & Scale Considerations

The movement computation is a full pass over active subscriptions, which is cheap at 10,000 and non-trivial at 500,000. Compute it once per month as a batch, store the results, and serve every dashboard from the stored table rather than recomputing on read. The batch should be idempotent and restartable — the (month, subscription_id) key gives you that for free — and should never modify a month that has been signed off, so late corrections become an explicit restatement rather than a silent rewrite of history.

Query patterns matter as much as the batch. Dashboards ask for the same handful of shapes over and over — MRR by month, the movement bridge, retention by cohort, churn by plan — and each of those is a grouped aggregate over the movement table. A composite index on (month, movement_type) covers the bridge, and a separate index on (account_id, month) covers cohort queries; without them, a table with a few million rows will still answer in a second but a table with a hundred million will not, and the first sign of trouble is usually a dashboard timing out during a board meeting.

Retention queries deserve a materialised layer of their own once the account base passes six figures. Cohort analysis is inherently a self-join across months, and computing it live for a rolling twenty-four-month window is expensive enough to notice. Materialise the cohort matrix nightly from the movement table, and treat it the same way as the movements themselves: derived, reproducible, and never edited in place.

Currency handling is the other scaling concern. Storing movements in their native currency and converting at report time keeps history correct when rates move, but it means every aggregate query joins a rate table. The pragmatic compromise is to store both the native amount and a converted amount at the month’s closing rate, so ordinary reporting is a plain sum while the native figures remain available for audit.

Frequently Asked Questions

Should MRR include annual plans at one-twelfth? Yes. MRR is a normalised run rate, not cash. Reporting the full annual amount in the invoiced month produces a chart dominated by billing timing, which tells you nothing about the health of the business.

How do I treat a customer who pauses their subscription? A pause is contraction to zero while the account remains, not churn, provided your definition says so and says so consistently. Whatever you choose, apply it to both the numerator and the denominator of retention or the number becomes meaningless.

Where should metered usage sit? In a separate component reported alongside recurring MRR. Committed usage that recurs predictably can be included in a combined figure, but only if you can also show the split — investors and operators both need to see how much of the run rate is contractually durable.

Why does my MRR not match my bank account? It never will, and it should not. MRR excludes tax, ignores payment timing, spreads annual invoices, and takes no position on collection. Cash and MRR answer different questions; the bridge in this page is how you keep both honest.

Can I compute all of this in the dashboard tool instead? You can, and the definitions will fork the first time someone edits a query. Computing movements in the database, versioned with your application code and covered by tests, is what makes the number defensible when it is challenged.