Sales and proceeds

1-- core.app_store_connect.sales_and_proceeds
2-- Units, customer sales and estimated developer proceeds per app and product, the
3-- Sales and Trends view of App Store Connect.
4--
5-- Grain: one row per day x app SKU x product (Apple ID) x country x product category
6-- x customer currency x proceeds currency.
7-- Depends on: staging.app_store_connect.sales_report
8--
9-- CURRENCIES ARE PART OF THE GRAIN. customer_sales is in customer_currency and
10-- developer_proceeds in proceeds_currency, and a single country can pay in more than
11-- one. Convert before summing across rows; summing as-is adds yen to euros.
12--
13-- app_sku ties an in-app purchase to its app: Apple fills parent_identifier with the
14-- app's SKU on in-app purchase rows and leaves it empty on the app's own rows.
15--
16-- Units count updates and redownloads too. Filter product_category to
17-- 'app_download' for downloads, or to the purchase categories for revenue units.
18
19SELECT
20    date_day,
21    COALESCE(NULLIF(parent_identifier, ''), sku)        AS app_sku,
22    apple_id,
23    ANY_VALUE(title)                                    AS title,
24    ANY_VALUE(sku)                                      AS sku,
25    country_code,
26    product_category,
27    customer_currency,
28    proceeds_currency,
29
30    SUM(units)                                          AS units,
31    SUM(IF(units > 0, units, 0))                        AS units_sold,
32    SUM(IF(units < 0, -units, 0))                       AS units_refunded,
33    SUM(customer_sales)                                 AS customer_sales,
34    SUM(developer_proceeds)                             AS developer_proceeds,
35    SUM(IF(units < 0, -developer_proceeds, 0))          AS proceeds_refunded
36FROM {{staging.app_store_connect.sales_report}}
37GROUP BY date_day, app_sku, apple_id, country_code, product_category, customer_currency, proceeds_currency