Campaign performance

1-- core.mailchimp.campaign_performance
2-- Mailchimp's campaign report, one row per sent campaign: who it went to, how much was
3-- delivered, opened and clicked, who unsubscribed, and attributed e-commerce revenue.
4--
5-- Grain: one row per campaign (sent campaigns only).
6-- Depends on: staging.mailchimp.campaign, .campaign_setting, .campaign_recipient,
7--             .campaign_report_summary, .campaign_member_recipient, .member_activity
8--
9-- Two kinds of numbers live side by side here, on purpose:
10--   * Mailchimp's own: opens, unique_opens, open_rate, clicks, unique_clickers,
11--     click_rate and the e-commerce columns, straight from the campaign report. These
12--     match the Mailchimp UI.
13--   * Rebuilt from the "sent to" list: recipients, hard and soft bounces, delivered.
14--     Mailchimp's open and click rates are over delivered, which is why it is here:
15--     unique_opens / delivered should land on open_rate.
16--
17-- `unsubscribes` comes from the member activity feed, which Mailchimp caps at the last
18-- 50 events per member. It is a floor and can undercount for old campaigns sent to very
19-- active members. An unsubscribe only counts for a campaign when the member is on that
20-- campaign's sent-to list, so the numerator of `unsubscribe_rate` is always a subset of
21-- its denominator. A member who unsubscribes twice from one campaign counts once.
22--
23-- The member-level aggregates are built per campaign before the join, so the joins are
24-- one-to-one and nothing fans out.
25
26WITH delivery AS (
27    SELECT
28        campaign_id,
29        COUNT(*)                                            AS recipients,
30        COUNTIF(delivery_status = 'hard')                   AS hard_bounces,
31        COUNTIF(delivery_status = 'soft')                   AS soft_bounces,
32        COUNTIF(open_count > 0)                             AS recipients_opened
33    FROM {{staging.mailchimp.campaign_member_recipient}}
34    GROUP BY 1
35),
36
37unsubscribes AS (
38    -- unsub events tied to a campaign, kept only for members that campaign was sent to
39    SELECT
40        a.campaign_id,