ReferenceSource Models
Microsoft Ads Models
Copy-and-paste Microsoft Ads model templates for campaign-level performance reporting.
Copy and Paste these Microsoft Ads Models into your Data Layer to quickly go from raw data souces -> business ready Microsoft 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.
Campaign Level Reporting
Base
microsoft_ads_campaign_performance_base
-- campaign <> day <> device <> spend <> conversions, etc.
select
md5(
concat_ws(
'::',
account_id,
campaign_id,
date::text,
currency_code,
ad_distribution,
device_type,
network,
delivered_match_type,
device_os,
top_vs_other,
bid_match_type
)
) as id --PK
, date
, campaign_id
, campaign_name
, ad_distribution
, impressions
, clicks
, spend
, conversions
, device_type
, device_os
, account_id
, currency_code
, network
, delivered_match_type
, top_vs_other
, bid_match_type
from {{sources.microsoft_ads.campaign_performance_daily_report}}microsoft_ads_campaign_performance_daily_base
--aggregates data to a date <> campaign
select
concat(date,campaign_id) as id --PK
, date
, campaign_id
, campaign_name
, sum(spend) as spend
from {{models.microsoft_ads_campaign_performance_base}}
group by 1,2,3,4