Traffic and conversion

1-- amazon_seller_traffic_and_conversion - sessions, page views, Buy Box share and
2-- conversion, at the grain each metric is actually measured on.
3--
4-- Grain: day x seller x marketplace x child ASIN. Currency: the marketplace's.
5-- Depends on: staging.amazon_seller.sales_and_traffic_by_asin, .listings
6--
7-- WHY THIS MODEL IS AT ASIN GRAIN WHEN EVERYTHING ELSE IS AT SKU GRAIN. Because
8-- that is where Amazon measures it. Sessions and page views are counted on a detail
9-- page, and a detail page belongs to a child ASIN - not to a SKU. If two SKUs sit on
10-- the same ASIN (the usual case for a restocked or repackaged product), Amazon
11-- repeats the SAME session count on both rows of the SKU-grain report.
12--
13-- So SUM(sessions) over SKUs double-counts traffic, and every conversion rate
14-- computed from it is understated by exactly that factor. This is the single most
15-- common error in Amazon reporting after the report-generation duplicates, and it is
16-- invisible: the numbers look plausible, just wrong, and they get worse the more
17-- SKUs share an ASIN.
18--
19-- The fix is not clever. Roll traffic up with MAX per ASIN (every SKU row carries the
20-- same value, so any one of them is the value) and units with SUM (those genuinely
21-- are per SKU), then divide. Doing it in that order is the whole model.
22
23WITH by_asin AS (
24    SELECT
25        t.date,
26        t.amazon_seller,
27        t.marketplace,
28        t.parent_asin,
29        t.child_asin,
30
31        -- Traffic: MAX, not SUM. Identical across the SKUs of one ASIN.
32        MAX(t.sessions)                 AS sessions,
33        MAX(t.browser_sessions)         AS browser_sessions,
34        MAX(t.mobile_app_sessions)      AS mobile_app_sessions,
35        MAX(t.page_views)               AS page_views,
36        MAX(t.browser_page_views)       AS browser_page_views,
37        MAX(t.mobile_app_page_views)    AS mobile_app_page_views,
38        MAX(t.sessions_b2b)             AS sessions_b2b,
39        MAX(t.page_views_b2b)           AS page_views_b2b,
40