Audience growth daily

1-- core.mailchimp.audience_growth_daily
2-- Mailchimp's audience growth and activity, per audience per day: new subscribers,
3-- unsubscribes and other changes, plus the sending activity on that day.
4--
5-- Grain: one row per day x audience (list).
6-- Depends on: staging.mailchimp.list_activity, .list
7--
8-- `net_change` follows Mailchimp's audience growth chart: subscribes and other adds
9-- (imports, API) in, unsubscribes and other removes (cleaned, deleted, archived) out.
10-- Summing it over a window gives the change in subscribed contacts for that window.
11--
12-- `unique_opens` and `recipient_clicks` are per day as Mailchimp reports them, so they
13-- cannot be summed into unique people over a longer window. The open and click rates are
14-- per row; re-derive them from summed counts when you aggregate.
15
16SELECT
17    a.day,
18    a.list_id,
19    l.list_name,
20
21    a.subscribes,
22    a.other_adds,
23    a.unsubscribes,
24    a.other_removes,
25    COALESCE(a.subscribes, 0) + COALESCE(a.other_adds, 0)
26        - COALESCE(a.unsubscribes, 0) - COALESCE(a.other_removes, 0)  AS net_change,
27
28    a.emails_sent,
29    a.hard_bounces,
30    a.soft_bounces,
31    a.unique_opens,
32    a.recipient_clicks,
33    SAFE_DIVIDE(a.unique_opens, a.emails_sent)                        AS unique_open_rate,
34    SAFE_DIVIDE(a.recipient_clicks, a.emails_sent)                    AS click_rate
35FROM {{staging.mailchimp.list_activity}} a
36LEFT JOIN {{staging.mailchimp.list}} l ON l.list_id = a.list_id