Ticket volume by day

1-- core.freshdesk.ticket_volume_by_day
2-- Tickets created, tickets first responded to, and reply traffic per day.
3-- Grain: day x freshdesk_account x group x channel.
4-- Depends on: core.freshdesk.ticket_lifecycle, staging.freshdesk.ticket_conversation
5--
6-- Built on a day spine, so a quiet group or channel returns zeros instead of a gap.
7--
8-- NO RESOLVED COUNT AND NO BACKLOG HISTORY. The synced data does not record when
9-- a ticket was resolved, so "resolved per day" and the backlog on a past day cannot
10-- be computed. unresolved_now is a snapshot of tickets created on that day that
11-- are still unresolved today, not a backlog. Materialise ticket_lifecycle daily
12-- and keep the runs if you need a backlog trend.
13--
14-- Each metric is dated on its own event: created on creation, first responses on
15-- the reply, replies on the reply. Groups and channels are the ticket's current ones.
16
17WITH tickets AS (
18    SELECT *, IFNULL(group_id, '') AS group_key
19    FROM {{core.freshdesk.ticket_lifecycle}}
20    WHERE created_date IS NOT NULL
21),
22
23replies AS (
24    SELECT
25        t.freshdesk_account,
26        t.group_key,
27        t.channel,
28        DATE(c.created_at, t.reporting_timezone) AS day,
29        COUNTIF(c.is_incoming)                                            AS customer_replies,
30        COUNTIF(NOT COALESCE(c.is_incoming, FALSE)
31                AND NOT COALESCE(c.is_private, FALSE)
32                AND c.source_code IS DISTINCT FROM 2
33                AND NOT c.is_auto_response)                               AS public_outgoing_replies
34    FROM {{staging.freshdesk.ticket_conversation}} c
35    JOIN tickets t
36      ON t.freshdesk_account = c.freshdesk_account
37     AND t.ticket_id         = c.ticket_id
38    GROUP BY 1, 2, 3, 4
39),
40