Settlement ledger

1-- amazon_seller_settlement_ledger - the settlement report turned into a P&L.
2--
3-- Grain: settlement date x seller x marketplace x order x SKU. Currency: the
4-- settlement's own.
5-- Depends on: staging.amazon_seller.settlement
6--
7-- WHAT THIS ANSWERS THAT NOTHING ELSE CAN: how much of what a customer paid you
8-- actually kept. Amazon deducts referral fees, FBA fulfilment fees, storage,
9-- advertising, refund administration and a long tail of service fees before the
10-- deposit lands, and none of it appears in any sales report. Sellers who model only
11-- ordered_product_sales are typically surprised by 25-45% of gross - and it is not
12-- a flat percentage, so you cannot approximate it with one.
13--
14-- HOW IT WORKS. staging.amazon_seller.settlement classifies every line into a
15-- ledger_category; this model pivots those categories into columns so one row is one
16-- order-SKU-day and the fee structure is readable across it. Amazon signs the report
17-- itself - revenue positive, fees and refunds negative - and that convention is
18-- preserved, which is the only reason the whole thing sums back to the deposit.
19--
20-- Every fee column is therefore NEGATIVE. net_proceeds is a sum, not a subtraction:
21--     product_revenue + selling_fees + fba_fees + ...
22-- Writing it with minus signs is how a fee ends up added back and margin comes out
23-- above 100%.
24--
25-- ORDER-LEVEL LINES CARRY NO SKU. Storage fees, advertising and subscription charges
26-- are charged to the account, not to a product, and arrive with sku NULL. They are
27-- kept - dropping them understates cost by the entire fixed-cost base - which means
28-- you cannot join this model to a SKU dimension with an INNER JOIN.
29-- core.amazon_seller.asin_profitability handles the allocation.
30
31SELECT
32    s.posted_date                                   AS date,
33    s.amazon_seller,
34    s.marketplace,
35    s.settlement_id,
36    s.amazon_order_id,
37    s.sku,
38
39    -- What the customer paid.
40    ROUND(SUM(CASE WHEN s.ledger_category = 'revenue'       AND NOT s.is_refund THEN s.amount ELSE 0 END), 2) AS product_revenue,