Étapes du cycle de vie HubSpot dans l'entrepôt

Le modèle d’étapes du cycle de vie de HubSpot est inhabituellement adapté à l’analytique en entrepôt. La plupart du suivi des étapes CRM nécessite soit une table d’historique dédiée soit une reconstruction basée sur des snapshots pour savoir quand un contact a transitionné entre étapes. HubSpot fait quelque chose de plus simple : chaque contact obtient une propriété de timestamp dédiée pour chaque étape par laquelle il passe.

La progression standard est : Subscriber → Lead → Marketing Qualified Lead (MQL) → Sales Qualified Lead (SQL) → Opportunity → Customer → Evangelist. Quand un contact entre dans chaque étape, HubSpot estampille une propriété « Became a [Stage] Date » sur l’enregistrement du contact lui-même :

property_became_a_lead_date
property_became_a_marketing_qualified_lead_date
property_became_a_sales_qualified_lead_date
property_became_a_customer_date

Ce sont des timestamps de premier contact — ils enregistrent quand le contact a atteint cette étape pour la première fois, et ils ne sont pas écrasés si le contact est réaffecté d’une quelconque façon. Cela en fait des ancres fiables pour l’analyse de l’entonnoir.

La contrainte unidirectionnelle

Par défaut, HubSpot empêche la régression des étapes du cycle de vie. Une fois qu’un contact est marqué comme Customer, HubSpot ne permet pas de le ramener à Lead via les actions normales de l’interface. C’est une fonctionnalité pour l’intégrité des données, pas un bug.

Le résultat pratique : si un contact a un timestamp became_customer_at, cela signifie qu’il a genuinement atteint le statut de client à ce moment-là. Vous n’avez pas à vous inquiéter qu’une inversion d’étape invalide le timestamp. La date signifie ce qu’elle dit.

L’exception est les configurations d’étapes de cycle de vie personnalisées où les administrateurs ont désactivé cette contrainte. Si votre portail HubSpot a des paramètres d’étapes non standard, vérifiez que la règle unidirectionnelle est appliquée avant de traiter ces timestamps comme fiables.

Extraction des timestamps du cycle de vie dans le modèle de base

Mappez explicitement les propriétés d’étapes du cycle de vie dans votre modèle de base :

-- base__hubspot__contact.sql
SELECT
    id                                                          AS contact_id,
    property_email                                              AS contact__email,
    property_lifecyclestage                                     AS contact__lifecycle_stage,
    property_hs_lead_status                                     AS contact__lead_status,
    CAST(property_createdate AS TIMESTAMP)                      AS contact__created_at,
    CAST(property_became_a_lead_date AS TIMESTAMP)              AS contact__became_lead_at,
    CAST(property_became_a_marketing_qualified_lead_date
         AS TIMESTAMP)                                          AS contact__became_mql_at,
    CAST(property_became_a_sales_qualified_lead_date
         AS TIMESTAMP)                                          AS contact__became_sql_at,
    CAST(property_became_a_customer_date AS TIMESTAMP)          AS contact__became_customer_at,
    _fivetran_deleted                                           AS contact__is_deleted
FROM {{ source('hubspot', 'contact') }}
WHERE NOT COALESCE(_fivetran_deleted, FALSE)

C’est suffisant pour la plupart des analyses d’entonnoir. Les timestamps sont sur l’enregistrement du contact, pas enfouis dans une table d’historique. Pas de jointures nécessaires pour les métriques de cycle de vie basiques.

Si vous utilisez le package dbt_hubspot, configurez-les comme colonnes pass-through sur le modèle de contacts du package pour les rendre disponibles dans la sortie du package :

vars:
  hubspot__contact_pass_through_columns:
    - name: property_became_a_lead_date
      alias: contact__became_lead_at
    - name: property_became_a_marketing_qualified_lead_date
      alias: contact__became_mql_at
    - name: property_became_a_sales_qualified_lead_date
      alias: contact__became_sql_at
    - name: property_became_a_customer_date
      alias: contact__became_customer_at

Construction du mart de l’entonnoir du cycle de vie

Avec les timestamps du cycle de vie sur l’enregistrement du contact, le modèle d’entonnoir est une simple agrégation :

-- mrt__marketing__lifecycle_funnel.sql
SELECT
    COUNT(contact_id)                                           AS funnel__leads,
    COUNTIF(contact__became_mql_at IS NOT NULL)                 AS funnel__mqls,
    COUNTIF(contact__became_sql_at IS NOT NULL)                 AS funnel__sqls,
    COUNTIF(contact__became_customer_at IS NOT NULL)            AS funnel__customers,
    SAFE_DIVIDE(
        COUNTIF(contact__became_mql_at IS NOT NULL),
        COUNT(contact_id)
    )                                                           AS funnel__lead_to_mql_rate,
    SAFE_DIVIDE(
        COUNTIF(contact__became_sql_at IS NOT NULL),
        COUNTIF(contact__became_mql_at IS NOT NULL)
    )                                                           AS funnel__mql_to_sql_rate,
    SAFE_DIVIDE(
        COUNTIF(contact__became_customer_at IS NOT NULL),
        COUNTIF(contact__became_sql_at IS NOT NULL)
    )                                                           AS funnel__sql_to_customer_rate
FROM {{ ref('hubspot__contacts') }}
WHERE contact__became_lead_at IS NOT NULL

Ajoutez une dimension temporelle pour suivre l’évolution des taux de conversion mois après mois :

SELECT
    DATE_TRUNC(contact__became_lead_at, MONTH)                  AS cohort_month,
    COUNT(contact_id)                                           AS funnel__leads,
    COUNTIF(contact__became_mql_at IS NOT NULL)                 AS funnel__mqls,
    SAFE_DIVIDE(
        COUNTIF(contact__became_mql_at IS NOT NULL),
        COUNT(contact_id)
    )                                                           AS funnel__lead_to_mql_rate
FROM {{ ref('hubspot__contacts') }}
WHERE contact__became_lead_at IS NOT NULL
GROUP BY 1
ORDER BY 1

Cela regroupe les contacts par le mois où ils sont devenus lead pour la première fois et montre les taux de conversion pour chaque cohorte — utile pour suivre si la qualité des leads s’améliore ou se dégrade dans le temps.

Le problème des contacts fusionnés

Les contacts fusionnés sont la principale source d’anomalies dans les données de cycle de vie. Quand HubSpot fusionne deux enregistrements de contacts, le contact survivant hérite des propriétés des deux. Si le contact fusionné avait un became_a_customer_date d’il y a un an et que le became_a_lead_date du contact survivant est plus récent, vous vous retrouvez avec un contact qui est « devenu client » avant de « devenir lead ».

Ces séquences sont chronologiquement impossibles dans une progression réelle du cycle de vie. Ce sont presque toujours des artefacts de fusion.

Comment les détecter dans votre modèle de base ou une vérification de qualité des données :

-- Test dbt ou modèle de qualité pour signaler les artefacts de fusion
SELECT
    contact_id,
    contact__became_lead_at,
    contact__became_mql_at,
    contact__became_sql_at,
    contact__became_customer_at
FROM {{ ref('base__hubspot__contact') }}
WHERE
    -- Client avant lead
    (contact__became_customer_at < contact__became_lead_at)
    -- MQL avant lead
    OR (contact__became_mql_at < contact__became_lead_at)
    -- SQL avant MQL
    OR (contact__became_sql_at < contact__became_mql_at)
    -- Client avant SQL
    OR (contact__became_customer_at < contact__became_sql_at)

Que faire avec les enregistrements signalés dépend de vos objectifs d’analyse. Pour les comptages de volumes d’entonnoir, vous pouvez vouloir les exclure entièrement. Pour l’attribution des revenus, vous pouvez vouloir les conserver mais les exclure des calculs de durée d’étape (les jours de lead à MQL sont sans signification si les dates sont inversées). Documentez quelle que soit la décision que vous prenez — ces enregistrements resurgiront dans les conversations QA.

Le problème des artefacts de fusion n’est pas unique à HubSpot, mais le modèle de timestamp du cycle de vie le fait apparaître clairement. C’est en fait un signal utile : si vous voyez beaucoup de séquences inversées, cela suggère une activité active de fusion de contacts dans HubSpot qui peut valoir la peine d’être investiguée d’un point de vue d’hygiène des données.

Métriques de vélocité

Les timestamps du cycle de vie permettent des calculs de vélocité genuinement utiles pour les équipes commerciales et marketing :

SELECT
    contact_id,
    DATE_DIFF(contact__became_mql_at, contact__became_lead_at, DAY)     AS contact__days_lead_to_mql,
    DATE_DIFF(contact__became_sql_at, contact__became_mql_at, DAY)      AS contact__days_mql_to_sql,
    DATE_DIFF(contact__became_customer_at, contact__became_sql_at, DAY) AS contact__days_sql_to_customer
FROM {{ ref('hubspot__contacts') }}
WHERE
    contact__became_customer_at IS NOT NULL
    -- Exclure les artefacts de fusion
    AND contact__became_customer_at > contact__became_lead_at
    AND contact__became_sql_at > contact__became_mql_at

Ces métriques de vélocité appartiennent à un mart contact 360 ou comme métriques autonomes. La segmentation par canal d’acquisition, source de lead ou owner de deal révèle les différences de vélocité d’étape entre les segments.

Pour la connexion des données d’étapes du cycle de vie au suivi des deals, voir Modélisation des étapes de deal HubSpot. Pour le contexte complet du pipeline, voir le guide HubSpot vers BigQuery.