Product sales

1-- core.traede.product_sales
2-- Invoiced units, revenue, cost and gross margin per variant per day.
3-- Grain: day x traede_account x currency x variant.
4-- Depends on: staging.traede.invoice, .invoice_line, .variant, .product
5--
6-- Read from booked invoice LINES on the invoice date. Revenue here is line net
7-- (after line discount, ex VAT). It does not include shipping or handling, and it
8-- will sit below invoiced_sales_over_time.net_sales by any discount applied on the
9-- invoice header rather than on lines.
10--
11-- COST AND MARGIN. Cost is the variant's CURRENT cost_price from Traede x units.
12-- Two limits follow:
13--   1. It is today's cost, not the cost when the goods were sold.
14--   2. Traede does not document which currency cost_price is in. It is normally the
15--      account's base currency. Set base_currency below to yours. Margin is only
16--      computed for invoice lines in that currency; lines in other currencies get
17--      NULL cost and margin rather than a number that subtracts EUR from SEK.
18-- unit_landed_cost is returned alongside, for brands that report margin after
19-- freight and duty.
20--
21-- No margin percentage is returned per row. Average it in BI as
22-- SUM(gross_margin) / SUM(net_sales_with_cost); averaging row percentages weights
23-- a one-unit day the same as a thousand-unit day.
24
25WITH settings AS (
26    SELECT 'EUR' AS base_currency   -- change to your Traede account's base currency
27),
28
29lines AS (
30    SELECT
31        i.invoice_date AS date,
32        i.traede_account,
33        i.currency,
34        i.invoice_id,
35        l.product_id,
36        l.variant_id,
37        l.quantity,
38        l.line_gross_amount,
39        l.line_discount_amount,
40        l.line_net_amount