Rep activity

1-- core.hubspot_v2.rep_activity
2-- Calls, emails, meetings, notes and tasks logged per owner per day.
3-- Grain: day x owner. Day is in the portal's time zone.
4-- Depends on: staging.hubspot_v2.engagement, .owner, .account_details
5--
6-- Recreates HubSpot's "Activities" report (activities by type and assigned user).
7-- Activities land on the day they happened (`timestamp` in HubSpot), not the day
8-- they were logged: a call logged on Monday for last Friday counts on Friday.
9--
10-- WHAT COUNTS AS WHAT. HubSpot's engagement types are mapped as follows:
11--   emails    EMAIL (sent from HubSpot or logged), INCOMING_EMAIL, FORWARDED_EMAIL
12--   calls     CALL; connected_calls are calls with status COMPLETED
13--   meetings  MEETING; meetings_completed have outcome COMPLETED
14--   notes     NOTE
15--   tasks     TASK, counted on their due/occurrence date; tasks_completed have
16--             status COMPLETED. HubSpot does not expose the completion day here,
17--             so completed tasks sit on the task's own date.
18-- Anything else (e.g. CONVERSATION_SESSION) is counted in other_activities so
19-- the total always reconciles to the raw row count.
20--
21-- Activities with no owner are kept under owner_id NULL rather than dropped.
22
23WITH portal AS (
24    SELECT hubspot_portal, time_zone FROM {{staging.hubspot_v2.account_details}}
25),
26
27activity AS (
28    SELECT
29        e.hubspot_portal,
30        DATE(e.occurred_at, COALESCE(p.time_zone, 'UTC')) AS activity_date,
31        e.owner_id,
32        COUNT(*)                                                           AS total_activities,
33        COUNTIF(e.engagement_type IN ('EMAIL', 'INCOMING_EMAIL', 'FORWARDED_EMAIL')) AS emails,
34        COUNTIF(e.engagement_type = 'CALL')                                AS calls,
35        COUNTIF(e.engagement_type = 'CALL' AND e.status = 'COMPLETED')     AS connected_calls,
36        SUM(CASE WHEN e.engagement_type = 'CALL' THEN e.call_duration_minutes END) AS call_minutes,
37        COUNTIF(e.engagement_type = 'MEETING')                             AS meetings,
38        COUNTIF(e.engagement_type = 'MEETING' AND e.meeting_outcome = 'COMPLETED') AS meetings_completed,
39        COUNTIF(e.engagement_type = 'NOTE')                                AS notes,
40        COUNTIF(e.engagement_type = 'TASK')                                AS tasks,