Build a Net and Gross Revenue Retention Bridge from Billing Data

1. 1

   Write the definitions down before pulling data, and have finance sign them: ARR = month-end recurring revenue x 12 after recurring discounts; leave out one-time fees, services, taxes, and usage overages (report usage separately if it matters); convert every currency at one fixed rate for both dates so exchange rates do not show up as expansion or contraction; and say how past-due and paused subscriptions count

2. 2

   Get each billing account's ARR at the start and the end of the period (by default the trailing 12 months, point to point): the best source is a month-end recurring revenue snapshot per customer saved every month from Stripe or your billing system; if you have no snapshots, rebuild both dates from the recurring line items of invoices whose service period covers the date, and label the result as a rebuild

3. 3

   Roll billing accounts up to the customer: map every billing customer id to one CRM account (the parent when a company buys through several entities or moved to a new billing account), so a migration, a re-paper, or a merged account is not counted as churn plus a new logo; list every account the mapping changed

4. 4

   Classify each customer by comparing end ARR with start ARR: new (no ARR at the start and never paid before), reactivation (no ARR at the start but paid before), expansion (end above start), contraction (end below start but above zero, for example seat drops or a downgrade), churn (ARR at the start, none at the end), or flat

5. 5

   Build the bridge and check that it closes: start ARR + new + reactivation + expansion - contraction - churn = end ARR; then GRR = (start ARR - contraction - churn) / start ARR, which can never be above 100%, NRR = (start ARR + expansion - contraction - churn) / start ARR, the same as the end ARR of customers who had ARR at the start divided by start ARR, and logo churn = churned customers / starting customers; new and reactivated customers are in the bridge but never in GRR or NRR

6. 6

   Tie the end ARR to the ARR finance reports for the same date, explain every difference over a tolerance finance sets, and list the five largest expansion, contraction, and churn lines with their CRM owner so account managers can confirm the reason (seats, price, plan, or lost)

7. 7

   Have an LLM draft the monthly commentary from the computed cells only: paste the bridge table into Claude or ChatGPT (with customer IDs instead of names if your AI policy asks for it) with an instruction to copy numbers exactly, do no arithmetic, and name the three largest movements; check every number in the draft against the cells before it goes out, then post the bridge and commentary to the finance and CS leadership channel in Slack, and keep each month's snapshot, mapping, and bridge as the audit trail

Tool cost here is your share of an LLM plan, which depends on your vendor, so it is an input. Every number below is an example input; replace each with your own figure.

Inputs (all assumptions)

Replace each value with your own figure.

• THours a month to build the bridge by hand (exports, account mapping, classification, tie-out, write-up)5AssumptionTime the next month-end retention report from export to sent summary.
• VHours a month with the template (refresh the snapshot, review the mapping and tie-out, check the commentary)1AssumptionTime the first two monthly runs.
• RLoaded hourly cost of the FP&A or RevOps analyst$75AssumptionSalary plus benefits, divided by working hours.
• KMonthly tool cost for the LLM plan$20AssumptionYour share of the Claude or ChatGPT plan, from current vendor pricing and your invoices; Stripe, the CRM, Google Sheets, and Slack are usually already paid for.

Formulas

Time saved per monthT - V

5 - 1 = 4 hours

Time saved per week(T - V) / 4.33

4 / 4.33 = 0.92 hours, shown rounded as 1 hour per week on this page

Value of time saved per month(T - V) x R

4 x $75 = $300

Break-even hours per monthK / R

$20 / $75 = 0.27 hours, so the run covers its tool cost once it saves about 16 minutes a month

NRR (fictional example data)(start ARR + expansion - contraction - churn) / start ARR

($520,000 + $69,000 - $30,000 - $90,000) / $520,000 = 90.2%

• The math counts analyst time only. A bridge that closes may change renewal, pricing, or CS staffing decisions, but what that is worth depends on your business, so it is not added here.
• Point-to-point retention ignores what happens between the two dates: a customer that churned and came back inside the period looks flat. If that matters, run the bridge monthly and sum the movements.
• Logo churn from this bridge sets how long a new customer is expected to stay, which caps the acquisition cost a channel can earn back; the CAC payback by channel playbook uses it.
• Rerun the math after the first two months with your real review time V.

Template outline

The structure to recreate in your own workspace. Field names and thresholds are starting points.

Workbook: Revenue retention bridge (one tab set per period; finance owns the definitions)

Tab definitions (signed by finance):
  ARR = month-end recurring revenue x 12, after recurring discounts
  Excluded: one-time fees, services, taxes, usage overages
  FX: one fixed rate per currency for both dates
  Past due and paused subscriptions: counted or not, written here
  Period: trailing 12 months, point to point (start date, end date)

Tab arr_snapshot (billing, one row per billing account):
  billing_account_id, start_arr, end_arr, paid_before_start (yes or no), source (snapshot | invoice rebuild)

Tab customer_map (billing account to CRM account, reviewed monthly):
  billing_account_id, customer_key (parent account), crm_owner, mapping_note

Tab customers (one row per customer_key):
  start_arr = SUMIFS(arr_snapshot.start_arr, customer_key)
  end_arr = SUMIFS(arr_snapshot.end_arr, customer_key)
  movement = new | reactivation | expansion | contraction | churn | flat
  change = end_arr - start_arr

Tab bridge:
  start_arr + new + reactivation + expansion - contraction - churn = end_arr (must close to the dollar)
  GRR = (start_arr - contraction - churn) / start_arr
  NRR = (start_arr + expansion - contraction - churn) / start_arr
  logo_churn = churned customers / starting customers
  tie_out = end_arr - finance reported ARR, explained if above tolerance

Worked example (fictional example data, not a benchmark):
  Customer  Start ARR   End ARR   Movement
  C-01       $120,000  $150,000   expansion $30,000
  C-02        $90,000   $90,000   flat
  C-03        $80,000   $60,000   contraction $20,000 (seat drop)
  C-04        $60,000        $0   churn $60,000
  C-05        $50,000   $70,000   expansion $20,000
  C-06        $40,000   $30,000   contraction $10,000
  C-07        $30,000        $0   churn $30,000
  C-08        $30,000   $45,000   expansion $15,000
  C-09             $0   $40,000   new
  C-10             $0   $25,000   reactivation (paid before the start date)
  C-11        $20,000   $24,000   expansion $4,000 (billing account B-11 closed, B-12 opened under the same parent)

  Start ARR $520,000 + new $40,000 + reactivation $25,000 + expansion $69,000
    - contraction $30,000 - churn $90,000 = end ARR $534,000
  GRR = ($520,000 - $30,000 - $90,000) / $520,000 = 76.9%
  NRR = ($520,000 + $69,000 - $30,000 - $90,000) / $520,000 = $469,000 / $520,000 = 90.2%
  Logo churn = 2 of 9 starting customers = 22.2%
  Without the parent roll-up: B-11 is $20,000 churn and B-12 $24,000 new, so GRR 73.1% and NRR 85.6%

LLM commentary prompt (reads the bridge table, does no math):
"You write a monthly revenue retention note for a CFO and the head of CS. Use only the numbers in the
 table below and copy them exactly. Do not calculate, round, or add numbers. Write four lines: the bridge
 from start to end ARR; GRR and NRR with their formulas; the three largest movements by customer and owner;
 the open tie-out differences. End with: Definitions on the definitions tab."
Check: every number in the note must appear in the bridge or customers tab, or the note is held.

Slack to #finance-cs monthly:
"Retention {{period}}: start ARR {{start_arr}}, end ARR {{end_arr}}. GRR {{grr}}, NRR {{nrr}}, logo churn {{logo_churn}}.
 Expansion {{expansion}}, contraction {{contraction}}, churn {{churn}}, new {{new}}, reactivation {{reactivation}}.
 Bridge: {{sheet_url}}"