Calls

1-- core.gong.calls
2-- Every Gong call with its rep, participants, talk time, Gong's interaction
3-- statistics and the CRM records it is linked to, on one row.
4--
5-- Grain: one row per gong_account x call.
6-- Depends on: staging.gong.call, .call_participant, .call_speaker,
7--             .call_interaction, .call_crm_object, .user
8--
9-- Every child table is aggregated to call grain in its own CTE BEFORE the joins.
10-- Joining participants and speakers raw would multiply talk time by the number of
11-- participants.
12--
13-- THE REP is the call's primary_user_id: the Gong user who owns the call (usually
14-- the host). It is the dimension Gong's own Team Stats use.
15--
16-- TWO KINDS OF TALK RATIO:
17--   rep_talk_ratio / company_talk_ratio   computed here from per-speaker talk
18--       seconds: the rep's (or everyone internal's) share of all talk time on the
19--       call. Always 0 to 1, and auditable from call_speaker.
20--   gong_talk_ratio and the other gong_* columns   Gong's own interaction
21--       statistics, passed through as Gong reports them. Use these when the number
22--       has to match the Gong UI; see the README on units.
23
24WITH participants AS (
25    SELECT
26        gong_account,
27        call_id,
28        COUNT(*)                              AS participants,
29        COUNTIF(affiliation = 'Internal')     AS internal_participants,
30        COUNTIF(affiliation = 'External')     AS external_participants
31    FROM {{staging.gong.call_participant}}
32    GROUP BY 1, 2
33),
34
35speaker_affiliation AS (
36    -- A speaker counts as internal if Gong tied them to a user or the matching
37    -- participant is affiliated Internal.
38    SELECT
39        s.gong_account,
40        s.call_id,