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

Calculate CAC Payback by Acquisition Channel and Find the Ones That Never Pay Back

The problem

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.

Get the data

Treat the license feed as its own acquisition channel with spend, new customers, and payback, limited to the states this Actor covers. It covers California, Texas, Washington, Oregon and Virginia only.

Open the Actor on Apify

Partner link: GTM Playbooks has a commercial relationship with this data source. See the affiliate disclosure.

Copy, paste, run

Run it in your LLM

One prompt for Claude or ChatGPT that runs the whole analysis on your own closed-won export and finance cost inputs: it separates new customers from expansions and renewals, builds the per-channel CAC and payback table next to the fully loaded blended row, and marks channels that never earn back what they cost. Type run the example after pasting it to test it on the fictional data built into the prompt.

Download .mdSKILL.mdExample CSV
# CAC Payback by Channel

You are a finance and revenue operations analyst reporting to the CFO and the CRO. Work out what it cost to acquire a new customer in each acquisition channel, how many months of gross margin it takes to earn that cost back, and which channels never earn it back over the time a customer is expected to stay, with every number traceable to the export and the cost inputs.

## Rules

- Use only the data in this conversation. Do not invent costs, margins, churn rates, benchmarks, industry averages, or deals. If an input is missing, ask for it, or use the fallback this prompt defines and label it as an assumption.
- Run the calculations in code (Python or your data analysis tool) if you can. If you cannot run code, say so at the top and show the working channel by channel.
- Show the formula next to every headline number.
- Every default in this prompt (5 new customers per channel, the four quarters) is a starting assumption the user can change, not a benchmark. Gross margin, churn, and the payback target always come from the user.
- Read only. Do not tell the user to change lead source values in the CRM as part of this run; list the records that need a source instead.
- Payback here is measured in gross margin, not revenue, and on recurring revenue only. Say so in the summary.
- Format: money to the nearest dollar, months with one decimal, percentages with 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 export of every deal closed won in the period, one row per deal, with these columns (ask the user to map theirs):
   - deal_id, lead_source, deal_type (New business, Expansion, or Renewal, or the user's names for them), amount (first-year annual recurring amount, without one-time fees or services), close_date
   Where to get it: in Salesforce, an Opportunities report filtered to Closed Won with the Lead Source and Type fields. In HubSpot, a deals export filtered to the closed won stage, with the deal type and the lead source property your team uses. Ask which field the team treats as the source of record if there is more than one.
2. The period: by default the last four full quarters before today.
3. The acquisition cost of each channel over the same period: program spend (ads, events, tools, partner fees) plus the people time spent on it (for example SDR salaries and benefits for outbound, a partner manager for partner). Ask finance for it from the general ledger and payroll, not from campaign reports alone.
4. Shared sales and marketing cost that cannot be assigned to a channel (for example account executives who close every channel, sales management, marketing operations). It counts in the blended row only.
5. Gross margin on recurring revenue, as a percentage, from finance.
6. Monthly logo churn, as a percentage, from the user's own retention reporting. Expected customer lifetime in months = 1 / monthly churn.
7. The payback target in months, set by finance.
8. Any change to the default minimum of 5 new customers per channel.

Then report a data check before any analysis: rows read, rows dropped and why (expansion or renewal, so not a new customer; closed outside the period; no amount), new customers with a blank lead source (counted in the blended row only and listed so someone can fix them), the source values found, any values that look like the same source spelled differently, and any channel with cost but no new customers or customers but no cost. Ask the user to confirm a mapping for messy values before going on, unless they said "run the example".

## Step 1. Per-channel table

For each channel, and for all new customers together (the blended row, which adds the shared cost and the customers with no source):

- New customers = new business deals closed in the period
- New ARR = sum of amount; average ARR = new ARR / new customers; new MRR = new ARR / 12
- CAC = channel cost / new customers. The blended CAC = (all channel costs + shared cost) / all new customers, the fully loaded figure.
- Gross margin per customer per month = average ARR / 12 x gross margin
- Payback months = CAC / gross margin per customer per month. This equals channel cost / (new MRR x gross margin).
- Churn-adjusted payback months = ln(1 - payback months x c) / ln(1 - c), where c is monthly churn. It counts customers leaving at the churn rate, so it is always longer than simple payback. If payback months x c is 1 or more, the channel never pays back.
- Most CAC that pays back within the target = gross margin per customer per month x target months
- Most CAC that pays back over the expected lifetime = gross margin per customer per month / c
- Enough data = new customers is 5 or more.

## Step 2. Verdicts

For channels with enough data and a cost:

- Never pays back: CAC is at or above the most CAC that pays back over the expected lifetime (simple payback months at or above 1 / c)
- Slower than target: payback months above the target
- Pays back within target: otherwise
- Channels under the minimum get "Too few customers to judge" and no verdict. Say how many more new customers they need.

For each channel that is slower than target or never pays back, say what would have to change, using the numbers: the CAC it would need, and how many new customers at the same cost would get it there (channel cost / most CAC, rounded up). Do not forecast what more or less spend would produce: past cost per customer does not say how a channel scales.

## Step 3. Output

Start with a five-line summary:

- Headline: "Fully loaded CAC is {blended CAC}, paid back in {blended payback} months of gross margin ({churn-adjusted} with churn); by channel, payback runs from {fastest} ({channel}) to {slowest} ({channel})."
- Channels that never pay back, with CAC against the most CAC the expected lifetime supports.
- Channels slower than the target and what would have to change.
- Channels with too few customers to judge.
- One line: "Payback is measured in gross margin on recurring revenue from new customers closed in {period}; cash timing depends on billing terms."

Then these tables:

1. Data check
2. Per-channel table (channel, new customers, new ARR, average ARR, cost, CAC, gross margin per customer per month, payback months, churn-adjusted payback months, most CAC for target, most CAC for lifetime, verdict), with the blended row last
3. Verdicts with the reason and what would have to change
4. New customers with no lead source, and suspected duplicate source values
5. Assumptions, each labeled as an assumption, including: costs and customers are matched in the same period, so a channel with a long sales cycle is charged this period's spend for customers from earlier spend; churn is one company-wide rate unless the user gave one per channel; amounts are first-year recurring revenue from the CRM.

If you can create files, offer the per-channel table as a CSV download.

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

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

- Period: 2025-10-01 to 2026-09-30
- Cost per channel over the period (example inputs): Inbound demo request $180,000, Outbound $480,000, Partner $90,000, Event $300,000
- Shared sales and marketing cost not assigned to a channel (example input): $60,000
- Gross margin on recurring revenue (example input): 75%
- Monthly logo churn (example input): 2%
- Payback target (example input): 18 months
- Defaults: all as written above

```csv
deal_id,lead_source,deal_type,amount,close_date
C-001,Inbound demo request,New business,15000,2025-10-03
C-002,Inbound demo request,New business,21000,2025-10-14
C-003,Inbound demo request,New business,15000,2025-10-26
C-004,Inbound demo request,New business,21000,2025-11-07
C-005,Inbound demo request,New business,15000,2025-11-19
C-006,Inbound demo request,New business,21000,2025-12-01
C-007,Inbound demo request,New business,15000,2025-12-13
C-008,Inbound demo request,New business,21000,2025-12-24
C-009,Inbound demo request,New business,15000,2026-01-05
C-010,Inbound demo request,New business,21000,2026-01-17
C-011,Inbound demo request,New business,15000,2026-01-29
C-012,Inbound demo request,New business,21000,2026-02-10
C-013,Inbound demo request,New business,15000,2026-02-22
C-014,Inbound demo request,New business,21000,2026-03-05
C-015,Inbound demo request,New business,15000,2026-03-17
C-016,Inbound demo request,New business,21000,2026-03-29
C-017,Inbound demo request,New business,15000,2026-04-10
C-018,Inbound demo request,New business,21000,2026-04-22
C-019,Inbound demo request,New business,15000,2026-05-04
C-020,Inbound demo request,New business,21000,2026-05-15
C-021,Inbound demo request,New business,15000,2026-05-27
C-022,Inbound demo request,New business,21000,2026-06-08
C-023,Inbound demo request,New business,15000,2026-06-20
C-024,Inbound demo request,New business,21000,2026-07-02
C-025,Inbound demo request,New business,15000,2026-07-14
C-026,Inbound demo request,New business,21000,2026-07-25
C-027,Inbound demo request,New business,15000,2026-08-06
C-028,Inbound demo request,New business,21000,2026-08-18
C-029,Inbound demo request,New business,15000,2026-08-30
C-030,Inbound demo request,New business,21000,2026-09-11
C-031,Outbound,New business,25000,2025-10-07
C-032,Outbound,New business,35000,2025-11-05
C-033,Outbound,New business,25000,2025-12-05
C-034,Outbound,New business,35000,2026-01-03
C-035,Outbound,New business,25000,2026-02-02
C-036,Outbound,New business,35000,2026-03-03
C-037,Outbound,New business,25000,2026-04-02
C-038,Outbound,New business,35000,2026-05-02
C-039,Outbound,New business,25000,2026-05-31
C-040,Outbound,New business,35000,2026-06-30
C-041,Outbound,New business,25000,2026-07-29
C-042,Outbound,New business,35000,2026-08-28
C-043,Partner,New business,20000,2025-10-12
C-044,Partner,New business,29000,2025-11-20
C-045,Partner,New business,20000,2025-12-29
C-046,Partner,New business,29000,2026-02-07
C-047,Partner,New business,20000,2026-03-18
C-048,Partner,New business,29000,2026-04-27
C-049,Partner,New business,20000,2026-06-05
C-050,Partner,New business,29000,2026-07-15
C-051,Partner,New business,29000,2026-08-23
C-052,Event,New business,8000,2025-10-18
C-053,Event,New business,12000,2025-12-16
C-054,Event,New business,8000,2026-02-13
C-055,Event,New business,12000,2026-04-13
C-056,Event,New business,8000,2026-06-11
C-057,Event,New business,12000,2026-08-09
C-058,,New business,20000,2025-12-30
C-059,,New business,20000,2026-06-25
C-060,Inbound demo request,Expansion,12000,2026-02-11
C-061,Outbound,Expansion,20000,2026-05-19
C-062,Partner,Renewal,25000,2026-07-08
C-063,Outbound,New business,30000,2025-09-22
C-064,Inbound demo request,New business,18000,2026-10-05
```

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 every deal closed won in the last four full quarters with lead source, deal type, first-year recurring amount, and close date (a Salesforce Opportunities report or a HubSpot deals export). Give the cost of each channel and the shared sales and marketing cost from finance, plus gross margin, monthly logo churn, and your payback target. The prompt lists the columns.
  4. Read the data check first and confirm how messy source values map, then take the per-channel table and the "what would have to change" lines to the budget review.

Before you paste CRM and cost data

Pasting an export sends it to the LLM vendor. 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, lead sources, deal types, amounts, and close dates, plus cost totals by channel. It reads the data and changes nothing in your CRM or ledger.

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.

Fully loaded CAC
$1,110,000 / 59 new customers = $18,814
Blended payback
$1,110,000 / ($102,083 new MRR x 75% gross margin) = 14.5 months, 16.9 with churn
Expected customer lifetime
1 / 2% monthly churn = 50 months
Never pays back
Event: CAC $50,000 vs at most $31,250 over the lifetime
Slower than the 18-month target
Outbound: 21.3 months; CAC $40,000 would need to fall to $33,750, or 15 new customers at the same cost
Rows excluded
5: C-060 (expansion, not a new customer), C-061 (expansion, not a new customer), C-062 (renewal, not a new customer), C-063 (closed outside the period), C-064 (closed outside the period)
New customers with no lead source
2, $40,000 new ARR, counted in the blended row only
Expected per-channel table
ChannelVerdictNew customersCostCACGM per customer per monthPayback (months)With churn (months)
Inbound demo requestPays back within target30$180,000$6,000$1,1255.35.6
PartnerPays back within target9$90,000$10,000$1,5636.46.8
OutboundSlower than target12$480,000$40,000$1,87521.327.5
EventNever pays back6$300,000$50,000$62580.0Never
All channels (fully loaded)59$1,110,000$18,814$1,29814.516.9

Outbound brings the largest customers but costs $40,000 each, so it takes 21.3 months of gross margin to pay back against an 18-month target. Event costs $50,000 per customer on $625 of monthly gross margin: 80 months, longer than the 50 months a customer is expected to stay, so it never pays back on these inputs.

Implementation steps

  1. 1

    Write the definitions down with finance before pulling data: a new customer is a deal closed won with a new business type in the period (expansions and renewals are not acquisitions); the amount is first-year recurring revenue without one-time fees or services; the channel is one agreed lead source field mapped to a short list, with blanks kept as Unknown; the period is by default the last four full quarters

  2. 2

    Export every deal closed won in the period from HubSpot or Salesforce: deal ID, lead source, deal type, amount, and close date; drop expansions, renewals, rows outside the period, and rows with no amount, and list each dropped row with the reason

  3. 3

    Get the fully loaded acquisition cost of each channel for the same period from finance, out of the general ledger and payroll rather than campaign reports: program spend (ads, event fees, tools, partner fees) plus the people time spent on that channel (SDR salaries and benefits for outbound, a partner manager for partner); keep cost that serves every channel (account executives, sales management, marketing operations) as shared cost, which goes only into the blended row; count commissions paid in the period as acquisition cost even if the books capitalize them under ASC 340-40

  4. 4

    Compute per channel: CAC = channel cost / new customers; gross margin per customer per month = average new ARR / 12 x gross margin; payback months = CAC / gross margin per customer per month, which is the same as channel cost / (new MRR x gross margin); payback uses gross margin, not revenue, because the cost of serving the customer has to be paid before anything is left to recover CAC

  5. 5

    Add churn: expected customer lifetime = 1 / monthly logo churn; churn-adjusted payback = ln(1 - payback x churn) / ln(1 - churn), which counts customers leaving along the way; a channel never pays back when CAC is at or above gross margin per customer per month / monthly churn, the most a customer is expected to earn back over its lifetime

  6. 6

    Judge each channel by written rules: under 5 new customers (an assumption you can change) gets no verdict; otherwise never pays back, slower than the payback target finance sets, or pays back within target; for each channel that misses, show the most CAC the target allows and how many new customers at the same cost would get it there, without forecasting what more or less spend would produce

  7. 7

    Put the blended row last with all channel costs plus shared cost over all new customers, including those with no source, so the fully loaded CAC ties to total sales and marketing cost; run it with the copy-paste prompt on this page in Claude or ChatGPT, or with formulas in the template, and check every number against the tabs

  8. 8

    Repeat each quarter on the same definitions, post the per-channel table and the channels that miss to the finance and revenue leadership channel in Slack, and bring it to the budget review next to the win rate by lead source table, keeping each quarter's export, cost tab, and result as the audit trail

Template outline

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

Google Sheet: CAC payback by channel FYxx Qx (RevOps pulls the CRM export; finance owns the cost tab and the inputs)

Tab inputs (finance): period start and end, gross_margin, monthly_churn, payback_target_months, min_customers (default 5)

Tab won_deals (CRM export, closed won in the period by close date):
  deal_id, lead_source, deal_type, amount (first-year recurring), close_date
  channel = lookup in source_map, "Unknown" if blank
  new_customer = deal_type is New business AND close_date in the period AND amount > 0

Tab source_map: raw_value, channel (one row per spelling found in the export)

Tab costs (finance, same period, from the ledger and payroll):
  channel, program_spend, people_cost, cost = program_spend + people_cost
  shared_cost (AEs, sales management, marketing ops): blended row only

Tab by_channel (one row per channel plus a blended row):
  customers = COUNTIFS(channel, c, new_customer, TRUE)
  new_arr = SUMIFS(amount, channel, c, new_customer, TRUE); avg_arr = new_arr / customers
  cac = cost / customers
  gm_per_month = avg_arr / 12 x gross_margin
  payback = cac / gm_per_month
  lifetime = 1 / monthly_churn
  payback_with_churn = IF(payback x monthly_churn >= 1, "Never", LN(1 - payback x monthly_churn) / LN(1 - monthly_churn))
  max_cac_target = gm_per_month x payback_target_months
  max_cac_lifetime = gm_per_month / monthly_churn
  verdict = IF(customers < min_customers, "Too few customers to judge",
    IF(cac >= max_cac_lifetime, "Never pays back",
    IF(payback > payback_target_months, "Slower than target", "Pays back within target")))
  customers_needed = ROUNDUP(cost / max_cac_target)

Worked example (example data, fictional; costs, margin, churn, and target are example inputs, not benchmarks):
  Period 2025-10-01 to 2026-09-30. Gross margin 75%, monthly churn 2%, target 18 months.
  64 rows read; 5 dropped: C-060 and C-061 (expansion), C-062 (renewal), C-063 and C-064 (closed outside the period)
  Lifetime = 1 / 0.02 = 50 months

  Inbound demo request: 30 customers, 15 x $15,000 + 15 x $21,000 = $540,000 new ARR, average $18,000
    CAC = $180,000 / 30 = $6,000
    GM per month = $18,000 / 12 x 0.75 = $1,125
    Payback = $6,000 / $1,125 = 5.3 months (check: $180,000 / ($45,000 MRR x 0.75 = $33,750) = 5.3)
    With churn = ln(1 - 5.333 x 0.02) / ln(0.98) = ln(0.8933) / ln(0.98) = 5.6 months. Pays back within target.
  Partner: 9 customers, 4 x $20,000 + 5 x $29,000 = $225,000, average $25,000
    CAC = $90,000 / 9 = $10,000; GM per month = $25,000 / 12 x 0.75 = $1,562.50
    Payback = $10,000 / $1,562.50 = 6.4 months; with churn ln(1 - 0.128) / ln(0.98) = 6.8. Pays back within target.
  Outbound: 12 customers, 6 x $25,000 + 6 x $35,000 = $360,000, average $30,000
    CAC = $480,000 / 12 = $40,000; GM per month = $30,000 / 12 x 0.75 = $1,875
    Payback = $40,000 / $1,875 = 21.3 months; with churn ln(1 - 0.4267) / ln(0.98) = 27.5. Slower than target.
    Most CAC for 18 months = $1,875 x 18 = $33,750; $480,000 / $33,750 = 14.2, so 15 new customers at the same cost (3 more)
  Event: 6 customers, 3 x $8,000 + 3 x $12,000 = $60,000, average $10,000
    CAC = $300,000 / 6 = $50,000; GM per month = $10,000 / 12 x 0.75 = $625
    Payback = $50,000 / $625 = 80 months, longer than the 50-month lifetime: 80 x 0.02 = 1.6, at or above 1. Never pays back.
    Most CAC over the lifetime = $625 / 0.02 = $31,250; $300,000 / $31,250 = 9.6, so 10 new customers at the same cost (4 more)
  Unknown source: 2 customers, $40,000 new ARR (blended row only; listed for a source fix)

  Blended (fully loaded): cost $180,000 + $480,000 + $90,000 + $300,000 + $60,000 shared = $1,110,000
    Customers 30 + 9 + 12 + 6 + 2 = 59; new ARR $540,000 + $225,000 + $360,000 + $60,000 + $40,000 = $1,225,000
    CAC = $1,110,000 / 59 = $18,813.56
    Payback = $1,110,000 / ($1,225,000 / 12 x 0.75 = $76,562.50) = 14.5 months; with churn 16.9
    Without the $60,000 shared cost: $1,050,000 / $76,562.50 = 13.7 months

Slack to #finance-revenue quarterly:
"CAC payback {{period}}: fully loaded CAC {{blended_cac}}, payback {{blended_payback}} months of gross margin
 ({{blended_with_churn}} with churn). Never pays back: {{never_list}}. Slower than {{target}} months: {{slow_list}}.
 Too few customers to judge: {{too_few_list}}. Table: {{sheet_url}}"

Tab history: quarter, channel, customers, cost, cac, payback, payback_with_churn, verdict, unknown_share

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 the table by hand (export, cleanup, cost tie-out with finance, math, write-up)7.5AssumptionTime the next quarterly CAC review from export to sent summary.
  • 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 quarterT - V
7.5 - 1 = 6.5 hours
Time saved per week(T - V) / 13
6.5 / 13 = 0.5 hours
Value of time saved per quarter(T - V) x R
6.5 x $75 = $487.50
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
Outbound payback (fictional example data)(channel cost / new customers) / (average new ARR / 12 x gross margin)
($480,000 / 12 = $40,000) / ($30,000 / 12 x 0.75 = $1,875) = 21.3 months
Never-pays-back ceiling for Event (fictional example data)monthly gross margin per customer / monthly churn
$625 / 0.02 = $31,250, below the $50,000 Event CAC
  • The math counts analyst time only. Moving budget between channels may be worth far more or far less than that, depending on how each channel responds to spend, which past cost per customer does not tell you, so it is not added here.
  • Cost and customers are matched in the same period, so a channel with a long sales cycle is charged this period's spend for customers that earlier spend produced. If cycles differ a lot by channel, lag the cost window by the median cycle and say so.
  • Payback here is on bookings-based recurring revenue. Annual prepay collects a year of cash up front, so cash payback can be much shorter than gross margin payback; monthly billing tracks it closely.
  • One company-wide churn rate is applied to every channel unless you have churn by channel. Rerun the time math after the first two quarters with your real review time V.

Common questions

How do you calculate CAC payback by channel?
CAC = channel cost / new customers from that channel, and payback months = CAC / (average new ARR / 12 x gross margin). In the fictional example, Outbound cost $480,000 for 12 customers, so CAC is $40,000, and $40,000 / ($30,000 / 12 x 0.75 = $1,875) = 21.3 months.
Why measure payback on gross margin instead of revenue?
Revenue first pays for hosting, support, and the other costs of serving the customer, and only the gross margin is left to recover what it cost to win them. Payback on revenue is shorter by the gross margin ratio: at 75% margin, a 16-month revenue payback is really 21.3 months.
When does a channel never pay back?
When CAC is at or above monthly gross margin per customer / monthly churn, the most a customer is expected to earn back before leaving. In the fictional example, Event customers bring $625 a month at 2% churn, so the ceiling is $31,250, and Event CAC is $50,000.
What goes into channel cost?
Program spend plus the people time spent on that channel, such as SDR salaries for outbound or a partner manager for partner, taken from the ledger and payroll. Cost that serves every channel, such as account executives and marketing operations, is shared cost and goes only into the fully loaded blended CAC. Expansions and renewals are left out of the customer count because they are not acquisitions.
How much time does it save?
With the example inputs, building the table by hand takes 7.5 hours a quarter and reviewing the prompt or template run takes 1, so 6.5 hours a quarter are saved, about 0.5 hours a week. That is worth $487.50 a quarter at $75 an hour against an example $60 a quarter LLM plan share.

Keep going

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