How to calculate CAC payback in SQL
Contents:
What CAC payback actually measures
CAC payback is the number of months it takes to recover the money you spent acquiring a customer. It is the single most important sanity check on a growth budget. If your CAC payback is 8 months and you have 18 months of runway, you can keep spending. If payback is 36 months and you have 12 months of runway, every additional ad dollar is making your situation worse.
Imagine your VP of Growth pings you on Friday at 5pm: "Investors want CAC payback by channel, by Monday morning, for the last three quarters." This is the most common ad-hoc request at any Series A or B company. The metric bridges two teams that normally talk past each other. Finance cares about cash recovery and runway. Marketing cares about leads, conversions, and channel mix. CAC payback is the one number that translates marketing investment into a finance horizon both teams trust. When a Stripe or Notion analyst gets asked about acquisition efficiency in an interview, the question is almost always "compute CAC payback at the cohort level and tell me which channel is underperforming."
The formula
In its simplest form, CAC payback is a ratio of money out to gross margin in:
CAC Payback (months) = CAC / (ARPU_monthly × Gross Margin)CAC is the total marketing spend in a period divided by the number of new customers acquired in that period. ARPU_monthly is the average revenue per user, expressed monthly — not annual, not quarterly. Gross Margin is the percentage of revenue you keep after paying the variable cost of serving that customer. For a typical SaaS company this is somewhere between 70% and 85%. For a marketplace it might be 20-30%. For a pure software product with no hosting cost per user it can approach 95%.
Gross margin appears in the denominator because you cannot pay back a marketing investment with revenue you immediately hand over to AWS, Stripe, and your support team. You only pay it back with the slice you keep. Skipping gross margin is the single most common mistake in CAC payback and it makes every payback number look 20-40% better than it really is.
Baseline SQL: monthly CAC payback
Assume three tables: users(user_id, signup_date, acquisition_channel), transactions(user_id, amount, status, paid_at), and marketing_spend(month, channel, spend). The first query computes a clean monthly CAC payback over the last year, blended across channels.
WITH new_users AS (
SELECT
DATE_TRUNC('month', signup_date) AS month,
COUNT(*) AS new_users
FROM users
WHERE signup_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'
GROUP BY 1
),
spend AS (
SELECT
DATE_TRUNC('month', month) AS month,
SUM(spend) AS total_spend
FROM marketing_spend
WHERE month >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'
GROUP BY 1
),
cac AS (
SELECT
n.month,
s.total_spend / NULLIF(n.new_users, 0) AS cac_per_user
FROM new_users n
JOIN spend s ON s.month = n.month
),
arpu AS (
SELECT
AVG(t.amount) AS avg_monthly_arpu
FROM transactions t
WHERE t.status = 'success'
AND t.paid_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
c.month,
c.cac_per_user,
a.avg_monthly_arpu,
c.cac_per_user / NULLIF(a.avg_monthly_arpu * 0.80, 0) AS cac_payback_months
FROM cac c
CROSS JOIN arpu a
ORDER BY c.month;The 0.80 in the final division is a gross margin assumption — replace it with your real number from finance. The CROSS JOIN works here only because arpu returns a single row; if you ever expand it to per-month ARPU, switch to a normal join on month. The result is one row per month with a payback in months: a value of 14 means a customer acquired in that month is projected to break even on month 14.
CAC payback by channel
The blended number above is the one most teams stop at, and it is also where most growth budgets get wasted. A blended payback of 12 months almost always hides a brutal split: paid social at 28 months, content/SEO at 4 months, referral at 1.5 months. Killing the worst channel can drop your blended payback by half overnight.
WITH by_channel AS (
SELECT
u.acquisition_channel,
COUNT(DISTINCT u.user_id) AS new_users,
AVG(t.amount) AS avg_arpu
FROM users u
LEFT JOIN transactions t
ON t.user_id = u.user_id
AND t.status = 'success'
AND t.paid_at >= u.signup_date
AND t.paid_at < u.signup_date + INTERVAL '30 days'
WHERE u.signup_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.acquisition_channel
),
spend AS (
SELECT
channel,
SUM(spend) AS total_spend
FROM marketing_spend
WHERE month >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY channel
)
SELECT
bc.acquisition_channel,
bc.new_users,
bc.avg_arpu,
s.total_spend / NULLIF(bc.new_users, 0) AS cac,
(s.total_spend / NULLIF(bc.new_users, 0))
/ NULLIF(bc.avg_arpu * 0.80, 0) AS cac_payback_months
FROM by_channel bc
JOIN spend s ON s.channel = bc.acquisition_channel
ORDER BY cac_payback_months;The subtle detail in this query is the bounded join on transactions: we only count revenue in the first 30 days of a user's life, which gives a clean first-month ARPU and avoids contaminating the channel comparison with tenure effects. A user acquired three years ago has had 36 months to upsell; a user acquired last month has had four weeks. Comparing their ARPUs side-by-side is a category error.
Cohort-based CAC payback
The cleanest version of the metric tracks each monthly cohort against its own spend, then watches cumulative gross profit climb toward the CAC line. The cohort breaks even when cumulative gross profit equals CAC.
WITH cohort_size AS (
SELECT
DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS users_in_cohort
FROM users
WHERE signup_date >= '2025-01-01'
GROUP BY 1
),
cohort_spend AS (
SELECT
DATE_TRUNC('month', month) AS cohort_month,
SUM(spend) AS spend
FROM marketing_spend
WHERE month >= '2025-01-01'
GROUP BY 1
),
cohort_revenue AS (
SELECT
DATE_TRUNC('month', u.signup_date) AS cohort_month,
DATE_PART('month', AGE(DATE_TRUNC('month', t.paid_at),
DATE_TRUNC('month', u.signup_date)))::int AS months_since_signup,
SUM(t.amount) AS revenue
FROM users u
JOIN transactions t
ON t.user_id = u.user_id
AND t.status = 'success'
WHERE u.signup_date >= '2025-01-01'
GROUP BY 1, 2
),
joined AS (
SELECT
r.cohort_month,
r.months_since_signup,
SUM(r.revenue) OVER (
PARTITION BY r.cohort_month
ORDER BY r.months_since_signup
) AS cumulative_revenue,
s.users_in_cohort,
sp.spend
FROM cohort_revenue r
JOIN cohort_size s ON s.cohort_month = r.cohort_month
JOIN cohort_spend sp ON sp.cohort_month = r.cohort_month
)
SELECT
cohort_month,
months_since_signup,
cumulative_revenue * 0.80 AS cumulative_gross_profit,
spend AS cohort_cac_total,
(cumulative_revenue * 0.80) / NULLIF(spend, 0) AS payback_progress
FROM joined
ORDER BY cohort_month, months_since_signup;payback_progress reaches 1.0 at the point of CAC payback. Plotting this column per cohort gives you a payback curve — and the slope of that curve is the real diagnostic. Flat curves with low slope mean weak retention is killing payback; steep early curves that plateau mean the cohort upgrades fast then leaves.
Common pitfalls
When teams calculate CAC payback for the first time, the most common mistake is to compute everything on gross revenue without gross margin. The formula still produces a number, but it lies. A SaaS team with 70% gross margin that reports payback on gross revenue is understating their true payback by 43%. The fix is to multiply ARPU by your real gross margin from the income statement — not your aspirational margin, the audited one — before you divide.
Another trap is mixing annual and monthly ARPU. If your customers pay annual contracts upfront and your transactions table records each charge at full annual amount, dividing CAC by an "annual ARPU" of 1,200 gives a payback that looks like one month. That is not how cash works. Either normalize ARPU to its monthly equivalent, or change the unit to years and adjust the gross margin treatment accordingly. The convention in interviews and board decks is months.
The third pitfall is ignoring churn entirely. CAC payback is a forward-looking projection — you are assuming the user will still be paying at month 18 when you quote an 18-month payback. If monthly churn is 8%, the median user is gone by month 12, so an 18-month "payback" is fiction for half your cohort. Either pair CAC payback with LTV/CAC, or compute payback only on the surviving fraction of each cohort using a window function on subscription state.
A fourth pitfall is using one blended CAC across all channels. Referral traffic costs near zero and converts on intent; paid social costs $200 a click and converts on impulse. Averaging them into one CAC number throws away the only signal your finance team actually wants. Always segment by channel, and within paid channels by campaign type, before reporting a number to anyone who can move budget.
Finally, teams routinely ignore pricing trends. If your product has had three price increases in 18 months and ARPU is computed on a trailing 30-day window, your payback number is artificially flattering — recent cohorts pay more than older cohorts on the same SQL definition. Either freeze pricing in the ARPU calculation using the cohort's own tier, or document the price changes alongside the metric so finance does not double-count the lift.
Optimization tips
For large transaction tables, the joins in the cohort query become the bottleneck. Pre-aggregate revenue into a monthly fact table — fact_user_revenue(user_id, month, revenue) — and build cohort metrics on that, not on raw transactions. On Snowflake or BigQuery this typically cuts query time 10-50x for tables above 100M rows. On Databricks, materialize the aggregate as a Delta table with ZORDER BY user_id, month and the cohort join becomes nearly free.
Always partition marketing_spend and transactions by month. Leaving both as a single partition means every cohort query full-scans both, which on a 500M-row table will time out. If you cannot repartition, at least cluster on paid_at and month — most modern warehouses prune correctly given a WHERE month >= ... predicate.
For dashboards that refresh hourly, do not recompute the cohort revenue from scratch each time. Maintain a rolling incremental table: append yesterday's transactions and recompute only the most recent month's cumulative window. Linear, Vercel, and most growth-stage SaaS companies run their CAC payback dashboards this way. For one-off interview or board questions, skip the optimization — write the clean query, run it once, put the result in the deck.
Related reading
- How to calculate ARPU in SQL
- How to calculate blended CAC in SQL
- How to calculate churn in SQL
- How to calculate ARR in SQL
- SQL window functions interview questions
To drill questions like this daily, NAILDD is launching with 500+ SQL problems.
FAQ
What is a good CAC payback period?
For B2B SaaS, the rule of thumb at Series B and beyond is 12-18 months. Under 12 is strong and earns you the right to raise growth spending. Above 24 is a yellow flag that usually triggers a board conversation about pricing or channel mix. For B2C subscription products with lower price points and higher churn, the bar is tighter: 6-12 months is healthy and under 6 is excellent. Enterprise software with seven-figure annual contracts can sustain 24-36 months because the contracts are stickier and gross margins are higher.
CAC payback or LTV/CAC — which should I use?
Use both, because they answer different questions. CAC payback is a cash-flow and runway question: how soon does this investment come back. LTV/CAC is a unit-economics question: across the customer's whole life, are we creating value. A company with 9-month payback and LTV/CAC of 1.2 is recovering cash quickly but barely making money long-term. A company with 24-month payback and LTV/CAC of 8 is in a great long-term position but may run out of cash before they see it. Both metrics are interview standards.
Should I include gross margin in the calculation?
Yes, almost always. Without gross margin you are pretending that every dollar of revenue is recoverable, which is never true outside of pure software with near-zero variable cost. The exception is when comparing against an industry benchmark also defined on gross revenue — but even then, report both numbers side by side. In interviews, computing on gross margin signals senior-analyst thinking.
My CAC payback is under 12 months. What should I do?
Spend more. A payback under 12 months means every marketing dollar is recovered inside the year, so lean harder into your best-performing channels. The constraints to watch are channel saturation (paid channels get more expensive as you scale) and capital (you still need cash to spend now, even though you will recover it). Short payback periods are the strongest signal in unit economics that growth budget is undersized.
My CAC payback is over 24 months. What should I do?
Three levers in order of speed. First, cut the worst channel — pause the one with payback above 36 months for a quarter. Blended payback usually drops 20-30% within a month. Second, review pricing. If you have not raised prices in 18+ months, a 10-15% increase translates directly into 10-15% better payback. Third, improve free-to-paid conversion: if your rate is 3%, getting it to 4% is a 33% lift in payback. Combined, these can halve a 30-month payback within two quarters.
How does CAC payback differ for marketplaces?
Marketplaces have a structural problem: gross margin is typically 15-30%, not the 75-85% of pure SaaS. A marketplace with a CAC of $80 and a monthly ARPU of $40 looks like a 2-month payback on gross revenue but is actually a 7-month payback at 30% margin. Marketplaces also have to allocate CAC between supply and demand acquisition, which makes the denominator ambiguous — per driver, per rider, or per transaction. The standard convention is to track payback per active demand-side user and treat supply-side acquisition as a separate cost line, but check the company's reporting standard before answering in an interview.