Zoho CRM Rep Activity

-- staging.zoho_crm.call
-- Thin wrapper over raw `call`. Casts, renames, parses the duration string.
--
-- NO PARENT LINK. Same as task: Zoho's Calls module has What_Id / Who_Id, the
-- connector does not sync them. owner_id is the only key.
--
-- DURATION IS A STRING, NOT A NUMBER. `call_duration` arrives as text with one
-- colon. Zoho's API reference documents the field as hh:mm; the CRM UI shows
-- mm:ss for short calls, and there is no Call_Duration_in_seconds column on this
-- stream to disambiguate. This model parses it as the DOCUMENTED hh:mm.
--
-- Reconcile call_duration_minutes against one call of known length before
-- reporting on it. If your org turns out to emit mm:ss, divide by 60.
-- tests/assert_call_duration_parses.sql guards the shape, not the unit.
select
    'org_1' as zoho_org
  , cast(id as string) as call_id
  , nullif(trim(cast(subject as string)), '') as subject
  , nullif(trim(cast(call_type as string)), '') as call_type
  , cast(call_start_time as timestamp) as call_start_time
  , cast(call_duration as string) as call_duration_raw
  , case
        when regexp_contains(
            cast(call_duration as string)
          , r'^\s*\d+:\d{1,2}\s*$'
        ) then safe_cast(
            split(trim(cast(call_duration as string)), ':') [offset(0)] as int64
        ) * 60 + safe_cast(
            split(trim(cast(call_duration as string)), ':') [offset(1)] as int64
        )
    end as call_duration_minutes
  , cast(owner_id as string) as owner_id
  , cast(created_time as timestamp) as created_time
  , cast(modified_time as timestamp) as modified_time
from
    {{raw.zoho_crm.call}}

-- staging.zoho_crm.event
-- Thin wrapper over raw `event` (Zoho's Meetings module). Casts, renames, derives
-- duration from the start and end timestamps.
--
-- NO PARENT LINK. Same as task and call: owner_id is the only foreign key.
select
    'org_1' as zoho_org
  , cast(id as string) as event_id
  , nullif(trim(cast(event_title as string)), '') as event_title
  , cast(start_date_time as timestamp) as start_date_time
  , cast(end_date_time as timestamp) as end_date_time
  , -- Unlike call, event carries both ends, so duration is computed rather than
    -- parsed out of a string and the unit is unambiguous.
    timestamp_diff(
        cast(end_date_time as timestamp)
      , cast(start_date_time as timestamp)
      , minute
    ) as duration_minutes
  , nullif(trim(cast(location as string)), '') as location
  , cast(owner_id as string) as owner_id
  , cast(created_time as timestamp) as created_time
  , cast(modified_time as timestamp) as modified_time
from
    {{raw.zoho_crm.event}}

-- staging.zoho_crm.task
-- Thin wrapper over raw `task`. Casts, renames, derives is_completed.
--
-- NO PARENT LINK. Zoho's Tasks module has What_Id / Who_Id (the deal, account
  , -- contact or lead the task hangs off), but the connector does not sync them. The
-- only foreign key on this stream is owner_id, so a task can be counted per rep
-- and per day and nothing else. Do not join it to a deal - there is no key to
-- join on.
select
    'org_1' as zoho_org
  , cast(id as string) as task_id
  , nullif(trim(cast(subject as string)), '') as subject
  , nullif(trim(cast(status as string)), '') as status
  , nullif(trim(cast(priority as string)), '') as priority
  , cast(due_date as date) as due_date
  , -- Zoho's default Task statuses are Not Started, Deferred, In Progress
  , -- Completed and Waiting for input. Matched on a pattern rather than equality
    -- so a renamed or translated status does not silently read as incomplete.
    lower(cast(status as string)) like '%complet%' as is_completed
  , cast(owner_id as string) as owner_id
  , cast(created_time as timestamp) as created_time
  , cast(modified_time as timestamp) as modified_time
from
    {{raw.zoho_crm.task}}

-- staging.zoho_crm.user
-- Thin wrapper over raw `user`. Casts, renames, derives is_active. No other logic.
--
-- This is the only dimension every other stream can join to: every record-owning
-- module carries owner_id, and nothing else in the Zoho schema carries a foreign
-- key. Sync this stream even if you think you do not need it.
--
-- Single Zoho org. To add another, UNION ALL a second block below pointing at that
-- org's connector with a different zoho_org label. Keep it in staging so the core
-- models never have to know how many orgs there are.
select
    'org_1' as zoho_org
  , cast(id as string) as user_id
  , nullif(trim(cast(full_name as string)), '') as full_name
  , cast(first_name as string) as first_name
  , cast(last_name as string) as last_name
  , lower(nullif(trim(cast(email as string)), '')) as email
  , cast(status as string) as status
  , -- Zoho keeps deactivated users in the module rather than deleting them, so
    -- filtering on this is how you get "reps who could take a deal today".
    lower(cast(status as string)) = 'active' as is_active
  , cast(role_name as string) as role_name
  , cast(profile_name as string) as profile_name
  , cast(created_time as timestamp) as created_time
  , cast(modified_time as timestamp) as modified_time
from
    {{raw.zoho_crm.user}}

-- analytics.zoho_crm.rep_activity
-- BI-facing contract over the core model. See analytics/deal_pipeline.sql for why
-- this layer exists even when it is a passthrough.
--
-- Reminder for whoever binds a dashboard to this: activity here is per rep only.
-- There is no deal or account key on Zoho's call, task and event streams in this
-- connector, so do not label a tile "activity per opportunity".
select
    *
from
    {{core.zoho_crm.rep_activity}}