Measure subscription expansion and contraction revenue

An MRR bridge explains how opening monthly recurring value becomes closing monthly recurring value. Separate expansion, contraction, churn, new customers and reactivation so that growth from acquisition cannot hide deterioration among existing customers.

MRR is a defined recurring run-rate measure. It is not automatically cash collected, recognized revenue or the sum of invoice payments. Use one consistent contract before building the bridge.

Classify changes in a synthetic subscription snapshot

The subscription fixture stores opening and closing MRR in paise plus an indicator of whether a zero-opening customer had paid previously. The data describe a fictional subscription business and do not represent NeuraPath pricing.

For opening customers, a higher positive closing value is expansion; a lower positive value is contraction; a zero closing value is churn. For zero-opening customers, positive closing value is new or reactivated according to the historical indicator.

SELECT
 SUM(start_mrr_paise) AS opening_mrr,
 SUM(CASE WHEN start_mrr_paise>0 AND end_mrr_paise>start_mrr_paise
          THEN end_mrr_paise-start_mrr_paise ELSE 0 END) AS expansion,
 SUM(CASE WHEN end_mrr_paise>0 AND end_mrr_paise<start_mrr_paise
          THEN start_mrr_paise-end_mrr_paise ELSE 0 END) AS contraction,
 SUM(CASE WHEN start_mrr_paise>0 AND end_mrr_paise=0
          THEN start_mrr_paise ELSE 0 END) AS churn,
 SUM(CASE WHEN start_mrr_paise=0 AND ever_paid_before=0
          THEN end_mrr_paise ELSE 0 END) AS new_mrr,
 SUM(CASE WHEN start_mrr_paise=0 AND ever_paid_before=1
          THEN end_mrr_paise ELSE 0 END) AS reactivated_mrr,
 SUM(end_mrr_paise) AS closing_mrr
FROM subscriptions;

The bridge is:

70,000 + 5,000 − 8,000 − 30,000 + 18,000 + 5,000 = 60,000 paise.

Opening MRR declines by 10,000 paise overall. New and reactivated value partly offset the losses among opening customers.

Reconcile independently and calculate retention

from math import isclose
from build_and_verify import database

db = database()
rows = db.execute('SELECT * FROM subscriptions').fetchall()
db.close()
opening = sum(start for customer,start,end,prior in rows)
closing = sum(end for customer,start,end,prior in rows)
expansion = sum(end-start for _,start,end,_ in rows if 0 < start < end)
contraction = sum(start-end for _,start,end,_ in rows if 0 < end < start)
churn = sum(start for _,start,end,_ in rows if start > 0 and end == 0)
new = sum(end for _,start,end,prior in rows if start == 0 and not prior)
reactivated = sum(end for _,start,end,prior in rows if start == 0 and prior)
assert (opening,expansion,contraction,churn,new,reactivated,closing) == (
    70000,5000,8000,30000,18000,5000,60000)
assert opening+expansion-contraction-churn+new+reactivated == closing
nrr = (opening+expansion-contraction-churn)/opening
grr = (opening-contraction-churn)/opening
assert isclose(nrr, 37000/70000) and isclose(grr, 32000/70000)
print({'nrr':nrr, 'grr':grr, 'closing_over_opening':closing/opening})

Net revenue retention is approximately 52.86% under this contract. Gross revenue retention is approximately 45.71%, excluding expansion. Closing MRR divided by opening MRR is about 85.71%, but that includes new and reactivated customers and therefore is not the same retention measure.

Make the accounting scope explicit

Decide how discounts, usage-based charges, annual contracts, currency conversion and tax affect recurring value. Keep one-time setup charges separate unless the metric contract explicitly includes them, in which case its name and interpretation need to reflect that choice.

For multiple currencies, a constant-currency bridge can separate commercial movement from exchange-rate movement. Do not add incompatible currency amounts or silently attribute FX changes to account expansion.

This example uses endpoint snapshots. If a customer upgrades and downgrades within the period, the bridge shows its net endpoint movement, not every intermediate event. An event-based movement report can answer a different operational question, but it must reconcile to the same closing state under compatible rules.

Use the bridge to direct investigation

C03 accounts for 30,000 paise of lost recurring value, while C02 contracts by 8,000. Those are distinct investigations. Acquisition success should not prevent the team from understanding either change.

The companion customer churn article shows why one lost customer can have a much larger revenue effect than its share of the customer count. Read the two measures together rather than substituting one for the other.

Exercise: add a zero-opening customer with unknown prior-payment history. Keep its new-versus-reactivated classification unresolved while preserving the total bridge. Explain which source would resolve the classification.

NeuraPath's Data Analytics with Generative AI course connects SQL reconciliation with commercial reporting. A strong recurring-revenue analysis explains every movement and keeps acquisition separate from retention.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Diagnose a conversion drop with a segmented checklist.
• Continue with Build a customer health score without hiding missing inputs.