Response times by month

1-- core.zendesk.response_times_by_month
2-- First reply time, full resolution time, requester wait time and CSAT for the
3-- tickets solved in each month: the "Efficiency" and "Satisfaction" views of
4-- Zendesk's Support dashboard.
5-- Grain: month x zendesk_account x group x channel.
6-- Depends on: core.zendesk.ticket_lifecycle
7--
8-- POPULATION: tickets solved (or closed) in the month, by their latest solve
9-- date. That matches how Explore's efficiency metrics are usually dated, and it
10-- means every ticket in a row has a finished resolution time. Unsolved tickets
11-- are in ticket_volume_by_day, not here.
12--
13-- MEDIANS do not add up. A month's median across all groups is not the average
14-- of the group medians, so never roll this table up in BI for a median. Sum the
15-- _sum_ and ticket columns and divide for a mean at any level; for a median at a
16-- coarser grain, query ticket_lifecycle directly. Medians use APPROX_QUANTILES,
17-- which is approximate on large inputs.
18--
19-- CSAT is dated on the solve month as well: the rating on the ticket carries no
20-- timestamp of its own. csat_score = good / (good + bad), Zendesk's formula.
21
22WITH solved AS (
23    SELECT *
24    FROM {{core.zendesk.ticket_lifecycle}}
25    WHERE is_solved
26      AND resolved_date IS NOT NULL
27)
28
29SELECT
30    DATE_TRUNC(resolved_date, MONTH)                         AS month,
31    zendesk_account,
32    group_id,
33    ANY_VALUE(group_name)                                    AS group_name,
34    channel,
35
36    COUNT(*)                                                 AS tickets_solved,
37    COUNTIF(first_reply_minutes_calendar IS NOT NULL)        AS tickets_with_first_reply,
38    COUNTIF(is_one_touch)                                    AS one_touch_tickets,
39    SAFE_DIVIDE(COUNTIF(is_one_touch), COUNT(*))             AS one_touch_rate,
40    COUNTIF(is_two_touch)                                    AS two_touch_tickets,