Programmatic Marketing Model

Copy and Paste these Programmatic Marketing Models into your Data Layer to quickly go from raw data sources to business ready cross-channel marketing reporting. Please note:

• Models must be added chronological order from top to bottom (so that they can reference each other).
• When adding your own models you must update the reference to include your source names.
• The set of channels is tenant-specific — add or remove UNION branches to match the ad platforms connected in your workspace.
• All spend, conversion, and impression metrics are normalized to a common daily schema so channels stack cleanly; data_source labels each row's origin.

Campaign Level:

Base

A single UNION that normalizes each ad channel into one common daily schema, one row per campaign per day, tagged with data_source. Tailor the branches to your connected sources. When adding a channel:

• Select the same columns in the same order; emit null :: float for metrics a channel doesn't provide.
• Aggregate more granular sources (ad/adset level) up to campaign × day.
• Resolve channel-specific quirks (name lookups, attribution joins, micros→dollars) inside that branch, not downstream.

daily_programmatic_marketing_performance_base

select 
  id 
  , report_date
  , campaign_id
  , campaign_name
  , n_conversions
  , conversions_in_dollars
  , n_impressions
  , spend
  , n_clicks
  , 'google ads' as data_source 
from {{models.google_campaign_performance_base}}

union 

select 
  b.id 
  , b.report_date
  , b.campaign_id
  , hist.campaign_name
  , b.n_conversions
  , b.conversions_in_dollars
  , b.n_impressions
  , b.spend
  , b.n_clicks
  , 'tiktok ads' as data_source 
from {{models.tiktok_campaigns_performance_base}} b 
left join {{models.tiktok_campaign_history_base}} hist on b.campaign_id = hist.campaign_id 
  and hist.index = 1 

union 

select 
  cb.id 
  , cb.report_date
  , cb.campaign_id
  , cb.campaign_name
  , action_fact.n_purchases as n_conversions
  , null :: float as conversions_in_dollars
  , cb.n_impressions
  , cb.spend
  , cb.n_clicks
  , 'Facebook ads' as data_source 
from {{models.facebook_basic_campaigns_base}} cb 
left join {{models.facebook_campaign_action_facts}} action_fact on cb.campaign_id = action_fact.campaign_id
  and cb.report_date = action_fact.date 

Output

A thin, channel-agnostic layer on top of the base — no per-channel logic here. It exposes the normalized metrics and adds derived efficiency ratios (ctr, cpc, cost_per_conversion, roas), guarded against divide-by-zero. This is the model dashboards and explores read from.

Note: the ratio columns are row-level; for correct blended figures across rows, use the custom ratio aggregations (e.g. ROAS = Sum(conversion value) / Sum(spend)) rather than summing/averaging the row-level ratio.

daily_programmatic_marketing_performance_output

select
    b.id --pk
  , b.report_date
  , b.data_source
  , b.campaign_id
  , b.campaign_name
  , b.n_impressions
  , b.n_clicks
  , b.spend
  , b.n_conversions
  , b.conversions_in_dollars
  -- derived efficiency metrics (guarded against divide-by-zero)
  , b.n_clicks::float / nullif(b.n_impressions, 0) as ctr
  , b.spend / nullif(b.n_clicks, 0) as cpc
  , b.spend / nullif(b.n_conversions, 0) as cost_per_conversion
  , b.conversions_in_dollars / nullif(b.spend, 0) as roas
from {{models.daily_programmatic_marketing_performance_base}} b

✅ Show model in documents

Aggregations

• CTR

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.n_clicks}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.n_impressions}}), 0)

• CPC

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.spend}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.n_clicks}}), 0)

• CPM

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.spend}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.n_impressions}}), 0) * 1000

• Cost per Conversion

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.spend}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.n_conversions}}), 0)

• Conversion Rate

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.spend}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.n_conversions}}), 0)

• ROAS

  • Customer Formula

    SUM({{prop.daily_programmatic_marketing_performance_output.conversions_in_dollars}})
    / NULLIF(SUM({{prop.daily_programmatic_marketing_performance_output.spend}}), 0)

• Total Spend
  • Property: spend
  • Operator: Sum
• Total Impressions
  • Property: n_impressions
  • Operator: Sum
• Total Clicks
  • Property: n_clicks
  • Operator: Sum
• Total Conversions
  • Property: n_conversions
  • Operator: Sum
• Total Conversion Value
  • Property: conversions_in_dollars
  • Operator: Sum
• Campaigns
  • Property: campaign_id
  • Operator: Count Distinct