Campaign performance daily

1-- core.google_ads.campaign_performance_daily
2-- Daily campaign performance with the rates the Google Ads UI shows: CTR, CPC,
3-- 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.google_ads.campaign_stats, .campaign, .account
7--
8-- Read from the campaign report, not summed from ad_performance_daily: Google's
9-- ad level report leaves out campaigns that have no ads in the classic sense
10-- (Performance Max, Smart, some App campaigns), so only the campaign report ties
11-- to account spend.
12--
13-- conversions follows the same definition as ad_performance_daily: primary
14-- conversion actions, each action's own attribution model and window, dated on
15-- the interaction day. Rates are ratios of sums; to report a week or a month,
16-- re-sum the base columns and divide again.
17
18WITH daily AS (
19    SELECT
20        date_day,
21        account_id,
22        campaign_id,
23        MAX(account_name)             AS report_account_name,
24        MAX(campaign_name)            AS report_campaign_name,
25        SUM(spend)                    AS spend,
26        SUM(impressions)              AS impressions,
27        SUM(clicks)                   AS clicks,
28        SUM(interactions)             AS interactions,
29        SUM(conversions)              AS conversions,
30        SUM(conversion_value)         AS conversion_value,
31        SUM(view_through_conversions) AS view_through_conversions,
32        SUM(video_views)              AS video_views
33    FROM {{staging.google_ads.campaign_stats}}
34    GROUP BY 1, 2, 3
35)
36
37SELECT
38    d.date_day,
39    d.account_id,
40    COALESCE(acc.account_name, d.report_account_name) AS account_name,