Email stats daily

1-- core.sendgrid.email_stats_daily
2-- SendGrid's global Stats overview, rebuilt: the delivery funnel and engagement for all
3-- mail sent through the account, per day.
4--
5-- Grain: one row per day, as SendGrid's Stats API buckets it.
6-- Depends on: staging.sendgrid.stat
7--
8-- The funnel, in SendGrid's terms:
9--   requests   messages you asked SendGrid to send
10--   processed  accepted for delivery (requests minus drops)
11--   delivered  accepted by the receiving server
12--   bounces, blocks, deferred   rejected permanently, rejected for reputation or policy,
13--              or delayed and retried
14--   *_drops    never attempted, because the address was already suppressed
15--
16-- Rates follow SendGrid's own definitions: delivery and bounce rates are over requests,
17-- open, click, spam and unsubscribe rates are over delivered. They are per day. To report
18-- a week or a month, sum the counts and divide again; averaging the daily rates weights a
19-- quiet Sunday the same as a campaign day.
20--
21-- `unique_opens` and `unique_clicks` are unique per day as SendGrid reports them, so they
22-- cannot be summed into a unique count over a longer window. Apple Mail Privacy
23-- Protection pre-fetches images, which inflates opens; treat clicks as the firmer signal.
24
25SELECT
26    date,
27    requests,
28    processed,
29    delivered,
30    deferred,
31    bounces,
32    blocks,
33    invalid_emails,
34    bounce_drops,
35    spam_report_drops,
36    unsubscribe_drops,
37    COALESCE(bounce_drops, 0) + COALESCE(spam_report_drops, 0)
38        + COALESCE(unsubscribe_drops, 0)                        AS total_drops,
39    opens,
40    unique_opens,