Ad Group Report
Generates a report that combines data from the "ad_performance_daily_report," "ad_group," "campaign," and "account" tables in the Microsoft Ads integration. It joins these tables based on specific columns and calculates aggregated metrics such as clicks, impressions, and spend. The resulting report includes information on the date, account, campaign, ad group, device, network, and currency code.
- Grain
- day x ad group x device OS x device type x network x currency
- Built from
- 4Raw tables
- 1Core
- 1Analytics
- Reads from Microsoft Ads
- accountad_groupad_performance_daily_reportcampaign
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.microsoft_ads.ad_group_report
2-- Daily Microsoft Advertising delivery per ad group, split by device and
3-- network, labelled with its campaign and account.
4--
5-- Grain: day x ad group 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. conversions and revenue are
8-- Microsoft's own attributed figures.
9--
10-- Built from the ad-level daily report rolled up to the ad group.
11--
12-- Depends on: raw.microsoft_ads.ad_performance_daily_report, ad_group,
13-- campaign, account
14--
15-- From the weld.app template library, rewritten. Not reconciled against
16-- Microsoft Ads; check the output against the source before relying on it.
17
18WITH delivery AS (
19 SELECT
20 date AS date_day,
21 account_id,
22 campaign_id,
23 ad_group_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
35),
36
37ad_group_lookup AS (
38 SELECT id AS ad_group_id, ad_group AS ad_group_name
39 FROM {{raw.microsoft_ads.ad_group}}
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 platform. It combines data from multiple tables including ad_performance_daily_report, ad_group, campaign, and account. The template uses common table expressions (CTEs) to organize the data retrieval process. The SQL code starts by creating a CTE named "report" that selects all columns from the ad_performance_daily_report table. Similarly, CTEs named "ad_groups", "campaigns", and "accounts" select all columns from their respective tables. The "joined" CTE combines the data from the previous CTEs using left joins based on the corresponding IDs. It retrieves columns such as date_day, account_name, account_id, campaign_name, campaign_id, ad_group_name, ad_group_id, device_os, device_type, network, currency_code, clicks, impressions, and spend. Additional pass-through columns can be manually specified if needed. Finally, the SQL query selects all columns from the "joined" CTE, providing a consolidated view of the data. This SQL template is useful for generating ad group reports in the Microsoft Ads platform. It allows you to analyze and gain insights into various performance metrics such as clicks, impressions, and spend. By aggregating the data at the ad group level, you can evaluate the effectiveness of different ad groups, campaigns, and accounts over time.
Raw tables it reads
- raw.microsoft_ads.account
- 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.