Campaign performance daily

1-- core.microsoft_ads.campaign_performance_daily
2-- Daily campaign performance with the rates the Microsoft Advertising UI shows:
3-- CTR, CPC, CPM, cost per conversion and ROAS.
4--
5-- Grain: date_day x account_id x campaign_id. Currency: the account's.
6-- Depends on: staging.microsoft_ads.campaign_performance_daily_report,
7-- .campaign, .account
8--
9-- Read from the campaign report, not summed from ad_performance_daily: the ad
10-- report has no rows for Performance Max, so only the campaign report ties to
11-- account spend.
12--
13-- conversions counts the conversion goals set to "Include in conversions",
14-- under each goal's own conversion window, dated on the day of the ad click.
15-- conversion_value is the matching revenue. all_conversions and all_revenue count
16-- every goal, including those left out of conversions. View-through
17-- conversions are also carried on their own.
18--
19-- Rates are ratios of sums; to report a week or a month, re-sum the base
20-- columns and divide again.
21
22WITH daily AS (
23    SELECT
24        date_day,
25        account_id,
26        campaign_id,
27        MAX(currency)                 AS report_currency,
28        SUM(spend)                    AS spend,
29        SUM(impressions)              AS impressions,
30        SUM(clicks)                   AS clicks,
31        SUM(conversions)              AS conversions,
32        SUM(revenue)                  AS conversion_value,
33        SUM(all_conversions)          AS all_conversions,
34        SUM(all_revenue)              AS all_revenue,
35        SUM(view_through_conversions) AS view_through_conversions
36    FROM {{staging.microsoft_ads.campaign_performance_daily_report}}
37    GROUP BY 1, 2, 3
38)
39
40SELECT