Account Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.microsoft_ads.ad_performance_daily_report}}
7 )
8 , accounts as (
9 select
10 *
11 from
12 {{raw.microsoft_ads.account}}
13 )
14 , joined as (
15 select
16 report.date as date_day
17 , accounts.name account_name
18 , accounts.id 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(report.clicks) as clicks
25 , sum(report.impressions) as impressions
26 , sum(report.spend) as spend -- Additional pass-through columns should be manually specified if needed
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 -- Adjust these numbers according to the selected columns
39 )
40select
41 *
42from
43 joined+------------+----------------+-------------+-------------------+------------+-------------+---------+----------------+--------+-------------+-------+
| date_day | account_name | account_id | account_timezone | device_os | device_type | network | currency_code | clicks | impressions | spend |
+------------+----------------+-------------+-------------------+------------+-------------+---------+----------------+--------+-------------+-------+
| 2022-01-01 | Account A | 123456789 | UTC | iOS | Mobile | Search | USD | 100 | 1000 | 50.00 |
| 2022-01-01 | Account B | 987654321 | UTC | Android | Tablet | Display | USD | 200 | 2000 | 100.00|
| 2022-01-02 | Account A | 123456789 | UTC | iOS | Mobile | Search | USD | 150 | 1500 | 75.00 |
| 2022-01-02 | Account B | 987654321 | UTC | Android | Tablet | Display | USD | 250 | 2500 | 125.00|
+------------+----------------+-------------+-------------------+------------+-------------+---------+----------------+--------+-------------+-------+Generates a report that combines data from two tables: `microsoft_ads.ad_performance_daily_report` and `microsoft_ads.account`. This template is useful for analyzing and gaining insights into the performance of Microsoft Ads accounts. The SQL code begins by creating a temporary table called `report`, which contains all the columns from the `microsoft_ads.ad_performance_daily_report` table. Similarly, the `accounts` table is created to store all the columns from the `microsoft_ads.account` table. The `joined` table is then created by joining the `report` and `accounts` tables using the `account_id` column. This table includes columns such as `date_day`, `account_name`, `account_id`, `account_timezone`, `device_os`, `device_type`, `network`, `currency_code`, `clicks`, `impressions`, and `spend`. Additional pass-through columns can be manually specified if needed. Finally, the SQL code selects all columns from the `joined` table, which represents the final output of the SQL query. By running this SQL template, you can obtain a comprehensive account report that includes information about dates, account details, device types, network, currency, clicks, impressions, and spend. This report can provide valuable insights into the performance of Microsoft Ads accounts, allowing for analysis and optimization of advertising campaigns.
