Audience summary

1-- core.mailchimp.audience_summary
2-- One row per audience: Mailchimp's own contact counts next to the same counts rebuilt
3-- from the synced members, plus member quality signals.
4--
5-- Grain: one row per audience (list), as of the last sync.
6-- Depends on: staging.mailchimp.list, .member
7--
8-- The `*_count` columns are Mailchimp's numbers from the audience itself and match the
9-- Audience dashboard. The `members_*` columns count the synced member rows by status. The
10-- two should agree; a gap usually means the member stream is behind or was filtered.
11-- Archived contacts are counted only in `members_archived`; Mailchimp leaves them out of
12-- its audience totals.
13
14WITH members AS (
15    SELECT
16        list_id,
17        COUNTIF(status = 'subscribed')                      AS members_subscribed,
18        COUNTIF(status = 'unsubscribed')                    AS members_unsubscribed,
19        COUNTIF(status = 'cleaned')                         AS members_cleaned,
20        COUNTIF(status = 'pending')                         AS members_pending,
21        COUNTIF(status = 'transactional')                   AS members_transactional,
22        COUNTIF(status = 'archived')                        AS members_archived,
23        AVG(IF(status = 'subscribed', member_rating, NULL)) AS avg_member_rating_subscribed,
24        COUNTIF(status = 'subscribed'
25                AND signed_up_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY))
26                                                            AS subscribed_last_30_days
27    FROM {{staging.mailchimp.member}}
28    GROUP BY 1
29)
30
31SELECT
32    l.list_id,
33    l.list_name,
34    l.created_at,
35    l.is_double_optin,
36
37    l.subscribed_count,
38    l.unsubscribed_count,
39    l.cleaned_count,
40    l.total_contacts,