Shop performance

1-- core.tiktok_shop.shop_performance
2-- TikTok's own shop analytics per day, on a continuous date spine, with the
3-- ratios Seller Center shows recomputed from their parts.
4--
5-- Grain: day x account x shop. Days TikTok reported nothing for appear with
6-- zeros, so a chart of a quiet shop does not silently skip days.
7--
8-- WHAT IT IS FOR. These are TikTok's numbers, not rebuilt ones: GMV, orders,
9-- units, buyers, page views. Use them as the reference that
10-- core.tiktok_shop.sales_over_time is reconciled against, and for the traffic
11-- metrics no order table carries. GMV follows TikTok's own definition, which is
12-- not the same as net_sales here (it is not reduced by the refunds that land on
13-- later days), so expect the two to differ and compare trends, not single days.
14--
15-- Only shops in the US, UK, Singapore, Vietnam and Thailand get this data from
16-- TikTok. avg_product_page_visitors is a daily average and is not summed.
17--
18-- Depends on: staging.tiktok_shop.shop_performance
19
20WITH perf AS (
21    SELECT * FROM {{staging.tiktok_shop.shop_performance}}
22),
23
24bounds AS (
25    SELECT tiktok_account, shop_id, MIN(date) AS first_date, MAX(date) AS last_date,
26           ANY_VALUE(currency) AS currency
27    FROM perf
28    GROUP BY tiktok_account, shop_id
29),
30
31spine AS (
32    SELECT b.tiktok_account, b.shop_id, b.currency, d AS date
33    FROM bounds b, UNNEST(GENERATE_DATE_ARRAY(b.first_date, b.last_date)) AS d
34)
35
36SELECT
37    s.date,
38    s.tiktok_account,
39    s.shop_id,
40    COALESCE(p.currency, s.currency)               AS currency,