Voice calls by day

1-- core.gorgias.voice_calls_by_day
2-- Phone calls handled by Gorgias Voice per day: volume, answered, voicemail and
3-- talk time, the core of the "Voice" statistics.
4-- Grain: day x gorgias_account x direction x status.
5-- Depends on: staging.gorgias.voice_call
6--
7-- Dated on when the call started (created time if the provider sent no start).
8--
9-- ANSWERED means Gorgias recorded an agent, or an external forwarding number, as
10-- having answered. Gorgias does not publish the full list of status and
11-- termination_status values, so the status is kept as a dimension instead of
12-- being mapped to missed/abandoned buckets here; look at the values your account
13-- produces and group them in BI or in analytics.
14--
15-- duration is in seconds and covers the call as Gorgias recorded it. Average
16-- talk time is over answered calls only, so missed calls with a few seconds of
17-- ringing do not drag it down.
18
19WITH calls AS (
20    SELECT
21        c.*,
22        DATE(COALESCE(c.started_at, c.created_at), tz.reporting_timezone) AS call_date,
23        (c.answered_by_agent_id IS NOT NULL
24         OR c.answered_by_external_number IS NOT NULL)        AS is_answered
25    FROM {{staging.gorgias.voice_call}} c
26    -- Only for the time zone, set once in staging.gorgias.ticket.
27    JOIN (SELECT DISTINCT gorgias_account, reporting_timezone
28          FROM {{staging.gorgias.ticket}}) tz
29      ON tz.gorgias_account = c.gorgias_account
30)
31
32SELECT
33    call_date                                              AS day,
34    gorgias_account,
35    COALESCE(direction, 'unknown')                         AS direction,
36    COALESCE(status, 'unknown')                            AS status,
37
38    COUNT(*)                                               AS calls,
39    COUNTIF(is_answered)                                   AS answered_calls,
40    COUNTIF(NOT is_answered)                               AS unanswered_calls,