Creative performance daily

1-- core.amazon_attribution.creative_performance_daily
2-- Daily click-throughs and Amazon conversions per Attribution creative: what your
3-- off-Amazon traffic (search, social, email, influencers) did once it reached Amazon.
4--
5-- Grain: date_day x profile_id x advertiser x campaign_id x ad_group_id x ad_id.
6-- Currency: the profile's.
7-- Depends on: staging.amazon_attribution.creative_performance, .advertiser, .profile
8--
9-- The first twelve columns are the cross-channel ad contract (date_day,
10-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
11-- impressions, clicks, conversions, conversion_value) shared with the paid ads
12-- templates in this repo. Amazon Attribution only sees what happens after the click,
13-- so some of them are honestly empty:
14--   spend, impressions        NULL. Spend and impressions live in the ad platform
15--                             that served the ad; join them from that template.
16--   campaign_name, ad_name    NULL. The report carries ids only.
17--   account                   the Attribution advertiser.
18--   ad_id                     the Attribution creative (tag); 'unknown' for clicks
19--                             Amazon could not tie to a creative.
20--   clicks                    click-throughs to Amazon.
21--   conversions, value        total purchases and sales: promoted products plus
22--                             brand halo, 14 day window, dated on the click.
23
24WITH advertiser AS (
25    -- The report names the advertiser but carries no id. Resolve it within the
26    -- profile, one id per name, so the join cannot fan out.
27    SELECT profile_id, advertiser_name, MIN(advertiser_id) AS advertiser_id
28    FROM {{staging.amazon_attribution.advertiser}}
29    GROUP BY 1, 2
30)
31
32SELECT
33    p.date_day,
34    a.advertiser_id                    AS account_id,
35    p.advertiser_name                  AS account_name,
36    p.campaign_id,
37    CAST(NULL AS STRING)               AS campaign_name,
38    p.creative_id                      AS ad_id,
39    CAST(NULL AS STRING)               AS ad_name,
40    CAST(NULL AS NUMERIC)              AS spend,