Ad Group Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.microsoft_ads.ad_performance_daily_report}}
7 )
8 , ad_groups as (
9 select
10 *
11 from
12 {{raw.microsoft_ads.ad_group}}
13 )
14 , campaigns as (
15 select
16 *
17 from
18 {{raw.microsoft_ads.campaign}}
19 )
20 , accounts as (
21 select
22 *
23 from
24 {{raw.microsoft_ads.account}}
25 )
26 , joined as (
27 select
28 report.date date_day
29 , accounts.name account_name
30 , accounts.id account_id
31 , campaigns.campaign campaign_name
32 , campaigns.id campaign_id
33 , ad_groups.ad_group ad_group_name
34 , ad_groups.id ad_group_id
35 , report.device_os
36 , report.device_type
37 , report.network
38 , report.currency_code
39 , sum(report.clicks) as clicks
40 , sum(report.impressions) as impressions
41 , sum(report.spend) as spend -- Additional pass-through columns should be manually specified if needed
42 from
43 report
44 left join accounts on report.account_id = accounts.id
45 left join campaigns on report.campaign_id = campaigns.id
46 left join ad_groups on report.ad_group_id = ad_groups.id
47 group by
48 1
49 , 2
50 , 3
51 , 4
52 , 5
53 , 6
54 , 7
55 , 8
56 , 9
57 , 10
58 , 11 -- Adjust these numbers according to the selected columns
59 )
60select
61 *
62from
63 joined+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+
| 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 | spend |
+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+
| 2022-01-01 | Account A | 12345678901 | Campaign 1 | 98765432101 | Ad Group 1 | 12345678901 | iOS | Mobile | Search | USD | 100 | 1000 | 50.00 |
| 2022-01-01 | Account A | 12345678901 | Campaign 1 | 98765432101 | Ad Group 1 | 12345678901 | Android | Mobile | Search | USD | 150 | 2000 | 75.00 |
| 2022-01-01 | Account A | 12345678901 | Campaign 1 | 98765432101 | Ad Group 2 | 23456789012 | iOS | Mobile | Search | USD | 50 | 500 | 25.00 |
| 2022-01-01 | Account B | 23456789012 | Campaign 2 | 87654321098 | Ad Group 3 | 34567890123 | iOS | Mobile | Display | USD | 200 | 3000 | 100.00 |
| 2022-01-01 | Account B | 23456789012 | Campaign 2 | 87654321098 | Ad Group 4 | 45678901234 | Android | Mobile | Display | USD | 75 | 1000 | 50.00 |
+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+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.
