Zoho CRM Deal Flow by Month

-- staging.zoho_crm.deal
-- Thin wrapper over raw `deal`. Casts, renames, normalises blanks. Stage
-- classification is business logic and lives in core.zoho_crm.deal_pipeline.
--
-- ACCOUNT KEY: as on contact, Zoho's lookup is called Account_Name, so the raw
-- column is `account_name_id`. It holds the account's id and is renamed here.
--
-- WHAT IS NOT HERE, because the connector does not sync it: no is_won / is_closed
-- boolean, no probability, no expected_revenue, no currency, no lead_source, no
-- contact_id, no campaign_id and no pipeline name. Won/lost has to be derived from
-- the stage string, and amount is in whatever single currency the org reports in.
select
    'org_1' as zoho_org
  , cast(id as string) as deal_id
  , nullif(trim(cast(deal_name as string)), '') as deal_name
  , nullif(trim(cast(stage as string)), '') as stage
  , -- No currency column exists on the stream, so this is org-reporting currency.
    -- A multi-currency Zoho org cannot be summed correctly from this connector.
    cast(amount as numeric) as amount
  , cast(closing_date as date) as closing_date
  , 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.deal}}

-- 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.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.deal_flow_by_month
-- BI-facing contract over the core model. See analytics/deal_pipeline.sql for why
-- this layer exists even when it is a passthrough.
select
    *
from
    {{core.zoho_crm.deal_flow_by_month}}