Shopify + Meta + Google Ads in one Snowflake model

The three-silo problem

Each platform reports itself generously and describes the world differently. Shopify knows orders, customers and refunds, keyed by order and timestamped in UTC. Meta reports spend and its own attributed conversions at ad-set-by-day grain, in the ad account's timezone, restating recent days as attribution settles. Google Ads reports cost in micros, segments by its own date column, and attributes conversions by its own rules. Add the platforms' self-reported revenue together and you will usually exceed what Shopify banked – both claim the same orders.

The fix isn't a smarter dashboard connector; it's a model: a small set of dataflows that prep each source to one shape and join them where they genuinely agree – the calendar and the channel. Spend is a fact. Orders are facts. Platform-attributed revenue is an opinion, kept, but labelled.

Where this guide starts

We assume a connector – Fivetran, Airbyte, or a native loader – already lands raw data into Snowflake schemas like RAW_SHOPIFY, RAW_META_ADS and RAW_GOOGLE_ADS. Loading is a solved problem; everything after loading is the part teams get stuck on, and it follows the five prep jobs from the data prep guide: dedupe, standardise units, resolve identity, define grain, conform calendar and channels.

Prep each source to one shape

The destination shape for spend is one table, one row per day per channel, in one currency and one timezone, with one channel vocabulary. Orders prep to a clean daily revenue table alongside it.

-- One spend table, one vocabulary: 'meta', 'google'
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;

-- Orders deduped, refunds netted, UTC converted once
create or replace table PREP_DAILY_REVENUE as
with orders as (
  select order_id,
         convert_timezone('UTC', 'Europe/London', created_at) as created_local,
         total_price - coalesce(total_refunded, 0)            as net_revenue,
         case when customer_order_index = 1
              then 1 else 0 end                               as is_first_order
  from PREP_ORDERS
)
select cast(created_local as date) as date_day,
       sum(net_revenue)            as net_revenue,
       count(*)                    as orders,
       sum(is_first_order)         as new_customers
from orders
group by 1;

Every hard decision happens here, once: the reporting timezone (Europe/London above – pick yours and never revisit it mid-series), refunds netted at source, and the first-order flag derived from the resolved customer identity rather than Shopify's guest-checkout view.

The date–channel spine

Never join spend to revenue directly – a channel that was paused on Tuesday simply has no row, and an inner join silently drops the day. Build a complete spine of every date and every channel first, then left-join facts onto it, so zero-spend days exist as zeros instead of gaps.

-- A complete calendar for the last 2 years
with days as (
  select dateadd('day', seq4(), dateadd('year', -2, current_date)) as date_day
  from table(generator(rowcount => 731))
),
channels as (
  select distinct channel from PREP_CHANNEL_SPEND
)
select d.date_day, c.channel
from days d
cross join channels c
where d.date_day <= current_date;

The output model: MARKETING_DAILY

The final dataflow joins spend onto the spine, brings daily revenue alongside, and computes the ratios the business asks for. One row per day per channel, with a totals view rolling up to one row per day.

-- Spend by channel + revenue context + MER inputs
create or replace table MARKETING_DAILY as
select sp.date_day,
       sp.channel,
       coalesce(s.spend, 0)         as spend,
       r.net_revenue                as site_net_revenue,
       r.orders                     as site_orders,
       r.new_customers              as site_new_customers,
       round(r.net_revenue /
             nullif(sum(coalesce(s.spend, 0)) over (
               partition by sp.date_day
             ), 0), 2)              as mer
from SPINE sp
left join PREP_CHANNEL_SPEND s using (date_day, channel)
left join PREP_DAILY_REVENUE r using (date_day)
where sp.date_day >= dateadd('year', -2, current_date);

Note what this table deliberately does not claim: it never allocates Shopify revenue to channels. Site revenue is repeated per-day as context, and MER (revenue over total spend) is computed at the day level. Channel-level revenue attribution is a different, opinionated exercise – that's the channel CAC territory covered in Blended CAC vs channel CAC, and it should live in clearly named columns if you add it, never disguised as fact.

Keeping it running

This model is four dataflows with real dependencies: two spend preps and the revenue prep must finish before MARKETING_DAILY rebuilds, and all of them must run after the connectors sync. That is a schedule – a dependency chain with alerts – not four separate timers; the patterns are in Scheduling Snowflake dataflows. Two operational habits pay for themselves: rebuild the last few days on every run, because ad platforms restate recent spend; and check the output grain (one row per day per channel, exactly) before publishing, because a duplicate channel name is the classic silent doubler.

This is also the point where teams historically reached for a general-purpose desktop tool and a specialist to run it – rebuilding, from a blank canvas, a model that is substantially identical at every eCommerce brand. That rebuild is exactly what Refyner ships built in: the Blended CAC model covers revenue after ad spend across Meta and Google on your own Snowflake, prepped, scheduled and monitored, with the dataflows open for your analysts to adapt. The comparison with the desktop-tool route is in Refyner vs Alteryx; building and sampling are free, scheduled runs are pay-as-you-go – see pricing.

Keep reading: