NPS by unit month

1-- core.medallia.nps_by_unit_month
2-- Net Promoter Score per unit per month for your likelihood-to-recommend questions.
3-- Grain: month x medallia_account x unit x question. Months are UTC calendar
4-- months of `responded_at`.
5-- Depends on: core.medallia.answer_detail, staging.medallia.field_meta_data
6--
7-- WHICH QUESTION IS NPS is instance configuration in Medallia, not a fixed field
8-- id. The `nps_questions` CTE below picks questions whose name mentions
9-- "recommend". Check what it selects with tests/assert_medallia__nps_questions_are_0_to_10.sql
10-- and replace the rule with an explicit list of question ids (for example
11-- `WHERE field_id IN ('q_ltr')`) once you know yours. This is the one edit most
12-- instances need.
13--
14-- Promoters score 9 to 10, passives 7 to 8, detractors 0 to 6; NPS is the share of
15-- promoters minus the share of detractors, times 100, from counts. Roll up by
16-- summing the counts and recomputing, never by averaging monthly NPS values: that
17-- weights a unit with five answers the same as one with five hundred.
18
19WITH nps_questions AS (
20    SELECT medallia_account, field_id AS question_id
21    FROM {{staging.medallia.field_meta_data}}
22    WHERE LOWER(field_name) LIKE '%recommend%'
23      AND COALESCE(data_type, '') <> 'COMMENT'
24),
25
26scored AS (
27    SELECT
28        a.medallia_account,
29        DATE_TRUNC(a.responded_date, MONTH) AS month,
30        a.unit_identifier,
31        a.unit_name,
32        a.question_id,
33        a.question_name,
34        a.score,
35        a.score IS NOT NULL AND a.score BETWEEN 0 AND 10 AND a.score = TRUNC(a.score) AS is_valid_score
36    FROM {{core.medallia.answer_detail}} a
37    INNER JOIN nps_questions q
38            ON q.medallia_account = a.medallia_account
39           AND q.question_id      = a.question_id
40    WHERE a.responded_at IS NOT NULL