Zoho CRM Account 360

-- staging.zoho_crm.account
-- Thin wrapper over raw `account`. Casts, renames, normalises blanks to NULL.
--
-- Single Zoho org. To add another, UNION ALL a second block below pointing at that
-- org's connector with a different zoho_org label.
select
    'org_1' as zoho_org
  , cast(id as string) as account_id
  , nullif(trim(cast(account_name as string)), '') as account_name
  , nullif(trim(cast(industry as string)), '') as industry
  , nullif(trim(cast(phone as string)), '') as phone
  , nullif(trim(cast(website as string)), '') as website
  , -- Zoho flattens its lookup fields into _id / _name / _email triples. Keep the
    -- id for joining and drop the denormalised name - staging.zoho_crm.user is the
    -- single source of truth for what a user is called, so a rep who is renamed in
    -- Zoho does not leave stale labels scattered across every module.
    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.account}}

-- staging.zoho_crm.contact
-- Thin wrapper over raw `contact`. Casts, renames, builds full_name.
--
-- NOTE ON THE ACCOUNT KEY: Zoho's lookup field is called Account_Name, so the
-- connector emits `account_name_id` (the account's id) and `account_name_name`
-- (its label). The _id column is the real foreign key despite the name. It is
-- renamed to account_id here so core reads like a normal star schema.
select
    'org_1' as zoho_org
  , cast(id as string) as contact_id
  , cast(first_name as string) as first_name
  , cast(last_name as string) as last_name
  , nullif(
        trim(
            concat(
                coalesce(cast(first_name as string), '')
              , ' '
              , coalesce(cast(last_name as string), '')
            )
        )
      , ''
    ) as full_name
  , lower(nullif(trim(cast(email as string)), '')) as email
  , cast(account_name_id as string) as account_id
  , 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.contact}}

-- staging.zoho_crm.note
-- Thin wrapper over raw `note`. Casts, renames, normalises blanks.
--
-- THE ONE STREAM WITH A REAL PARENT LINK. Every other activity module (call, task
  , -- event) loses its What_Id / Who_Id in this connector, but note keeps its parent:
-- Zoho's Parent_Id lookup arrives as `parent_id_id`, and it is the only way to tie
-- anything a rep wrote down back to the deal, account, contact or lead it was
-- about.
--
-- WHAT MODULE IS THE PARENT? Zoho's API exposes $se_module to say which one, and
-- the connector does not sync it. Record ids are globally unique across Zoho
-- modules though, so the module can be recovered by joining parent_id to each
-- module in turn and seeing which one matches - that is what
-- core.zoho_crm.account_360 does. `parent_name` is the label Zoho denormalised
-- onto the note and is kept only as a fallback for a parent that no longer exists.
select
    'org_1' as zoho_org
  , cast(id as string) as note_id
  , nullif(trim(cast(note_title as string)), '') as note_title
  , nullif(trim(cast(note_content as string)), '') as note_content
  , cast(parent_id_id as string) as parent_id
  , nullif(trim(cast(parent_id_name as string)), '') as parent_name
  , 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.note}}

-- 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}}

-- zoho_crm_deal_pipeline - every deal, classified, with its account and owner.
-- Grain: one row per deal. Currency: org reporting currency (see scope note).
-- Depends on: staging.zoho_crm.deal, .account, .user
--
-- This is the deal-grain model the other reports read. Classify once here rather
-- than repeating the stage logic in every downstream query.
--
-- SCOPE: current state, not history. Zoho CRM has no history tables in Weld, and
-- this connector syncs no stage-change audit, so there is no way to ask what the
-- pipeline looked like last Tuesday or how long a deal sat in Negotiation. Every
-- deal shows only where it stands now. If you need stage velocity or a pipeline
-- snapshot over time, materialise this model daily and keep the runs.
--
-- Joins are written with explicit ON rather than USING: both deal and account
-- carry owner_id, so a USING(owner_id) join further down would be ambiguous.
with
    deal as (
        select
            d.*
          , -- Zoho ships no is_won / is_closed boolean, so won and lost have to be
            -- read out of the stage string. Matched on a pattern, not an equality
            -- list, because Zoho's own defaults include "Closed-Lost to Competition"
            -- and most orgs add their own stages on top.
            --
            -- Won is tested first so a stage that somehow contains both words is
            -- counted once. tests/assert_deal_stages_are_classified.sql lists every
            -- stage falling through to Open - read it before trusting the split.
            case
                when lower(d.stage) like '%won%' then 'Won'
                when lower(d.stage) like '%lost%' then 'Lost'
                else 'Open'
            end as stage_status
        from
            {{staging.zoho_crm.deal}} d
    )
select
    d.zoho_org
  , d.deal_id
  , d.deal_name
  , d.stage
  , d.stage_status
  , d.stage_status = 'Open' as is_open
  , d.stage_status = 'Won' as is_won
  , d.stage_status = 'Lost' as is_lost
  , d.amount
  , -- Split out so BI can sum a column instead of writing the CASE again. An open
    -- deal contributes to pipeline_amount only; a closed one to won or lost.
    case
        when d.stage_status = 'Open' then d.amount
    end as pipeline_amount
  , case
        when d.stage_status = 'Won' then d.amount
    end as won_amount
  , case
        when d.stage_status = 'Lost' then d.amount
    end as lost_amount
  , date(d.created_time) as created_date
  , d.closing_date
  , d.created_time
  , d.modified_time
  , -- Age of the deal. For a closed deal this is how long it took; for an open one
    -- how long it has been sitting. closing_date is Zoho's EXPECTED close date and
    -- it is not cleared when a deal closes, so on a won or lost deal it is the
    -- forecast that was in place, not necessarily the day money changed hands.
    date_diff(
        coalesce(
            case
                when d.stage_status <> 'Open' then d.closing_date
            end
          , current_date()
        )
      , date(d.created_time)
      , day
    ) as age_days
  , -- Open deals whose expected close date has already passed: the cheapest
    -- pipeline-hygiene number there is, and usually the first thing a sales lead
    -- asks for.
    --
    -- COALESCE, because closing_date can be NULL and NULL < CURRENT_DATE() is
    -- NULL, not FALSE. Without it the flag is tri-valued and a downstream
    -- WHERE NOT is_overdue quietly drops every deal with no expected close date.
    -- No close date means not overdue.
    coalesce(
        d.stage_status = 'Open'
        and d.closing_date < current_date()
      , false
    ) as is_overdue
  , case
        when d.stage_status = 'Open' then date_diff(d.closing_date, current_date(), day)
    end as days_to_expected_close
  , d.account_id
  , a.account_name
  , a.industry
  , d.owner_id
  , u.full_name as owner_name
  , u.email as owner_email
  , u.role_name as owner_role
  , -- A deal owned by a deactivated rep is unmanaged pipeline. NULL here means the
    -- owner_id did not resolve at all - see tests/assert_owner_ids_resolve.sql.
    u.is_active as owner_is_active
from
    deal d
    left join {{staging.zoho_crm.account}} a on a.zoho_org = d.zoho_org
    and a.account_id = d.account_id
    -- LEFT, not INNER: an owner who has been deleted from Zoho no longer appears in
    -- the user module, and an inner join would silently drop their deals from the
    -- pipeline total.
    left join {{staging.zoho_crm.user}} u on u.zoho_org = d.zoho_org
    and u.user_id = d.owner_id

-- analytics.zoho_crm.account_360
-- BI-facing contract over the core model. See analytics/deal_pipeline.sql for why
-- this layer exists even when it is a passthrough.
--
-- This is the natural source for a reverse-ETL sync back into Zoho: writing
-- open_pipeline_amount or is_stale_with_open_pipeline onto the Account record puts
-- the warehouse's view in front of the reps who need it.
select
    *
from
    {{core.zoho_crm.account_360}}