Weld
Start free
Zoho CRM model

Zoho CRM Deal Pipeline

Every Zoho deal classified as open, won or lost, with its account and owner joined on. Zoho ships no is_won flag, so the outcome is derived from the stage string - and the model shows you which stages fell through.

See the SQLAll Zoho CRM models
Grain
one row per deal
Built from
  1. 3
    Raw tables
  2. 3
    Staging
  3. 1
    Core
  4. 1
    Analytics
Reads from Zoho CRM
accountdealuser
Ships as
Weld and dbt SQL, open source on GitHub
core.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.
16
17WITH 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_zoho_crm__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 {{staging.zoho_crm.deal}} d
34)
35
36SELECT
37    d.zoho_org,
38    d.deal_id,
39    d.deal_name,
40    d.stage,

This model and the 4 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

The deal-grain model the rest of the Zoho CRM set reads. It classifies every deal as Open, Won or Lost from the stage string, because Zoho CRM exposes no is_won or is_closed boolean and no probability field. Amount is split into pipeline_amount, won_amount and lost_amount so BI can sum a column instead of repeating the CASE, and each deal carries its age, an is_overdue flag for open deals past their expected close date, its account and industry, and its owner including whether that rep is still active. Deals whose owner has been deleted from Zoho are kept rather than dropped, so the pipeline total stays complete.

Raw tables it reads

  • raw.zoho_crm.account
  • raw.zoho_crm.deal
  • raw.zoho_crm.user

More Zoho CRM models

Put Zoho CRM in front
of your agents.

Connect Zoho CRM, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us