Ad Report

Generates an ad report by joining data from multiple tables related to Microsoft Ads. It retrieves information such as date, account name and ID, campaign name and ID, ad group name and ID, ad name and ID, device OS, device type, network, currency code, clicks, impressions, and spend. The template performs a left join on the specified tables and groups the data based on selected columns. The final result includes all columns from the joined data.

1with
2    report as (
3        select
4            *
5        from
6            {{raw.microsoft_ads.ad_performance_daily_report}}
7    )
8  , ads as (
9        select
10            *
11        from
12            {{raw.microsoft_ads.ad}}
13    )
14  , ad_groups as (
15        select
16            *
17        from
18            {{raw.microsoft_ads.ad_group}}
19    )
20  , campaigns as (
21        select
22            *
23        from
24            {{raw.microsoft_ads.campaign}}
25    )
26  , accounts as (
27        select
28            *
29        from
30            {{raw.microsoft_ads.account}}
31    )
32  , joined as (
33        select
34            report.date date_day
35          , accounts.name account_name
36          , accounts.id account_id
37          , campaigns.campaign campaign_name
38          , campaigns.id campaign_id
39          , ad_groups.ad_group ad_group_name
40          , ad_groups.id ad_group_id
41          , ads.title ad_name
42          , ads.title ad_name
43          , ads.id ad_id
44          , report.device_os
45          , report.device_type
46          , report.network
47          , report.currency_code
48          , sum(report.clicks) as clicks
49          , sum(report.impressions) as impressions
50          , sum(report.spend) as spend
51            -- Additional pass-through columns should be manually specified if needed
52        from
53            report
54            left join ads on report.ad_id = ads.id
55            left join ad_groups on report.ad_group_id = ad_groups.id
56            left join campaigns on report.campaign_id = campaigns.id
57            left join accounts on report.account_id = accounts.id
58        group by
59            1
60          , 2
61          , 3
62          , 4
63          , 5
64          , 6
65          , 7
66          , 8
67          , 9
68          , 10
69          , 11
70          , 12
71          , 13
72          , 14 -- Adjust these numbers according to the selected columns
73    )
74select
75    *
76from
77    joined

Example of output from model:

+------------+--------------+------------+----------------+-------------+-----------------+--------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------+-------------+-------+
|  date_day  | account_name | account_id | campaign_name  | campaign_id | ad_group_name   | ad_group_id  |     ad_name     |     ad_name     |     ad_id      |   device_os    |  device_type   |    network      | currency_code  | clicks | impressions | spend |
+------------+--------------+------------+----------------+-------------+-----------------+--------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------+-------------+-------+
| 2022-01-01 |  Microsoft   |     123    |  Winter Sales  |     456     |  Electronics    |     789      |  Best TV Deals  |  Best TV Deals  |     101010      |    Windows      |    Desktop     |    Microsoft    |      USD       |   50   |    1000     |  50.00|
| 2022-01-01 |  Microsoft   |     123    |  Winter Sales  |     456     |  Electronics    |     789      |  Best TV Deals  |  Best TV Deals  |     101010      |    Windows      |    Mobile      |    Microsoft    |      USD       |   25   |    500      |  25.00|
| 2022-01-02 |  Microsoft   |     123    |  Winter Sales  |     456     |  Electronics    |     789      |  Best TV Deals  |  Best TV Deals  |     101010      |    Windows      |    Desktop     |    Microsoft    |      USD       |   60   |    1200     |  60.00|
| 2022-01-02 |  Microsoft   |     123    |  Winter Sales  |     456     |  Electronics    |     789      |  Best TV Deals  |  Best TV Deals  |     101010      |    Windows      |    Mobile      |    Microsoft    |      USD       |   30   |    600      |  30.00|
+------------+--------------+------------+----------------+-------------+-----------------+--------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------+-------------+-------+

Retrieves data from the Microsoft Ads integration. It combines multiple tables, including ad_performance_daily_report, ad, ad_group, campaign, and account, to generate a comprehensive report on ad performance. The SQL code begins by creating temporary tables for each of the relevant tables from the Microsoft Ads integration. These tables are then joined together using left joins based on the respective IDs. The joined table includes columns such as date_day, account_name, account_id, campaign_name, campaign_id, ad_group_name, ad_group_id, ad_name, ad_id, device_os, device_type, network, currency_code, clicks, impressions, and spend. This SQL template is useful for analyzing and reporting on ad performance data from Microsoft Ads. By aggregating data from different tables, it provides insights into various metrics such as clicks, impressions, and spend across different dimensions like date, account, campaign, ad group, and ad. This information can help advertisers and marketers evaluate the effectiveness of their advertising campaigns, identify trends, and make data-driven decisions to optimize their advertising strategies.

Ad Report

Ready to start modeling your own microsoft-ads data?