Ad performance daily

1-- core.snapchat_ads.ad_performance_daily
2-- Daily delivery and purchase results per ad, the Ads Manager "Ads" view by day.
3--
4-- Grain: date_day x account_id x ad_id. Currency: the ad account's.
5-- Depends on: staging.snapchat_ads.ad_daily_report, .ad, .ad_squad, .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-- In Snapchat terms: clicks are swipe ups, and the ad set level is the ad squad.
13--
14-- conversions = purchases reported by the Snap Pixel, app events and the
15-- Conversions API, under Snapchat's default attribution: 28 days after a swipe
16-- up plus 1 day after a view, counted together. conversion_value is the matching
17-- purchase value. Snapchat keeps restating both until about 48 hours after the
18-- day ends.
19--
20-- date_day is the ad account's own reporting day, the same day Ads Manager shows.
21
22WITH daily AS (
23    SELECT
24        r.*,
25        -- Snapchat days start at midnight in the account timezone. Without a
26        -- timezone, fall back to the UTC date of that instant.
27        COALESCE(
28            DATE(r.report_start_at, acc.account_timezone),
29            DATE(r.report_start_at)
30        ) AS date_day
31    FROM {{staging.snapchat_ads.ad_daily_report}} r
32    LEFT JOIN {{staging.snapchat_ads.ad_account}} acc
33        ON acc.account_id = r.account_id
34)
35
36SELECT
37    d.date_day,
38    d.account_id,
39    acc.account_name,
40    sq.campaign_id,