Campaign performance daily

1-- core.amazon_attribution.campaign_performance_daily
2-- Daily campaign results as the Amazon Attribution console shows them: click-throughs,
3-- detail page views, add to carts, purchases and sales, promoted and total, with the
4-- funnel rates and the Brand Referral Bonus.
5--
6-- Grain: date_day x profile_id x advertiser x campaign_id. Currency: the profile's.
7-- Depends on: staging.amazon_attribution.campaign_performance, .advertiser, .profile
8--
9-- Built from the campaign report rather than by rolling up creatives: only the
10-- campaign and ad group reports carry the Brand Referral Bonus.
11-- Rates are ratios of sums at this grain. For a week or a month, re-sum the base
12-- columns and divide again rather than averaging the daily rates.
13
14WITH advertiser AS (
15    SELECT profile_id, advertiser_name, MIN(advertiser_id) AS advertiser_id
16    FROM {{staging.amazon_attribution.advertiser}}
17    GROUP BY 1, 2
18)
19
20SELECT
21    c.date_day,
22    c.profile_id,
23    pr.profile_name,
24    pr.currency,
25    a.advertiser_id,
26    c.advertiser_name,
27    c.campaign_id,
28
29    c.click_throughs,
30    c.detail_page_views,
31    c.add_to_carts,
32    c.purchases,
33    c.units_sold,
34    c.sales,
35    c.total_detail_page_views,
36    c.total_add_to_carts,
37    c.total_purchases,
38    c.total_units_sold,
39    c.total_sales,
40    c.total_sales - c.sales                                     AS brand_halo_sales,