ReferenceSource Models
Meta (Facebook) Ads Models
Copy-and-paste Meta (Facebook) Ads model templates for ad-level and campaign-level performance reporting.
Copy and Paste these Facebook Ads Models into your Data Layer to quickly go from raw data souces -> business ready Facebook Ads 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.
Ad Level Reporting
facebook_ads_ad_performance
select
md5(ad_id ||'-'|| date) as id --PK
, date
, ad_id
, adset_id
, campaign_id
, ad_name
, impressions as n_impressions
, reach
, inline_link_clicks as n_inline_link_clicks
, spend
from {{sources.facebook_ads.ad_performance}}Campaign Level Reporting
Base
facebook_basic_campaigns_base
-- campaign <> day <> spend and other non conversion metrics
select
concat(c.date,'-', c.campaign_id) as id --PK
, c.date as report_date
, c.campaign_id
, c.campaign_name
, c.impressions as n_impressions -- # of times ad was shown on people's screens
, c.reach -- total unique people who saw ad
, c.inline_link_clicks as n_clicks
, c.spend
from {{sources.facebook.basic_campaign}} c facebook_campaign_actions_base
select
md5(campaign_id ||'-'|| date ||'-'|| action_type ) as id --PK
, campaign_id
, date
, index
, action_type
, value
, _7_d_click
, _1_d_view
from {{sources.meta_ads.basic_campaign_actions}}facebook_campaign_cost_per_action_type_base
This Data Model is not yet completed. Fact
facebook_campaign_action_facts
select
campaign_id --PK
, date
, sum(case when action_type = 'omni_purchase' then value else null end) as n_purchases
, sum(case when action_type = 'omni_purchase' then "_7_d_click" else null end) as n_purchases_7d_click
, sum(case when action_type = 'omni_purchase' then "_1_d_view" else null end) as n_purchases_1_d_click
from {{models.facebook_campaign_actions_base}}
group by 1,2