Campaign performance

1-- core.grin.campaign_performance
2-- One row per campaign: the creator funnel, the content it produced, the sales it
3-- drove and what it cost. Grin's campaign report, with the costs on the same row.
4--
5-- Grain: one row per grin_account x campaign.
6-- Depends on: staging.grin.campaign, .campaign_conversion,
7--             core.grin.creator_campaign_performance
8--
9-- Rolled up from creator_campaign_performance, so the two always agree; every
10-- campaign is kept, including ones with no activity yet.
11--
12-- RATES ARE RATIOS OF SUMS: engagement rate is total engagements over total reach,
13-- not the average of each post's rate, so one viral post on a small account cannot
14-- dominate the campaign figure.
15--
16-- RETURN IS ONLY COMPUTED WHEN THE CURRENCIES MATCH. Revenue is in the store's
17-- currency, payables in US dollars. revenue_per_payable_usd is filled only when the
18-- campaign's conversions are all in USD. Seeding cost is in the currency product
19-- costs were entered in and is left out of the ratio; add it yourself once you
20-- know which currency that is.
21
22WITH campaign_rollup AS (
23    SELECT
24        grin_account,
25        campaign_id,
26        COUNT(*)                                   AS creators,
27        COUNTIF(proposals > 0)                     AS creators_proposed,
28        COUNTIF(is_accepted)                       AS creators_accepted,
29        COUNTIF(is_declined AND NOT is_accepted)   AS creators_declined,
30        COUNTIF(is_completed)                      AS creators_completed,
31        COUNTIF(posts > 0)                         AS creators_posted,
32        COUNTIF(conversions > 0)                   AS creators_with_sales,
33        SUM(agreed_fee)                            AS agreed_fees,
34        SUM(posts)                                 AS posts,
35        SUM(stories)                               AS stories,
36        SUM(impressions)                           AS impressions,
37        SUM(reach)                                 AS reach,
38        SUM(views)                                 AS views,
39        SUM(engagements)                           AS engagements,
40        SUM(imv)                                   AS imv,