NPS by month

1-- core.qualtrics.nps_by_month
2-- Net Promoter Score per NPS question per month.
3-- Grain: month x qualtrics_account x survey x question. Months are UTC calendar
4-- months of `recorded_at`. Only questions Qualtrics defines as NPS (type MC,
5-- selector NPS), and only non-test, non-spam responses. Recorded but unfinished
6-- responses count when they answered the NPS question, as in Qualtrics' reports.
7-- Depends on: core.qualtrics.answer_detail
8--
9-- Promoters score 9 to 10, passives 7 to 8, detractors 0 to 6, and NPS is the
10-- share of promoters minus the share of detractors, times 100. The bucket counts
11-- are returned, so a quarter or a year is recomputed from summed counts. Never
12-- average the monthly NPS values: that weights a month with ten answers the same
13-- as one with a thousand.
14--
15-- THE SCORE IS THE EXPORTED VALUE, which for an NPS question is the choice's
16-- recode. Qualtrics' default NPS recodes are 0 to 10. If someone has changed the
17-- recode values on a question, its scores are wrong here and in Qualtrics' own
18-- NPS widget alike; `invalid_scores` counts values outside 0 to 10 so it shows up.
19
20WITH nps_answers AS (
21    SELECT
22        qualtrics_account,
23        survey_id,
24        survey_name,
25        question_id,
26        question_text,
27        DATE_TRUNC(recorded_date, MONTH) AS month,
28        response_number AS score
29    FROM {{core.qualtrics.answer_detail}}
30    WHERE question_type = 'MC'
31      AND selector      = 'NPS'
32      AND NOT is_test_or_spam
33      AND response_value IS NOT NULL
34),
35
36scored AS (
37    SELECT
38        *,
39        score IS NOT NULL AND score BETWEEN 0 AND 10 AND score = TRUNC(score) AS is_valid_score
40    FROM nps_answers