GTM Playbooks
Get it built
Finance/FP&AIntermediateLast reviewed Run it in your LLM

Measure Logo Churn and Gross Retention by Acquisition Cohort

The problem

Net revenue retention is usually one period number, so nobody can see whether last winter's cohort is still paying, how much of its starting ARR has churned, and whether expansion on the logos that stayed is hiding a logo problem.

Copy, paste, run

Run it in your LLM

One prompt for Claude or ChatGPT that turns a monthly ARR ledger into a vintage curve: whether last winter's customers are still paying, how much of their starting ARR has churned, and whether expansion is masking it. Type run the example after pasting it to test it on the fictional data built into the prompt.

Download .mdSKILL.mdExample CSV
# Gross Retention and Churn by Cohort

You are a finance and RevOps lead reporting to the CFO. From a monthly ARR ledger, pick one acquisition cohort (customers whose first paid month is the same), and measure logo retention, gross revenue retention, net revenue retention, dollars churned, and the constant monthly logo churn those ending rates imply, with every number traceable to the ledger.

## Rules

- Use only the data in this conversation. Do not invent customers, retention benchmarks, or industry averages. If something is missing, ask for it.
- Run the calculations in code if you can. If you cannot, say so at the top and show the working customer by customer.
- Show the formula next to every headline number.
- Gross retention never counts expansion: remaining ARR at an age is the sum of min(ARR at that age, starting ARR) over the cohort. Expansion is the rest, and NRR uses remaining + expansion.
- Read only. Do not change anything in billing or the CRM. Do not recommend action on any individual customer. Report what the numbers imply.
- Format: retention as percentages with one decimal, money to the nearest dollar.

## Step 0. Inputs

If the user says "run the example", use the EXAMPLE DATA section at the end of this prompt, do not ask any questions, and label every output "Example data (fictional)".

Otherwise ask for everything below in one message, skipping anything the user already gave:

1. A CSV of monthly ARR, one row per customer per month, with these columns:
   - customer_id, month (YYYY-MM), arr (recurring ARR in force that month, after recurring discounts, before one-time fees)
   Where to get it: a month-end recurring revenue snapshot per customer from Stripe or the billing system. Use the same ARR definition as the net revenue retention bridge playbook.
2. The cohort month: customers whose first month with ARR above zero is this YYYY-MM. There is no default; do not pick a month for the user.
3. The ages to report: by default 3, 6, and 12 months if those months exist in the ledger; otherwise every age the ledger has.

Then report a data check: rows read; rows left out and why (duplicate customer-month, month not YYYY-MM, no ARR); customers in the cohort; starting logos and starting ARR. Ask the user to confirm before going on, unless they said "run the example".

## Step 1. Starting book

A customer is in the cohort when their first month with ARR above zero equals the cohort month. Starting logos N0 = that count. Starting ARR A0 = sum of those first-month ARR amounts. Customers who first paid in any other month are out of this run (list how many).

## Step 2. At each age

Age t is t months after the cohort month. For each customer, ARR at age t is the ledger amount for that month, or 0 if the month is present at 0, and missing if the month was not exported (do not treat missing as churn unless the user says the export is complete; in the example the export is complete at 0, 3, 6, and 12).

- Logos remaining Nt = customers with ARR > 0 at age t.
- Logo retention = Nt / N0. Logo churn = 1 - Nt / N0.
- Remaining starting ARR At = sum of min(ARR at t, starting ARR) over the cohort. GRR = At / A0. Dollars churned = A0 - At.
- Expansion Et = sum of max(0, ARR at t - starting ARR). NRR = (At + Et) / A0.
- Implied constant monthly logo churn = 1 - (Nt / N0)^(1 / t) when t > 0. This is the monthly rate that would produce the observed logo retention if churn were constant. It is a description of the curve, not a forecast.

## Step 3. Output

Start with a five-line summary:

- Headline: "Cohort {month}: {N0} logos, {A0} starting ARR; at month 12 (or the latest age) logo retention {logo}, GRR {grr}, NRR {nrr}."
- Churn: dollars churned at that age, and the implied monthly logo churn.
- Shape: logo vs GRR vs NRR at 3, 6, and 12 (or whatever ages exist), and whether expansion is masking logo or dollar churn.
- One line: "{excluded} rows left out; {other} customers first paid in a different month."
- One line: "Gross retention leaves expansion out; the NRR bridge playbook is the period view, this is the vintage view."

Then these tables:

1. Data check
2. By age (age, logos remaining, logo retention, logo churn, remaining starting ARR, GRR, expansion, NRR, dollars churned, implied monthly logo churn)
3. Assumptions, each labeled as an assumption, including: ARR definition; missing months; implied monthly churn assumes a constant rate; history is not a forecast.

If you can create files, offer the age table as a CSV download.

## EXAMPLE DATA (fictional, for testing this prompt)

Use this only when the user says "run the example". The company and customers are made up.

- Cohort month: 2025-01
- Ages present: 0, 3, 6, 12

```csv
customer_id,month,arr
C-01,2025-01,12000
C-02,2025-01,12000
C-03,2025-01,12000
C-04,2025-01,12000
C-05,2025-01,12000
C-06,2025-01,12000
C-07,2025-01,12000
C-08,2025-01,12000
C-09,2025-01,12000
C-10,2025-01,12000
C-01,2025-04,12000
C-02,2025-04,12000
C-03,2025-04,12000
C-04,2025-04,12000
C-05,2025-04,12000
C-06,2025-04,12000
C-07,2025-04,12000
C-08,2025-04,12000
C-09,2025-04,12000
C-10,2025-04,0
C-01,2025-07,14400
C-02,2025-07,12000
C-03,2025-07,12000
C-04,2025-07,12000
C-05,2025-07,12000
C-06,2025-07,12000
C-07,2025-07,12000
C-08,2025-07,12000
C-09,2025-07,0
C-10,2025-07,0
C-01,2026-01,18000
C-02,2026-01,12000
C-03,2026-01,12000
C-04,2026-01,12000
C-05,2026-01,12000
C-06,2026-01,12000
C-07,2026-01,12000
C-08,2026-01,0
C-09,2026-01,0
C-10,2026-01,0
C-01,2025-01,12000
C-11,2025-02,9000
C-12,2025-13,12000
C-13,2025-01,
```

How to run it

  1. Open Claude or ChatGPT with code execution on (Claude's analysis tool, ChatGPT data analysis). Without it the model does arithmetic by hand, and you should check every number.
  2. Paste the prompt. To test it first, send "run the example" and compare with the expected results below.
  3. Attach a CSV of monthly ARR with customer ID, month (YYYY-MM), and ARR, and tell the prompt the cohort month (first paid month). The prompt lists the columns and where to find them.
  4. Read the data check first, then take month-12 GRR, NRR, and implied monthly logo churn to the finance review next to the NRR bridge for the same period.

Before you paste billing data

Customer-level ARR is commercially sensitive. Check your company's AI and data policy, prefer a business or enterprise plan, and drop names: the prompt only needs customer IDs, months, and amounts. It reads the export and changes nothing in billing or the CRM.

Expected results on the example data

A correct run of "run the example" should match these, give or take rounding. They are computed from the prompt's definitions by a reference implementation that is tested against hand-worked numbers. The data is fictional.

Data check
44 rows; 10 logos, $120,000 starting ARR; left out: C-01 2025-01 (duplicate customer-month), C-12 2025-13 (month is not YYYY-MM), C-13 2025-01 (no ARR)
Month 3
9 logos, GRR 90.0%, logo churn 10.0%
Month 6
8 logos, GRR 80.0%, NRR 82.0%, expansion $2,400
Month 12
7 logos, GRR 70.0%, NRR 75.0%, $36,000 churned
Expected retention by cohort age
AgeLogosLogo retentionGRRNRRImplied monthly churn
3990.0%90.0%90.0%3.5%
6880.0%80.0%82.0%3.7%
12770.0%70.0%75.0%2.9%

Ten January 2025 customers start at $120,000. By month 12 seven are still paying, GRR is 70.0% ($36,000 of starting ARR gone), and NRR is 75.0% because $6,000 of expansion sits on C-01. Implied monthly logo churn of 2.9% describes that curve if churn were constant; it is not a forecast for the next vintage.

Logo, gross, and net retention by cohort age

Enter starting logos and starting ARR, then remaining logos and remaining starting ARR (expansion stripped) at each age. Expansion is a separate input and only affects NRR. Prefilled with the fictional example.

At each age

Logo retention at month 12
70.0%
7 of 10 logos still paying
Gross revenue retention
70.0%
$36,000 of starting ARR gone
Net revenue retention
75.0%
$6,000 expansion on top of remaining starting ARR
Implied monthly logo churn
2.9%
1 - (logo retention)^(1 / age). A description of the curve, not a forecast.
Starting ARR
$120,000
10 logos
Remaining starting ARR
$84,000
GRR x starting ARR at month 12
Retention by cohort age
AgeLogosLogo retentionGRRNRRImplied monthly churn
Month 3990.0%90.0%90.0%3.5%
Month 6880.0%80.0%82.0%3.7%
Month 12770.0%70.0%75.0%2.9%

Remaining ARR is expansion-stripped: each customer counts at most their starting ARR, so GRR cannot go above 100%. Expansion sits in NRR only. Implied monthly logo churn is the constant rate that would produce the observed logo retention; real churn is rarely constant. The NRR bridge playbook is the period view; this is the vintage view.

Implementation steps

  1. 1

    Write the definitions down before pulling data, the same ARR definition as the net revenue retention bridge: ARR = month-end recurring revenue x 12 after recurring discounts; leave out one-time fees; a customer is in a cohort when their first month with ARR above zero is that month; age is months since that cohort month; gross remaining ARR at an age is the sum of min(ARR at that age, starting ARR), so expansion cannot lift GRR above 100%

  2. 2

    Export a monthly ARR ledger, one row per customer per month, with customer ID, month (YYYY-MM), and ARR, from month-end billing snapshots. Drop duplicate customer-months, months that are not YYYY-MM, and rows with no ARR, and list each dropped row

  3. 3

    Pick one cohort month (there is no default) and keep only customers whose first paid month is that month. Starting logos N0 and starting ARR A0 are the count and the sum of those first-month amounts. Count how many customers first paid in a different month so the filter is visible

  4. 4

    At each age the ledger has (by default 3, 6, and 12 months) compute logos remaining, logo retention Nt / N0, remaining starting ARR, GRR, dollars churned A0 - At, expansion (ARR above each customer's start), and NRR = (At + expansion) / A0. Implied monthly logo churn = 1 - (Nt / N0)^(1 / t), the constant monthly rate that would produce the observed logo retention; label it as a description of the curve, not a forecast

  5. 5

    Read GRR next to logo retention: if GRR is below logo retention, the logos that stayed also shrank (seats or price); if NRR is well above GRR, expansion on the remaining logos is masking churn. Neither comparison is a benchmark, it is a description of this cohort

  6. 6

    Take the month-12 (or latest) GRR, NRR, and implied monthly logo churn to the finance and CS review with the NRR bridge for the same period, so the vintage view and the period view sit next to each other. Keep the customer list for CS, not for a public slide

  7. 7

    Run it with the copy-paste prompt on this page in Claude or ChatGPT, or with formulas in the template, check starting logos against billing, and use the calculator on this page to test other ages and expansion amounts

  8. 8

    Rerun when each new month closes, adding the new age to the curve, and start a new tab for the next cohort month so winter and summer vintages stay separate

Template outline

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

Google Sheet: Cohort retention FYxx (Finance owns it)

Tab inputs: cohort_month (YYYY-MM), arr_definition (same as the NRR bridge)

Tab ledger (one row per customer per month): customer_id, month, arr
  first_paid_month = MINIFS(month, customer_id, c, arr, ">0")
  in_cohort = first_paid_month = cohort_month
  age = months between cohort_month and month

Tab starting:
  N0 = COUNT of in_cohort customers
  A0 = SUM of first-month ARR for those customers

Tab by_age:
  Nt = COUNTIFS(in_cohort, ARR at age t > 0)
  At = SUM of MIN(ARR at t, starting ARR) over the cohort
  Et = SUM of MAX(0, ARR at t - starting ARR)
  logo_retention = Nt / N0
  GRR = At / A0
  NRR = (At + Et) / A0
  implied_monthly_logo_churn = 1 - (Nt / N0)^(1 / t)

Worked example (example data, fictional): January 2025 cohort, 10 customers x $12,000 = $120,000 starting ARR
  44 rows read. Left out: C-01 2025-01 listed twice; C-12 month 2025-13; C-13 no ARR.
    C-11 first paid February 2025, not this cohort.
  Age 3 (2025-04): C-10 at $0. 9 logos, remaining $108,000, GRR 90.0%, logo churn 10.0%,
    implied monthly 1 - 0.9^(1/3) = 3.5%
  Age 6 (2025-07): C-09 at $0; C-01 at $14,400. 8 logos, remaining $96,000 (C-01 capped at $12,000),
    expansion $2,400, GRR 80.0%, NRR 82.0%
  Age 12 (2026-01): C-08 at $0; C-01 at $18,000. 7 logos, remaining $84,000, expansion $6,000,
    GRR 70.0%, NRR 75.0%, logo retention 70.0%, dollars churned $36,000,
    implied monthly 1 - 0.7^(1/12) = 2.9%

Slack to #finance after each month closes:
"Cohort {{cohort_month}} at month {{age}}: {{logos}} of {{starting_logos}} logos still paying
 ({{logo_retention}}); GRR {{grr}}; NRR {{nrr}}; {{churned}} of starting ARR gone.
 Implied monthly logo churn {{monthly_churn}}. Sheet: {{sheet_url}}"

Tab history: cohort_month, age, logos, GRR, NRR, implied monthly churn

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 (all assumptions)

Replace each value with your own figure.

  • THours a quarter to build one cohort curve by hand (pull monthly ARR, tag first paid month, score each age, write it up)5AssumptionTime the next quarter-end retention review.
  • VHours a quarter with the prompt or template (export, review the data check and the results)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; billing exports, Google Sheets, and Slack are usually already paid for.

Formulas

Time saved per quarterT - V
5 - 1 = 4 hours
Time saved per week(T - V) / 13
4 / 13 = 0.3 hours
Value of time saved per quarter(T - V) x R
4 x $75 = $300
Tool cost per quarterK x 3
$20 x 3 = $60
Break-even hours per quarterK x 3 / R
$60 / $75 = 0.8 hours, so the run covers its tool cost once it saves 48 minutes a quarter
Month-12 GRR (fictional example data)sum of min(ARR at month 12, starting ARR) / starting ARR
$84,000 / $120,000 = 70.0%; NRR ($84,000 + $6,000) / $120,000 = 75.0%
  • The time math counts analyst time only. What a 70% GRR cohort is worth depends on how you replace or expand it, so it is not added to the time savings.
  • A cohort curve is history, not a forecast: the next vintage can churn faster. Recheck each new month before using implied monthly churn in a plan.
  • Rerun the time math after the first two months with your real review time V.

Common questions

How do you measure gross retention for a cohort?
Pick customers whose first paid month is the cohort month. At each age, remaining starting ARR is the sum of min(ARR now, starting ARR) over those customers, and GRR is that sum divided by starting ARR, so expansion cannot lift it above 100%. In the fictional example the January 2025 cohort starts at $120,000 and has $84,000 of that left at month 12, GRR 70.0%.
What is the difference between logo churn and revenue churn?
Logo churn is the share of starting customers no longer paying; revenue churn (1 - GRR) is the share of starting ARR gone, including seat drops on customers who stayed. In the fictional example at month 12 both are 30% because the seven remaining logos are still at their $12,000 start except for expansion, which GRR ignores.
How can NRR look healthy while logos are leaving?
Expansion on the customers who stayed is added only in NRR. In the fictional example month-12 GRR is 70.0% and NRR is 75.0% because $6,000 of expansion sits on top of $84,000 remaining starting ARR. Read both numbers; NRR alone hides the seven of ten logos still paying.
What is implied monthly logo churn?
It is 1 - (logo retention)^(1 / age in months), the constant monthly rate that would produce the observed retention if churn were even across the year. In the fictional example 70% logo retention at month 12 implies 2.9% a month. Real churn is rarely constant, so treat it as a description of the curve, not a forecast.
How is a cohort curve different from the NRR bridge?
The NRR bridge is a period view: every customer who had ARR at the start of the window, regardless of when they first paid. A cohort curve is a vintage view: only the customers who started in one month, followed as they age. Run both. The bridge explains this year's number; the curve tells you whether last winter's customers are still there.

Keep going

Related playbooks

View all Finance/FP&A

Need help implementing a playbook?

Our team builds custom Clay workflows and AI automations for revenue teams.

Get it built for you