Response volume

1-- core.typeform.response_volume
2-- Submitted responses per form per day, with time to complete.
3-- Grain: day x typeform_account x form. Dates are UTC days of `submitted_at`.
4-- Depends on: staging.typeform.response, .form
5--
6-- Built on a day spine so a form with a quiet day still returns a row of zeros
7-- instead of vanishing from the series, which would let a BI line chart draw
8-- straight over the gap.
9--
10-- WHAT THIS IS NOT: a completion rate. The connector syncs submitted responses
11-- only, with no views, starts or partial responses, so there is no denominator.
12-- Typeform's Results page shows those figures; this model reconciles to its
13-- "Responses" count.
14
15WITH responses AS (
16    SELECT
17        typeform_account,
18        form_id,
19        response_token,
20        DATE(submitted_at) AS submitted_date,
21        calculated_score,
22        -- Time from opening the form to submitting it. A respondent who leaves the
23        -- tab open overnight produces a huge value, so the median is returned
24        -- alongside the mean.
25        TIMESTAMP_DIFF(submitted_at, landed_at, SECOND) AS seconds_to_complete
26    FROM {{staging.typeform.response}}
27    WHERE submitted_at IS NOT NULL
28),
29
30day_spine AS (
31    SELECT day
32    FROM UNNEST(GENERATE_DATE_ARRAY(
33        (SELECT MIN(submitted_date) FROM responses),
34        CURRENT_DATE(),
35        INTERVAL 1 DAY
36    )) AS day
37),
38
39forms AS (
40    -- Only forms that have ever received a response, so drafts and abandoned