Zoho CRM Rep Activity

1-- zoho_crm_rep_activity - calls, meetings and tasks logged per rep per day.
2-- Grain: day x owner.
3-- Depends on: staging.zoho_crm.call, .event, .task, .user
4--
5-- READ THIS BEFORE USING IT. This model counts activity per REP, never per deal,
6-- account, contact or lead. That is not a design choice - Zoho's Calls, Events and
7-- Tasks modules all carry What_Id / Who_Id pointing at the record the activity
8-- belongs to, and the Weld connector does not sync either field. owner_id is the
9-- only foreign key on all three streams.
10--
11-- So this model answers "how much is each rep doing" and cannot answer "how much
12-- activity did we put into the deals we won". Any dashboard promising
13-- activity-to-outcome attribution from this connector alone is inventing the link.
14-- Until the connector syncs the parent ids, the join has to come from somewhere
15-- else - a calendar or dialer source keyed on email, or Zoho's Notes module,
16-- whose parent id IS synced (parent_id_id in raw, parent_id after staging).
17--
18-- WHY THERE IS NO tasks_completed COLUMN: the connector syncs is_completed but no
19-- completion timestamp. Completions can be counted as a current-state total, never
20-- placed on a day. Booking them on modified_time would move a task's completion
21-- every time anyone edited it afterwards.
22
23WITH activity AS (
24    SELECT zoho_org, owner_id, DATE(call_start_time) AS activity_date,
25           1 AS calls_logged, call_duration_minutes AS call_minutes,
26           0 AS events_held, 0 AS event_minutes,
27           0 AS tasks_created
28    FROM {{staging.zoho_crm.call}}
29    WHERE call_start_time IS NOT NULL
30
31    UNION ALL
32
33    SELECT zoho_org, owner_id, DATE(start_date_time) AS activity_date,
34           0, 0,
35           1 AS events_held, duration_minutes AS event_minutes,
36           0
37    FROM {{staging.zoho_crm.event}}
38    WHERE start_date_time IS NOT NULL
39
40    UNION ALL