Microsoft ads
Aggregated Daily Account Report
Extracts a daily breakdown of advertising performance on Bing, grouped by accounts. It integrates data from the daily account performance report with the account table to deliver metrics like clicks, impressions, and spend for each account.
1with
2 report as (
3 select
4 *
5 from
6 {{raw.Microsoft.account_performance_daily_report}}
7 )
8 , accounts as (
9 select
10 *
11 from
12 {{raw.Microsoft.account}}
13 )
14 , joined as (
15 select
16 date
17 , accounts.name as account_name
18 , report.account_id
19 , accounts.time_zone as account_timezone
20 , report.device_os
21 , report.device_type
22 , report.network
23 , report.currency_code
24 , sum(clicks) as clicks
25 , sum(impressions) as impressions
26 , sum(spend) as spend
27 from
28 report
29 left join accounts on report.account_id = accounts.id
30 group by
31 1
32 , 2
33 , 3
34 , 4
35 , 5
36 , 6
37 , 7
38 , 8
39 )
40select
41 *
42from
43 joinedExample of output from model:
+------------+--------------+------------+------------+----------------+--------------+----------+--------+-------------+-------+
| date | account_name | account_id | device_os | device_type | network | currency | clicks | impressions | spend |
+------------+--------------+------------+------------+----------------+--------------+----------+--------+-------------+-------+
| 2023-04-20 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1000 | 5000 | 150.0 |
| 2023-04-19 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1100 | 5100 | 160.0 |
| 2023-04-18 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1150 | 5200 | 165.0 |
| 2023-04-17 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1200 | 5300 | 170.0 |
| 2023-04-16 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1250 | 5400 | 175.0 |
| 2023-04-15 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1300 | 5500 | 180.0 |
| 2023-04-14 | Account_One | acc_001 | Windows | Desktop | Bing | USD | 1350 | 5600 | 185.0 |
+------------+--------------+------------+------------+----------------+--------------+----------+--------+-------------+-------+This SQL model delivers a granular, daily report of advertising activity on Bing at the account level. By merging insights from the daily account performance report with account-specific data, advertisers get a comprehensive view of metrics like clicks, impressions, and total expenditure. This detailed analysis aids in refining advertising strategies by examining the performance of individual accounts based on device, network, and other attributes.
