Microsoft ads
Aggregated Daily Ad Group Performance Report
Extracts a daily breakdown of advertising performance on Bing, grouped by ad groups. It integrates data from the daily ad group performance report with the ad group table to deliver metrics like clicks, impressions, and spend for each ad group.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.Microsoft.ad_group_performance_daily_report}}
7 )
8 , ad_group as (
9 select
10 *
11 from
12 {{raw.Microsoft.ad_group}}
13 )
14 , joined as (
15 select
16 date
17 , ad_group.ad_group as ad_group_name
18 , report.ad_group_id
19 , report.device_os
20 , report.device_type
21 , report.network
22 , report.currency_code
23 , sum(clicks) as clicks
24 , sum(impressions) as impressions
25 , sum(spend) as spend
26 from
27 report
28 left join ad_group on report.ad_group_id = ad_group.id
29 group by
30 1
31 , 2
32 , 3
33 , 4
34 , 5
35 , 6
36 , 7
37 )
38select
39 *
40from
41 joinedExample of output from model:
+------------+--------------+--------------+------------+----------------+--------------+----------+--------+-------------+-------+
| date | ad_group_name| ad_group_id | device_os | device_type | network | currency | clicks | impressions | spend |
+------------+--------------+--------------+------------+----------------+--------------+----------+--------+-------------+-------+
| 2023-04-20 | AdGroup_One | group_001 | Windows | Desktop | Bing | USD | 200 | 4000 | 50.0 |
| 2023-04-19 | AdGroup_One | group_001 | Windows | Desktop | Bing | 190 | 3900 | 48.5 |
| 2023-04-18 | AdGroup_One | group_001 | Windows | Desktop | Bing | 185 | 3850 | 47.0 |
| 2023-04-17 | AdGroup_One | group_001 | Windows | Desktop | Bing | 180 | 3800 | 46.0 |
| 2023-04-16 | AdGroup_One | group_001 | Windows | Desktop | Bing | 175 | 3750 | 45.0 |
| 2023-04-15 | AdGroup_One | group_001 | Windows | Desktop | Bing | 170 | 3700 | 44.0 |
| 2023-04-14 | AdGroup_One | group_001 | Windows | Desktop | Bing | 165 | 3650 | 43.0 |
+------------+--------------+--------------+------------+----------------+--------------+----------+--------+-------------+-------+This SQL model offers a granular, daily insight into advertising activity on Bing at the ad group level. By fusing data from the daily ad group performance report with ad group-specific details, advertisers gain a clear perspective on metrics such as clicks, impressions, and total expenditure for each ad group. This in-depth analysis aids advertisers in refining their strategies, focusing on the performance of individual ad groups based on devices, networks, and other considerations.
