Conversions by day

1-- core.grin.conversions_by_day
2-- Affiliate and discount-code sales over time, per campaign: orders, revenue and
3-- commission on the day Grin recorded the conversion.
4--
5-- Grain: day x grin_account x campaign x conversion type x currency.
6-- Depends on: staging.grin.campaign_conversion, .campaign
7--
8-- Currency is in the grain so a store selling in two currencies never sums them.
9-- conversion_type is Grin's own label for how the order was attributed, passed
10-- through as Grin sends it.
11--
12-- VOIDS LAND ON THE CONVERSION DAY, not the void day. A refund in March of a
13-- January order lowers January. That keeps this model reconciling to
14-- campaign_performance; the consequence is that a closed month can still move.
15--
16-- Conversions without a creator are left out, the same population
17-- campaign_performance counts, so the two reconcile.
18--
19-- TIME ZONE: days are cut in UTC. Change the 'UTC' literal to your reporting zone.
20--
21-- Spine: each campaign x type x currency gets every day between its first and its
22-- last conversion, so a quiet day inside a campaign's selling period is a zero, not
23-- a gap.
24
25WITH params AS (
26    SELECT 'UTC' AS tz
27),
28
29daily AS (
30    SELECT
31        cv.grin_account,
32        cv.campaign_id,
33        COALESCE(cv.conversion_type, 'unknown')            AS conversion_type,
34        COALESCE(cv.currency, 'unknown')                   AS currency,
35        DATE(cv.converted_at, p.tz)                        AS date,
36        COUNTIF(NOT cv.is_voided)                          AS conversions,
37        COUNTIF(cv.is_voided)                              AS voided_conversions,
38        SUM(IF(NOT cv.is_voided, cv.amount, 0))            AS revenue,
39        SUM(IF(cv.is_voided, cv.amount, 0))                AS voided_revenue,
40        SUM(IF(NOT cv.is_voided, cv.commission_amount, 0)) AS commission,