Creator campaign performance

1-- core.grin.creator_campaign_performance
2-- What each creator did on each campaign, and what it cost and returned: proposal
3-- status, content posted and its reach, attributed sales, commissions, what is
4-- owed to them and what their seeded products cost.
5--
6-- Grain: one row per grin_account x campaign x creator (contact).
7-- Depends on: staging.grin.campaign, .proposal, .content, .content_stat,
8--             .campaign_conversion, .campaign_payable, .contact_order,
9--             .product_selection_item, .product, .product_variant, .contact
10--
11-- Every source is aggregated to campaign x creator in its own CTE BEFORE the
12-- joins. Joining content to conversions raw would multiply revenue by the number
13-- of posts.
14--
15-- A creator appears if they have ANY of a proposal, content, a conversion, a
16-- payable or a seeding order on the campaign, so a creator who posted without a
17-- proposal, or earned commission on an old code, is not dropped.
18--
19-- THREE CURRENCIES, NOT ONE. Read before computing a return:
20--   revenue, commission   the conversion's currency (your store's); `currency`
21--                         says which, and is NULL when one campaign x creator mixes
22--                         currencies, in which case the sums are not meaningful.
23--   payables_usd          US dollars, Grin's own conversion.
24--   seeding_cost          the account currency the product costs were entered in.
25-- campaign_performance only computes a cost-to-revenue ratio when these line up.
26
27WITH proposals AS (
28    -- One creator can have more than one proposal on a campaign (re-invited after
29    -- a decline). Counted; each flag is TRUE if any of them reached that state.
30    SELECT
31        grin_account,
32        campaign_id,
33        contact_id,
34        COUNT(*)                                   AS proposals,
35        LOGICAL_OR(accepted_at IS NOT NULL)        AS is_accepted,
36        LOGICAL_OR(declined_at IS NOT NULL
37                   AND accepted_at IS NULL)        AS is_declined,
38        LOGICAL_OR(is_agreed)                      AS is_agreed,
39        LOGICAL_OR(completed_at IS NOT NULL OR is_complete) AS is_completed,
40        SUM(IF(accepted_at IS NOT NULL, price, 0)) AS agreed_fee,