Agent performance by month

1-- core.zendesk.agent_performance_by_month
2-- Solved tickets, reply and resolution times and CSAT per agent per month: the
3-- "Agent activity" style leaderboard.
4-- Grain: month x zendesk_account x assignee.
5-- Depends on: core.zendesk.ticket_lifecycle
6--
7-- ATTRIBUTION is to the CURRENT assignee of each solved ticket, which is the
8-- agent who owned it at solve time in almost every case. The connector syncs no
9-- assignment history, so a ticket handed over after it was solved moves with it.
10-- The first reply may have come from someone else: Zendesk's metric does not
11-- record who replied.
12--
13-- Same population and dating as response_times_by_month: tickets solved in the
14-- month, by latest solve date. Medians do not roll up; see that model's header.
15
16SELECT
17    DATE_TRUNC(resolved_date, MONTH)                         AS month,
18    zendesk_account,
19    assignee_id,
20    ANY_VALUE(assignee_name)                                 AS assignee_name,
21    ANY_VALUE(assignee_email)                                AS assignee_email,
22
23    COUNT(*)                                                 AS tickets_solved,
24    COUNTIF(is_one_touch)                                    AS one_touch_tickets,
25    COUNTIF(was_reopened)                                    AS reopened_tickets,
26    SUM(replies)                                             AS public_agent_replies,
27
28    AVG(first_reply_minutes_calendar)                        AS first_reply_minutes_calendar_avg,
29    APPROX_QUANTILES(first_reply_minutes_calendar, 2)[SAFE_OFFSET(1)] AS first_reply_minutes_calendar_median,
30    AVG(full_resolution_minutes_calendar)                    AS full_resolution_minutes_calendar_avg,
31    APPROX_QUANTILES(full_resolution_minutes_calendar, 2)[SAFE_OFFSET(1)] AS full_resolution_minutes_calendar_median,
32    AVG(full_resolution_minutes_business)                    AS full_resolution_minutes_business_avg,
33    APPROX_QUANTILES(full_resolution_minutes_business, 2)[SAFE_OFFSET(1)] AS full_resolution_minutes_business_median,
34
35    COUNTIF(is_csat_good)                                    AS csat_good,
36    COUNTIF(is_csat_bad)                                     AS csat_bad,
37    SAFE_DIVIDE(COUNTIF(is_csat_good), COUNTIF(has_csat_rating)) AS csat_score
38FROM {{core.zendesk.ticket_lifecycle}}
39WHERE is_solved
40  AND resolved_date IS NOT NULL