Rep activity

1-- core.salesforce.rep_activity
2-- Calls, emails, completed tasks and meetings logged per rep per day.
3-- Grain: date_day x salesforce_org x owner_id.
4-- Depends on: staging.salesforce.task, .event, .user
5--
6-- WHICH DAY AN ACTIVITY LANDS ON:
7--   calls and emails  -> the task's Date (activity_date): the day of the logged
8--                        call or email. Falls back to the created day if empty.
9--   completed tasks   -> other closed tasks, on their Date, which for a to-do is
10--                        the due date. The connector's standard fields carry no
11--                        completion timestamp that every org has, so the due
12--                        date is the closest stable day.
13--   tasks created     -> the day the task was created.
14--   meetings          -> the event's start day. Only events that have started
15--                        count; future meetings are scheduled, not held.
16--
17-- Nothing after today is counted. Each activity is counted once, for its owner.
18-- Activities that hang off a record (what_id, who_id, account_id) are kept in
19-- staging for anyone who wants them per opportunity or account; this model is
20-- per rep only.
21
22WITH activity AS (
23    SELECT
24        salesforce_org,
25        owner_id,
26        COALESCE(activity_date, DATE(created_at))                  AS date_day,
27        IF(task_subtype = 'Call' OR (task_subtype IS NULL AND task_type = 'Call'), 1, 0) AS calls,
28        IF(task_subtype = 'Call' OR (task_subtype IS NULL AND task_type = 'Call'),
29           call_duration_seconds / 60.0, NULL)                     AS call_minutes,
30        IF(task_subtype IN ('Email', 'ListEmail')
31           OR (task_subtype IS NULL AND task_type = 'Email'), 1, 0) AS emails,
32        IF(is_closed
33           AND COALESCE(task_subtype, 'Task') NOT IN ('Call', 'Email', 'ListEmail')
34           AND COALESCE(task_type, '') NOT IN ('Call', 'Email'), 1, 0) AS tasks_completed,
35        0                                                          AS tasks_created,
36        0                                                          AS meetings,
37        CAST(NULL AS FLOAT64)                                      AS meeting_minutes
38    FROM {{staging.salesforce.task}}
39
40    UNION ALL