Answer detail

1-- core.medallia.answer_detail
2-- Every answer on every feedback record, in long format, with the question name,
3-- option label and numeric score resolved.
4-- Grain: medallia_account x feedback record x question x position. A
5-- single-valued answer is one row; a multi-valued one is one row per value.
6-- Depends on: staging.medallia.feedback_answer, .feedback, .field_meta_data,
7--             .field_option, .unit
8--
9-- `score` is the option's numeric value for a fixed-option question (the number
10-- behind "9 - Very likely"), or the answer itself when it is a plain number.
11-- Comments and other text answers have a NULL score and their text in
12-- `answer_label`.
13
14SELECT
15    a.medallia_account,
16    a.feedback_id,
17    f.status,
18    f.survey_source,
19    f.survey_method,
20    f.responded_at,
21    DATE(f.responded_at) AS responded_date,
22    f.unit_identifier,
23    u.unit_name,
24
25    a.question_id,
26    m.field_name AS question_name,
27    m.data_type,
28    a.position,
29    a.answer_rank,
30    a.answer_value,
31    -- The option label when the answer is an option id, otherwise the value as
32    -- given.
33    COALESCE(o.option_name, a.answer_value) AS answer_label,
34    COALESCE(CAST(o.numeric_value AS FLOAT64), SAFE_CAST(a.answer_value AS FLOAT64)) AS score
35FROM {{staging.medallia.feedback_answer}} a
36-- INNER: an answer whose record is missing cannot be dated or placed in a unit.
37INNER JOIN {{staging.medallia.feedback}} f
38        ON f.medallia_account = a.medallia_account
39       AND f.feedback_id      = a.feedback_id
40LEFT JOIN {{staging.medallia.field_meta_data}} m