Ticket lifecycle

1-- core.freshdesk.ticket_lifecycle
2-- One row per ticket with its dimensions, a first response time derived from the
3-- conversation log, reply counts and the latest CSAT answer.
4-- Grain: freshdesk_account x ticket. The other core models read this one.
5--
6-- FIRST RESPONSE is the earliest conversation that is
7--   * sent by an agent (user_id is in `agent`),
8--   * not incoming, not private, not a note (source_code 2), and
9--   * not an automated reply.
10-- first_response_minutes is calendar time from ticket creation to that reply.
11-- Freshdesk's own report can also show business hours; ticket stats are not
12-- synced, so only calendar time is available. Outbound tickets
13-- (an agent wrote first) are flagged and left out of response-time averages.
14--
15-- RESOLUTION TIME IS NOT HERE. There is no resolved_at or closed_at,
16-- and updated_at moves on every later edit (Freshdesk's auto-close alone adds
17-- days), so any resolution time built from it would be wrong. is_resolved says
18-- whether a ticket is resolved NOW, which is all the data supports.
19--
20-- Spam is dropped. Freshdesk's ticket list hides spam and deleted tickets, so a
21-- ticket marked spam or deleted after it was synced stops updating and stays in
22-- the warehouse with its last state; see the README.
23
24WITH tickets AS (
25    SELECT *
26    FROM {{staging.freshdesk.ticket}}
27    WHERE NOT is_spam
28),
29
30agent_replies AS (
31    SELECT
32        c.freshdesk_account,
33        c.ticket_id,
34        c.user_id,
35        c.created_at
36    FROM {{staging.freshdesk.ticket_conversation}} c
37    JOIN {{staging.freshdesk.agent}} a
38      ON a.freshdesk_account = c.freshdesk_account
39     AND a.agent_id          = c.user_id
40    WHERE NOT COALESCE(c.is_incoming, FALSE)