--- name: net-revenue-retention-bridge description: "Build a revenue retention bridge from billing data: start to end ARR by new, expansion, contraction, and churn, with GRR, NRR, and a parent roll-up. Use when net revenue retention is often one number pulled from a dashboard with no bridge behind it, so nobody can say how much came from expansion, how much leaked to seat drops and downgrades, and how much was lost to churn, and a customer that moved to a new billing account shows up as churn plus a new logo." --- # Build a Net and Gross Revenue Retention Bridge from Billing Data Net revenue retention is often one number pulled from a dashboard with no bridge behind it, so nobody can say how much came from expansion, how much leaked to seat drops and downgrades, and how much was lost to churn, and a customer that moved to a new billing account shows up as churn plus a new logo. ## When to use this skill - The user wants to set up or run this workflow: "Build a Net and Gross Revenue Retention Bridge from Billing Data". - The user describes this problem: Net revenue retention is often one number pulled from a dashboard with no bridge behind it, so nobody can say how much came from expansion, how much leaked to seat drops and downgrades, and how much was lost to churn, and a customer that moved to a new billing account shows up as churn plus a new logo. ## At a glance - Category: Finance/FP&A - Difficulty: Intermediate - Time saved: 1 hours per week. This figure is an estimate, not a measured result. ## Tools Options separated by "or" are alternatives; use whichever the user already has. - Stripe - HubSpot or Salesforce - Google Sheets - Claude or ChatGPT - Slack ## Steps 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. 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. 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. 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. 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. 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. 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 ## Cost, time saved, and break-even 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 (every value is an assumption; replace it with your own figure) - T = 5 (assumption): Hours a month to build the bridge by hand (exports, account mapping, classification, tie-out, write-up). Time the next month-end retention report from export to sent summary. - V = 1 (assumption): Hours a month with the template (refresh the snapshot, review the mapping and tie-out, check the commentary). Time the first two monthly runs. - R = $75 (assumption): Loaded hourly cost of the FP&A or RevOps analyst. Salary plus benefits, divided by working hours. - K = $20 (assumption): Monthly tool cost for the LLM plan. Your 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 month: T - 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 month: K / 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% ### Notes - 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 Field names, thresholds, and message text are starting points to adapt. ~~~text 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}}" ~~~ ## Guardrails - Work only in the user's own accounts and workspaces, with their credentials. - Confirm with the user before any step that writes to a CRM, sends email or messages, spends credits, or moves money. - Treat every hours, cost, and threshold figure as a starting assumption and replace it with the user's numbers. - Do not invent data, prices, or results. If a vendor price matters, check the vendor's current pricing page. ## Source https://gtmplaybooks.io/playbooks/net-revenue-retention-bridge/