How to calculate CAC by channel in SQL

Why blended CAC lies to you

Imagine your growth lead pings you on a Sunday night: "Finance wants a defensible CAC number for the board deck by Monday morning. They have $90 in their head from last quarter's blended report. Can we hold that line?" You pull the obvious query — total paid spend divided by total new paid customers — and the answer is $92. Good enough for the meeting. Three months later the company has doubled spend on a channel that is actually burning cash.

This is the failure mode blended customer acquisition cost is famous for. A blended number is an average of averages: an organic-heavy month with a cheap PR moment papers over a paid search campaign paying $400 per signup. The aggregate looks fine because organic and referral pull the mean down. Budget reallocation and channel kill-decisions need the per-channel breakdown, not the headline.

The SQL is not hard. The hard part is the data model — joining marketing spend to acquired users on a consistent channel key, deciding what "acquired" means, and being honest about attribution. Every snippet below runs on Postgres or Snowflake with minor dialect tweaks; on BigQuery, swap DATE_TRUNC('month', x)::DATE for DATE_TRUNC(x, MONTH).

The base formula

The shape of the metric is one line:

CAC_channel = (spend on channel in period) / (users acquired through channel in period)

What counts as "acquired" is the first product decision. Most teams pick one of three definitions and stick with it: first signup (cheapest to measure, noisiest), first activation event (your aha moment), or first paid customer (cleanest, but lags spend by weeks). SaaS anchors on first paid, consumer apps on first install or first activation, marketplaces on first transaction. Lock it down before the SQL, not after.

The second decision is which spend to include. Pure media spend (Google Ads, Meta Ads, TikTok Ads) is the floor. Fully-loaded CAC also includes growth team salaries, tooling like Segment or Iterable, agency retainers, and creative production. Boards want fully-loaded; channel managers want media-only. Compute both, label them clearly, and let the reader choose.

CAC by utm_source

The simplest cut is by utm_source. Marketing spend is bucketed by channel, new users carry a utm_source attribute from their first session, and you divide one by the other inside a single month.

WITH new_users AS (
    SELECT
        utm_source AS channel,
        user_id
    FROM users
    WHERE created_at >= '2026-04-01'
      AND created_at <  '2026-05-01'
),
spend AS (
    SELECT
        channel,
        SUM(amount) AS total_spend
    FROM marketing_spend
    WHERE spend_date >= '2026-04-01'
      AND spend_date <  '2026-05-01'
    GROUP BY channel
)
SELECT
    s.channel,
    s.total_spend,
    COUNT(nu.user_id)                              AS new_users,
    s.total_spend / NULLIF(COUNT(nu.user_id), 0)   AS cac
FROM spend s
LEFT JOIN new_users nu USING (channel)
GROUP BY s.channel, s.total_spend
ORDER BY cac DESC NULLS LAST;

A few details. LEFT JOIN from spend to users means channels that received budget but produced no signups still show up — surfacing as NULL CAC or, with a tweak, infinity. You want them visible because a $10k spend with zero attributed users is a fire-alarm, not a row to filter out. NULLIF(COUNT, 0) guards against division by zero on the opposite case — a channel logged zero spend but somehow got users (usually a UTM tracking bug).

For production reporting, wrap this in a view that also computes share-of-spend and share-of-acquisitions. A channel with $400 CAC at 1% of acquired users is a different problem from one with $400 CAC at 30%.

CAC by region and platform

Channel performance is rarely uniform across geographies. The same Meta Ads campaign produces a $40 customer in Germany and a $250 customer in the United States because creative, competition, and conversion rates all differ. Without the segment dimension the global average will mislead anyone allocating budget at the country level.

WITH cohort AS (
    SELECT
        u.user_id,
        u.utm_source AS channel,
        u.country
    FROM users u
    WHERE u.created_at >= '2026-04-01'
      AND u.created_at <  '2026-05-01'
),
spend AS (
    SELECT
        channel,
        country,
        SUM(amount) AS spend
    FROM marketing_spend
    WHERE spend_date >= '2026-04-01'
      AND spend_date <  '2026-05-01'
    GROUP BY channel, country
)
SELECT
    s.channel,
    s.country,
    s.spend,
    COUNT(c.user_id)                       AS new_users,
    s.spend / NULLIF(COUNT(c.user_id), 0)  AS cac
FROM spend s
LEFT JOIN cohort c USING (channel, country)
GROUP BY s.channel, s.country, s.spend
ORDER BY s.country, cac DESC NULLS LAST;

The same pattern works for platform (ios vs android vs web), for device class, or for a coarser geo bucket like region (NA, EMEA, APAC, LATAM). The constraint is whether your marketing spend table is itself segmented. If your ads platform only exports country-level rollups, you cannot synthesize a state-level breakdown — allocating Texas spend by population share of the US total produces a number that looks precise and is wrong.

When the segment dimension has many values, an interactive dashboard beats a static query. Surface the channel-by-country grid, let the user filter, and provide a benchmark row (the global CAC for that channel) so each cell has context.

CAC over time

A one-month snapshot tells you the level. A time series tells you the trend, and the trend is what catches saturation early. Most paid channels look great until bidding competition rises, audiences saturate, fatigue kicks in, and CAC creeps up 5% a month for two quarters before anyone notices.

WITH monthly_spend AS (
    SELECT
        DATE_TRUNC('month', spend_date)::DATE AS month,
        channel,
        SUM(amount) AS spend
    FROM marketing_spend
    WHERE spend_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', spend_date), channel
),
monthly_users AS (
    SELECT
        DATE_TRUNC('month', created_at)::DATE AS month,
        utm_source AS channel,
        COUNT(*)   AS new_users
    FROM users
    WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', created_at), utm_source
)
SELECT
    s.month,
    s.channel,
    s.spend,
    u.new_users,
    s.spend / NULLIF(u.new_users, 0) AS monthly_cac
FROM monthly_spend s
LEFT JOIN monthly_users u USING (month, channel)
ORDER BY s.channel, s.month;

A trailing twelve months at monthly grain is the typical default. Weekly is too noisy for paid channels with long conversion windows; quarterly hides inflection points. To flag rising CAC programmatically, add a window function that compares each month to the trailing three-month average and surface anything that has drifted more than one standard deviation.

A growing CAC on a single channel is rarely a clean signal. It is usually some mix of seasonality, competitive pressure, audience exhaustion, and tracking drift. Before alerting the channel manager, eyeball whether absolute spend rose, whether new-user volume held, and whether the underlying click-to-signup conversion moved. CAC is a ratio, and the ratio can change for reasons that have nothing to do with the channel.

Common pitfalls

The first trap is attribution model. First-touch, last-touch, position-based, and data-driven multi-touch all produce different CAC numbers from the same raw events, and the differences are often material — 30% swings are routine on paid social. The fix is not to chase the "correct" model; pick one, document it in the warehouse layer, and version any change so historical comparisons survive. If analytics switches from last-touch to data-driven mid-quarter without telling Finance, you will spend a week explaining why CAC "jumped" 25%.

A second trap is treating organic as free. Organic CAC is not zero — it is the content team, the SEO tooling, the freelance writers, and the engineering hours behind the programmatic SEO pages. A fully-loaded organic CAC bakes those into the numerator and divides by organic signups. The number is usually still lower than paid, but pretending it is zero makes paid channels look worse than they are in head-to-head comparisons.

A third trap is mismatching the spend window and the conversion window. Spend in April produces signups in April and May for short-cycle products, and signups well into the second half of the year for long-cycle B2B. If you divide April spend by April signups for a 60-day sales cycle, you will underestimate CAC. The fix is to compute CAC on a cohort basis — match each signup back to the spend that drove it within a defined attribution window — or use rolling 90-day numerators and denominators that absorb the lag.

A fourth trap is computing CAC without LTV in the same view. A $50 CAC sounds cheap until you discover those customers churn in two months at a $20 monthly contribution margin. The rule of thumb is that lifetime value should exceed three times CAC, and payback period should sit under twelve months. Showing CAC alongside LTV and payback by channel turns the table from a vanity ranking into a decision tool.

A fifth trap is letting branded paid contaminate the numerator. When users search your company name on Google and click a paid ad, the conversion is mostly incremental zero — they would have clicked the organic result a row below. Counting branded paid as a real acquisition channel inflates paid performance and starves real growth channels of credit. Split branded and non-branded paid in the keyword report and treat them as separate channels.

Optimization tips

Precompute the spend table at the same grain as the user table. If marketing spend lands at daily-channel-country and your user table is at the user level, summarize spend into a marketing_spend_daily view and use it as the join target everywhere. This avoids re-aggregating the same numbers across ten dashboards and gives every analyst a single source of truth.

Add indexes on the join keys: marketing_spend(channel, country, spend_date) and users(utm_source, country, created_at). On Snowflake or BigQuery, where indexes are not a thing, cluster on the same columns — clustering by created_at and utm_source on the users table makes month-by-channel scans much cheaper on a year of data.

Materialize the time-series cut. The twelve-month rolling view above gets called from a dashboard ten times a day; materializing it as a daily refresh removes recomputation cost and gives consistent numbers across users hitting the dashboard at slightly different times. dbt's incremental materializations are the usual implementation.

Surface sanity-check rows in the same query. Add a CTE that surfaces spend with no users, users with no spend (truly organic, or tracking bugs), and channels whose CAC moved more than 30% month-over-month. A query that surfaces its own anomalies is twice as useful as one that just returns the numbers.

Related reading

• How to calculate blended CAC in SQL
• How to calculate install attribution in SQL
• How to calculate ARPU in SQL
• How to calculate churn in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ analytics SQL problems modeled on real interviews from Stripe, Airbnb, Notion, and DoorDash.

FAQ

What counts as an "acquired" user?

It depends on the product. SaaS companies usually anchor on first paid customer because that is when revenue starts and sales attribution settles. Consumer apps anchor on first install or first activation because the paid step is much later in the funnel. Marketplaces sometimes use first completed transaction. The right answer is whichever step your CFO uses to compute LTV — CAC and LTV need the same denominator to produce a meaningful ratio.

How long should the attribution lag window be?

For consumer products with low-friction purchase, seven to thirty days captures most conversions. For B2B with sales cycles, ninety days is the floor and a year is not unreasonable for enterprise. Size this empirically: pull the histogram of days from first touch to first paid customer over twelve months and pick the percentile that captures 90 to 95% of conversions. Revisit it annually as the product changes.

Are branded paid search and organic search different channels?

Yes, and treating them as the same is one of the most common reporting errors. Branded paid clicks would mostly have been organic clicks anyway, so incremental acquisitions are close to zero. Non-branded paid is the real test of paid acquisition. Split them in the keyword report and label them clearly. Branded paid is a "defensive ad" budget — keeping competitors from buying your brand term — more than a true acquisition channel.

What is a "healthy" CAC number?

There is no universal number — healthy CAC is whatever produces an LTV-to-CAC ratio above three and a payback period under twelve months for your business model. A consumer subscription with $15 monthly revenue and 18 months of retention should target CAC under $90. An enterprise SaaS with $50k annual contracts can absorb CAC in the thousands. The ratio matters far more than the absolute level.

How do you compute CAC for a specific customer segment?

Harder than channel-level CAC because spend is rarely segmented by customer type at the source. The typical path is to attribute users to a segment from a signup survey, a firmographic enrichment service, or a self-reported plan tier, then push that segment into the cohort table and join to spend. Some users land in unknown-segment — report that row explicitly rather than reallocating. Hiding unknowns by spreading them across known segments looks clean but produces numbers no one can defend in a review.

Can you calculate CAC entirely in SQL, or do you need a BI tool?

The arithmetic is pure SQL. The reporting layer — letting a non-analyst filter by date, channel, and country interactively — is where a BI tool earns its keep. Most teams settle on SQL views for the canonical computation, a BI tool on top for self-service exploration, and a scheduled Slack digest that surfaces anomalies without anyone opening a dashboard.