Score summary

1-- core.zigpoll.score_summary
2-- Average score per rating question per month, with NPS where the scale is 0 to 10.
3-- Grain: month x zigpoll_account x poll x slide. Months are UTC calendar months of
4-- the response's `created_at`. Only range and star-rating slides, synthetic polls
5-- excluded.
6-- Depends on: core.zigpoll.answer_detail, staging.zigpoll.slide_answer
7--
8-- Zigpoll has no NPS question type: an NPS question is a range slide whose options
9-- are 0 to 10. The model reads each slide's options and classifies it as NPS only
10-- when they are exactly the eleven whole numbers 0 to 10. Any other scale gets an
11-- average and no NPS, because promoter and detractor cut-offs only mean something
12-- on the 0 to 10 scale.
13--
14-- NPS = share of promoters (9 to 10) minus share of detractors (0 to 6), times 100,
15-- computed from counts. Roll longer periods up by summing the counts and
16-- recomputing, never by averaging monthly NPS values.
17
18WITH slide_scales AS (
19    SELECT
20        zigpoll_account,
21        slide_id,
22        COUNT(*)                                    AS options,
23        COUNTIF(SAFE_CAST(answer_title AS INT64) IS NOT NULL) AS numeric_options,
24        MIN(SAFE_CAST(answer_title AS INT64))       AS scale_min,
25        MAX(SAFE_CAST(answer_title AS INT64))       AS scale_max
26    FROM {{staging.zigpoll.slide_answer}}
27    GROUP BY 1, 2
28),
29
30scored AS (
31    SELECT
32        d.zigpoll_account,
33        d.poll_id,
34        d.poll_title,
35        d.slide_id,
36        d.slide_title,
37        d.slide_type,
38        DATE_TRUNC(d.response_date, MONTH) AS month,
39        -- The selected option's title is the number on the scale; the display
40        -- string is the fallback when no option row was synced.