Feedback volume

1-- core.medallia.feedback_volume
2-- Feedback records created and responses received per unit per day, with response
3-- rate.
4-- Grain: day x medallia_account x unit. Dates are UTC days.
5-- Depends on: staging.medallia.feedback, .unit
6--
7-- TWO DATES, ON PURPOSE. A record is created when the invitation goes out
8-- (`created_at`) and answered later (`responded_at`). The model counts each on its
9-- own date:
10--   records_created     records whose created_at falls on the day;
11--   responses           records whose responded_at falls on the day;
12--   created_and_responded  of the records created that day, how many have been
13--                       answered by now.
14-- `response_rate` is created_and_responded / records_created, a cohort rate: it is
15-- the rate for invitations sent that day, and it keeps rising for recent days as
16-- late answers arrive. Do not divide responses by records_created; the two count
17-- different records.
18--
19-- Records with no creation date (web intercepts and other unsolicited feedback, on
20-- most instances) count as responses but never in the cohort columns.
21--
22-- Built on a day spine so a quiet day returns zeros instead of disappearing.
23
24WITH feedback AS (
25    SELECT
26        medallia_account,
27        feedback_id,
28        unit_identifier,
29        DATE(created_at)   AS created_date,
30        DATE(responded_at) AS responded_date
31    FROM {{staging.medallia.feedback}}
32),
33
34day_spine AS (
35    SELECT day
36    FROM UNNEST(GENERATE_DATE_ARRAY(
37        -- Earliest of either date. LEAST() would return NULL if one side has none.
38        (SELECT MIN(d) FROM feedback, UNNEST([created_date, responded_date]) AS d),
39        CURRENT_DATE(),
40        INTERVAL 1 DAY