Earnings monthly

1-- core.google_play.earnings_monthly
2-- What Google Play charged, kept and refunded per app and product, by month: the
3-- Earnings report in Play Console, reduced to the lines finance needs.
4--
5-- Grain: one row per transaction month (Pacific Time) x package x product (SKU) x
6-- merchant currency.
7-- Depends on: staging.google_play.earnings
8--
9-- Every bucket is a plain SUM of Google's signed amounts in your payout currency,
10-- and net_earnings is the sum of all lines. That is why net_earnings ties to the
11-- payout: nothing is re-signed or ABS()-ed on the way. Lines whose type is not one
12-- of the named buckets (rebills, adjustments, new types Google adds) are in
13-- other_amounts; watch that column rather than assuming it stays zero.
14--
15-- This will not match the revenue chart in Play Console. That chart is built from
16-- the estimated sales report, in buyer currencies, before withholding tax and
17-- chargebacks. Earnings are what Google actually pays out; reconcile to the payout.
18
19SELECT
20    DATE_TRUNC(transaction_date, MONTH)                                     AS month_start,
21    package_name,
22    sku_id,
23    ANY_VALUE(product_title)                                                AS product_title,
24    ANY_VALUE(product_type)                                                 AS product_type,
25    merchant_currency,
26
27    -- Orders, not lines: description holds the order ID, and a renewal gets its own.
28    COUNT(DISTINCT IF(transaction_type = 'Charge', description, NULL))      AS charged_orders,
29    COUNT(DISTINCT IF(transaction_type = 'Charge refund', description, NULL)) AS refunded_orders,
30    SUM(IF(transaction_type = 'Charge', amount_merchant_currency, 0))       AS gross_charges,
31    SUM(IF(transaction_type = 'Charge refund', amount_merchant_currency, 0)) AS charge_refunds,
32    SUM(IF(transaction_type = 'Google fee', amount_merchant_currency, 0))   AS google_fees,
33    SUM(IF(transaction_type = 'Google fee refund', amount_merchant_currency, 0)) AS google_fee_refunds,
34    SUM(IF(transaction_type IN ('Tax', 'Tax refund'), amount_merchant_currency, 0)) AS taxes,
35    SUM(IF(transaction_type NOT IN ('Charge', 'Charge refund', 'Google fee',
36                                    'Google fee refund', 'Tax', 'Tax refund')
37           OR transaction_type IS NULL, amount_merchant_currency, 0))      AS other_amounts,
38    SUM(amount_merchant_currency)                                           AS net_earnings,
39
40    -- Google's effective cut of what buyers paid, after refunds on both sides.