Product performance daily

1-- core.amazon_attribution.product_performance_daily
2-- Daily Amazon Attribution results per product: which ASINs your off-Amazon traffic
3-- sold, how much of it was brand halo, and how many buyers were new to the brand.
4--
5-- Grain: date_day x profile_id x advertiser x campaign_id x ad_group_id x asin x
6-- product_conversion_type. Currency: the profile's.
7-- Depends on: staging.amazon_attribution.product_performance, .profile
8--
9-- The raw products report is keyed on product category, subcategory and name,
10-- not on ASIN. This model groups on ASIN and sums, so two rows that share an ASIN
11-- collapse into one. Amazon keeps 90 days of this report.
12
13SELECT
14    p.date_day,
15    p.profile_id,
16    pr.profile_name,
17    pr.currency,
18    p.advertiser_name,
19    p.campaign_id,
20    p.ad_group_id,
21    p.asin,
22    p.product_conversion_type,
23    ANY_VALUE(p.product_name)        AS product_name,
24    ANY_VALUE(p.brand_name)          AS brand_name,
25    ANY_VALUE(p.product_category)    AS product_category,
26    ANY_VALUE(p.product_subcategory) AS product_subcategory,
27    ANY_VALUE(p.marketplace)         AS marketplace,
28
29    SUM(p.detail_page_views)                    AS detail_page_views,
30    SUM(p.add_to_carts)                         AS add_to_carts,
31    SUM(p.purchases)                            AS purchases,
32    SUM(p.units_sold)                           AS units_sold,
33    SUM(p.sales)                                AS sales,
34    SUM(p.new_to_brand_purchases)               AS new_to_brand_purchases,
35    SUM(p.new_to_brand_units_sold)              AS new_to_brand_units_sold,
36    SUM(p.new_to_brand_sales)                   AS new_to_brand_sales,
37    SUM(p.brand_halo_detail_page_views)         AS brand_halo_detail_page_views,
38    SUM(p.brand_halo_add_to_carts)              AS brand_halo_add_to_carts,
39    SUM(p.brand_halo_purchases)                 AS brand_halo_purchases,
40    SUM(p.brand_halo_units_sold)                AS brand_halo_units_sold,