Weld logo
microsoft-ads logo
Microsoft ads

Account Report

Generates an account report by integrating with Microsoft Ads. It retrieves data from the ad_performance_daily_report and account tables, joins them based on the account_id, and calculates metrics such as clicks, impressions, and spend. The resulting report includes information about the date, account name, account ID, time zone, device OS, device type, network, and currency code.
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
Example of output from model:
+------------+----------------+-------------+-------------------+------------+-------------+---------+----------------+--------+-------------+-------+
| 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.

Ready to start modeling your own microsoft-ads data?

Get started building your data warehouse with microsoft-ads and connect all your apps and databases.

microsoft-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.