Agent performance by month

1-- core.gorgias.agent_performance_by_month
2-- Per agent per month: tickets closed and how fast, messages sent, first
3-- responses given, and satisfaction on their tickets.
4-- Grain: month x gorgias_account x agent.
5-- Depends on: core.gorgias.ticket_lifecycle, staging.gorgias.ticket_message
6--
7-- Each block is dated and attributed on its own event:
8--   tickets_closed, resolution_*, survey_*   closed month, the CURRENT assignee
9--   first_responses, first_response_*        first reply month, the agent who sent it
10--   messages_sent                            message month, the sender
11-- The connector syncs no assignment history, so closed tickets go to whoever is
12-- assigned now. Do not divide a column by one from another block.
13-- Medians do not roll up across months.
14
15WITH tz AS (
16    SELECT DISTINCT gorgias_account, reporting_timezone
17    FROM {{core.gorgias.ticket_lifecycle}}
18),
19
20closed AS (
21    SELECT
22        gorgias_account,
23        assignee_id                                        AS agent_id,
24        DATE_TRUNC(resolved_date, MONTH)                   AS month,
25        ANY_VALUE(assignee_name)                           AS agent_name,
26        COUNT(*)                                           AS tickets_closed,
27        COUNTIF(is_one_touch)                              AS one_touch_tickets,
28        AVG(resolution_minutes)                            AS resolution_minutes_avg,
29        APPROX_QUANTILES(resolution_minutes, 2)[SAFE_OFFSET(1)] AS resolution_minutes_median,
30        COUNTIF(survey_score IS NOT NULL)                  AS surveys_scored,
31        AVG(survey_score)                                  AS average_survey_score,
32        SAFE_DIVIDE(COUNTIF(survey_score >= 4), COUNTIF(survey_score IS NOT NULL)) AS satisfaction_rate
33    FROM {{core.gorgias.ticket_lifecycle}}
34    WHERE is_closed AND assignee_id IS NOT NULL AND resolved_date IS NOT NULL
35    GROUP BY 1, 2, 3
36),
37
38first_responses AS (
39    SELECT
40        l.gorgias_account,