Email campaign performance

1-- core.iterable.email_campaign_performance
2-- One row per email campaign with its whole-life numbers and rates: the headline table
3-- in Iterable's Campaign Analytics for email.
4--
5-- Grain: one row per campaign (email campaigns, plus any campaign id that has email
6-- sends). Sends without a campaign id are left out; they are in email_performance_daily.
7-- Depends on: staging.iterable.email_send, .email_open, .email_click, .email_bounce,
8--             .email_complaint, .email_unsubscribe, .campaigns
9--
10-- Every event is attributed to its campaign whenever it happened, so opens and clicks
11-- that arrive days after the send still count for the campaign.
12--
13-- Unique counts are people, not messages: Iterable counts a unique open or click once per
14-- user per campaign, keyed on email (or userId in userId projects). For a blast campaign
15-- that is the same as one per message, but a triggered campaign can send one person many
16-- messages, and counting messages would put unique opens above recipients. Email is used
17-- here because it is on every email event in every project type.
18--
19-- Rates follow Iterable's definitions: delivered is sends minus bounces; unique open,
20-- unique click, complaint and unsubscribe rates are over delivered; click-to-open is
21-- unique clicks over unique opens. Each event table is aggregated per campaign before the
22-- join, so nothing fans out.
23--
24-- Triggered campaigns (journeys, API sends) keep sending, so their numbers grow with
25-- every sync. Compare them over a fixed window with email_performance_daily instead.
26
27WITH sends AS (
28    SELECT campaign_id, COUNT(*) AS sends, COUNT(DISTINCT email) AS recipients,
29           MIN(event_at) AS first_send_at, MAX(event_at) AS last_send_at
30    FROM {{staging.iterable.email_send}}
31    GROUP BY 1
32),
33
34bounces AS (
35    SELECT campaign_id, COUNT(*) AS bounces
36    FROM {{staging.iterable.email_bounce}}
37    GROUP BY 1
38),
39
40opens AS (