Calculating MRR Movements from Subscription Data

The question “why did MRR change last month?” has one good answer and many bad ones. The good answer is a table of movements that sums exactly to the difference between two months. The bad answers involve a spreadsheet, three definitions, and a number that nobody can reproduce a quarter later. This guide sits under Billing Analytics & Revenue Metrics and covers the computation itself: the inputs, the classification, and the invariant that proves the result is right.

The design constraint that shapes everything is reproducibility. The number will be quoted externally, challenged internally, and recomputed months later during diligence, so the job that produces it must be deterministic, idempotent, and free of any dependence on when it happens to run. That rules out reading the clock inside the computation and rules out any running-total column.

You reach for this the first time two dashboards disagree. Usually one is summing invoices and calling it MRR, the other is summing subscription amounts, and neither is wrong so much as undefined — they answer different questions and nobody wrote down which question the board deck was asking.

Trade-offs

Source of truth	Reproducible	Handles annual plans	Handles mid-month changes	Effort
Invoices issued	Yes, but it is cash timing	No — spikes on the invoice month	Only via proration lines	Low
Provider’s own MRR report	Not by you	Provider-defined	Provider-defined	Very low
Subscription state at month boundaries	Yes	Yes, normalised	Yes, at the boundary	Medium
Event-sourced from every change	Yes, to the second	Yes	Yes, continuously	High

Boundaries are enough for most businesses; the case for events is a large volume of sign-ups that never survive a full month.

The provider-report row is worth ruling out explicitly. It is fast to adopt and impossible to defend, because you cannot state its definition, cannot reproduce it, and cannot reconcile it against your ledger. It is a reasonable sanity check and a poor system of record.

Step-by-Step Implementation

1. Snapshot the monthly amount at each boundary

The input is a pair of numbers per subscription: what it contributed per month at the start of the prior month, and what it contributes at the start of this one. Both come from subscription state, normalised to a monthly figure.

-- Monthly contribution per subscription as of a boundary instant.
CREATE OR REPLACE VIEW subscription_monthly_amount AS
SELECT s.subscription_id,
       s.account_id,
       s.currency,
       sum(
         CASE si.interval
           WHEN 'month' THEN si.unit_amount_minor * si.quantity
           WHEN 'year'  THEN (si.unit_amount_minor * si.quantity) / 12.0
           WHEN 'quarter' THEN (si.unit_amount_minor * si.quantity) / 3.0
         END
         * (10000 - coalesce(si.discount_bps, 0)) / 10000.0
       )::bigint AS monthly_minor
  FROM subscriptions s
  JOIN subscription_items si USING (subscription_id)
 WHERE s.status IN ('active', 'past_due')
 GROUP BY s.subscription_id, s.account_id, s.currency;

Including past_due is a deliberate choice: a subscription in retry is still a subscription, and excluding it produces a churn spike that reverses days later. Exclude trialing, because nothing recurring has been committed yet.

2. Classify the change

def classify(prior_minor: int, current_minor: int, churned_before: bool) -> str:
    if prior_minor == 0 and current_minor > 0:
        return "reactivation" if churned_before else "new"
    if prior_minor > 0 and current_minor == 0:
        return "churn"
    if current_minor > prior_minor:
        return "expansion"
    if current_minor < prior_minor:
        return "contraction"
    return "unchanged"

Five live types plus unchanged. Writing a row even for unchanged subscriptions costs storage and buys a strong invariant: the row count for a month equals the number of subscriptions active in that month or the previous one, which makes a missing subscription detectable rather than invisible.

3. Write one row per subscription per month

Two inputs, one pure function, one keyed row — the shape that makes a rerun a no-op rather than a duplication.

4. Assert the bridge balances

-- The invariant: movements must explain the entire change between months.
WITH level AS (
  SELECT month, sum(current_minor) AS mrr FROM mrr_movements GROUP BY month
), bridge AS (
  SELECT month, sum(delta_minor) AS delta FROM mrr_movements GROUP BY month
)
SELECT l.month, l.mrr, p.mrr AS prior_mrr, b.delta,
       l.mrr - p.mrr - b.delta AS residual        -- must be zero
  FROM level l
  JOIN level p ON p.month = l.month - INTERVAL '1 month'
  JOIN bridge b ON b.month = l.month
 WHERE l.mrr - p.mrr - b.delta <> 0;

Any row returned is a bug. Fail the job rather than publishing a bridge that does not add up — a report that is quietly off by a rounding error trains everyone to stop trusting the residual, which is the only control you have.

5. Freeze and restate

Once a month is reported, its rows are immutable. Corrections arriving later — a backdated cancellation, a quantity fixed retroactively — become a restatement row in the current month with a reason, not an edit to a closed one. This keeps the number you published and the number in the database either identical or visibly different for a recorded cause.

Handling the Cases That Break Naive Implementations

Four situations produce wrong movements in almost every first implementation, and each has a specific fix.

Currency migrations. An account moving from USD to EUR pricing looks like a churn and a new sale to a subscription-keyed comparison. Detect same-account currency changes explicitly and emit a migration movement with a zero net delta in the reporting currency, so the month a pricing rollout ships does not show a spike in both directions.

Subscription replacement. Some flows cancel a subscription and create a new one rather than amending in place — plan switches, provider migrations, and contract renewals often work this way. Keyed by subscription, that is churn plus new. Keyed by account, it is correctly nothing or an expansion. Compute movements per subscription for operational detail and aggregate retention per account, and never let the account-level metric inherit subscription-level churn.

Trials converting. A trial contributes nothing while it runs, so conversion is a new movement on the day the first recurring charge takes effect. The trap is including trialing subscriptions in the boundary snapshot, which books revenue that does not exist and then books churn when the trial lapses unconverted.

Pauses. A paused subscription contributes zero and the account remains. Whether that is contraction to zero or churn is a definitional choice, and the only wrong answer is applying it inconsistently between the numerator and the denominator of retention. Whichever you choose, encode it as a status list in one place rather than a condition repeated in five queries.

The general pattern across all four is that the naive failure comes from keying on the wrong entity or from including a status that should be excluded. Both are cheap to fix at the view layer and expensive to fix after three months of published numbers.

All four failures come from keying on the wrong entity or including the wrong status — both fixable at the view layer.

What to Store Alongside the Movement

A movement row that contains only a type and a delta is auditable in principle and useless in practice, because answering “why is this account showing contraction?” then requires re-running the job with logging. Store the inputs that produced the classification, and the answer becomes a single-row lookup.

At minimum keep the prior and current monthly amounts, the currency, and the subscription’s status at each boundary. The status pair is what distinguishes a genuine downgrade from an artefact: a subscription that went from active to past_due while keeping the same amount should show unchanged, and seeing both statuses on the row makes an incorrect classification obvious at a glance.

It is also worth storing the dominant driver where one exists. If a contraction was caused by a quantity reduction on a single item, recording that item’s price key turns “contraction of $400” into “contraction of $400 on the seats line”, which is the difference between a number and an insight. Keep this as a nullable annotation rather than a required field — some movements have several drivers and forcing a single one produces misleading attribution.

Finally, record the job run that produced the row. When a rerun changes something it should not have, the run identifier is what lets you diff two executions rather than reasoning about which one was current. It costs one column and it is the first thing you will want during an investigation.

ALTER TABLE mrr_movements
  ADD COLUMN prior_status   TEXT,
  ADD COLUMN current_status TEXT,
  ADD COLUMN driver_price_id TEXT,
  ADD COLUMN run_id         UUID NOT NULL;

Do not, however, store the derived level — the running MRR total — on the movement row. It is tempting because it makes charting trivial, and it is the exact denormalisation that eventually disagrees with the sum of the deltas. Compute the level in the query that needs it.

Verification & Testing

The strongest test is the invariant itself: generate a synthetic population with known movements, run the job, and assert the residual is exactly zero. Then perturb it — remove a subscription from the current boundary, duplicate one in the prior — and assert the residual becomes non-zero, proving the check has teeth.

Test idempotency by running the same month twice and asserting the row set is byte-identical. Then run months out of order — March before February — and assert both are correct, which fails immediately if anyone introduced a dependency on the previous run having happened.

Finally, table-test the classifier across every boundary pair: zero to positive with and without prior churn, positive to zero, increases, decreases, and equality. Include the awkward case of a subscription whose amount changes by a single minor unit, because rounding in the normalisation view can otherwise turn an unchanged subscription into a stream of one-unit expansions and contractions that pollute the bridge.

Gotchas & Production Pitfalls

Including trialing subscriptions. Books revenue that was never committed and produces phantom churn when the trial ends.
Excluding past-due subscriptions. Produces a churn spike on the day a card fails and a reactivation spike when it recovers, neither of which is real.
A running-total column. Any stored cumulative figure will eventually disagree with the sum of its parts. Derive the level from the movements every time.
Wall-clock reads inside the job. Makes a rerun produce different output than the original run, which destroys reproducibility silently.
Rounding per item rather than per subscription. Normalising each item to a monthly figure and rounding individually accumulates error; sum in higher precision and round once.

Frequently Asked Questions

Should the movement job run daily or monthly? Monthly for the reported figure, and optionally daily for an in-progress view clearly labelled as such. A daily-updating “current month MRR” that changes under people is fine as an operational signal and unusable as a reported number.

How are multi-currency accounts handled? Compute movements in the native currency and convert at a fixed month-end rate for aggregation, storing both. Converting at report time with a live rate makes last month’s published figure change today.

What about accounts with several subscriptions? Compute per subscription, aggregate per account for retention. Mixing the two levels is the most common source of retention figures that operators do not recognise.

Does this replace the provider’s revenue reporting? It should become the authoritative one, with the provider’s report used as a cross-check. Two numbers that agree within a small tolerance is reassuring; two numbers where only one can be explained is not.