Opportunity stage history
Every stage each opportunity has been in, when it got there, when it left and how long it stayed.
- Grain
- one row per opportunity x stage entry
- Built from
- 5Raw tables
- 5Staging
- 2Core
- 1Analytics
- Reads from Salesforce
- accountopportunityopportunity_historyopportunity_stageuser
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.salesforce.opportunity_stage_history
2-- Every stage each opportunity has been in, when it got there, when it left and
3-- how long it stayed. Grain: one row per opportunity x stage entry. An
4-- opportunity that returns to a stage it already visited gets a second row.
5-- Depends on: staging.salesforce.opportunity_history, .opportunity_stage,
6-- core.salesforce.opportunity_pipeline
7--
8-- HOW IT IS BUILT. OpportunityHistory writes a row whenever stage, amount,
9-- probability, close date or forecast category changes. Rows where the stage did
10-- not change (an amount edit, say) are dropped, so what remains is one row per
11-- stage entry. An entry lasts until the next one; the latest is still running and
12-- has exited_at NULL.
13--
14-- entered_closed_at on the closing entry is when the opportunity actually moved
15-- to Closed Won or Closed Lost, which close_date on the opportunity does not
16-- reliably tell you.
17--
18-- moved_backwards compares the Setup sort order of the two stages. Stages that
19-- are no longer in the picklist have no sort order and are never called
20-- backwards.
21
22WITH changes AS (
23 SELECT
24 salesforce_org,
25 opportunity_id,
26 history_id,
27 stage_name,
28 amount,
29 changed_at,
30 changed_by_id,
31 LAG(stage_name) OVER (
32 PARTITION BY salesforce_org, opportunity_id
33 ORDER BY changed_at, history_id
34 ) AS previous_stage_name
35 FROM {{staging.salesforce.opportunity_history}}
36 WHERE stage_name IS NOT NULL
37),
38
39entries AS (
40 SELECT *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().
How it counts. And where it stops.
HOW IT IS BUILT. OpportunityHistory writes a row whenever stage, amount, probability, close date or forecast category changes. Rows where the stage did not change (an amount edit, say) are dropped, so what remains is one row per stage entry. An entry lasts until the next one; the latest is still running and has exited_at NULL. entered_closed_at on the closing entry is when the opportunity actually moved to Closed Won or Closed Lost, which close_date on the opportunity does not reliably tell you. moved_backwards compares the Setup sort order of the two stages. Stages that are no longer in the picklist have no sort order and are never called backwards.
Raw tables it reads
- raw.salesforce.account
- raw.salesforce.opportunity
- raw.salesforce.opportunity_history
- raw.salesforce.opportunity_stage
- raw.salesforce.user
More Salesforce models
Put Salesforce in front
of your agents.
Connect Salesforce, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.