Ticket lifecycle

1-- core.gorgias.ticket_lifecycle
2-- One row per ticket with its dimensions, first response and resolution times
3-- derived from the message log and close timestamp, message counts and CSAT.
4-- Grain: gorgias_account x ticket. The other ticket models read this one.
5--
6-- EXCLUDED: spam, trashed and merged tickets. Gorgias' statistics leave them out,
7-- and a merged ticket's messages live on in the ticket it was merged into.
8--
9-- FIRST RESPONSE is the first public message from the helpdesk side that was not
10-- sent by a rule, after the customer's first message:
11--   start  = the customer's first message (the ticket's creation time if none)
12--   reply  = earliest message with from_agent, public, rule_id empty, after start
13-- Messages written by AI Agent or by an app through the API are counted as agent
14-- replies, because nothing on the synced message reliably marks them.
15-- first_response_via shows how the reply was sent, so you can filter.
16-- Tickets the helpdesk started (outbound) are flagged and left out of averages.
17--
18-- RESOLUTION is creation to the ticket's LATEST close. Gorgias overwrites
19-- closed_datetime when a reopened ticket is closed again, so a ticket closed,
20-- reopened and closed counts once, on its last close. resolved_at is NULL for a
21-- ticket that is open now.
22--
23-- All durations are calendar minutes. Gorgias does not apply business hours.
24
25WITH tickets AS (
26    SELECT *
27    FROM {{staging.gorgias.ticket}}
28    WHERE NOT is_spam
29      AND NOT is_merged
30      AND trashed_at IS NULL
31      AND created_at IS NOT NULL
32),
33
34messages AS (
35    SELECT *
36    FROM {{staging.gorgias.ticket_message}}
37    WHERE failed_at IS NULL
38),
39
40first_customer AS (