Microsoft ads
Aggregated Daily Campaign Performance Report
Extracts a daily breakdown of advertising performance on Bing, grouped by campaigns. It integrates data from the daily campaign performance report with the campaign table to deliver metrics like clicks, impressions, and spend for each campaign.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.Microsoft.campaign_performance_daily_report}}
7 )
8 , campaign as (
9 select
10 *
11 from
12 {{raw.Microsoft.campaign}}
13 )
14 , joined as (
15 select
16 date
17 , campaign.campaign as campaign_name
18 , report.campaign_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 campaign on report.campaign_id = campaign.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 | campaign_name| campaign_id | device_os | device_type | network | currency | clicks | impressions | spend |
+------------+--------------+--------------+------------+----------------+--------------+----------+--------+-------------+-------+
| 2023-04-20 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1000 | 5000 | 150.0 |
| 2023-04-19 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1100 | 5100 | 160.0 |
| 2023-04-18 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1150 | 5200 | 165.0 |
| 2023-04-17 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1200 | 5300 | 170.0 |
| 2023-04-16 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1250 | 5400 | 175.0 |
| 2023-04-15 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1300 | 5500 | 180.0 |
| 2023-04-14 | Campaign_One | camp_001 | Windows | Desktop | Bing | USD | 1350 | 5600 | 185.0 |
+------------+--------------+--------------+------------+----------------+--------------+----------+--------+-------------+-------+This SQL model presents a granular, daily snapshot of advertising activity on Bing at the campaign level. By merging data from the daily campaign performance report with campaign-specific details, advertisers can understand metrics such as clicks, impressions, and total expenditure for each campaign. This comprehensive analysis aids in optimizing advertising strategies, focusing on the performance of individual campaigns based on devices, networks, and other factors.
