Weld logo
microsoft-ads logo
Microsoft ads

Campaign Report

Generates a campaign report by integrating with Microsoft Ads. It combines data from the campaign performance daily report, campaign, and account tables. The joined table includes information such as date, account name and ID, campaign name and ID, campaign type and status, device OS and type, network, currency code, clicks, impressions, and spend.
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
Example of output from model:
+------------+--------------+------------+-----------------+--------------+----------------+----------------+------------+--------------+----------------+-------+-------------+-------+
| 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.

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.