Response times by month

1-- core.gorgias.response_times_by_month
2-- First response time, resolution time, messages per ticket, one-touch and
3-- zero-touch rates and satisfaction for the tickets created in each month: the
4-- "Support performance" overview.
5-- Grain: created month x gorgias_account x team x channel.
6-- Depends on: core.gorgias.ticket_lifecycle
7--
8-- DATED ON TICKET CREATION, which is how Gorgias' own first response time and
9-- resolution time statistics are dated. So the latest months fill in as tickets
10-- get answered and closed: tickets_closed and the resolution figures for the
11-- current month will keep rising for weeks.
12--
13-- Outbound tickets (the helpdesk wrote first) are excluded from first response
14-- figures only; they count everywhere else.
15--
16-- SATISFACTION: Gorgias scores 1 to 5. average_survey_score is the mean score;
17-- satisfaction_rate is the share of answers scored 4 or 5; response_rate is
18-- answered over sent. Recompute ratios from the counts after rolling up.
19-- Medians do not roll up either: query ticket_lifecycle for another grain.
20
21SELECT
22    DATE_TRUNC(created_date, MONTH)                            AS month,
23    gorgias_account,
24    team_id,
25    ANY_VALUE(team_name)                                       AS team_name,
26    channel,
27
28    COUNT(*)                                                   AS tickets_created,
29    COUNTIF(is_closed)                                         AS tickets_closed,
30    COUNTIF(NOT is_outbound)                                   AS inbound_tickets,
31    COUNTIF(NOT is_outbound AND first_response_at IS NOT NULL) AS inbound_tickets_responded,
32
33    SUM(IF(is_outbound, NULL, first_response_minutes))         AS first_response_minutes_sum,
34    AVG(IF(is_outbound, NULL, first_response_minutes))         AS first_response_minutes_avg,
35    APPROX_QUANTILES(IF(is_outbound, NULL, first_response_minutes), 2)[SAFE_OFFSET(1)]
36                                                               AS first_response_minutes_median,
37
38    SUM(resolution_minutes)                                    AS resolution_minutes_sum,
39    AVG(resolution_minutes)                                    AS resolution_minutes_avg,
40    APPROX_QUANTILES(resolution_minutes, 2)[SAFE_OFFSET(1)]    AS resolution_minutes_median,