Weld
Start free
Zoho CRM model

Zoho CRM Account 360

One row per account: its people, its open and won pipeline, and when anyone last wrote anything down. Flags accounts sitting on open pipeline with no note in 90 days.

See the SQLAll Zoho CRM models
Grain
account
Built from
  1. 5
    Raw tables
  2. 5
    Staging
  3. 2
    Core
  4. 1
    Analytics
Reads from Zoho CRM
accountcontactdealnoteuser
Ships as
Weld and dbt SQL, open source on GitHub
core.zoho_crm.account_360
1-- zoho_crm_account_360 - one row per account, with its people, pipeline and notes.
2-- Grain: account. Currency: org reporting currency.
3-- Depends on: staging.zoho_crm.account, .contact, .user, .note,
4--             core.zoho_crm.deal_pipeline
5--
6-- The account-level view the rest of the schema can actually support: who works
7-- there, what is open, what has been won, and when anybody last wrote anything
8-- down. This is the model to reverse-ETL back into Zoho, or to join to product
9-- usage and billing data for a real customer view.
10--
11-- NOT IN HERE: calls, meetings and tasks. They carry no account key in this
12-- connector - see core.zoho_crm.rep_activity for why. last_note_at is the closest
13-- honest proxy for account engagement the connector allows, and it only reflects
14-- what reps bothered to write down.
15
16WITH contacts AS (
17    SELECT
18        zoho_org,
19        account_id,
20        COUNT(*)                                  AS contacts,
21        COUNTIF(email IS NOT NULL)                AS contacts_with_email,
22        MAX(created_time)                         AS last_contact_added_at
23    FROM {{staging.zoho_crm.contact}}
24    WHERE account_id IS NOT NULL
25    GROUP BY 1, 2
26),
27
28deals AS (
29    SELECT
30        zoho_org,
31        account_id,
32        COUNT(*)                     AS deals_total,
33        COUNTIF(is_open)             AS deals_open,
34        COUNTIF(is_won)              AS deals_won,
35        COUNTIF(is_lost)             AS deals_lost,
36        COUNTIF(is_overdue)          AS deals_overdue,
37        SUM(pipeline_amount)         AS open_pipeline_amount,
38        SUM(won_amount)              AS won_amount,
39        SUM(lost_amount)             AS lost_amount,
40        MIN(CASE WHEN is_open THEN closing_date END) AS next_expected_close,

This model and the 7 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 account-level view the Zoho schema can actually support - contacts and how many are reachable by email, deals open, won, lost and overdue with amounts, the next expected close date, and note activity. Notes reach an account three ways: written on the account itself, on one of its deals, or on one of its contacts. Zoho does not sync the field that says which module a note's parent belongs to, but record ids are globally unique across Zoho modules, so one join per route resolves it. Calls, meetings and tasks are deliberately absent because they carry no account key in this connector, which makes last_note_at the closest honest proxy for engagement available. is_stale_with_open_pipeline flags the accounts worth chasing, and the model is a natural source for a reverse-ETL sync writing pipeline value back onto the Zoho Account record.

Raw tables it reads

  • raw.zoho_crm.account
  • raw.zoho_crm.contact
  • raw.zoho_crm.deal
  • raw.zoho_crm.note
  • 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