May 18, 2026·12 min read

How to calculate blended CAC in SQL

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Contents:

What blended CAC is and why boards trust it

Paid CAC is the metric your performance marketing team lives inside. Take everything you spent on Google Ads, Meta, and TikTok, divide by users who clicked through one of those ads, and you have a number you can optimize daily. It is clean and tightly scoped. It is also a partial picture, because half the business does not show up in either the numerator or the denominator.

Blended CAC is the honest version. The numerator is the total cost of acquiring customers across every motion — paid ads plus content team payroll plus SEO tooling plus the sales reps who close enterprise deals plus the attribution stack that ties it together. The denominator is every new customer the business added, whether they came from a Google search, a referral, a podcast spot, or a cold outbound email. When a board at Stripe, Notion, or DoorDash asks "what does it really cost us to add a customer," blended CAC is the only answer that survives a follow-up question.

This post walks through the SQL you need to compute blended CAC end to end: the base query, the paid versus blended decomposition, monthly dynamics, and the pitfalls that quietly distort the ratio. Every query is runnable in Postgres with minor edits for Snowflake, BigQuery, or Redshift.

Formula and the data model

The base definition fits on one line. Add up everything you spent on getting customers in a period, divide by everyone who became a customer in that same period.

blended_CAC = (total marketing + sales spend) / (total new customers)

The relationship between blended and paid is the most useful fact in the room when leadership asks why CAC moved.

blended_CAC = paid_share × paid_CAC + organic_share × organic_CAC

If organic CAC is effectively zero variable cost, the blended number is dragged down by every organic signup. The bigger the organic share, the wider the gap between paid and blended. That gap tells you how much of growth is "free" relative to the paid engine.

For every example below, assume two tables that match what you would find in a healthy product warehouse — a marketing spend ledger and a users dimension.

marketing_spend(amount, spend_date, channel_type, channel)
  -- channel_type: 'paid', 'content', 'sales', 'tooling', 'brand'

users(user_id, created_at, utm_source, acquisition_channel)

The spend table is wider than just paid ad platforms — it captures headcount allocation, SaaS tooling, and brand spend that finance treats as acquisition cost. The users table captures everyone who signed up, with UTM and an internal acquisition_channel label that has been reconciled across last-touch and self-reported attribution.

The base blended CAC query

The simplest version of blended CAC asks one question for one period: what did we spend, how many customers did we add, and what does the ratio look like. The query below does this for a single calendar month.

WITH spend AS (
    SELECT SUM(amount) AS total_spend
    FROM marketing_spend
    WHERE spend_date >= '2026-04-01'
      AND spend_date <  '2026-05-01'
),
all_new AS (
    SELECT COUNT(*) AS new_users
    FROM users
    WHERE created_at >= '2026-04-01'
      AND created_at <  '2026-05-01'
)
SELECT
    s.total_spend,
    a.new_users,
    s.total_spend / NULLIF(a.new_users, 0) AS blended_cac
FROM spend s
CROSS JOIN all_new a;

The date filter is a half-open interval on both sides — that is intentional, and it is the only way to make month-over-month comparisons add up without double-counting boundary days. The NULLIF guard avoids divide-by-zero in months when acquisition collapsed to nothing, which happens more often than you would expect during early-stage product pivots.

If you are computing blended CAC for a B2B business, total_spend must include the sales organization. A loaded SDR at a Series B startup costs roughly $120k to $150k per year fully burdened, and a senior AE running mid-market deals at a company like Linear or Vercel is north of $250k. Leaving sales out of the numerator understates true CAC by a factor of two for any sales-led motion.

The most informative single chart in a growth review is paid CAC and blended CAC plotted together over time. The gap between them is the value of your organic engine, and the trend of the gap tells you whether organic is keeping up with paid spend or being outrun by it.

WITH paid_spend AS (
    SELECT SUM(amount) AS spend
    FROM marketing_spend
    WHERE channel_type = 'paid'
      AND spend_date >= '2026-04-01'
      AND spend_date <  '2026-05-01'
),
paid_users AS (
    SELECT COUNT(*) AS n
    FROM users
    WHERE utm_source IN ('google_ads', 'meta_ads', 'tiktok_ads', 'reddit_ads')
      AND created_at >= '2026-04-01'
      AND created_at <  '2026-05-01'
),
total_spend AS (
    SELECT SUM(amount) AS spend
    FROM marketing_spend
    WHERE spend_date >= '2026-04-01'
      AND spend_date <  '2026-05-01'
),
all_users AS (
    SELECT COUNT(*) AS n
    FROM users
    WHERE created_at >= '2026-04-01'
      AND created_at <  '2026-05-01'
)
SELECT
    ps.spend / NULLIF(pu.n, 0)     AS paid_cac,
    ts.spend / NULLIF(au.n, 0)     AS blended_cac,
    ps.spend / NULLIF(pu.n, 0)
      - ts.spend / NULLIF(au.n, 0) AS cac_gap,
    1.0 * pu.n / NULLIF(au.n, 0)   AS paid_share
FROM paid_spend ps
CROSS JOIN paid_users pu
CROSS JOIN total_spend ts
CROSS JOIN all_users au;

A wide gap — paid CAC several multiples of blended — means organic is doing real work, and the blended number is the one that reflects unit economics. A narrow gap means the business is buying most of its growth. Neither is inherently bad, but the two states call for different capital allocation decisions.

A subtle trap: defining "paid users" purely by UTM source produces an undercount, because some paid users will land via a paid ad, leave, and return through a direct or organic touch. Attribution windows of seven to thirty days, with last-paid-touch precedence, are the default for performance marketing teams at companies like Airbnb and Uber. If your warehouse exposes that attribution in a paid_touched_within_30d flag, prefer it over raw UTM.

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Monthly dynamics and trend signal

The single-period blended CAC is fine for a board slide, but the more useful artifact is the monthly time series. A blended CAC that is steadily rising means each month of growth costs more than the last — and unless ARPU or LTV is rising in lockstep, payback period is quietly stretching out.

WITH monthly_spend AS (
    SELECT
        DATE_TRUNC('month', spend_date)::DATE AS month,
        SUM(amount)                           AS spend
    FROM marketing_spend
    GROUP BY DATE_TRUNC('month', spend_date)
),
monthly_users AS (
    SELECT
        DATE_TRUNC('month', created_at)::DATE AS month,
        COUNT(*)                              AS new_users
    FROM users
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    s.month,
    s.spend,
    u.new_users,
    s.spend / NULLIF(u.new_users, 0)                              AS blended_cac,
    LAG(s.spend / NULLIF(u.new_users, 0)) OVER (ORDER BY s.month) AS prev_cac,
    s.spend / NULLIF(u.new_users, 0)
      - LAG(s.spend / NULLIF(u.new_users, 0)) OVER (ORDER BY s.month)
        AS mom_change
FROM monthly_spend s
JOIN monthly_users u USING (month)
ORDER BY s.month;

The LAG window function gives you month-over-month change in a single query, which is what a finance partner will ask for the moment they see the blended CAC line. A rising trend over three or more consecutive months signals something structural — channel saturation, audience exhaustion, or a competitor outbidding you on auction-based platforms. A one-month spike is usually a launch artifact and rarely worth a war room. For seasonal businesses, compute year-over-year change instead by swapping in LAG(..., 12).

Common pitfalls

The most common mistake teams make on their first blended CAC pull is leaving sales costs out of the numerator. For any product with a real sales motion — even a freemium SaaS where a small AE team handles enterprise upgrades — the headcount allocation can easily match the paid ad budget. A blended CAC that excludes sales runs at roughly half of true CAC for a sales-led business, and the gap shows up violently the first time finance ties the dashboard back to the income statement.

Another frequent trap is counting only paid users in the blended denominator. The whole point of the blended number is that the denominator includes everyone — organic signups, referrals, branded search, and self-serve trials that converted. Filtering those out turns blended back into paid CAC under a different name, and the resulting metric usually looks much worse than reality.

Fully loading headcount costs that are only partly dedicated to acquisition is another silent inflator. A designer who spends fifty percent of their time on marketing assets and fifty percent on product UI should be allocated at fifty percent into the acquisition numerator, not at one hundred. Most finance systems already track this via project codes — pull the allocation table once instead of guessing.

Ignoring tooling costs is the inverse mistake. Attribution platforms, marketing automation, CRM seats for the marketing org, and brand monitoring belong in the CAC numerator. At a Series B or later company these tools easily add up to ten to fifteen percent of paid spend, and dropping them understates CAC enough that LTV-to-CAC ratios get reported a quarter point too high.

Comparing your blended CAC against another company's published number without normalizing for stage, model, and segment is the fastest way to draw the wrong conclusion. A late-stage product with two million customers and a mature brand will have wildly different CAC than a Series A team buying its first thousand users. Likewise, a product-led growth company will compare unfavorably with a sales-led one if you forget to back out the sales numerator. Always state the comparison set explicitly when sharing the number with anyone outside the analytics team.

Optimization tips

Partitioning marketing_spend and users by spend_date and created_at respectively at the month grain keeps the queries linear in months scanned rather than total table size, which matters once history crosses three years. The monthly dynamics query is a natural fit for a materialized view that refreshes daily, so the BI tool can render the chart instantly without re-aggregating millions of users and tens of thousands of spend rows on every dashboard load. Snowflake's incremental materialized views and BigQuery's scheduled queries both handle the refresh cadence cleanly.

If the spend table has channel-level granularity at the day level, consider denormalizing into a wide monthly fact with columns for month, paid_spend, content_spend, sales_spend, tooling_spend, and brand_spend. The wide shape is easier to query and version-control than a thin pivot, and most finance tooling expects it anyway.

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

FAQ

Boards, investors, and the CFO almost always want blended, because it is the only number that ties cleanly back to the income statement and the cash burn line. Performance marketing teams correctly live inside paid CAC, because that is the lever they pull daily. Both numbers belong on the same dashboard; the gap between them is the most informative signal of all and should be plotted as its own series.

What does a "good" blended CAC look like by business model?

There is no universal answer without context, but rough benchmarks help. Consumer subscription apps typically run blended CAC in the $50 to $200 range, mid-market B2B SaaS lands in the $500 to $2,000 band, and enterprise B2B routinely sits above $5,000 with some industrial verticals north of $20,000. The right reference point is always your own LTV-to-CAC ratio — a CAC of $1,500 is excellent for a product with $15,000 LTV and catastrophic for a product with $1,500 LTV.

Should refunded or quickly churned customers count in the denominator?

Finance teams usually compute two versions side by side. Gross new customers includes everyone who signed up; net new customers subtracts those who refunded or churned inside the first thirty or sixty days. The net-new version is more conservative and is the one most boards prefer for unit economics discussions, while the gross version is what performance marketing reports against ad platforms.

How do I treat fully loaded versus partly allocated headcount?

Allocate pro-rata by time. A senior PMM who runs ABM forty percent of the time should land in the numerator at forty percent of their fully loaded cost. Most companies past Series A maintain a project allocation table in finance — pull from that table rather than guessing. If no allocation exists, an interim split that everyone signs off on is better than a number that has the wrong absolute level by a factor of two or three.

Should a free trial conversion count as a CAC event?

CAC is conventionally computed per paid customer, not per signup, because the goal is to measure the cost of acquiring revenue rather than activity. The trial-to-paid conversion rate is its own metric and belongs on a separate row of the funnel. If you have a freemium product where a significant share of revenue comes from upgrades months after signup, you might also compute a "qualified lead" CAC for monitoring the top of the funnel, but it should never replace the paid-customer-denominated CAC for unit economics work.

Does brand spend belong in blended CAC?

For acquisition-focused CAC, yes — brand investments that move organic and direct traffic should sit in the numerator. Most growth-mature companies include brand in the blended numerator and publish a "brand-adjusted" blended CAC alongside the headline number so reviewers can see both views.

Related reading