Blended CAC vs channel CAC: definitions, formulas, and how to build both in Snowflake

The two definitions, side by side

Blended CAC is robust and boring: there is no attribution argument to have, because it makes no attribution claim. Channel CAC is decision-useful but built on modelling choices, and ad platforms grade their own homework – add up each platform's self-reported conversions and you will usually have acquired more customers than actually exist.

When to use which

Use blended CAC for board reporting and trend: monthly grain, one fixed new-customer definition, tracked against the same series for years. Use channel CAC for the growth team's weekly budget decisions, where what matters is relative movement – Meta getting dearer while Google holds – rather than the absolute number.

The most useful signal is often the divergence between them. If blended CAC is climbing while every channel CAC looks flat, something outside the attribution net has changed: organic is decaying, a channel mix shift is under way, or spend is leaking into campaigns that acquire nobody. That comparison only works if both numbers are built from the same prepped tables, over the same date spine, with the same definition of "new customer" – which is why this is a prep and modelling problem before it is a dashboard problem.

Prep: the two tables you need

Both metrics come from two prepped tables. If your raw Shopify, Meta and Google Ads data isn't conformed yet, start with the companion guide Shopify + Meta + Google Ads in one Snowflake model – the short version is below.

• PREP_CHANNEL_SPEND – one row per day per channel, spend in one currency, one timezone, one channel vocabulary.
• MODEL_CUSTOMERS – one row per customer, with first_order_date and a first_touch_channel derived from the UTM on their first order.

-- Conform Meta + Google spend into one table
create or replace table PREP_CHANNEL_SPEND as
select cast(date_start as date)   as date_day,
       'meta'                     as channel,
       sum(spend)                 as spend
from RAW_META_ADS.INSIGHTS
group by 1, 2
union all
select segments_date              as date_day,
       'google'                   as channel,
       sum(cost_micros) / 1e6     as spend
from RAW_GOOGLE_ADS.CAMPAIGN_STATS
group by 1, 2;

Building blended CAC in Snowflake

Aggregate spend and new customers to the same grain – monthly is the sane default for reporting – and divide. The only subtlety is the guard against dividing by zero in a quiet month.

-- Monthly blended CAC: all spend over all new customers
with spend as (
  select date_trunc('month', date_day) as month,
         sum(spend)                    as total_spend
  from PREP_CHANNEL_SPEND
  group by 1
),
new_customers as (
  select date_trunc('month', first_order_date) as month,
         count(*)                              as new_customers
  from MODEL_CUSTOMERS
  group by 1
)
select s.month,
       s.total_spend,
       n.new_customers,
       round(s.total_spend / nullif(n.new_customers, 0), 2) as blended_cac
from spend s
left join new_customers n using (month)
order by 1;

Three decisions are hiding in those few lines, and they should be written down next to the dashboard: a new customer means first ever order, not first order this year; "spend" means paid acquisition spend only (decide whether gifting, affiliates and retention campaigns count); and the month boundary uses your reporting timezone, not each platform's.

Building channel CAC in Snowflake

Channel CAC needs an attribution rule. The simplest defensible one – and the right starting point for most brands – is first-touch from the UTM source on the customer's first order, conformed to the same channel vocabulary as your spend table.

-- Channel CAC for one month, first-touch attribution
with attributed as (
  select first_touch_channel         as channel,
         count(distinct customer_id) as new_customers
  from MODEL_CUSTOMERS
  where first_order_date >= '2026-06-01'
    and first_order_date <  '2026-07-01'
  group by 1
),
spend as (
  select channel,
         sum(spend) as channel_spend
  from PREP_CHANNEL_SPEND
  where date_day >= '2026-06-01'
    and date_day <  '2026-07-01'
  group by 1
)
select s.channel,
       s.channel_spend,
       a.new_customers,
       round(s.channel_spend / nullif(a.new_customers, 0), 2) as channel_cac
from spend s
left join attributed a using (channel)
order by s.channel_spend desc;

Two rules keep this trustworthy. First, customers with no usable UTM go into an explicit unattributed bucket – never spread them proportionally across paid channels, because that quietly flatters every channel CAC. Second, the join is on the conformed channel name, so the prep decision from earlier (meta, not facebook) is doing real work here: an unconformed name silently drops a channel's customers while keeping its spend.

Keeping both honest

CAC numbers decay without process. The checklist that keeps them comparable month after month:

• One date spine and one timezone across spend and orders – both dataflows read the same calendar.
• One new-customer definition, defined once in MODEL_CUSTOMERS and inherited everywhere – never re-derived per dashboard.
• Decide refund treatment explicitly: a fully refunded first order either counts as an acquisition or it doesn't, but pick once.
• Publish the attribution model next to the channel numbers, so nobody mistakes first-touch for ground truth.
• Schedule both dataflows daily, sequenced after the ad and order connectors finish syncing, with an alert on failure – a CAC that silently stopped refreshing is worse than no CAC at all.

That last point is where spreadsheets and refresh-only tools give out: this is a dependency chain, not a timer. It is also exactly the shape of Refyner's built-in Blended CAC model – revenue after ad spend and blended CAC across Meta and Google, prepped, scheduled and monitored on your own Snowflake. If you're currently rebuilding this logic inside a BI tool's dataflow feature, see Refyner vs Power BI Dataflow for where that path strains; the cost model (prep free, scheduled runs pay-as-you-go) is on the pricing page.

Keep reading: