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.
- Grain
- day x campaign x device OS x device type x network x currency
- Built from
- 3Raw tables
- 1Core
- 1Analytics
- Reads from Microsoft Ads
- accountcampaigncampaign_performance_daily_report
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.microsoft_ads.campaign_report
2-- Daily Microsoft Advertising delivery per campaign, split by device and
3-- network, with the campaign's type and current status.
4--
5-- Grain: day x campaign x device OS x device type x network x currency.
6-- Dates are the report dates Microsoft Advertising returns, in the account's
7-- time zone. Spend is in currency_code. campaign_status is the campaign's
8-- status today, not on the report date. conversions and revenue are
9-- Microsoft's own attributed figures.
10--
11-- Depends on: raw.microsoft_ads.campaign_performance_daily_report, campaign,
12-- account
13--
14-- From the weld.app template library, rewritten. Not reconciled against
15-- Microsoft Ads; check the output against the source before relying on it.
16
17WITH delivery AS (
18 SELECT
19 date AS date_day,
20 account_id,
21 campaign_id,
22 device_os,
23 device_type,
24 network,
25 currency_code,
26 SUM(impressions) AS impressions,
27 SUM(clicks) AS clicks,
28 SUM(spend) AS spend,
29 SUM(conversions) AS conversions,
30 SUM(revenue) AS revenue
31 FROM {{raw.microsoft_ads.campaign_performance_daily_report}}
32 GROUP BY 1, 2, 3, 4, 5, 6, 7
33),
34
35campaign_lookup AS (
36 SELECT
37 id AS campaign_id,
38 campaign AS campaign_name,
39 campaign_type,
40 status AS campaign_statusThis model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.microsoft_ads.account
- raw.microsoft_ads.campaign
- raw.microsoft_ads.campaign_performance_daily_report
More Microsoft Ads models
Put Microsoft Ads in front
of your agents.
Connect Microsoft Ads, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.