Campaign Report
1with
2 report as (
3 select
4 *
5 from
6 {{raw.microsoft_ads.campaign_performance_daily_report}}
7 )
8 , campaigns as (
9 select
10 *
11 from
12 {{raw.microsoft_ads.campaign}}
13 )
14 , accounts as (
15 select
16 *
17 from
18 {{raw.microsoft_ads.account}}
19 )
20 , joined as (
21 select
22 report.date date_day
23 , accounts.name account_name
24 , accounts.id account_id
25 , campaigns.campaign campaign_name
26 , campaigns.id campaign_id
27 , campaigns.campaign_type
28 , campaigns.status as campaign_status
29 , report.device_os
30 , report.device_type
31 , report.network
32 , report.currency_code
33 , sum(report.clicks) as clicks
34 , sum(report.impressions) as impressions
35 , sum(report.spend) as spend -- Additional pass-through columns should be manually specified if needed
36 from
37 report
38 left join accounts on report.account_id = accounts.id
39 left join campaigns on report.campaign_id = campaigns.id
40 group by
41 1
42 , 2
43 , 3
44 , 4
45 , 5
46 , 6
47 , 7
48 , 8
49 , 9
50 , 10
51 , 11 -- Adjust these numbers according to the selected columns
52 )
53select
54 *
55from
56 joined+------------+--------------+------------+-----------------+--------------+----------------+----------------+------------+--------------+----------------+-------+-------------+-------+
| date_day | account_name | account_id | campaign_name | campaign_id | campaign_type | campaign_status| device_os | device_type | network | currency_code | clicks | spend |
+------------+--------------+------------+-----------------+--------------+----------------+----------------+------------+--------------+----------------+-------+-------------+-------+
| 2022-01-01 | Account A | 1234567890 | Campaign 1 | 9876543210 | Search | Active | iOS | Mobile | Search Network | USD | 100 | 50.00 |
| 2022-01-01 | Account A | 1234567890 | Campaign 1 | 9876543210 | Search | Active | Android | Mobile | Search Network | USD | 150 | 75.00 |
| 2022-01-01 | Account A | 1234567890 | Campaign 2 | 2468135790 | Display | Paused | iOS | Tablet | Display Network| USD | 200 | 100.00|
| 2022-01-01 | Account B | 9876543210 | Campaign 3 | 1357924680 | Video | Active | Android | Mobile | Video Network | USD | 50 | 25.00 |
+------------+--------------+------------+-----------------+--------------+----------------+----------------+------------+--------------+----------------+-------+-------------+-------+Generates a report on campaign performance using data from the Microsoft Ads integration. This SQL model combines data from three tables: campaign_performance_daily_report, campaign, and account. The SQL code begins by creating a temporary table called "report" that selects all columns from the campaign_performance_daily_report table. Similarly, two more temporary tables, "campaigns" and "accounts," are created to select all columns from the campaign and account tables, respectively. Next, a table called "joined" is created by joining the "report" table with the "accounts" and "campaigns" tables. This join is performed based on the account_id and campaign_id columns. The "joined" table includes columns such as date_day, account_name, account_id, campaign_name, campaign_id, campaign_type, campaign_status, 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" table, which represents the joined data from the previous steps. This query will return a comprehensive report on campaign performance, including metrics such as clicks, impressions, and spend, grouped by date, account, campaign, and other relevant dimensions. This SQL template can be useful for analyzing and monitoring the performance of campaigns running on the Microsoft Ads platform. It provides insights into key metrics and allows for deeper analysis of campaign performance trends over time.
