Ad performance daily

1-- core.reddit_ads.ad_performance_daily
2-- Daily delivery and purchase results per ad, the Ads Manager "Ads" tab by day.
3--
4-- Grain: date_day x account_id x ad_id. Currency: the ad account's.
5-- Depends on: staging.reddit_ads.ad_performance, .ad, .ad_group, .campaign, .ad_account
6--
7-- The first twelve columns are the cross-channel ad contract (date_day,
8-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
9-- impressions, clicks, conversions, conversion_value) shared with the other paid
10-- ads templates in this repo, so they can be stacked with UNION ALL.
11--
12-- conversions = purchase click-through + view-through conversions, which is what
13-- Ads Manager shows as "Purchases" under the account's attribution windows.
14-- conversion_value is the matching purchase value. Both are dated on the day of
15-- the ad interaction, not the day of the purchase, and Reddit keeps updating them
16-- as late conversions arrive.
17
18WITH daily AS (
19    -- SUM, not pick-one: with a non-UTC report timezone a local date is spread
20    -- over two UTC report windows.
21    SELECT
22        date_day,
23        account_id,
24        ad_id,
25        SUM(impressions)                AS impressions,
26        SUM(clicks)                     AS clicks,
27        SUM(spend)                      AS spend,
28        SUM(purchase_click_conversions) AS purchase_click_conversions,
29        SUM(purchase_view_conversions)  AS purchase_view_conversions,
30        SUM(purchase_value)             AS purchase_value
31    FROM {{staging.reddit_ads.ad_performance}}
32    GROUP BY 1, 2, 3
33)
34
35SELECT
36    d.date_day,
37    d.account_id,
38    acc.account_name,
39    a.campaign_id,
40    c.campaign_name,