Weld
Start free
Microsoft Ads model

Account Report

Generates an account report by integrating with Microsoft Ads. It retrieves data from the ad_performance_daily_report and account tables, joins them based on the account_id, and calculates metrics such as clicks, impressions, and spend. The resulting report includes information about the date, account name, account ID, time zone, device OS, device type, network, and currency code.

See the SQLAll Microsoft Ads models
Grain
day x account x device OS x device type x network x currency
Built from
  1. 2
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Microsoft Ads
accountad_performance_daily_report
Ships as
Weld and dbt SQL, open source on GitHub
core.microsoft_ads.account_report
1-- core.microsoft_ads.account_report
2-- Daily Microsoft Advertising delivery per ad account, split by device and
3-- network, with the account's name and time zone.
4--
5-- Grain: day x account x device OS x device type x network x currency.
6-- Dates are the report dates Microsoft Advertising returns, in the account's
7-- own time zone (account_timezone). Spend is in currency_code.
8-- conversions and revenue are Microsoft's own attributed figures.
9--
10-- Built from the ad-level daily report, so it counts delivery that Microsoft
11-- attributes to an ad. Check it against the account report in the Microsoft
12-- Advertising UI before relying on it.
13--
14-- Depends on: raw.microsoft_ads.ad_performance_daily_report, account
15--
16-- From the weld.app template library, rewritten. Not reconciled against
17-- Microsoft Ads; check the output against the source before relying on it.
18
19WITH delivery AS (
20    SELECT
21        date                                        AS date_day,
22        account_id,
23        device_os,
24        device_type,
25        network,
26        currency_code,
27        SUM(impressions)                            AS impressions,
28        SUM(clicks)                                 AS clicks,
29        SUM(spend)                                  AS spend,
30        SUM(conversions)                            AS conversions,
31        SUM(revenue)                                AS revenue
32    FROM {{raw.microsoft_ads.ad_performance_daily_report}}
33    GROUP BY 1, 2, 3, 4, 5, 6
34),
35
36account_lookup AS (
37    SELECT id AS account_id, name AS account_name, time_zone AS account_timezone
38    FROM {{raw.microsoft_ads.account}}
39)
40

This 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().

Notes

How it counts. And where it stops.

Generates a report that combines data from two tables: `microsoft_ads.ad_performance_daily_report` and `microsoft_ads.account`. This template is useful for analyzing and gaining insights into the performance of Microsoft Ads accounts. The SQL code begins by creating a temporary table called `report`, which contains all the columns from the `microsoft_ads.ad_performance_daily_report` table. Similarly, the `accounts` table is created to store all the columns from the `microsoft_ads.account` table. The `joined` table is then created by joining the `report` and `accounts` tables using the `account_id` column. This table includes columns such as `date_day`, `account_name`, `account_id`, `account_timezone`, `device_os`, `device_type`, `network`, `currency_code`, `clicks`, `impressions`, and `spend`. Additional pass-through columns can be manually specified if needed. Finally, the SQL code selects all columns from the `joined` table, which represents the final output of the SQL query. By running this SQL template, you can obtain a comprehensive account report that includes information about dates, account details, device types, network, currency, clicks, impressions, and spend. This report can provide valuable insights into the performance of Microsoft Ads accounts, allowing for analysis and optimization of advertising campaigns.

Raw tables it reads

  • raw.microsoft_ads.account
  • raw.microsoft_ads.ad_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.

Start for freeTalk to us