Weld logo
microsoft-ads logo
Microsoft ads

Ad Group Report

Generates a report that combines data from the "ad_performance_daily_report," "ad_group," "campaign," and "account" tables in the Microsoft Ads integration. It joins these tables based on specific columns and calculates aggregated metrics such as clicks, impressions, and spend. The resulting report includes information on the date, account, campaign, ad group, device, network, and currency code.
1with
2    report as (
3        select
4            *
5        from
6            {{raw.microsoft_ads.ad_performance_daily_report}}
7    )
8  , ad_groups as (
9        select
10            *
11        from
12            {{raw.microsoft_ads.ad_group}}
13    )
14  , campaigns as (
15        select
16            *
17        from
18            {{raw.microsoft_ads.campaign}}
19    )
20  , accounts as (
21        select
22            *
23        from
24            {{raw.microsoft_ads.account}}
25    )
26  , joined as (
27        select
28            report.date date_day
29          , accounts.name account_name
30          , accounts.id account_id
31          , campaigns.campaign campaign_name
32          , campaigns.id campaign_id
33          , ad_groups.ad_group ad_group_name
34          , ad_groups.id ad_group_id
35          , report.device_os
36          , report.device_type
37          , report.network
38          , report.currency_code
39          , sum(report.clicks) as clicks
40          , sum(report.impressions) as impressions
41          , sum(report.spend) as spend -- Additional pass-through columns should be manually specified if needed
42        from
43            report
44            left join accounts on report.account_id = accounts.id
45            left join campaigns on report.campaign_id = campaigns.id
46            left join ad_groups on report.ad_group_id = ad_groups.id
47        group by
48            1
49          , 2
50          , 3
51          , 4
52          , 5
53          , 6
54          , 7
55          , 8
56          , 9
57          , 10
58          , 11 -- Adjust these numbers according to the selected columns
59    )
60select
61    *
62from
63    joined
Example of output from model:
+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+
| date_day   | account_name | account_id  | campaign_name   | campaign_id  | ad_group_name   | ad_group_id  | device_os  | device_type  | network     | currency_code  | clicks | impressions | spend     |
+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+
| 2022-01-01 | Account A    | 12345678901 | Campaign 1      | 98765432101 | Ad Group 1      | 12345678901  | iOS        | Mobile       | Search      | USD            | 100    | 1000       | 50.00     |
| 2022-01-01 | Account A    | 12345678901 | Campaign 1      | 98765432101 | Ad Group 1      | 12345678901  | Android    | Mobile       | Search      | USD            | 150    | 2000       | 75.00     |
| 2022-01-01 | Account A    | 12345678901 | Campaign 1      | 98765432101 | Ad Group 2      | 23456789012  | iOS        | Mobile       | Search      | USD            | 50     | 500        | 25.00     |
| 2022-01-01 | Account B    | 23456789012 | Campaign 2      | 87654321098 | Ad Group 3      | 34567890123  | iOS        | Mobile       | Display     | USD            | 200    | 3000       | 100.00    |
| 2022-01-01 | Account B    | 23456789012 | Campaign 2      | 87654321098 | Ad Group 4      | 45678901234  | Android    | Mobile       | Display     | USD            | 75     | 1000       | 50.00     |
+------------+--------------+-------------+-----------------+--------------+-----------------+--------------+------------+--------------+-------------+--------+------------+-----------+

Retrieves data from the Microsoft Ads platform. It combines data from multiple tables including ad_performance_daily_report, ad_group, campaign, and account. The template uses common table expressions (CTEs) to organize the data retrieval process. The SQL code starts by creating a CTE named "report" that selects all columns from the ad_performance_daily_report table. Similarly, CTEs named "ad_groups", "campaigns", and "accounts" select all columns from their respective tables. The "joined" CTE combines the data from the previous CTEs using left joins based on the corresponding IDs. It retrieves columns such as date_day, account_name, account_id, campaign_name, campaign_id, ad_group_name, ad_group_id, device_os, device_type, network, currency_code, clicks, impressions, and spend. Additional pass-through columns can be manually specified if needed. Finally, the SQL query selects all columns from the "joined" CTE, providing a consolidated view of the data. This SQL template is useful for generating ad group reports in the Microsoft Ads platform. It allows you to analyze and gain insights into various performance metrics such as clicks, impressions, and spend. By aggregating the data at the ad group level, you can evaluate the effectiveness of different ad groups, campaigns, and accounts over time.

Ready to start modeling your own microsoft-ads data?

Get started building your data warehouse with microsoft-ads and 100+ more apps and databases available.

microsoft-ads logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.