Email performance daily

1-- core.iterable.email_performance_daily
2-- Email sends, opens, clicks, bounces, complaints and unsubscribes per campaign per day:
3-- the email metrics in Iterable's Campaign Analytics, as a daily series.
4--
5-- Grain: one row per day (UTC, event time) x campaign.
6-- Depends on: staging.iterable.email_send, .email_open, .email_click, .email_bounce,
7--             .email_complaint, .email_unsubscribe, .campaigns
8--
9-- Each event is counted on the day it happened, so an email sent on Monday and opened on
10-- Tuesday adds a send to Monday and an open to Tuesday. That is the right view for "what
11-- happened this week". For "how did this campaign do", use email_campaign_performance,
12-- which is not split by day.
13--
14-- `unique_opens` and `unique_clicks` count distinct people (by email) who opened or
15-- clicked that campaign that day, the way Iterable counts uniques: per user, not per
16-- message. They are unique per day and cannot be summed into unique counts over a longer
17-- window.
18-- Events with no campaign id (some transactional API sends) are grouped under a NULL
19-- campaign rather than dropped.
20
21WITH events AS (
22    SELECT 'send' AS event_type, campaign_id, message_id, email, event_at FROM {{staging.iterable.email_send}}
23    UNION ALL
24    SELECT 'open', campaign_id, message_id, email, event_at FROM {{staging.iterable.email_open}}
25    UNION ALL
26    SELECT 'click', campaign_id, message_id, email, event_at FROM {{staging.iterable.email_click}}
27    UNION ALL
28    SELECT 'bounce', campaign_id, message_id, email, event_at FROM {{staging.iterable.email_bounce}}
29    UNION ALL
30    SELECT 'complaint', campaign_id, message_id, email, event_at FROM {{staging.iterable.email_complaint}}
31    UNION ALL
32    SELECT 'unsubscribe', campaign_id, message_id, email, event_at FROM {{staging.iterable.email_unsubscribe}}
33),
34
35daily AS (
36    SELECT
37        DATE(event_at)                                                          AS date,
38        campaign_id,
39        COUNTIF(event_type = 'send')                                            AS sends,
40        COUNTIF(event_type = 'bounce')                                          AS bounces,