Mailbox provider stats daily

1-- core.sendgrid.mailbox_provider_stats_daily
2-- SendGrid's Mailbox Provider stats, rebuilt: deliverability and engagement by the
3-- receiving provider. This is where a reputation problem shows up first, usually as
4-- blocks or deferrals at one provider while the others look normal.
5--
6-- Grain: one row per day x mailbox provider.
7-- Depends on: staging.sendgrid.mailbox_provider_stat
8--
9-- SendGrid does not report requests per provider, so the denominator for delivery and
10-- bounce rates here is attempted mail: delivered + bounces + blocks. Deferred mail is
11-- retried and usually lands later as delivered or bounced, so it is not added again.
12-- Engagement rates are over delivered, as in email_stats_daily.
13
14SELECT
15    date,
16    mailbox_provider,
17    delivered,
18    deferred,
19    bounces,
20    blocks,
21    drops,
22    opens,
23    unique_opens,
24    clicks,
25    unique_clicks,
26    spam_reports,
27
28    COALESCE(delivered, 0) + COALESCE(bounces, 0) + COALESCE(blocks, 0) AS attempted,
29    SAFE_DIVIDE(delivered,
30        COALESCE(delivered, 0) + COALESCE(bounces, 0) + COALESCE(blocks, 0)) AS delivery_rate,
31    SAFE_DIVIDE(COALESCE(bounces, 0) + COALESCE(blocks, 0),
32        COALESCE(delivered, 0) + COALESCE(bounces, 0) + COALESCE(blocks, 0)) AS rejection_rate,
33    SAFE_DIVIDE(unique_opens, delivered)                        AS unique_open_rate,
34    SAFE_DIVIDE(unique_clicks, delivered)                       AS unique_click_rate,
35    SAFE_DIVIDE(spam_reports, delivered)                        AS spam_report_rate
36FROM {{staging.sendgrid.mailbox_provider_stat}}