Weld
Start free
Zoho CRM model

Zoho CRM Deal Flow by Month

Deals created, won and lost per month per rep, with win rate by count and by value. Built on a month spine so a quiet month returns zeros instead of vanishing from the chart.

See the SQLAll Zoho CRM models
Grain
month x owner
Built from
  1. 3
    Raw tables
  2. 3
    Staging
  3. 2
    Core
  4. 1
    Analytics
Reads from Zoho CRM
accountdealuser
Ships as
Weld and dbt SQL, open source on GitHub
core.zoho_crm.deal_flow_by_month
1-- zoho_crm_deal_flow_by_month - deals created, won and lost per month per rep.
2-- Grain: month x owner. Currency: org reporting currency.
3-- Depends on: core.zoho_crm.deal_pipeline
4--
5-- Built on a month spine so a rep with no activity in a month still returns a row
6-- of zeros rather than disappearing from the series - otherwise a BI line chart
7-- interpolates straight over the quiet month and the gap is invisible.
8--
9-- WHICH DATE EACH EVENT LANDS ON:
10--   created  -> created_time, which is a real audit timestamp.
11--   won/lost -> closing_date, which is Zoho's EXPECTED close date.
12--
13-- That second one is the compromise this model cannot avoid. The connector syncs
14-- no actual close timestamp and no stage history, so closing_date is the only
15-- close date available. If a rep sets a deal to Closed Won in June while its
16-- closing_date still says March, this model books the win in March. modified_time
17-- would move it to June, but modified_time changes on ANY edit, so a note added in
18-- August would move the win again. A forecast date that is wrong once beats a date
19-- that moves every time somebody touches the record.
20--
21-- The fix is upstream, not here: materialise deal_pipeline daily and derive real
22-- close dates from when stage_status first changed.
23
24WITH month_spine AS (
25    SELECT month
26    FROM UNNEST(GENERATE_DATE_ARRAY(
27        -- Start the spine at the first deal rather than a hardcoded date, so the
28        -- series never carries years of empty leading months.
29        (SELECT DATE_TRUNC(MIN(created_date), MONTH) FROM {{core.zoho_crm.deal_pipeline}}),
30        -- End a year out: closing_date is a forecast, so pipeline legitimately
31        -- sits in the future and must not be truncated away.
32        DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR), MONTH),
33        INTERVAL 1 MONTH
34    )) AS month
35),
36
37owners AS (
38    -- Only reps who actually appear on a deal. Spining every Zoho user against
39    -- every month would pad the output with support and admin logins.
40    SELECT DISTINCT zoho_org, owner_id, owner_name

This model and the 5 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.

Turns the deal pipeline into a monthly series per rep: deals created and their value, deals won and lost with amounts, open deals forecast to close in the month, win rate by count and by value, and average won deal size. Open deals are excluded from the win-rate denominator, because counting them as not-yet-won drags every current month down and makes the trend look like a collapse. Wins and losses are booked on closing_date - Zoho's expected close date - because the connector syncs no actual close timestamp and no stage history; modified_time would be worse, since it moves every time anyone edits the record. The month spine starts at the first deal and runs a year past today, so forecast pipeline is not truncated away.

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