Sales over time

1-- amazon_vendor_sales_over_time - the vendor sales report, one row per day, with the
2-- three revenue definitions separated and Amazon's markup made explicit.
3--
4-- Grain: day x vendor x marketplace x distributor view x selling program.
5-- Currency: the marketplace's.
6-- Depends on: staging.amazon_vendor.sales
7--
8-- THE THING TO UNDERSTAND BEFORE READING ANY COLUMN: as a vendor you have two
9-- different revenues and only one of them is yours.
10--
11--   shipped_cogs      what AMAZON PAYS YOU. Your revenue. This is the number that
12--                     belongs in your P&L, and it is called "cost of goods sold"
13--                     because it is Amazon's COGS, not yours. The naming has caused
14--                     more confusion in vendor reporting than anything else.
15--   shipped_revenue   what AMAZON CHARGES THE CUSTOMER. Amazon's revenue. Useful for
16--                     market share and price monitoring, and not yours.
17--   ordered_revenue   customer demand at Amazon's retail price, on the order date.
18--                     Leading indicator. Populated on the MANUFACTURING view only.
19--
20-- Report your own top line off shipped_cogs. Every vendor that has ever presented a
21-- suspiciously excellent Amazon quarter reported shipped_revenue by mistake.
22--
23-- THE GRAIN INCLUDES distributor_view AND selling_program because Amazon's four
24-- report variants OVERLAP - MANUFACTURING and SOURCING can describe the same ASIN
25-- and the same units. They are in the grain so that no dashboard can sum across them
26-- by accident. Pick one view in your BI filter; MANUFACTURING + RETAIL is the usual
27-- answer.
28
29SELECT
30    s.date,
31    s.amazon_vendor,
32    s.marketplace,
33    s.distributor_view,
34    s.selling_program,
35    s.currency,
36
37    -- Yours.
38    ROUND(SUM(s.shipped_cogs), 2)               AS shipped_cogs,
39    SUM(s.shipped_units)                        AS shipped_units,
40    ROUND(SAFE_DIVIDE(SUM(s.shipped_cogs), NULLIF(SUM(s.shipped_units), 0)), 2) AS average_cogs_per_unit,