Modélisation des étapes de deal HubSpot

Les transitions d’étapes de deal dans HubSpot ne se trouvent pas dans DEAL_PROPERTY_HISTORY. Fivetran expose une table DEAL_STAGE dédiée qui est conçue spécifiquement pour cette analyse. Utiliser DEAL_PROPERTY_HISTORY pour les données d’étapes produit des résultats incomplets ; cela ne fonctionne que si vous n’utilisez pas Fivetran ou si vous construisez directement depuis l’historique des propriétés.

La table DEAL_STAGE

Le connecteur HubSpot de Fivetran peuple une table DEAL_STAGE où chaque ligne représente un deal entrant dans une étape. Les colonnes clés :

deal_id — le deal auquel appartient cette transition
pipeline_stage_label — le nom d’étape lisible par un humain (« Proposal Sent », « Contract Signed »)
date_stage_entered — quand le deal est entré dans cette étape
date_stage_exited — quand le deal a quitté cette étape (NULL si toujours dans l’étape)
is_closed — si l’étape est une étape de clôture
label — le label de résultat (plus de détails ci-dessous)

Le pattern NULL de date_stage_exited est important. Un deal actuellement en « Negotiation » aura date_stage_entered défini et date_stage_exited à NULL. Utilisez COALESCE(date_stage_exited, CURRENT_TIMESTAMP()) lors du calcul du temps-par-étape pour gérer correctement les enregistrements ouverts.

La confusion is_closed vs label

is_closed est un booléen qui marque si une étape est une étape terminale — il est TRUE à la fois pour « Closed Won » et « Closed Lost ». Il vous dit qu’un deal a quitté le pipeline, mais pas dans quelle direction.

Pour distinguer gagné de perdu, utilisez la colonne label. Les valeurs dépendent de votre configuration de pipeline HubSpot, mais typiquement : « Won », « Lost », « Closed Won », « Closed Lost », ou des labels personnalisés que vous avez définis. La colonne is_closed est utile pour filtrer les deals clôturés ; la colonne label est ce dont vous avez besoin pour l’analyse win/loss.

-- Obtenir les deals clôturés avec le résultat
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

L’approche LOWER(label) LIKE '%won%' est plus robuste que la correspondance exacte de chaîne car les administrateurs du portail HubSpot personnalisent les labels d’étapes. Un client peut avoir « Closed - Won » ou « WON » au lieu du « Closed Won » par défaut. La correspondance flexible gère cela sans nécessiter des mises à jour constantes de configuration.

Analyse du temps-par-étape

Le cas d’usage le plus courant pour DEAL_STAGE est l’analyse du temps que les deals passent dans chaque étape et où ils stagnent. Construisez un modèle intermédiaire qui calcule la durée d’étape :

-- 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') }}

L’agrégation du mart vous donne ensuite les métriques de vélocité du pipeline que la direction commerciale veut :

-- 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  -- exclure les étapes terminales
GROUP BY deal_stage__name
ORDER BY stage__median_days DESC

La médiane et le 90e percentile ensemble sont plus utiles que la moyenne. Les moyennes pour le temps-par-étape sont déformées par les deals genuinement bloqués depuis des mois. La médiane vous dit ce qui est normal ; le 90e percentile vous dit où se trouve le problème des valeurs atypiques.

Utiliser le package dbt_hubspot

Le package dbt_hubspot (v1.6.1) fournit un modèle hubspot__deal_stages qui encapsule cette table avec le contexte du deal et du pipeline déjà joint. Construisez par-dessus plutôt que de recréer les jointures depuis zéro :

-- 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

Taux de conversion par étape

Au-delà du temps-par-étape, les données d’étapes de deal vous permettent de calculer les taux de conversion — quel pourcentage de deals qui entrent dans chaque étape progresse vers la suivante :

-- Entonnoir de conversion par étape
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

Cela vous dit : parmi tous les deals qui sont passés par « Proposal Sent », quelle fraction a finalement été gagnée ? Comparez entre les étapes pour trouver où votre pipeline fuit.

Connecter les étapes aux contacts

Les données d’étapes de deal deviennent plus précieuses quand on les connecte aux contacts impliqués. Parce que HubSpot utilise des associations many-to-many, vous devez joindre via la table de jonction contact-deal pour connecter les résultats des étapes de deal aux personnes impliquées :

-- Qui était associé aux deals gagnés par étape ?
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

Pour le tableau complet de la modélisation du pipeline HubSpot, voir le guide HubSpot vers BigQuery. Pour le suivi des étapes du cycle de vie côté contact, voir Étapes du cycle de vie HubSpot dans l’entrepôt.