Response volume

1-- core.qualtrics.response_volume
2-- Recorded responses per survey per day, finished and unfinished, with duration.
3-- Grain: day x qualtrics_account x survey. Dates are UTC days of `recorded_at`,
4-- the timestamp Qualtrics' own response counts use.
5-- Depends on: staging.qualtrics.survey_response, .survey
6--
7-- Previews, test responses, synthetic responses and spam are excluded from every
8-- count and returned separately in `excluded_test_or_spam`, so the exclusion is
9-- visible rather than silent. If your Data & Analysis view includes previews,
10-- add that column back before comparing.
11--
12-- Built on a day spine so a quiet day returns zeros instead of disappearing.
13--
14-- CHANNEL. Responses are also split by how the respondent arrived, from
15-- Qualtrics' distribution_channel: `email` (an email invite), `smsinvite` (an
16-- SMS invite), `anonymous` (the anonymous survey link) and `gl` (a personal
17-- link). Everything else (QR codes, social, offline app, imports) lands in
18-- responses_other_channel, so the five columns always add up to `responses`.
19--
20-- HOW FAR ABANDONERS GOT. Qualtrics records `progress` (0 to 100) on every
21-- response. For unfinished responses its mean says whether people drop out on
22-- the first page or near the end; the sum and count are returned so it can be
23-- rolled up as a ratio of sums.
24--
25-- FINISH RATE: `finished / responses` is the share of RECORDED responses that were
26-- finished. Responses still in progress are not exported, so this is not the share
27-- of people who started; for that, use distribution_performance (email invites
28-- only) or Qualtrics' own survey stats.
29
30WITH responses AS (
31    SELECT
32        qualtrics_account,
33        survey_id,
34        DATE(recorded_at) AS recorded_date,
35        is_test_or_spam,
36        is_finished,
37        duration_seconds,
38        progress_pct,
39        LOWER(distribution_channel) AS channel
40    FROM {{staging.qualtrics.survey_response}}