Les associations HubSpot comme tables de jonction

Le modèle de données HubSpot ne place pas les relations d’objets sur les objets eux-mêmes. Il n’y a pas de colonne company_id sur la table des contacts, pas de clé étrangère pointant d’un deal vers ses contacts associés. Chaque relation — contact vers société, contact vers deal, société vers deal, deal vers ticket — passe par une table de jonction dédiée.

Cela diffère de Salesforce, où un Contact dispose d’un champ AccountId pointant vers son Account. Dans HubSpot, joindre les contacts aux sociétés nécessite de passer par contact_company. Sans cela, les contacts associés à plusieurs sociétés sont entièrement manqués et les comptages de jointures sont incorrects.

L’inventaire des tables de jonction

Le connecteur HubSpot de Fivetran expose ces tables d’association, entre autres :

• contact_company — contacts vers sociétés (many-to-many)
• contact_deal — contacts vers deals (many-to-many)
• deal_company — deals vers sociétés (many-to-many)
• deal_ticket — deals vers tickets de support (many-to-many)
• engagement_contact — enregistrements d’engagement vers contacts

Chaque table dispose au minimum de : les deux IDs d’objet, une colonne label optionnelle, et des métadonnées comme _fivetran_synced. La colonne label est là où les choses deviennent intéressantes.

Labels d’association

Sur HubSpot Pro et Enterprise, les associations peuvent porter un label : « Decision Maker », « Billing Contact », « Primary Company », « Champion ». Ces labels sont définis dans les paramètres HubSpot et attachés au niveau de la relation, pas au niveau du contact. Un contact peut être « Decision Maker » sur un deal et « Billing Contact » sur un autre.

Les labels méritent d’être extraits. Ils encodent la structure du comité d’achat et les rôles des contacts d’une façon qui nécessiterait autrement des propriétés personnalisées. Une association contact_deal où label = 'Decision Maker' est analytiquement significative — ces contacts comptent davantage pour l’analyse de la vélocité des deals que les participants passifs.

Votre modèle de base pour les tables d’association doit préserver le label :

-- base__hubspot__contact_company.sql
SELECT
    contact_id,
    company_id,
    type_id                 AS association__type_id,
    label                   AS association__label,
    _fivetran_synced        AS association__synced_at
FROM {{ source('hubspot', 'contact_company') }}

Pas de filtre soft-delete nécessaire ici — ces tables tracent les relations, pas les enregistrements. Les associations d’un contact supprimé disparaissent quand l’enregistrement du contact est supprimé.

Le problème du fan-out

Quand vous joignez via des tables d’association, votre nombre de lignes se multiplie. Un contact associé à trois sociétés apparaît trois fois dans une jointure naïve sur contact_company. C’est un comportement correct pour le grain d’association, mais c’est presque jamais ce qu’un modèle mart veut.

Le symptôme : vous joignez les contacts aux sociétés et effectuez COUNT(DISTINCT contact_id) pour obtenir des comptes de contacts par société. Les résultats semblent incorrects. Les sociétés avec beaucoup de contacts partagés rapportent des chiffres gonflés parce que vous comptez au mauvais grain.

La solution dépend de la question à laquelle vous répondez.

Si vous avez besoin d’une ligne par contact avec du contexte de société, choisissez une association principale :

-- int__contact_primary_company.sql
SELECT
    contact_id,
    company_id,
    association__label
FROM {{ ref('base__hubspot__contact_company') }}
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY contact_id
    ORDER BY association__synced_at ASC
) = 1

Cela prend la société synchronisée le plus tôt comme « principale » — un proxy raisonnable pour la relation de société originale. Si votre équipe a établi une convention de label comme « Primary Company », filtrez sur celle-ci à la place :

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY contact_id
    ORDER BY
        CASE WHEN association__label = 'Primary Company' THEN 0 ELSE 1 END ASC,
        association__synced_at ASC
) = 1

Si vous avez besoin de métriques agrégées sur chaque société, agrégez depuis la table de jonction plutôt que de joindre :

-- int__company_contact_metrics.sql
SELECT
    company_id,
    COUNT(DISTINCT contact_id)                              AS company__contacts,
    COUNT(DISTINCT CASE
        WHEN association__label = 'Decision Maker'
        THEN contact_id
    END)                                                    AS company__decision_makers
FROM {{ ref('base__hubspot__contact_company') }}
GROUP BY company_id

Cela reste au grain de la société et gère correctement les contacts multi-sociétés. Le modèle intermédiaire expose ces métriques aux modèles mart qui en ont besoin.

Construire un modèle intermédiaire par type d’association

N’essayez pas de gérer tous les types d’association dans un seul modèle. Construisez un modèle intermédiaire dédié pour chaque pair :

• int__contact_company_mapped.sql — contact-vers-société avec résolution principale
• int__contact_deal_mapped.sql — paires contact-vers-deal avec labels
• int__company_deal_mapped.sql — relations société-vers-deal

Ces modèles intermédiaires deviennent le chemin de jointure canonique pour leur type d’association. Quand vos modèles mart ont besoin de connecter des contacts à des deals, ils référencent int__contact_deal_mapped, pas la table de jonction brute. Cela garde la logique de fan-out en un seul endroit.

-- int__contact_deal_mapped.sql
SELECT
    contact_id,
    deal_id,
    association__label,
    association__synced_at
FROM {{ ref('base__hubspot__contact_deal') }}

Le mart rejoint alors sur ceci :

-- mrt__sales__deal_contacts.sql
SELECT
    d.deal_id,
    d.deal__name,
    d.deal__amount,
    c.contact_id,
    c.contact__email,
    cdm.association__label
FROM {{ ref('hubspot__deals') }} AS d
INNER JOIN {{ ref('int__contact_deal_mapped') }} AS cdm
    ON d.deal_id = cdm.deal_id
LEFT JOIN {{ ref('hubspot__contacts') }} AS c
    ON cdm.contact_id = c.contact_id

Le grain ici est une ligne par paire deal-contact, ce qui est le bon grain pour un modèle deal-contacts. Toute agrégation se produit en aval ou dans un mart séparé.

La question de la société principale n’a pas de réponse parfaite

Le modèle d’association de HubSpot supporte genuinement les relations many-to-many, donc toute stratégie pour le réduire à une relation one-to-one implique une décision métier. Les trois approches que vous verrez en pratique :

La première association l’emporte. La première société à laquelle un contact a été lié est leur « principale ». Simple, déterministe, mais peut ne pas refléter la réalité si un contact change de société.

Sélection basée sur le label. Si votre équipe utilise un label « Primary Company » de façon cohérente, utilisez-le. Cela nécessite l’application de la convention de labeling dans HubSpot lui-même — si les commerciaux ne labellisent pas, cela se casse.

Synchronisation la plus récente. Prenez l’association synchronisée le plus récemment. Cela tend à refléter la relation de société « actuelle » mais est volatile si le timestamp de synchronisation n’est pas significatif.

Quelle que soit votre choix, documentez-le comme règle métier dans votre modèle intermédiaire. D’autres modèles mart s’appuieront sur cette hypothèse, et le choix doit être explicite et traçable.

Pour le contexte plus approfondi sur la façon dont cela diffère du modèle de clé étrangère de Salesforce, voir Salesforce vs HubSpot : Modèles de données. Pour le pipeline HubSpot complet, voir le guide HubSpot vers BigQuery.