CSAT by month

1-- core.freshdesk.csat_by_month
2-- Customer satisfaction per month, by the group and agent the response was given
3-- for: Freshdesk's "Satisfaction survey" report.
4-- Grain: response month x freshdesk_account x group x agent.
5-- Depends on: staging.freshdesk.satisfaction_rating, agent, group
6--
7-- Dated on when the customer answered, which is how Freshdesk's report dates it.
8-- Agent and group come from the response itself, not from the ticket's current
9-- state, so a ticket reassigned after the survey keeps its rating where it was.
10--
11-- csat_score = positive / all answers to the default question. Freshdesk counts
12-- neutral answers in the denominator, so they are kept there. Every count column
13-- is additive; csat_score is not, so recompute it from the counts after rolling up.
14
15WITH ratings AS (
16    SELECT
17        r.*,
18        DATE(r.created_at, t.reporting_timezone) AS rated_date
19    FROM {{staging.freshdesk.satisfaction_rating}} r
20    -- Only for the time zone; one row per helpdesk is enough.
21    JOIN (SELECT DISTINCT freshdesk_account, reporting_timezone
22          FROM {{staging.freshdesk.ticket}}) t
23      ON t.freshdesk_account = r.freshdesk_account
24)
25
26SELECT
27    DATE_TRUNC(r.rated_date, MONTH)                  AS month,
28    r.freshdesk_account,
29    r.group_id,
30    ANY_VALUE(g.group_name)                          AS group_name,
31    r.agent_id,
32    ANY_VALUE(a.agent_name)                          AS agent_name,
33
34    COUNT(*)                                         AS survey_responses,
35    COUNTIF(r.rating_value IS NOT NULL)              AS rated_responses,
36    COUNTIF(r.rating_value > 100)                    AS positive,
37    COUNTIF(r.rating_value = 100)                    AS neutral,
38    COUNTIF(r.rating_value < 100)                    AS negative,
39    SAFE_DIVIDE(COUNTIF(r.rating_value > 100),
40                COUNTIF(r.rating_value IS NOT NULL)) AS csat_score