Ad Report
Generates an ad report by joining data from multiple tables related to Microsoft Ads. It retrieves information such as date, account name and ID, campaign name and ID, ad group name and ID, ad name and ID, device OS, device type, network, currency code, clicks, impressions, and spend. The template performs a left join on the specified tables and groups the data based on selected columns. The final result includes all columns from the joined data.
- Grain
- day x ad x device OS x device type x network x currency
- Built from
- 5Raw tables
- 1Core
- 1Analytics
- Reads from Microsoft Ads
- accountadad_groupad_performance_daily_reportcampaign
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.microsoft_ads.ad_report
2-- Daily Microsoft Advertising delivery per ad, split by device and network,
3-- labelled with its ad group, campaign and account.
4--
5-- Grain: day x ad x device OS x device type x network x currency.
6-- Dates are the report dates Microsoft Advertising returns, in the account's
7-- time zone. Spend is in currency_code. ad_name is the ad's title, which some
8-- ad types leave empty. conversions and revenue are Microsoft's own attributed
9-- figures.
10--
11-- Depends on: raw.microsoft_ads.ad_performance_daily_report, ad, ad_group,
12-- campaign, account
13--
14-- From the weld.app template library, rewritten and corrected. Not reconciled
15-- against Microsoft Ads; check the output against the source before relying on it.
16
17WITH delivery AS (
18 SELECT
19 date AS date_day,
20 account_id,
21 campaign_id,
22 ad_group_id,
23 ad_id,
24 device_os,
25 device_type,
26 network,
27 currency_code,
28 SUM(impressions) AS impressions,
29 SUM(clicks) AS clicks,
30 SUM(spend) AS spend,
31 SUM(conversions) AS conversions,
32 SUM(revenue) AS revenue
33 FROM {{raw.microsoft_ads.ad_performance_daily_report}}
34 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
35),
36
37ad_lookup AS (
38 SELECT id AS ad_id, title AS ad_name
39 FROM {{raw.microsoft_ads.ad}}
40),This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
Retrieves data from the Microsoft Ads integration. It combines multiple tables, including ad_performance_daily_report, ad, ad_group, campaign, and account, to generate a comprehensive report on ad performance. The SQL code begins by creating temporary tables for each of the relevant tables from the Microsoft Ads integration. These tables are then joined together using left joins based on the respective IDs. The joined table includes columns such as date_day, account_name, account_id, campaign_name, campaign_id, ad_group_name, ad_group_id, ad_name, ad_id, device_os, device_type, network, currency_code, clicks, impressions, and spend. This SQL template is useful for analyzing and reporting on ad performance data from Microsoft Ads. By aggregating data from different tables, it provides insights into various metrics such as clicks, impressions, and spend across different dimensions like date, account, campaign, ad group, and ad. This information can help advertisers and marketers evaluate the effectiveness of their advertising campaigns, identify trends, and make data-driven decisions to optimize their advertising strategies.
Raw tables it reads
- raw.microsoft_ads.account
- raw.microsoft_ads.ad
- raw.microsoft_ads.ad_group
- raw.microsoft_ads.ad_performance_daily_report
- raw.microsoft_ads.campaign
More Microsoft Ads models
Put Microsoft Ads in front
of your agents.
Connect Microsoft Ads, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.