Salesforce Opportunity Stage Duration Analysis

Salesforce tracks stage transitions in the OpportunityFieldHistory table. With field history tracking enabled on StageName, this table provides a chronological record of when each opportunity moved between stages. Calculating time-in-stage from this data requires a specific modeling pattern using window functions.

The Source: OpportunityFieldHistory

Salesforce tracks field-level changes when field history tracking is enabled. For Opportunity, you need to ensure StageName has field history tracking turned on (this is a Salesforce admin setting, not a warehouse concern — but verify it before building the model, or you’ll have an empty table).

When enabled, OpportunityFieldHistory stores one row per field change:

• OpportunityId — which opportunity changed
• Field (or field_name) — which field changed (we want StageName)
• OldValue — the previous value
• NewValue — the new value
• CreatedDate — when the change happened

This gives you the raw material: a chronological sequence of stage transitions for each opportunity.

The Stage Duration Model

The pattern uses the LEAD window function to pair each stage entry with the next stage entry, then calculates the difference:

-- int__opportunity_stage_duration.sql
WITH

stage_changes AS (
    SELECT
        opportunity_id  AS opportunity__id,
        new_value       AS opportunity__stage,
        created_date    AS stage__entered_at,
        LEAD(created_date) OVER (
            PARTITION BY opportunity_id
            ORDER BY created_date
        ) AS stage__exited_at
    FROM {{ ref('base__salesforce__opportunity_field_history') }}
    WHERE field_name = 'StageName'
)

SELECT
    opportunity__id,
    opportunity__stage,
    stage__entered_at,
    stage__exited_at,
    DATE_DIFF(
        COALESCE(stage__exited_at, CURRENT_DATE()),
        stage__entered_at,
        DAY
    ) AS stage__days
FROM stage_changes

The LEAD function looks ahead to the next row within the same opportunity (partitioned by opportunity_id, ordered by created_date). For the current stage (the last one in the sequence), LEAD returns NULL — the COALESCE with CURRENT_DATE() handles this by computing how many days the deal has been in its current stage.

The output is one row per opportunity-stage combination, with the number of days spent in that stage.

Why This Differs from Snapshot-Based Duration

dbt snapshots can also track stage changes, but with an important difference: snapshots capture state at execution time (typically daily). If an opportunity moves from “Discovery” to “Proposal” and then to “Negotiation” in the same day, a daily snapshot captures only the final state. You’d see the deal jump from “Discovery” to “Negotiation” with no record of the “Proposal” stage.

OpportunityFieldHistory captures every individual change as it happens, regardless of when your snapshot runs. This gives you accurate per-stage timing even for fast-moving deals. The tradeoff is that OpportunityFieldHistory only exists if field history tracking is enabled, and Salesforce limits each object to tracking history on 20 fields.

Use OpportunityFieldHistory when you need precise stage timing. Use snapshots when you need point-in-time state for fields that aren’t tracked in field history.

Downstream Analysis Patterns

Per-Stage Benchmarks

Join the stage duration model to your opportunity mart for per-stage analysis by segment:

SELECT
    opportunity__stage,
    account__industry,
    AVG(stage__days) AS avg_days_in_stage,
    PERCENTILE_CONT(stage__days, 0.5) OVER (
        PARTITION BY opportunity__stage, account__industry
    ) AS median_days_in_stage
FROM {{ ref('int__opportunity_stage_duration') }}
INNER JOIN {{ ref('mrt__sales__opportunity_enhanced') }}
    USING (opportunity__id)
GROUP BY 1, 2

This produces per-stage averages by segment — for example, average days in a given stage by industry.

Bottleneck Detection

Flag deals that are significantly above the average for their current stage:

WITH

stage_benchmarks AS (
    SELECT
        opportunity__stage,
        AVG(stage__days) AS avg_days,
        STDDEV(stage__days) AS stddev_days
    FROM {{ ref('int__opportunity_stage_duration') }}
    WHERE stage__exited_at IS NOT NULL  -- only completed stages
    GROUP BY 1
)

SELECT
    sd.opportunity__id,
    sd.opportunity__stage,
    sd.stage__days,
    sb.avg_days,
    CASE
        WHEN sd.stage__days > sb.avg_days + 2 * sb.stddev_days THEN 'stalled'
        WHEN sd.stage__days > sb.avg_days + sb.stddev_days THEN 'at_risk'
        ELSE 'on_track'
    END AS stage__health
FROM {{ ref('int__opportunity_stage_duration') }} AS sd
INNER JOIN stage_benchmarks AS sb
    ON sd.opportunity__stage = sb.opportunity__stage
WHERE sd.stage__exited_at IS NULL  -- current stage only

This output can feed a Slack alert or CRM dashboard flagging deals by stage health.

Win Rate: The Companion Metric

Stage duration analysis pairs with win rate for a complete sales performance view.

BigQuery’s SAFE_DIVIDE handles the division-by-zero edge case cleanly:

SAFE_DIVIDE(
    COUNTIF(opportunity__is_won),
    COUNTIF(opportunity__is_closed)
) AS owner__win_rate

Partition by opportunity__owner_id for per-rep win rates, or by account__industry for segment analysis. Combine with AVG(opportunity__amount) and the stage duration model for a unified view of velocity, win rate, and deal size.

Record Type Considerations

If your org has multiple opportunity record types (New Business, Renewal, Upsell), stage duration analysis should be segmented by record type. Renewal cycles have completely different stage sequences and timing benchmarks than new business deals. Mixing them in a single average produces a number that describes neither pipeline accurately.

Filter your stage duration model by record type, or include record_type_id as a dimension so downstream analysis can slice by it.

Practical Notes

Field history retention. Salesforce retains field history data for 18-24 months by default (varies by edition). If you need longer history, extract and warehouse it before Salesforce purges it. Once it’s in BigQuery, you keep it forever.

Multiple changes on the same day. OpportunityFieldHistory.CreatedDate includes time, so two stage changes on the same day are distinguishable. Use TIMESTAMP_DIFF instead of DATE_DIFF if intraday precision matters for your analysis.

Skipped stages. Deals don’t always follow a linear stage path. A deal might jump from “Prospecting” directly to “Negotiation,” skipping “Qualification” and “Proposal.” The model handles this correctly — it records the actual transitions, not the expected path. But your benchmarks should account for this when computing averages: a deal that skipped three stages has fewer stage-duration rows, not zero-day entries for the skipped stages.