HubSpot Deal Stage Modeling

Deal stage transitions in HubSpot do not live in DEAL_PROPERTY_HISTORY. Fivetran surfaces a dedicated DEAL_STAGE table that is purpose-built for this analysis. Using DEAL_PROPERTY_HISTORY for stage data produces incomplete results; it works only when not using Fivetran or when building directly from property history.

The DEAL_STAGE Table

Fivetran’s HubSpot connector populates a DEAL_STAGE table where each row represents a deal entering a stage. The key columns:

deal_id — the deal this transition belongs to
pipeline_stage_label — the human-readable stage name (“Proposal Sent”, “Contract Signed”)
date_stage_entered — when the deal entered this stage
date_stage_exited — when the deal left this stage (NULL if still in stage)
is_closed — whether the stage is a closing stage
label — the outcome label (more on this below)

The date_stage_exited NULL pattern is important. A deal currently in “Negotiation” will have date_stage_entered set and date_stage_exited as NULL. Use COALESCE(date_stage_exited, CURRENT_TIMESTAMP()) when calculating time-in-stage to handle open records correctly.

The is_closed vs label Confusion

is_closed is a boolean that marks whether a stage is a terminal stage — it’s TRUE for both “Closed Won” and “Closed Lost.” It tells you that a deal left the pipeline, but not which direction.

To distinguish won from lost, use the label column. The values depend on your HubSpot pipeline configuration, but typically: “Won”, “Lost”, “Closed Won”, “Closed Lost”, or custom labels you’ve defined. The is_closed column is good for filtering to closed deals; the label column is what you need for win/loss analysis.

-- Get closed deals with outcome
SELECT
    deal_id,
    pipeline_stage_label        AS deal_stage__final_stage,
    date_stage_entered          AS deal_stage__closed_at,
    label                       AS deal_stage__outcome,
    CASE
        WHEN LOWER(label) LIKE '%won%' THEN TRUE
        WHEN LOWER(label) LIKE '%lost%' THEN FALSE
        ELSE NULL
    END                         AS deal_stage__is_won
FROM {{ source('hubspot', 'deal_stage') }}
WHERE is_closed = TRUE

The LOWER(label) LIKE '%won%' approach is more robust than exact string matching because HubSpot portal admins customize stage labels. A client might have “Closed - Won” or “WON” instead of the default “Closed Won.” The flexible matching handles this without requiring constant configuration updates.

Time-in-Stage Analysis

The most common use case for DEAL_STAGE is analyzing how long deals spend in each stage and where they stall. Build an intermediate model that calculates stage duration:

-- int__deal_stage_duration.sql
SELECT
    deal_id,
    pipeline_stage_label                AS deal_stage__name,
    date_stage_entered                  AS deal_stage__entered_at,
    date_stage_exited                   AS deal_stage__exited_at,
    DATE_DIFF(
        COALESCE(date_stage_exited, CURRENT_TIMESTAMP()),
        date_stage_entered,
        DAY
    )                                   AS deal_stage__days_in_stage,
    is_closed                           AS deal_stage__is_closed,
    label                               AS deal_stage__outcome
FROM {{ source('hubspot', 'deal_stage') }}

The mart aggregation then gives you the pipeline velocity metrics sales leadership wants:

-- mrt__sales__pipeline_velocity.sql
SELECT
    deal_stage__name,
    COUNT(DISTINCT deal_id)                                     AS stage__deal_count,
    AVG(deal_stage__days_in_stage)                              AS stage__avg_days,
    APPROX_QUANTILES(deal_stage__days_in_stage, 100)[OFFSET(50)]
                                                                AS stage__median_days,
    APPROX_QUANTILES(deal_stage__days_in_stage, 100)[OFFSET(90)]
                                                                AS stage__p90_days
FROM {{ ref('int__deal_stage_duration') }}
WHERE NOT deal_stage__is_closed  -- exclude terminal stages
GROUP BY deal_stage__name
ORDER BY stage__median_days DESC

The median and 90th percentile together are more useful than the average. Averages for time-in-stage get distorted by deals that are genuinely stuck for months. The median tells you what’s normal; the 90th percentile tells you where the outlier problem is.

Using the dbt_hubspot Package

The dbt_hubspot package (v1.6.1) provides a hubspot__deal_stages model that wraps this table with the deal and pipeline context already joined in. Build on top of it rather than recreating the joins from scratch:

-- mrt__sales__deal_pipeline.sql
WITH

deal_stages AS (
    SELECT
        deal_id,
        pipeline_stage_label            AS deal_stage__name,
        date_stage_entered              AS deal_stage__entered_at,
        date_stage_exited               AS deal_stage__exited_at,
        DATE_DIFF(
            COALESCE(date_stage_exited, CURRENT_TIMESTAMP()),
            date_stage_entered,
            DAY
        )                               AS deal_stage__days_in_stage
    FROM {{ ref('hubspot__deal_stages') }}
)

SELECT
    deal_stage__name,
    COUNT(DISTINCT deal_id)             AS stage__deals,
    AVG(deal_stage__days_in_stage)      AS stage__avg_days,
    APPROX_QUANTILES(deal_stage__days_in_stage, 100)[OFFSET(50)]
                                        AS stage__median_days
FROM deal_stages
GROUP BY deal_stage__name

Conversion Rate by Stage

Beyond time-in-stage, deal stage data lets you calculate conversion rates — what percentage of deals that enter each stage progress to the next one:

-- Stage conversion funnel
WITH

stage_counts AS (
    SELECT
        deal_stage__name,
        COUNT(DISTINCT deal_id)         AS deals_entered
    FROM {{ ref('int__deal_stage_duration') }}
    GROUP BY deal_stage__name
),

closed_won AS (
    SELECT
        prior_stage.deal_stage__name,
        COUNT(DISTINCT prior_stage.deal_id) AS deals_won
    FROM {{ ref('int__deal_stage_duration') }} AS prior_stage
    INNER JOIN {{ ref('int__deal_stage_duration') }} AS terminal_stage
        ON prior_stage.deal_id = terminal_stage.deal_id
        AND terminal_stage.deal_stage__is_closed = TRUE
        AND LOWER(terminal_stage.deal_stage__outcome) LIKE '%won%'
    GROUP BY prior_stage.deal_stage__name
)

SELECT
    sc.deal_stage__name,
    sc.deals_entered,
    COALESCE(cw.deals_won, 0)           AS deals_won,
    SAFE_DIVIDE(
        COALESCE(cw.deals_won, 0),
        sc.deals_entered
    )                                   AS stage_to_close_rate
FROM stage_counts AS sc
LEFT JOIN closed_won AS cw
    ON sc.deal_stage__name = cw.deal_stage__name

This tells you: of all deals that passed through “Proposal Sent”, what fraction ultimately closed won? Compare across stages to find where your pipeline leaks.

Connecting Stages to Contacts

Deal stage data becomes more valuable when connected to the contacts involved. Because HubSpot uses many-to-many associations, you need to join through the contact-deal bridge to connect deal stage outcomes to the people involved:

-- Who was associated with won deals by stage?
SELECT
    ds.deal_stage__name,
    c.contact__lifecycle_stage,
    COUNT(DISTINCT ds.deal_id)          AS deals_won
FROM {{ ref('int__deal_stage_duration') }} AS ds
INNER JOIN {{ ref('int__contact_deal_mapped') }} AS cdm
    ON ds.deal_id = cdm.deal_id
INNER JOIN {{ ref('hubspot__contacts') }} AS c
    ON cdm.contact_id = c.contact_id
WHERE
    ds.deal_stage__is_closed = TRUE
    AND LOWER(ds.deal_stage__outcome) LIKE '%won%'
GROUP BY ds.deal_stage__name, c.contact__lifecycle_stage

For the full picture of HubSpot pipeline modeling, see the HubSpot to BigQuery guide. For lifecycle stage tracking on the contact side, see HubSpot Lifecycle Stages in the Warehouse.