Payments daily

1-- core.go_high.payments_daily
2-- Money taken through HighLevel Payments per day: the Transactions view in Payments,
3-- summed. Amount, refunds, net, and how many customers paid.
4--
5-- Grain: one row per day (UTC, transaction created) x location x currency x source type x
6-- status.
7-- Depends on: staging.go_high.transaction
8--
9-- Status is part of the grain rather than filtered here, because HighLevel's status
10-- values differ by payment provider and are not fully documented. For collected money,
11-- filter to the statuses that mean a successful charge in your account (typically
12-- `succeeded`, plus the refunded states, since a refunded payment was collected first).
13-- Failed and pending rows carry an amount that was never collected.
14--
15-- Refunds are dated on the original payment, not the day the refund happened, because
16-- the transaction carries no refund date. Net for recent days falls as refunds arrive.
17-- Test-mode payments are excluded. Currencies are never mixed in a row.
18
19SELECT
20    DATE(created_at)                                            AS date,
21    location_id,
22    currency,
23    COALESCE(source_type, '(none)')                             AS source_type,
24    status,
25
26    COUNT(*)                                                    AS transactions,
27    COUNT(DISTINCT contact_id)                                  AS contacts,
28    COUNTIF(subscription_id IS NOT NULL)                        AS subscription_transactions,
29    SUM(amount)                                                 AS amount,
30    SUM(amount_refunded)                                        AS refunded_amount,
31    SUM(amount - amount_refunded)                               AS net_amount
32FROM {{staging.go_high.transaction}}
33WHERE is_live IS NOT FALSE
34  AND created_at IS NOT NULL
35GROUP BY 1, 2, 3, 4, 5