Distribution performance

1-- core.qualtrics.distribution_performance
2-- Email distribution funnel: sent, bounced, opened, started, finished, and the rates
3-- between them, per distribution.
4-- Grain: qualtrics_account x distribution. Dated by `sent_at`.
5-- Depends on: staging.qualtrics.distribution, .survey
6--
7-- This recreates the per-distribution stats on Qualtrics' Distributions tab. The
8-- counts are Qualtrics' own running totals as of the last time the distribution
9-- was synced (see staging/distribution.sql), so a distribution sent months
10-- ago may show slightly lower started and finished figures than the UI if
11-- respondents kept trickling in after it left the sync's look-back window.
12--
13-- RATES ARE PER DISTRIBUTION. To roll up to survey or month, sum the counts and
14-- divide, never average the rates. Reminders and thank-yous are separate rows,
15-- linked to their invite by `parent_distribution_id`; filter on
16-- `request_type = 'Invite'` before summing `sent` if you want people invited
17-- rather than emails sent.
18
19SELECT
20    d.qualtrics_account,
21    d.distribution_id,
22    d.parent_distribution_id,
23    d.survey_id,
24    s.survey_name,
25    d.request_type,
26    d.request_status,
27    d.link_type,
28    d.mailing_list_id,
29    d.from_name,
30    d.sent_at,
31    DATE(d.sent_at) AS sent_date,
32
33    d.sent,
34    d.failed,
35    d.bounced,
36    d.blocked,
37    d.skipped,
38    d.complaints,
39    d.opened,
40    d.started,