Zoho CRM Deal Pipeline

1-- zoho_crm_deal_pipeline - every deal, classified, with its account and owner.
2-- Grain: one row per deal. Currency: org reporting currency (see scope note).
3-- Depends on: staging.zoho_crm.deal, .account, .user
4--
5-- This is the deal-grain model the other reports read. Classify once here rather
6-- than repeating the stage logic in every downstream query.
7--
8-- SCOPE: current state, not history. Zoho CRM has no history tables in Weld, and
9-- this connector syncs no stage-change audit, so there is no way to ask what the
10-- pipeline looked like last Tuesday or how long a deal sat in Negotiation. Every
11-- deal shows only where it stands now. If you need stage velocity or a pipeline
12-- snapshot over time, materialise this model daily and keep the runs.
13--
14-- Joins are written with explicit ON rather than USING: both deal and account
15-- carry owner_id, so a USING(owner_id) join further down would be ambiguous.
16with
17    deal as (
18        select
19            d.*
20          , -- Zoho ships no is_won / is_closed boolean, so won and lost have to be
21            -- read out of the stage string. Matched on a pattern, not an equality
22            -- list, because Zoho's own defaults include "Closed-Lost to Competition"
23            -- and most orgs add their own stages on top.
24            --
25            -- Won is tested first so a stage that somehow contains both words is
26            -- counted once. tests/assert_deal_stages_are_classified.sql lists every
27            -- stage falling through to Open - read it before trusting the split.
28            case
29                when lower(d.stage) like '%won%' then 'Won'
30                when lower(d.stage) like '%lost%' then 'Lost'
31                else 'Open'
32            end as stage_status
33        from
34            {{staging.zoho_crm.deal}} d
35    )
36select
37    d.zoho_org
38  , d.deal_id
39  , d.deal_name
40  , d.stage
41  , d.stage_status
42  , d.stage_status = 'Open' as is_open
43  , d.stage_status = 'Won' as is_won
44  , d.stage_status = 'Lost' as is_lost
45  , d.amount
46  , -- Split out so BI can sum a column instead of writing the CASE again. An open
47    -- deal contributes to pipeline_amount only; a closed one to won or lost.
48    case
49        when d.stage_status = 'Open' then d.amount
50    end as pipeline_amount
51  , case
52        when d.stage_status = 'Won' then d.amount
53    end as won_amount
54  , case
55        when d.stage_status = 'Lost' then d.amount
56    end as lost_amount
57  , date(d.created_time) as created_date
58  , d.closing_date
59  , d.created_time
60  , d.modified_time
61  , -- Age of the deal. For a closed deal this is how long it took; for an open one
62    -- how long it has been sitting. closing_date is Zoho's EXPECTED close date and
63    -- it is not cleared when a deal closes, so on a won or lost deal it is the
64    -- forecast that was in place, not necessarily the day money changed hands.
65    date_diff(
66        coalesce(
67            case
68                when d.stage_status <> 'Open' then d.closing_date
69            end
70          , current_date()
71        )
72      , date(d.created_time)
73      , day
74    ) as age_days
75  , -- Open deals whose expected close date has already passed: the cheapest
76    -- pipeline-hygiene number there is, and usually the first thing a sales lead
77    -- asks for.
78    --
79    -- COALESCE, because closing_date can be NULL and NULL < CURRENT_DATE() is
80    -- NULL, not FALSE. Without it the flag is tri-valued and a downstream
81    -- WHERE NOT is_overdue quietly drops every deal with no expected close date.
82    -- No close date means not overdue.
83    coalesce(
84        d.stage_status = 'Open'
85        and d.closing_date < current_date()
86      , false
87    ) as is_overdue
88  , case
89        when d.stage_status = 'Open' then date_diff(d.closing_date, current_date(), day)
90    end as days_to_expected_close
91  , d.account_id
92  , a.account_name
93  , a.industry
94  , d.owner_id
95  , u.full_name as owner_name
96  , u.email as owner_email
97  , u.role_name as owner_role
98  , -- A deal owned by a deactivated rep is unmanaged pipeline. NULL here means the
99    -- owner_id did not resolve at all - see tests/assert_owner_ids_resolve.sql.
100    u.is_active as owner_is_active
101from
102    deal d
103    left join {{staging.zoho_crm.account}} a on a.zoho_org = d.zoho_org
104    and a.account_id = d.account_id
105    -- LEFT, not INNER: an owner who has been deleted from Zoho no longer appears in
106    -- the user module, and an inner join would silently drop their deals from the
107    -- pipeline total.
108    left join {{staging.zoho_crm.user}} u on u.zoho_org = d.zoho_org
109    and u.user_id = d.owner_id