Ticket lifecycle

1-- core.zendesk.ticket_lifecycle
2-- One row per ticket with its dimensions, Zendesk's own time metrics and CSAT.
3-- Grain: zendesk_account x ticket. The other three core models read this one.
4--
5-- WHAT COUNTS AS SOLVED: status `solved` or `closed`. `new`, `open`, `pending`
6-- and `hold` are unsolved, which is Zendesk's own "unsolved tickets" definition.
7-- Deleted tickets are dropped here: Zendesk Explore leaves them out too, and the
8-- incremental export keeps returning them with status `deleted`.
9--
10-- resolved_at: ticket_metric.solved_at, the LATEST solve. A ticket merged into
11-- another is closed without ever being solved, so solved_at is empty; for those
12-- the time the status last changed is used instead, otherwise a merged ticket
13-- would sit in the backlog forever. A ticket that is unsolved right now has no
14-- resolved_at even if it was solved once before and then reopened.
15--
16-- Time metrics are copied from ticket_metric in minutes, calendar and business.
17-- Calendar is wall clock time, business follows the schedule on the ticket.
18-- Report the one your team is measured on; mixing them is the usual reason a
19-- warehouse number disagrees with Explore.
20
21WITH tickets AS (
22    SELECT *
23    FROM {{staging.zendesk.ticket}}
24    WHERE status IS DISTINCT FROM 'deleted'
25)
26
27SELECT
28    t.zendesk_account,
29    t.ticket_id,
30    t.subject,
31    t.status,
32    t.status IN ('solved', 'closed')                       AS is_solved,
33    t.status IN ('new', 'open', 'pending', 'hold')         AS is_unsolved,
34    t.priority,
35    t.ticket_type,
36    COALESCE(t.channel, 'unknown')                         AS channel,
37    t.group_id,
38    COALESCE(g.group_name, IF(t.group_id IS NULL, 'No group', NULL)) AS group_name,
39    t.assignee_id,
40    COALESCE(a.user_name, IF(t.assignee_id IS NULL, 'Unassigned', NULL)) AS assignee_name,