Ad performance daily

1-- core.microsoft_ads.ad_performance_daily
2-- Daily delivery and conversions per ad, the Microsoft Advertising "Ads" report
3-- by day.
4--
5-- Grain: date_day x account_id x ad_id. Currency: the account's.
6-- Depends on: staging.microsoft_ads.ad_performance_daily_report, .ad, .ad_group,
7-- .campaign, .account
8--
9-- The leading columns follow the cross-channel ad contract shared with the other
10-- paid ads templates in this repo, so the models can be stacked with UNION ALL.
11--
12-- conversions counts the conversion goals set to "Include in conversions",
13-- under each goal's own conversion window, dated on the day of the ad click.
14-- conversion_value is the matching revenue. all_conversions and all_revenue count
15-- every goal, including those left out of conversions. View-through
16-- conversions are also carried on their own.
17--
18-- Covers ads only: Performance Max asset groups have no ad level rows, so this
19-- model sums to less than campaign_performance_daily in accounts that run them.
20
21WITH daily AS (
22    -- The report splits each ad and day by device, network, match type and more;
23    -- collapse to one row per ad. campaign and ad group never change for an ad.
24    SELECT
25        date_day,
26        account_id,
27        ad_id,
28        MAX(campaign_id)              AS campaign_id,
29        MAX(ad_group_id)              AS ad_group_id,
30        MAX(currency)                 AS report_currency,
31        SUM(spend)                    AS spend,
32        SUM(impressions)              AS impressions,
33        SUM(clicks)                   AS clicks,
34        SUM(conversions)              AS conversions,
35        SUM(revenue)                  AS conversion_value,
36        SUM(all_conversions)          AS all_conversions,
37        SUM(all_revenue)              AS all_revenue,
38        SUM(view_through_conversions) AS view_through_conversions
39    FROM {{staging.microsoft_ads.ad_performance_daily_report}}
40    GROUP BY 1, 2, 3