Sales by ASIN

1-- amazon_vendor_sales_by_asin - sales, traffic and Amazon's margin on one row per
2-- ASIN per day. The vendor equivalent of a product performance report.
3--
4-- Grain: day x vendor x marketplace x distributor view x selling program x ASIN.
5-- Currency: the marketplace's.
6-- Depends on: staging.amazon_vendor.sales, .traffic, .margin
7--
8-- WHY THE JOIN IS THE WHOLE MODEL. Amazon reports vendor sales, vendor traffic and
9-- vendor margin as three separate report types, on three separate schedules, and
10-- gives you no way to see them together. Conversion is the ratio of a number in the
11-- first to a number in the second; whether a well-converting ASIN is one Amazon
12-- makes money on is in the third. Individually each report answers almost nothing.
13--
14-- TWO JOIN HAZARDS, both quiet:
15--
16--   * Traffic and margin are NOT reported per distributor view or selling program -
17--     they are per ASIN per day, full stop. So joining them onto sales, which IS
18--     split four ways, repeats the same glance views against each variant. Sum
19--     glance_views across distributor views and you multiply traffic by up to four.
20--     Filter to one view - the grain warns you, it cannot stop you.
21--
22--   * LEFT JOIN from sales, not INNER. An ASIN can sell with no glance views
23--     recorded, and traffic reports lag sales by a day or more. INNER JOIN silently
24--     drops the newest day of sales, which is the day someone is looking at.
25
26WITH sales AS (
27    SELECT
28        date, amazon_vendor, marketplace, distributor_view, selling_program, asin,
29        currency,
30        SUM(shipped_cogs)      AS shipped_cogs,
31        SUM(shipped_revenue)   AS shipped_revenue,
32        SUM(shipped_units)     AS shipped_units,
33        SUM(ordered_revenue)   AS ordered_revenue,
34        SUM(ordered_units)     AS ordered_units,
35        SUM(customer_returns)  AS customer_returns
36    FROM {{staging.amazon_vendor.sales}}
37    GROUP BY 1, 2, 3, 4, 5, 6, 7
38)
39
40SELECT