How to calculate CAC in SQL

Why analysts compute CAC in SQL

CAC is the first number an investor, CFO, or CMO asks for, and it is almost never as simple as "spend divided by signups." The LTV-to-CAC ratio is the cleanest summary of whether unit economics actually work. If CAC sits above LTV times gross margin, every new customer is a loss leader; if it sits far below what the channel could bear, the business is underspending.

The hard part is the definition. Blended or paid-only? How do you allocate spend when a user touched three channels before signing up? Is the attribution window seven days, twenty-eight, or "all time"? Each answer reshapes the picture.

This post is a playbook of eight SQL recipes for the CAC questions analysts get at Stripe, Notion, DoorDash, and Linear. Every query runs in Postgres with minor edits for Snowflake, BigQuery, or Redshift.

Formula and data model

The base definition fits on one line.

CAC = marketing spend / new customers acquired

Three flavors show up in real warehouses. Blended CAC divides total marketing spend by all new users including organic. Paid CAC narrows both sides to paid channels and is what performance marketing optimizes daily. Fully loaded CAC adds team payroll, SaaS tooling, and brand spend on top — the version the board wants.

Every example below assumes three tables.

ad_spend     (day, channel, spend)
users        (user_id, signup_at, attribution_channel)
first_orders (user_id, first_order_at)

The users table carries a reconciled attribution_channel label, and first_orders records each user's first paid event for cases where "new customer" means a paying customer rather than a signup.

Blended CAC

The base blended query divides total spend by total new users for a period — the easiest number to compute, the easiest to misread, and the one boards open with.

WITH spend_total AS (
    SELECT SUM(spend) AS total_spend
    FROM ad_spend
    WHERE day BETWEEN '2026-01-01' AND '2026-03-31'
),
new_users AS (
    SELECT COUNT(*) AS new_users_cnt
    FROM users
    WHERE signup_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    st.total_spend,
    nu.new_users_cnt,
    st.total_spend::NUMERIC / NULLIF(nu.new_users_cnt, 0) AS blended_cac
FROM spend_total st, new_users nu;

Blended CAC is useful for the long view but hides paid efficiency. If half of signups arrive organically, blended CAC looks roughly half of paid CAC — flattering on a slide and misleading for any channel decision.

CAC by channel

For a channel-level budget decision, blended is useless. Aggregate spend and users per channel independently, then divide.

WITH spend_by_channel AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM ad_spend
    WHERE day BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY channel
),
users_by_channel AS (
    SELECT attribution_channel AS channel, COUNT(*) AS new_users
    FROM users
    WHERE signup_at BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY attribution_channel
)
SELECT
    sbc.channel,
    sbc.total_spend,
    ubc.new_users,
    sbc.total_spend::NUMERIC / NULLIF(ubc.new_users, 0) AS cac
FROM spend_by_channel sbc
LEFT JOIN users_by_channel ubc USING (channel)
ORDER BY cac;

attribution_channel is the output of whatever attribution model you trust — last touch, first touch, or multi-touch. Different models rank channels differently, so the comparison is only meaningful if everyone in the room reads the same definition.

CAC by acquisition cohort

A single-period CAC by channel hides trend. Cohort CAC compares spend and signups inside the same month for each channel, which surfaces saturation early.

WITH cohorts AS (
    SELECT
        DATE_TRUNC('month', signup_at) AS cohort_month,
        attribution_channel,
        COUNT(*)                        AS new_users
    FROM users
    GROUP BY 1, 2
),
spend AS (
    SELECT
        DATE_TRUNC('month', day) AS month,
        channel,
        SUM(spend)               AS spend
    FROM ad_spend
    GROUP BY 1, 2
)
SELECT
    c.cohort_month,
    c.attribution_channel,
    c.new_users,
    s.spend,
    s.spend::NUMERIC / NULLIF(c.new_users, 0) AS cac
FROM cohorts c
LEFT JOIN spend s
    ON s.month = c.cohort_month
   AND s.channel = c.attribution_channel
ORDER BY c.cohort_month, c.attribution_channel;

The bonus payoff is the time series per channel. A CAC creeping up on Meta or Google Ads month after month is almost always auction inflation or audience exhaustion, and three consecutive months in the wrong direction is the standard signal to rebalance the budget.

Paid-only CAC

Strip organic and direct out of both sides and you get the version performance marketing steers against.

WITH paid_spend AS (
    SELECT SUM(spend) AS paid_spend
    FROM ad_spend
    WHERE channel IN ('google_ads', 'meta_ads', 'tiktok_ads', 'reddit_ads')
      AND day BETWEEN '2026-01-01' AND '2026-03-31'
),
paid_users AS (
    SELECT COUNT(*) AS paid_users
    FROM users
    WHERE attribution_channel IN ('google_ads', 'meta_ads', 'tiktok_ads', 'reddit_ads')
      AND signup_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    paid_spend.paid_spend::NUMERIC / NULLIF(paid_users.paid_users, 0) AS paid_cac
FROM paid_spend, paid_users;

Keep the paid-channel list in a dimension table or dbt seed rather than hard-coded in every query. The moment two analysts copy-paste different lists, the dashboard and the monthly review will disagree.

CAC per paying customer

For most subscription products, a signup is not the milestone — first paid order is. Replacing users with first_orders turns CAC into a hard finance metric.

WITH spend AS (
    SELECT
        channel,
        DATE_TRUNC('month', day) AS month,
        SUM(spend)               AS spend
    FROM ad_spend
    GROUP BY 1, 2
),
first_buyers AS (
    SELECT
        u.attribution_channel AS channel,
        DATE_TRUNC('month', fo.first_order_at) AS month,
        COUNT(*)              AS new_buyers
    FROM first_orders fo
    JOIN users u ON u.user_id = fo.user_id
    GROUP BY 1, 2
)
SELECT
    s.channel,
    s.month,
    s.spend,
    fb.new_buyers,
    s.spend::NUMERIC / NULLIF(fb.new_buyers, 0) AS cac_per_buyer
FROM spend s
LEFT JOIN first_buyers fb
    ON fb.channel = s.channel AND fb.month = s.month
ORDER BY s.month, cac_per_buyer;

For SaaS reporting this is the line boards prefer — counting free signups in unit economics flatters the picture.

LTV to CAC ratio by channel

The single most important comparison in marketing analytics is the LTV-to-CAC ratio per channel. Three to one is the rule of thumb growth investors use as a sanity check on whether a channel is worth scaling.

WITH spend_by_channel AS (
    SELECT channel, SUM(spend) AS total_spend
    FROM ad_spend
    GROUP BY channel
),
users_by_channel AS (
    SELECT attribution_channel AS channel, COUNT(*) AS new_users
    FROM users
    GROUP BY attribution_channel
),
cac_channel AS (
    -- aggregate spend and users independently and only then divide;
    -- if you JOIN before aggregating, the spend row fans out by
    -- the number of users in the channel and CAC blows up.
    SELECT
        s.channel,
        s.total_spend::NUMERIC / NULLIF(u.new_users, 0) AS cac
    FROM spend_by_channel s
    LEFT JOIN users_by_channel u USING (channel)
),
ltv_channel AS (
    SELECT
        u.attribution_channel AS channel,
        AVG(user_ltv.total_revenue) AS avg_ltv
    FROM users u
    JOIN (
        SELECT user_id, SUM(total) AS total_revenue
        FROM orders
        WHERE status = 'paid'
        GROUP BY user_id
    ) user_ltv ON user_ltv.user_id = u.user_id
    GROUP BY u.attribution_channel
)
SELECT
    c.channel,
    c.cac,
    l.avg_ltv,
    l.avg_ltv / NULLIF(c.cac, 0) AS ltv_cac_ratio
FROM cac_channel c
JOIN ltv_channel l USING (channel)
ORDER BY ltv_cac_ratio DESC;

Pre-aggregating spend and users separately is non-negotiable. Join the raw tables before aggregating and every spend row fans out over every user in the channel, the denominator collapses, and CAC reports come in tens of times too high.

Fully loaded CAC

Performance ad spend is only one component. Marketing payroll, SaaS tooling, and brand investments belong in the numerator any time the CFO is in the room.

WITH all_marketing_cost AS (
    -- paid advertising
    SELECT SUM(spend) AS cost
    FROM ad_spend
    WHERE day BETWEEN '2026-01-01' AND '2026-03-31'

    UNION ALL

    -- marketing team payroll, fully burdened
    SELECT SUM(monthly_salary)
    FROM marketing_payroll
    WHERE month BETWEEN '2026-01-01' AND '2026-03-31'

    UNION ALL

    -- attribution, automation, and CRM seats
    SELECT SUM(cost)
    FROM marketing_tools
    WHERE month BETWEEN '2026-01-01' AND '2026-03-31'
),
total_cost AS (
    SELECT SUM(cost) AS total_marketing_cost FROM all_marketing_cost
),
new_users AS (
    SELECT COUNT(*) AS new_users_cnt
    FROM users
    WHERE signup_at BETWEEN '2026-01-01' AND '2026-03-31'
)
SELECT
    tc.total_marketing_cost,
    nu.new_users_cnt,
    tc.total_marketing_cost::NUMERIC / NULLIF(nu.new_users_cnt, 0) AS fully_loaded_cac
FROM total_cost tc, new_users nu;

Fully loaded CAC is materially higher than blended in almost every shop, and that is the point. At Series B or later, payroll and tooling routinely add forty to sixty percent on top of paid spend, and any board number that ignores them overstates LTV-to-CAC by a quarter point or more.

Linear attribution CAC

Last-click is the most common default and the least fair. Linear attribution splits credit evenly across every paid touch before signup, which is closer to how multi-touch journeys actually convert.

-- each touch before signup gets weight 1 / total touches per user
WITH touches AS (
    SELECT
        t.user_id,
        t.channel,
        t.touched_at
    FROM marketing_touches t
    JOIN users u ON u.user_id = t.user_id
    WHERE t.touched_at <= u.signup_at
),
user_touch_counts AS (
    SELECT user_id, COUNT(*) AS total_touches
    FROM touches
    GROUP BY user_id
),
attributed AS (
    SELECT
        t.user_id,
        t.channel,
        1.0 / utc.total_touches AS attribution_weight
    FROM touches t
    JOIN user_touch_counts utc USING (user_id)
)
SELECT
    channel,
    SUM(attribution_weight) AS attributed_users
FROM attributed
GROUP BY channel
ORDER BY attributed_users DESC;

The output attributed_users drops in as the denominator anywhere a raw COUNT(*) was used. The pattern extends to time-decayed or U-shaped attribution by replacing the constant 1.0 / total_touches weight with the curve of your choice.

Common pitfalls

Mixing blended and paid CAC inside the same dashboard without labels is the most expensive recurring mistake. A reviewer looking at a clean blended number thinks paid is doing twice as well as it really is, and the next budget cycle pours money into channels that were already breaking even. Every chart needs to state which CAC it shows, and ideally pair the two on the same axis.

Comparing CAC against LTV without applying gross margin is the second classic trap. An LTV of fifty dollars and a CAC of fifteen dollars looks like a healthy three-to-one ratio, but at thirty percent gross margin the real contribution is fifteen dollars against a fifteen-dollar acquisition cost. The honest comparison uses gross profit per user.

Forgetting NULLIF in the denominator looks like a stylistic preference until a channel runs at zero acquisition for a month and the entire dashboard fails. spend / NULLIF(new_users, 0) returns null instead of throwing, and the dashboard shows an obvious gap instead of a red error tile.

Conflating signups and paying customers quietly destroys reporting for subscription products. CAC per signup and CAC per paying customer can differ by a factor of three when trial-to-paid conversion sits near thirty percent. Decide once which milestone counts as a customer, document it, and never silently swap definitions between reviews.

Changing the attribution window retroactively is the slowest-burning of these mistakes. If CAC uses twenty-eight days and someone quietly rebuilds the dashboard against seven days a year later, every historical comparison is now meaningless. Window choices belong in a versioned metric definition, and any change should run parallel to the old definition for at least a quarter.

Optimization tips

Partitioning ad_spend and users on the date columns at day grain keeps queries scanning only the windows they need, which matters once the spend ledger crosses a million rows. The monthly CAC time series is a natural candidate for a materialized view refreshing daily — BigQuery scheduled queries, Snowflake dynamic tables, and Databricks materialized views all handle the cadence cleanly.

For LTV-to-CAC by channel, precompute cac_channel and ltv_channel into a thin nightly fact with one row per channel-month. The wide shape is easier to slice and easier for finance to reconcile against the ledger.

Related reading

• How to calculate blended CAC in SQL
• How to calculate CAC by channel in SQL
• How to calculate CAC payback in SQL
• How to calculate install attribution in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

Blended or paid CAC — which one matters more?

Paid CAC is the daily steering wheel for performance marketing because it isolates the dollars the team controls. Blended CAC is the answer for the board because it ties back to the income statement. Show both on every growth review, and plot the gap as its own series — a widening gap means organic is doing more of the work.

Do I really need a fully loaded CAC?

For a seed-stage company with two marketers, fully loaded barely differs from paid plus thin overhead. Past Series B it is mandatory: payroll and tooling can easily add half again on top of paid spend, and any LTV-to-CAC ratio that excludes them is materially overstated. Investors and acquirers ask for fully loaded by default.

How should I handle the attribution window?

Pick a sensible default — twenty-eight days is the industry standard for performance marketing — and write it into your metric definition document. Run alternative models in parallel for reference, but never silently change the headline definition. If a switch is necessary, run old and new side by side for at least a quarter so historical comparisons remain interpretable.

Should LTV-to-CAC always be above three to one?

Three to one is the rough floor used by growth investors and CFOs. Below two to one usually means CAC is climbing on saturation or LTV is dropping on churn. A ratio above five to one is not automatically good news — it often means the channel could absorb more budget and the business is leaving growth on the table.

Where do brand campaigns belong?

Brand investments rarely tie cleanly to attributed acquisitions, which is exactly why they belong in the fully loaded numerator rather than paid CAC. The clean way to publish is a headline fully loaded CAC plus a brand-adjusted version that strips brand spend out.