Measure LTV:CAC by Segment so Blended Payback Stops Hiding the Ones That Never Pay Back
The problem
Blended LTV:CAC hides that some segments never pay back while others carry the business, so budget and SDR time keep funding the wrong ones.
Run it in your LLM
One prompt for Claude or ChatGPT that turns closed-won deals and segment spend into the number a CFO asks for at budget time: which segments actually pay back, and which ones the blended LTV:CAC is hiding. Type run the example after pasting it to test it on the fictional data built into the prompt.
# LTV:CAC by Segment
You are a RevOps and FP&A lead reporting to the CFO. From a closed-won export and acquisition cost allocated by segment, compute LTV, CAC, payback months, and LTV:CAC by segment, flag segments under a stated ratio or under a minimum closed-won count, and show how much CAC would have to fall (or lifetime rise) to hit the target, with every number traceable to the export and the cost tab.
## Rules
- Use only the data in this conversation. Do not invent deals, CAC benchmarks, LTV:CAC rules of thumb, or industry averages. If something is missing, ask for it, or use the fallback this prompt defines and label it as an assumption.
- Run the calculations in code if you can. If you cannot run code, say so at the top and show the working by segment.
- Show the formula next to every headline number.
- Gross margin and expected lifetime come from finance, not from a benchmark. The target LTV:CAC and the minimum closed-won count come from the user. Do not suggest 3x as a rule.
- Read only. Do not change anything in the CRM or the budget. Report what the numbers imply and leave the spend decision to the user.
- Format: money to the nearest dollar, ratios to two decimals, months to one decimal.
## 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 closed-won deals, one row per deal, with these columns (ask the user to map theirs):
- deal_id, close_date (YYYY-MM-DD), segment, arr (first-year recurring), deal_type (New business, Expansion, or Renewal)
Where to get it: CRM closed-won opportunities. Only New business counts as an acquired customer.
2. A CSV of acquisition cost by segment for the same period:
- segment, spend, gross_margin_pct, lifetime_months
Spend is program spend plus people time allocated to that segment (ads, events, SDR time), from the ledger and payroll, not from ad-platform dashboards alone. Gross margin and expected lifetime months are finance assumptions. If the user has annual GRR G instead of lifetime, lifetime months = 1 / (1 - G^(1/12)), and label that conversion as an assumption.
3. The target LTV:CAC. There is no default; do not suggest a number.
4. The minimum closed-won count before a segment gets a ratio verdict, by default 10 (an assumption you can change).
5. The period: by default the last 12 months of close dates.
Then report a data check: rows read; rows left out and why (duplicate deal ID, not new business, closed outside the period, no ARR); deals kept; deals whose segment has no spend row (listed, left out of the segment table, not in the blended row). Ask the user to confirm before going on, unless they said "run the example".
## Step 1. LTV, CAC, and payback by segment
For each segment with spend and at least one kept new-business deal:
- n = closed-won count. Average ARR = sum of ARR / n. CAC = spend / n.
- LTV = average ARR x gross margin x (lifetime months / 12). This is gross-profit LTV over the stated life, with no expansion and no discounting.
- LTV:CAC = LTV / CAC. Payback months = CAC / (average ARR / 12 x gross margin), which is the same as spend / (new MRR x gross margin).
- Flag: "too few" when n is under the minimum; "under ratio" when LTV:CAC is below the target; both; or "clears".
- If LTV:CAC is below the target R: CAC would have to fall to LTV / R (a cut of CAC - LTV / R), or lifetime would have to rise to R x CAC x 12 / (ARR x gross margin). If the ratio already meets R, those cuts are 0.
- Say when payback months exceed the stated lifetime (the same as LTV:CAC under 1): CAC is not expected to come back inside that life.
## Step 2. Blended row
Blended CAC = total spend / total customers. Blended LTV = sum(n x LTV) / total customers. Blended LTV:CAC = total cohort LTV / total spend. Put it last so a blended number that looks close to the target can sit next to segments that never pay back.
## Step 3. Output
Start with a five-line summary:
- Headline: "Blended LTV:CAC {ratio} on {n} new customers; {k} of {segments} segments sit under the {target} target."
- Where it sits: the segments under the target, with their ratios, without arguing for a cut.
- Payback: the segments whose payback exceeds the stated lifetime.
- What would hit the target: CAC cut and extra lifetime months on each under-target segment.
- One line: "Period {start} to {end}; target {target}; {excluded} rows were left out."
Then these tables:
1. Data check
2. By segment (segment, customers, average ARR, spend, CAC, LTV, LTV:CAC, payback months, flag, CAC at the target, lifetime at the target)
3. Blended row
4. Assumptions, each labeled as an assumption, including: first-year ARR with no expansion in LTV; gross margin applied to ARR; lifetime months from finance (or from GRR as converted above); spend is fully loaded acquisition cost for that segment; history is not a forecast.
If you can create files, offer the segment table as a CSV download.
## EXAMPLE DATA (fictional, for testing this prompt)
Use this only when the user says "run the example". The company, segments, and deals are made up.
- Period: 2025-11-01 to 2026-10-31
- Target LTV:CAC 3, minimum closed-won 10 (example inputs, not benchmarks)
- Gross margin and lifetime months are on the spend tab (example inputs)
Closed-won deals:
```csv
deal_id,close_date,segment,arr,deal_type
E-1,2025-11-01,Enterprise,50000,New business
E-2,2025-12-02,Enterprise,50000,New business
E-3,2026-01-03,Enterprise,50000,New business
E-4,2026-02-04,Enterprise,50000,New business
E-5,2026-03-05,Enterprise,50000,New business
E-6,2026-04-06,Enterprise,50000,New business
E-7,2026-05-07,Enterprise,50000,New business
E-8,2026-06-08,Enterprise,50000,New business
E-9,2026-07-09,Enterprise,50000,New business
E-10,2026-08-10,Enterprise,50000,New business
E-11,2026-09-11,Enterprise,50000,New business
E-12,2026-10-12,Enterprise,50000,New business
M-1,2025-11-01,Mid-market,18000,New business
M-2,2025-12-02,Mid-market,18000,New business
M-3,2026-01-03,Mid-market,18000,New business
M-4,2026-02-04,Mid-market,18000,New business
M-5,2026-03-05,Mid-market,18000,New business
M-6,2026-04-06,Mid-market,18000,New business
M-7,2026-05-07,Mid-market,18000,New business
M-8,2026-06-08,Mid-market,18000,New business
M-9,2026-07-09,Mid-market,18000,New business
M-10,2026-08-10,Mid-market,18000,New business
M-11,2026-09-11,Mid-market,18000,New business
M-12,2026-10-12,Mid-market,18000,New business
M-13,2025-11-13,Mid-market,18000,New business
M-14,2025-12-14,Mid-market,18000,New business
M-15,2026-01-15,Mid-market,18000,New business
M-16,2026-02-16,Mid-market,18000,New business
M-17,2026-03-17,Mid-market,18000,New business
M-18,2026-04-18,Mid-market,18000,New business
M-19,2026-05-19,Mid-market,18000,New business
M-20,2026-06-20,Mid-market,18000,New business
M-21,2026-07-21,Mid-market,18000,New business
M-22,2026-08-22,Mid-market,18000,New business
M-23,2026-09-23,Mid-market,18000,New business
M-24,2026-10-24,Mid-market,18000,New business
M-25,2025-11-25,Mid-market,18000,New business
M-26,2025-12-26,Mid-market,18000,New business
M-27,2026-01-27,Mid-market,18000,New business
M-28,2026-02-01,Mid-market,18000,New business
M-29,2026-03-02,Mid-market,18000,New business
M-30,2026-04-03,Mid-market,18000,New business
M-31,2026-05-04,Mid-market,18000,New business
M-32,2026-06-05,Mid-market,18000,New business
M-33,2026-07-06,Mid-market,18000,New business
M-34,2026-08-07,Mid-market,18000,New business
M-35,2026-09-08,Mid-market,18000,New business
M-36,2026-10-09,Mid-market,18000,New business
M-37,2025-11-10,Mid-market,18000,New business
M-38,2025-12-11,Mid-market,18000,New business
M-39,2026-01-12,Mid-market,18000,New business
M-40,2026-02-13,Mid-market,18000,New business
S-1,2025-11-01,SMB,6000,New business
S-2,2025-12-02,SMB,6000,New business
S-3,2026-01-03,SMB,6000,New business
S-4,2026-02-04,SMB,6000,New business
S-5,2026-03-05,SMB,6000,New business
S-6,2026-04-06,SMB,6000,New business
S-7,2026-05-07,SMB,6000,New business
S-8,2026-06-08,SMB,6000,New business
S-9,2026-07-09,SMB,6000,New business
S-10,2026-08-10,SMB,6000,New business
S-11,2026-09-11,SMB,6000,New business
S-12,2026-10-12,SMB,6000,New business
S-13,2025-11-13,SMB,6000,New business
S-14,2025-12-14,SMB,6000,New business
S-15,2026-01-15,SMB,6000,New business
S-16,2026-02-16,SMB,6000,New business
S-17,2026-03-17,SMB,6000,New business
S-18,2026-04-18,SMB,6000,New business
S-19,2026-05-19,SMB,6000,New business
S-20,2026-06-20,SMB,6000,New business
S-21,2026-07-21,SMB,6000,New business
S-22,2026-08-22,SMB,6000,New business
S-23,2026-09-23,SMB,6000,New business
S-24,2026-10-24,SMB,6000,New business
S-25,2025-11-25,SMB,6000,New business
S-26,2025-12-26,SMB,6000,New business
S-27,2026-01-27,SMB,6000,New business
S-28,2026-02-01,SMB,6000,New business
S-29,2026-03-02,SMB,6000,New business
S-30,2026-04-03,SMB,6000,New business
S-31,2026-05-04,SMB,6000,New business
S-32,2026-06-05,SMB,6000,New business
S-33,2026-07-06,SMB,6000,New business
S-34,2026-08-07,SMB,6000,New business
S-35,2026-09-08,SMB,6000,New business
S-36,2026-10-09,SMB,6000,New business
S-37,2025-11-10,SMB,6000,New business
S-38,2025-12-11,SMB,6000,New business
S-39,2026-01-12,SMB,6000,New business
S-40,2026-02-13,SMB,6000,New business
S-41,2026-03-14,SMB,6000,New business
S-42,2026-04-15,SMB,6000,New business
S-43,2026-05-16,SMB,6000,New business
S-44,2026-06-17,SMB,6000,New business
S-45,2026-07-18,SMB,6000,New business
S-46,2026-08-19,SMB,6000,New business
S-47,2026-09-20,SMB,6000,New business
S-48,2026-10-21,SMB,6000,New business
S-49,2025-11-22,SMB,6000,New business
S-50,2025-12-23,SMB,6000,New business
S-51,2026-01-24,SMB,6000,New business
S-52,2026-02-25,SMB,6000,New business
S-53,2026-03-26,SMB,6000,New business
S-54,2026-04-27,SMB,6000,New business
S-55,2026-05-01,SMB,6000,New business
S-56,2026-06-02,SMB,6000,New business
S-57,2026-07-03,SMB,6000,New business
S-58,2026-08-04,SMB,6000,New business
S-59,2026-09-05,SMB,6000,New business
S-60,2026-10-06,SMB,6000,New business
S-61,2025-11-07,SMB,6000,New business
S-62,2025-12-08,SMB,6000,New business
S-63,2026-01-09,SMB,6000,New business
S-64,2026-02-10,SMB,6000,New business
S-65,2026-03-11,SMB,6000,New business
S-66,2026-04-12,SMB,6000,New business
S-67,2026-05-13,SMB,6000,New business
S-68,2026-06-14,SMB,6000,New business
S-69,2026-07-15,SMB,6000,New business
S-70,2026-08-16,SMB,6000,New business
S-71,2026-09-17,SMB,6000,New business
S-72,2026-10-18,SMB,6000,New business
S-73,2025-11-19,SMB,6000,New business
S-74,2025-12-20,SMB,6000,New business
S-75,2026-01-21,SMB,6000,New business
S-76,2026-02-22,SMB,6000,New business
S-77,2026-03-23,SMB,6000,New business
S-78,2026-04-24,SMB,6000,New business
S-79,2026-05-25,SMB,6000,New business
S-80,2026-06-26,SMB,6000,New business
V-1,2025-11-01,Events,30000,New business
V-2,2025-12-02,Events,30000,New business
V-3,2026-01-03,Events,30000,New business
V-4,2026-02-04,Events,30000,New business
E-1,2026-01-08,Enterprise,50000,New business
OLD-1,2025-10-15,Enterprise,50000,New business
NA-1,2026-03-12,SMB,,New business
EX-1,2026-04-20,Mid-market,18000,Expansion
```
Spend, gross margin, and lifetime by segment:
```csv
segment,spend,gross_margin_pct,lifetime_months
Enterprise,480000,80,48
Mid-market,480000,80,36
SMB,640000,75,18
Events,100000,80,24
```
How to run it
- 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.
- Paste the prompt. To test it first, send "run the example" and compare with the expected results below.
- Attach a CSV of new-business closed-won deals with deal ID, close date, segment, ARR, and deal type, plus a spend tab with segment, spend, gross margin, and lifetime months, and give the prompt your target LTV:CAC. The prompt lists the columns and where to find them.
- Read the data check first, then take the under-target segments and the CAC-cut line to the budget review. Keep the blended row; do not let it replace the segment table.
Before you paste deal and spend data
Win amounts and acquisition cost by segment are commercially sensitive. Check your company's AI and data policy, prefer a business or enterprise plan, and drop account names and contacts: the prompt only needs deal IDs, segments, dates, amounts, and cost totals. It reads the export and changes nothing in your CRM or budget.
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
- 140 rows; 136 new customers kept; left out: E-1 (duplicate deal ID), OLD-1 (closed outside the period), NA-1 (no ARR), EX-1 (expansion, not an acquisition)
- Blended LTV:CAC
- $4,380,000 / $1,700,000 = 2.58x on 136 customers (CAC $12,500)
- Under the 3.0x target
- Events 1.92x; SMB 0.84x
- SMB to hit 3.0x
- CAC would have to fall $5,750 to $2,250, or lifetime rise 46 months to 64
| Segment | Won | CAC | LTV | LTV:CAC | Payback | Flag |
|---|---|---|---|---|---|---|
| Enterprise | 12 | $40,000 | $160,000 | 4.00x | 12.0 mo | clears |
| Events | 4 | $25,000 | $48,000 | 1.92x | 12.5 mo | under ratio and too few |
| Mid-market | 40 | $12,000 | $43,200 | 3.60x | 10.0 mo | clears |
| SMB | 80 | $8,000 | $6,750 | 0.84x | 21.3 mo | under ratio |
Blended LTV:CAC is 2.58x, close to a 3.0x target, because Enterprise at 4.00x and Mid-market at 3.60x carry 80 SMB customers at 0.84x (payback 21.3 months on an 18-month life) and 4 Events customers at 1.92x. Events is also under the 10-deal floor. The blended number is not a reason to keep funding SMB at $8,000 CAC.
LTV:CAC by segment
Enter CAC, average ARR, gross margin, expected lifetime months, and closed-won count for each segment, plus your target LTV:CAC. LTV = ARR x gross margin x (lifetime / 12). Prefilled with the fictional example.
Segments
- Blended LTV:CAC
- 2.58x
- Cohort LTV $4,380,000 / spend $1,700,000
- Blended CAC
- $12,500
- 136 new customers
- Blended LTV
- $32,206
- Payback 13.5 months of gross margin
- Segments under the target
- 2
- Events 1.92x, SMB 0.84x
- CAC cut to hit the target
- $14,750
- Events $9,000 to $16,000; SMB $5,750 to $2,250
- Extra lifetime months to hit the target
- Events 13.5, SMB 46.0
- R x CAC x 12 / (ARR x gross margin) minus the stated life
| Segment | Won | CAC | LTV | LTV:CAC | Payback | Flag |
|---|---|---|---|---|---|---|
| Enterprise | 12 | $40,000 | $160,000 | 4.00x | 12.0 mo | Clears target |
| Events | 4 | $25,000 | $48,000 | 1.92x | 12.5 mo | Under target, too few deals |
| Mid-market | 40 | $12,000 | $43,200 | 3.60x | 10.0 mo | Clears target |
| SMB | 80 | $8,000 | $6,750 | 0.84x | 21.3 mo | Under target |
LTV uses the stated lifetime with no expansion and no discounting. Payback longer than the stated life means CAC is not expected to come back inside that life (the same as LTV:CAC under 1x). A blended ratio close to the target can still hide a segment that never pays back. The target is yours; this page does not treat 3x as a rule.
Implementation steps
- 1
Write the definitions down with finance before pulling data: a new customer is a closed-won new-business deal in the period (expansions and renewals are not acquisitions); ARR is first-year recurring revenue without one-time fees; CAC = fully loaded acquisition spend for that segment / closed-won count; LTV = average ARR x gross margin x (lifetime months / 12), with no expansion and no discounting; payback months = CAC / (ARR / 12 x gross margin). The target LTV:CAC is yours; do not import a 3x rule
- 2
Export closed-won deals for the last 12 months (an assumption you can change) with deal ID, close date, segment, ARR, and deal type. Drop duplicates, expansions, renewals, deals outside the period, and deals with no ARR, and list each dropped row. Get spend, gross margin, and expected lifetime months by segment from the ledger and payroll, not from ad-platform dashboards alone
- 3
If finance tracks annual GRR instead of lifetime, convert once and label it: lifetime months = 1 / (1 - GRR^(1/12)). Use the same GRR definition as the cohort retention playbook
- 4
For each segment compute n, average ARR, CAC, LTV, LTV:CAC, and payback months. Flag under the target ratio, under the minimum closed-won count (10 in the fictional example, an assumption), both, or clears. Say when payback exceeds the stated lifetime, which is the same as LTV:CAC under 1
- 5
For every segment under the target R, show the CAC that would hit it (LTV / R) and the lifetime that would hit it (R x CAC x 12 / (ARR x GM)). Those are sizing numbers, not a forecast of what more or less spend would produce
- 6
Put a blended row last: total cohort LTV / total spend. A blended ratio close to the target is not a reason to keep funding a segment whose own ratio is under 1
- 7
Run it with the copy-paste prompt on this page in Claude or ChatGPT, or with formulas in the template, check spend against the ledger, and use the calculator on this page to test other targets, margins, and lifetimes. Share the under-target segments with the CFO and the CRO; keep the CAC-cut line as a sizing device, not a hiring plan
- 8
Rerun each quarter on the same definitions and track LTV:CAC, payback, and CAC by segment so a mix shift or a cost change shows up in the next run
Template outline
The structure to recreate in your own workspace. Field names and thresholds are starting points.
Google Sheet: LTV:CAC by segment FYxx (Finance owns the cost tab; RevOps maintains the export)
Tab inputs: period_start, period_end, target_ratio (your target, not a benchmark), min_closed_won (default 10)
Tab deals (one row per closed-won deal): deal_id, close_date, segment, arr, deal_type
kept = deal_type = "New business" AND close_date in period AND arr > 0 AND not a duplicate
Tab spend (finance, same period): segment, spend, gm, lifetime_months
lifetime_from_grr = 1 / (1 - grr^(1/12)) // only if finance gives annual GRR instead of lifetime
Tab by_segment:
n = COUNTIFS(kept, segment)
avg_arr = SUMIFS(arr, kept, segment) / n
cac = spend / n
ltv = avg_arr x gm x lifetime_months / 12
ratio = ltv / cac
payback = cac / (avg_arr / 12 x gm)
cac_at_target = ltv / target_ratio
lifetime_at_target = target_ratio x cac x 12 / (avg_arr x gm)
flag = IF(n < min_closed_won, IF(ratio < target_ratio, "under ratio and too few", "too few"),
IF(ratio < target_ratio, "under ratio", "clears"))
Tab blended:
blended_cac = SUM(spend) / SUM(n)
blended_ltv = SUMPRODUCT(n, ltv) / SUM(n)
blended_ratio = SUMPRODUCT(n, ltv) / SUM(spend)
Worked example (example data, fictional): 12 months from 2025-11-01, target 3.0x, min 10 closed-won
140 rows read, 136 kept. Left out: E-1 listed twice; OLD-1 closed 2025-10-15; NA-1 no ARR; EX-1 expansion.
Enterprise: 12 x $50,000, spend $480,000, GM 80%, life 48 months
CAC $40,000. LTV $50,000 x 0.80 x 4 = $160,000. Ratio 4.00. Payback 12.0 months. Clears.
Mid-market: 40 x $18,000, spend $480,000, GM 80%, life 36
CAC $12,000. LTV $18,000 x 0.80 x 3 = $43,200. Ratio 3.60. Payback 10.0 months. Clears.
SMB: 80 x $6,000, spend $640,000, GM 75%, life 18
CAC $8,000. LTV $6,000 x 0.75 x 1.5 = $6,750. Ratio 0.84. Payback 21.3 months, longer than the 18-month life.
CAC at 3.0x = $6,750 / 3 = $2,250 (cut $5,750). Lifetime at 3.0x = 64 months (add 46).
Events: 4 x $30,000, spend $100,000, GM 80%, life 24. Too few (4 < 10) and under ratio.
CAC $25,000. LTV $48,000. Ratio 1.92. Payback 12.5 months.
CAC at 3.0x = $16,000 (cut $9,000). Lifetime at 3.0x = 37.5 months (add 13.5).
Blended: spend $1,700,000 on 136 logos. Cohort LTV $4,380,000. CAC $12,500. LTV $32,206. Ratio 2.58x.
Calculator on this page, prefilled with those four rows, target 3.0, min 10. Every money line above matches.
Slack to #finance-revenue after each quarter closes:
"LTV:CAC {{period}}: blended {{blended_ratio}}x on {{n}} new customers. Under the {{target}} target:
{{under_list}}. SMB-style never-pays-within-life: {{never_list}}. Sheet: {{sheet_url}}"
Tab history: quarter, segment, n, cac, ltv, ratio, payback, flagCost, 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 measure LTV:CAC by segment by hand (export closed-won, allocate spend, compute LTV and payback, write it up)6AssumptionTime the next quarterly budget review.
- VHours a quarter with the prompt or template (export, review the data check and the results)1AssumptionTime the first two quarterly 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; the CRM, Google Sheets, and Slack are usually already paid for.
Formulas
- Time saved per quarter
T - V - 6 - 1 = 5 hours
- Time saved per week
(T - V) / 13 - 5 / 13 = 0.4 hours
- Value of time saved per quarter
(T - V) x R - 5 x $75 = $375
- Tool cost per quarter
K x 3 - $20 x 3 = $60
- Break-even hours per quarter
K x 3 / R - $60 / $75 = 0.8 hours, so the run covers its tool cost once it saves 48 minutes a quarter
- Blended vs SMB LTV:CAC (fictional example data)
sum(n x LTV) / sum(spend); SMB LTV / SMB CAC - $4,380,000 / $1,700,000 = 2.58x blended; SMB $6,750 / $8,000 = 0.84x
- The time math counts analyst time only. Stopping spend on a segment that never pays back can be a much larger number, but that decision is yours, so it is not added to the time savings.
- History is not a forecast: a price change, a new motion, or a mix shift can move ARR, CAC, and lifetime. Recheck each quarter before cutting a channel.
- Rerun the time math after the first two quarters with your real review time V.
Common questions
- How do you calculate LTV:CAC by segment?
- CAC = segment spend / new customers. LTV = average ARR x gross margin x (lifetime months / 12). LTV:CAC is LTV / CAC, and payback months = CAC / (ARR / 12 x gross margin). In the fictional example SMB CAC is $8,000 and LTV is $6,750, so the ratio is 0.84x and payback is 21.3 months.
- Why is blended LTV:CAC misleading?
- It averages segments that pay back with segments that do not. In the fictional example blended LTV:CAC is 2.58x, close to a 3.0x target, while SMB sits at 0.84x on 80 of 136 customers and Events at 1.92x with only four deals. Budget decisions belong at the segment row, not the blended one.
- How many months does CAC take to pay back?
- Payback months = CAC / monthly gross profit per customer. In the fictional example Enterprise pays back in 12.0 months ($40,000 / $3,333) and SMB in 21.3 months, which is longer than its 18-month stated life, so that CAC is not expected to come back inside the life finance is using.
- How much would CAC have to fall to hit the target?
- CAC at the target = LTV / target ratio. In the fictional example SMB LTV is $6,750, so a 3.0x target needs CAC of $2,250, a $5,750 cut, or a lifetime of 64 months instead of 18. Those are sizing numbers, not a forecast of what a cheaper channel would do.
- What if a segment has too few closed-won deals?
- Give it no ratio verdict until it reaches the minimum count you set (10 in the fictional example). Events has four customers and a 1.92x ratio; the ratio is shown, but the flag is under target and too few, because one more deal moves it a lot.
Keep going
Related playbooks
- Finance/FP&AIntermediate
Calculate CAC Payback by Acquisition Channel and Find the Ones That Never Pay Back
CAC is usually reported as one blended number, or per channel from ad spend alone, so a channel that costs more in SDR salaries and event fees than its customers will ever earn back in gross margin looks fine in the average, and finance only learns it when the budget is already spent.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.5 hrs/week - RevOpsIntermediate
Measure Win Rate by Lead Source, Not One Blended Number
Ops leaders report one blended win rate, which hides that outbound, inbound demo requests, partners, and events convert and close very differently, so budget and SDR time keep going to the channels that look fine on average instead of the ones that turn pipeline into revenue.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPTEstimate: 0.5 hrs/week - Finance/FP&AIntermediate
Measure Logo Churn and Gross Retention by Acquisition Cohort
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.
StripeHubSpot or SalesforceGoogle Sheets+2Estimate: 0.3 hrs/week - Finance/FP&AIntermediate
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.
StripeHubSpot or SalesforceGoogle Sheets+2Estimate: 1 hrs/week - Finance/FP&AIntermediate
Compute the SaaS Magic Number so Blended CAC Stops Answering "Are We Efficient?"
Boards ask "are we efficient?" and finance answers with a blended CAC or burn multiple that hides whether last quarter's S&M actually produced enough net-new ARR to justify the spend.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.3 hrs/week - Finance/FP&AIntermediate
Compute Rule of 40 so Growth and Margin Are One Board Number, Not Two Stories
Growth-at-all-costs and margin-at-all-costs both look fine in isolation; Rule of 40 is the board check that growth % plus profit margin % should clear about 40 for a healthy SaaS tradeoff, and most teams do not have a shareable calculator for scenarios.
Google SheetsClaude or ChatGPTSlackEstimate: 0.2 hrs/week - Finance/FP&AIntermediate
Compute the SaaS Quick Ratio so Growth Quality Is Visible, Not the Balance-Sheet Quick Ratio
Boards hear "we grew" without seeing whether new-plus-expansion ARR outran churn-plus-contraction, and some packs paste the balance-sheet quick ratio (current assets minus inventory, over current liabilities) onto a SaaS slide by mistake.
Google SheetsClaude or ChatGPTSlackEstimate: 0.2 hrs/week - Finance/FP&AIntermediate
Compute the SaaS Burn Multiple so the Board Sees Dollars of Net Burn per Dollar of Net-New ARR
Boards ask how efficiently cash is turning into growth, and finance answers with a cash-down number that mixes a fundraise into the story, or with a blended CAC that never shows how many dollars of net burn it took to add a dollar of net-new ARR.
Google SheetsClaude or ChatGPTSlackEstimate: 0.3 hrs/week - Finance/FP&AIntermediate
Cost per Opportunity by Channel: Spend, SQOs, and Share of ACV
Channel spend is reviewed as a blended CAC, or as cost per click, so a source that produces cheap SQOs that never close looks efficient, a source with a high win rate and ACV looks expensive, and finance and RevOps argue about the budget without a shared cost per SQO and cost per expected closed-won deal.
HubSpot or SalesforceGoogle SheetsClaude or ChatGPT+1Estimate: 0.4 hrs/week
Need help implementing a playbook?
Our team builds custom Clay workflows and AI automations for revenue teams.
Get it built for you