Interroger les données HubSpot dans Snowflake
Dernière mise à jour: mai 31, 2024
Disponible avec le ou les abonnements suivants, sauf mention contraire :
Operations Hub Enterprise |
Après avoir connecté Snowflake à votre compte HubSpot, vous pouvez utiliser le partage de données de Snowflake pour interroger les données HubSpot depuis votre compte Snowflake.
Le décalage des données et la région de votre compte Snowflake
Vous aurez accès au schéma V2_LIVE qui est mis à jour toutes les 15 minutes, et au schéma V2_DAILY qui est mis à jour tous les jours.
Veuillez noter : les vues suivantes du schéma V2_LIVE ne sont mises à jour que quotidiennement : association_definitions, owners, pipelines, et pipeline_stages.
Configurer votre environnement Snowflake
Pour interroger vos données HubSpot dans Snowflake, vous devez sélectionner l'entrepôt, la base de données et le schéma à partir desquels vous souhaitez effectuer l'interrogation. Vous pouvez soit les définir au niveau de la feuille de calcul pour les inclure automatiquement dans vos requêtes, soit les ajouter manuellement.
Pour sélectionner votre entrepôt, votre base de données et votre schéma au niveau de la feuille de calcul :
- Dans la ligne supérieure de la feuille de calcul, cliquez sur la barre d'actions de la feuille de calcul.
- Dans la fenêtre contextuelle :
- Cliquez sur le menu déroulant Entrepôt, puis sélectionnez votre entrepôt.
- Cliquez sur le menu déroulant Base de données, puis sélectionnez votre base de données HubSpot.
- Cliquez sur le menu déroulant Schéma, puis sélectionnez le schéma que vous souhaitez utiliser:
- V2_DAILY : le schéma quotidien pour interroger les données basées sur les tables. Les données de ce schéma sont mises à jour une fois par jour. L'utilisation de ce schéma permet d'obtenir des requêtes plus rapides. Elle est recommandée pour les requêtes effectuées directement dans Snowflake.
- V2_LIVE : le schéma live basé sur les Secure Views des données HubSpot. Les données de ce schéma sont mises à jour toutes les 15 minutes au maximum. Si vous interrogez un grand ensemble de données, les requêtes peuvent être lentes. Dans ce cas, il est recommandé de copier les données dans votre propre tableau d'abord via un processus ETL, puis d'interroger ce tableau.
Une fois votre environnement configuré, vous pouvez effectuer des requêtes sur vos données HubSpot sans avoir besoin d'ajouter manuellement votre base de données ou votre type de schéma aux requêtes.
Pour ajouter manuellement votre base de données et votre type de schéma aux requêtes, ajoutez le nom de la base de données suivi du schéma au début du champ FROM
au début du champ Par exemple :
SELECT objectTypeId, property_createddate
FROM hubspot_share_name.V2_LIVE.objects_deals
LIMIT 1
Présentation des données
Les requêtes sont construites en utilisant SQL, et peuvent être utilisées pour récupérer des données HubSpot telles que :
- Objets: objets standard et personnalisés, tels que les contacts, les entreprises et les produits.
- Enregistrements: enregistrements individuels, tels qu'un contact individuel ou une entreprise.
- Associations: tous les types d'associations disponibles et les enregistrements actuellement associés.
- E-mails: données d'événements pour les événements HubSpot standard, tels que les ouvertures d'e-mails, et les événements comportementaux personnalisés.
- Listes: Listes de contacts et d'entreprisesHubSpot.
- Propriétaires : utilisateurs de votre compte HubSpot, en relation avec les fiches d'informations dont ils sont les propriétaires.
- Pipelines : pipelines de transactions et de tickets, y compris les phases du pipeline.
- Propriétés: propriétés et leurs valeurs sur les enregistrements CRM, y compris l'historique des propriétés.
Découvrez-en davantage sur le modèle de données CRM de HubSpot.
Remarque : . Au fur et à mesure de l'évolution du partage des données, nous introduirons des changements, dont certains seront radicaux. Il est fortement recommandé de s'abonner au journal des changements des développeurs de HubSpot. Ces modifications seront toujours publiées dans le journal des modifications avant toute mise à jour.
Limiter et ordonner les données renvoyées
Lorsque vous créez des requêtes, vous pouvez contrôler la quantité de données renvoyées, ainsi que l'ordre dans lequel elles sont renvoyées, en incluant LIMIT
et ORDER BY
dans votre requête. Par exemple, pour interroger tous les contacts avec leurs propriétés, mais ne renvoyer que trois résultats et les classer selon l'ID de contact, vous pouvez construire votre requête comme suit :
SELECT objectTypeId, updatedAt, ingestedAt
FROM objects_deals
ORDER BY objectId
LIMIT 10;
Types de données de colonne
Lorsque vous interrogez des données dans Snowflake, les colonnes qui sont renvoyées dépendent des données que vous interrogez. Vous pouvez afficher les colonnes disponibles pour un tableau ou une vue en question dans Snowflake en sélectionnant la vue dans la barre latérale gauche.
Remarque :pour le reste de ce document, nous utilisons le terme objet de base de données lorsque le contexte s'applique à la fois aux tables et aux vues.
Pour obtenir une colonne spécifique, incluez cette colonne dans le champ SELECT
de votre requête. Par exemple, la requête suivante ne renverra que les éléments suivants OBJECTID
et UpdatedAt
:
SELECTobjectId, updatedAt
FROM objects_contacts
Vous trouverez ci-dessous des définitions générales des colonnes qu'une requête peut renvoyer. Pour d'autres définitions non incluses ci-dessous, consultez les définitions des vues individuelles dans l'article ci-dessus.
- OBJECTTYPEID : une valeur VARCHAR qui représente le type d'un objet. Par exemple, 0-1 représente l'objet Contacts, 0-2 représente l'objet Entreprises et 2-1232 peut représenter un objet personnalisé dans votre compte. Dans les listes, il s'agit du type de liste (par exemple, contact ou entreprise). Vous pouvez interroger tous les ID d'objet et d'événement de votre compte en utilisant la vue object_and_event_type_definitions.
- OBJECTID : un nombre BIGINT qui représente un seul enregistrement d'un type d'objet donné dans votre compte HubSpot. Pour identifier une fiche d'informations, vous devez utiliser à la fois l'ID du type d'objet et l'ID de l'objet.
- INGESTEDAT : l'heure à laquelle une ligne de données a été ajoutée à Snowflake, représentée par une valeur TIMESTAMP_NTZ. Cela n'a aucun lien avec l'horodatage logique d'une opération de création ou de mise à jour dans HubSpot.
- En raison du traitement interne de HubSpot, l'horodatage d'ingestion d'une ligne peut être mis à jour même si aucune autre colonne de la ligne n'a changé.
- Lors de la création de processus ETL qui copient des données récemment modifiées, il est recommandé de baser vos requêtes sur l'horodatage d'ingestion. En outre, si votre flux copie les données quotidiennement, il est recommandé de copier les données des deux jours précédents pour tenir compte des délais.
- CREATEDAT et UPDATEDAT : l'horodatage logique de la création ou de la mise à jour des données dans HubSpot, représenté par une valeur TIMESTAMP_NTZ . Ces horodatages correspondront aux horodatages de HubSpot. Par exemple, l'heure affichée dans HubSpot pour la date de création d'une fiche d'informations sera la même que l'horodatage de la colonne CREATEDAT dans Snowflake. Elles ne sont pas directement liées à l'horodatage d'ingestion, et il n'est pas recommandé de baser les processus ETL sur ces propriétés.
- COMBINEDASSOCIATIONTYPE : une valeur VARCHAR qui identifie de manière unique une définition d'association.
Transformation des types de données
Il arrive que des données soient renvoyées par une requête avec des colonnes VARCHAR alors qu'il serait souhaitable qu'elles soient d'un autre type. Dans des cas comme celui-ci, il est recommandé de convertir le type de données VARCHAR dans les types souhaités en utilisant les fonctions Snowflake. Par exemple, les propriétés d'un enregistrement d'objet sont stockées sous le type de données VARCHAR dans la plupart des objets de base de données, mais elles contiennent parfois des valeurs numériques et des dates. Des fonctions telles que :
try_to_number, try_to_timestamp_ntz peuvent être utilisées pour convertir ces données VARCHAR en types NUMBER et DATETIME respectivement :
SELECT objectid, value AS amount_str, try_to_number(value) AS amountFROM object_properties
WHERE objecttypeid='0-3' AND name='amount'
LIMIT 1
Requêtes d'objets
Découvrez ci-dessous comment interroger des données à partir d'objets et de fiches d'informations HubSpot spécifiques ainsi que les données qui sont renvoyées.
Les données d'objets de différents types sont organisées et disponibles de deux manières :
- Individuel : Chaque type d'objet est stocké dans sa propre vue et ne contient que des enregistrements du même type d'objet, par exemple, objects_contacts ne contient que des enregistrements de contacts. Ainsi, il n'est pas nécessaire de spécifier un objecttypeid lors de l'interrogation. Ces vues sont plus faciles à interroger car vous n'avez pas besoin de connaître les ObjectTypeIds. Ils renvoient également des données dans un format plus exploitable, chaque ligne correspondant à un objet et à la version actuelle de ses propriétés. Tant que les requêtes ne sont pas lentes, il est conseillé d'utiliser ces vues pour récupérer les données des objets. Pour en savoir plus, consultez les vues d'Objects_X ci-dessous.
- Combiné : Tous les enregistrements de tous les types d'objets sont combinés en un seul objet de base de données. Ainsi, lors de l'interrogation, vous devez spécifier un filtre comme "WHERE objectTypeId='0-1'. Par exemple, vous pouvez utiliser les objecttypeids suivants lors de vos requêtes :
Nom de l'objet | ObjectTypeId |
Contacts | 0-1 |
Entreprises | 0-2 |
Lignes de transaction | 0-8 |
Un ID sera attribué aux objets personnalisés au moment de leur création. Ceux-ci seront identifiés comme 2-unique_ID. Par exemple, 2-12345. Découvrez-en davantage sur les définitions des types d'objets et d'événements.
Ces objets de base de données conviennent généralement si vous souhaitez obtenir de meilleures performances de requête que celles obtenues avec les vues objets_x. Voir plus de détails dans object_with_object_properties, object_properties, objects et object_properties_history ci-dessous.
objets_x vues
Dans cet objet de base de données, chaque type d'objet disponible, y compris les objets personnalisés, possède sa propre vue spécifique, chaque vue ne contenant que les enregistrements des objets de ce type spécifique. Ces vues sont nommées selon le format OBJECTS_<object type name> où <object type name> est le libellé de la forme plurielle dutype d'objet (si le type d'objet n'a pas de forme plurielle, <object type name> sera simplement le libellé de la forme singulière du nom du type d'objet concaténé avec "S").
Les requêtes adressées à cette vue renvoient une ligne pour chaque enregistrement d'un objet, chaque propriété de l'objet étant disponible dans une colonne distincte.
Vous trouverez ci-dessous la liste complète des types d'objets HubSpot (c'est-à-dire non personnalisés) disponibles, une brève description des données qu'ils représentent et les vues correspondantes :
Type d'objet HubSpot |
ObjectTypeId |
Description |
0-1 |
Informations sur les personnes qui interagissent avec votre entreprise |
|
0-2 |
Informations sur des entreprises ou des organisations individuelles |
|
0-3 |
Détails sur les opportunités de revenus avec un contact ou une entreprise. |
|
0-4 |
Stocke les données des actions CRM, y compris les notes, les tâches, les e-mails, les réunions et les appels. |
|
0-5 |
Représenter les demandes d'aide ou de soutien des clients. |
|
0-14 |
Utilisé pour partager des informations sur les prix avec des acheteurs potentiels. |
|
0-15 |
Détails des soumissions individuelles pour un formulaire HubSpot. |
|
0-8 |
Représentent un sous-ensemble de produits vendus dans une transaction. Lorsqu'un produit est associé à une transaction, il devient une ligne de produit. |
|
0-11 |
Détails des messages entrants provenant de plusieurs canaux. |
|
0 à 25 |
Détails de vos pages de destination |
|
0-27 |
Stocke des informations sur les listes de tâches |
|
0-28 |
Utilisé pour recueillir des informations sur vos visiteurs et vos contacts |
|
0-29 |
Détails sur les e-mails du Marketing Hub |
|
AD_ACCOUNT |
0-30 |
Stocke des informations sur vos comptes publicitaires sur Linkedin, Facebook et Google |
0-31 |
Détails sur vos campagnes publicitaires. Une campagne publicitaire peut contenir une ou plusieurs publicités |
|
AD_GROUP |
0-32 |
Regroupement logique des annonces au sein d'une campagne publicitaire |
PUBLICITÉ |
0-33 |
Détails sur les annonces individuelles |
0-35 |
Informations sur les actifs et le contenu marketing connexes, afin que vous puissiez facilement mesurer l'efficacité de vos efforts de marketing collectifs. |
|
SITE_PAGE |
0-38 |
Données sur les pages individuelles de vos sites web |
0-39 |
Données sur les articles de blog |
|
OBJECT LISTE |
0-45 |
Informations sur les regroupements d'enregistrements d'objets en fonction de leurs propriétés ou de leurs activités |
0-48 |
Appels effectués par d'autres enregistrements CRM, par exemple les contacts |
|
FACTURE |
0-53 |
Gérer et synchroniser les factures avec les systèmes comptables externes |
0-57 |
Informations sur les ressources média importées dans HubSpot |
|
0-58 |
Une séquence est une série de modèles d'e-mails ciblés et programmés pour entretenir les contacts au fil du temps |
|
0-72 |
La division des transactions permet de partager le crédit des transactions entre plusieurs utilisateurs |
|
0-83 |
Les documents de vente constituent une bibliothèque de contenu que toute votre équipe peut télécharger et partager avec vos contacts. |
|
0-19 |
Stocke les informations soumises à une enquête de satisfaction. |
|
0-69 |
Les abonnements contiennent les détails des paiements récurrents |
|
0-101 |
Contient des données sur les fonds collectés auprès des clients |
Chaque objet de la base de données renvoie les colonnes suivantes :
Nom de la colonne |
Type |
Description |
OBJECTTYPEID |
Varchar |
L'ID du type d'objet (par exemple, les contacts sont 0-1). |
OBJECTID |
Nombre |
L'ID de la fiche d'informations (par exemple, une fiche d'informations de contact avec l'ID 123752). |
UPDATEDAT |
Horodatage |
La date et l'heure de la dernière mise à jour de cet objet dans HubSpot. |
INGESTEDAT |
Horodatage |
La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
PROPERTY_* |
Varchar |
Pour chaque propriété d'objet de type date ou nombre (comme spécifié par property_definitions), il y aura deux colonnes :
|
Exemple 1
Récupérez le prénom, le nom et l'étape du cycle de vie de tous les contacts.
Requête :
SELECT objectId, property_firstname AS firstname, property_lastname AS lastname,property_lifecyclestage AS lifecyclestageFROM objects_contact
Les retours :
OBJECTID |
FIRSTNAME |
LASTNAME |
LIFECYCLESTAGE |
54325098 |
Hobbes |
Baron |
lead |
96805401 |
Milo |
Gold |
lead |
6922028 |
Henry |
Riley |
lead |
Exemple 2
Récupérez le nom et le stade de l'opération pour un maximum de trois opérations.
Requête :
SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmountFROM objects_deals
LIMIT 3
Renvoie :
OBJECTID |
NOM_DE_LA_PROPRIÉTÉ |
PROPRIÉTÉ_DEALSTAGE |
PROPRIÉTÉ_MONTANT DU MARCHÉ |
38960439 |
Nom de l'opération 1 |
ferméwon |
34199 |
321740651 |
Test Deal 123 |
14845019 |
8383663 |
98392101 |
Canopée Pop-Up |
ferméwon |
9309007 |
Exemple 3
Récupérez le nom de l'opération et l'étape des trois premières opérations par montant.
Requête :
Les données relatives au montant de l'opération étant disponibles sous la forme d'un type de données numérique, elles peuvent être utilisées pour le classement sans qu'il soit nécessaire d'effectuer une conversion ou un moulage.
SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmountFROM objects_deals
ORDER BY property_amount DESC
LIMIT 3
Renvoie :
OBJECTID |
NOM_DE_LA_PROPRIÉTÉ |
PROPRIÉTÉ_DEALSTAGE |
PROPRIÉTÉ_MONTANT DU MARCHÉ |
98392101 |
Canopée Pop-Up |
ferméwon |
9309007 |
321740651 |
Test Deal 123 |
14845019 |
8383663 |
38960439 |
Nom de l'opération 1 |
ferméwon |
34199 |
object_with_object_properties
Cet objet de base de données contient les données de vos enregistrements HubSpot. Les requêtes sur cet objet de base de données renvoient une ligne pour chaque enregistrement d'un type d'objet (par exemple, tous les enregistrements de contact du type d'objet Contacts). Toutes les propriétés sont stockées dans la colonne Propriétés, qui est une colonne Snowflake Variant / JSON.
Cette vue peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | L'ID du type d'objet (par exemple, contacts est 0-1 ). |
OBJECTID |
Nombre | L'ID de l'enregistrement (par exemple, un enregistrement de contact avec un ID de 123752 ). |
PROPERTIES |
Variante | Les valeurs de propriété de la fiche d'informations. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour de la fiche d'informations dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Remarque : Le 25 janvier 2022, la colonne timestamp de la table et de la vue object_with_object_properties, qui représente la date et l'heure de la dernière mise à jour de l'enregistrement de l'objet dans HubSpot, sera mise à jour. Ceci afin de s'aligner sur la façon dont les données de type date-heure sont représentées dans lereste du partage de données .
Comme il s'agit d'un changement de rupture, la nouvelle colonne, updatedat, a été ajoutée à la table et à la vue. Pendant les 90 prochains jours, ces deux nouvelles colonnes coexisteront avec timestamp, les colonnes qu'elles cherchent à remplacer. Cela vous donne l'occasion de passer à l'utilisation des nouvelles colonnes.
Après 90 jours, le 25 janvier 2022, l'horodatage de la colonne sera supprimé de la table et de la vue. Après cette date, toute tentative d'interroger la table ou la vue object_with_object_properties en utilisant les colonnes timestamp échouera.
Exemple 1
Récupérez tous les contacts et leurs propriétés.
Requête :
SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
Renvoie :
OBJECTTYPEID | OBJECTID | PROPERTIES |
0-1 | 38960439 | {"createdate": "1504656970152"...} |
0-1 | 321740651 | {"createdate" : "1590183081949"...} |
0-1 | 98392101 | {"createdate" : "1533566477279"...} |
... | ... | ... |
Exemple 2
Récupérer le nom de l'affaire et l'étape de trois affaires au maximum.
Requête :
SELECTobjectId,
properties:dealname::string AS dealname,
properties:dealstage::string AS dealstage,
properties:amount::number AS dealamount
FROM object_with_object_properties
WHERE objectTypeId = '0-3'
LIMIT 3;
Renvoie :
OBJECTID | DEALNAME | DEALSTAGE | DEALAMOUNT |
38960439 | Nom de la transaction 1 | fermé-perdu | 7488939 |
321740651 | Nom de la transaction 1 | ferméwon | 9292029 |
98392101 | Nom de la transaction 1 | fermé-perdu | 62626528 |
objects
Cet objet de base de données est un sous-ensemble des données de l'objet de base de données object_with_object_properties. Chaque ligne retournée renvoie l'ID d'objet pour chaque fiche d'informations. Cet objet de base de données ne renvoie que les enregistrements existants. Lorsqu'un enregistrement est supprimé dans HubSpot, il est supprimé de cet objet de base de données. Cet objet de base de données est un sous-ensemble des données de la table object_with_object_properties. Chaque ligne retournée renvoie l'ID d'objet pour chaque fiche d'informations. Cette vue renvoie uniquement les fiches d'informations existantes. Lorsqu'une fiche d'informations est supprimée dans HubSpot, elle est supprimée de cette vue.
Cet objet de base de données peut être utile si votre compte contient une grande quantité de données, ce qui entraîne une lenteur des vues object_with_object_properties et object_x. L'utilisation de cet objet de base de données avec l'objet de base de données object_properties permet de répliquer les données dans object_with_object_properties, tout en gardant le contrôle sur les propriétés qui sont retournées.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | L'ID du type d'objet (par exemple, contacts est 0-1 ). |
OBJECTID |
Nombre | L'ID de l'enregistrement (par exemple, un enregistrement de contact avec un ID de 123752 ). |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. Découvrez-en davantage sur les types de données des colonnes. |
Exemple 1
Récupérez toutes les fiches d'informations de contact.
Requête :
SELECT objectTypeId, objectId
FROM objects
WHERE objectTypeId = '0-1'
Renvoie :
OBJECTTYPEID | OBJECTID |
0-1 | 36721864 |
0-1 | 103580363 |
0-1 | 56047670 |
... | ... |
object_properties
Cet objet de base de données est un sous-ensemble de l'objet de base de données object_with_object_properties. Chaque ligne contient la valeur actuelle de la propriété définie sur un enregistrement donné. Par exemple, un contact aura une ligne pour sa propriété Prénom et une ligne pour sa propriété Nom. Si un enregistrement n'a pas de valeur pour une propriété, la ligne apparaîtra avec une chaîne vide ou n'apparaîtra pas du tout.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | L'ID du type d'objet (par exemple, contacts est 0-1 ). |
OBJECTID |
Nombre | L'ID de l'enregistrement (par exemple, un enregistrement de contact avec un ID de 123752 ). |
NAME |
Varchar | Le nom de la propriété. |
VALUE |
Horodatage | La valeur de la propriété. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour de la fiche d'informations dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez les propriétés du prénom, du nom et de la ville pour tous les contacts.
Requête :
SELECT objectTypeId, objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;
Renvoie :
OBJECTTYPEID | OBJECTID | NAME | VALUE |
0-1 | 10401 | firstname | Brian |
0-1 | 10401 | city | Dublin |
0-1 | 10401 | lastname | Gallagher |
0-1 | 23451 | firstname | Fernando |
0-1 | 23451 | lastname | Fierro |
0-1 | 25751 | firstname | Charles |
... | ... | ... | ... |
Exemple 2
Récupérez le prénom, le nom et l'étape du cycle de vie de tous les contacts, et combinez-les en une seule ligne par contact. Cet exemple utilise la syntaxe SQL PIVOT.
Requête :
SELECT objectId, firstname, lastname, lifecyclestage
FROM
(
SELECT objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'lifecyclestage')
)
PIVOT(MAX(value) FOR name IN ('firstname', 'lastname', 'lifecyclestage')) AS p(objectId, firstname, lastname, lifecyclestage);
Renvoie :
OBJECTTYPEID | OBJECTID | FIRSTNAME | LASTNAME | LIFECYCLESTAGE |
0-1 | 54325098 | Hobbes | Baron | lead |
0-1 | 96805401 | Milo | Gold | lead |
0-1 | 6922028 | Henry | Riley | lead |
... | ... | ... | ... | ... |
object_properties_history
Cet objet de base de données contient les valeurs historiques des propriétés. Pour les contacts, elle contient les 45 valeurs les plus récentes. Pour tous les autres objets, elle contient les 20 valeurs les plus récentes. Elle affiche une ligne pour chaque valeur de propriété historique. La valeur actuelle d'une propriété est déterminée par l'horodatage UPDATEDAT le plus récent dans l'historique de la propriété.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | L'ID du type d'objet (par exemple, contacts est 0-1 ). |
OBJECTID |
Nombre | L'ID de l'enregistrement (par exemple, un enregistrement de contact avec un ID de 123752 ). |
NAME |
Varchar | Le nom de la propriété. |
VALUE |
Horodatage | La valeur de la propriété. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour des données dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez l'historique d'une seule propriété pour un seul contact.
Requête :
SELECT objectId, name, value, updatedAt
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 136493192
AND name = 'hs_predictivescoringtier'
ORDER BY UPDATEDAT DESC;
Renvoie :
OBJECTID | NAME | VALUE | UPDATEDAT |
136493192 |
hs_predictivescoringtier |
tier_1 |
2022-06-22 22:45:05.931 |
136493192 |
hs_predictivescoringtier |
tier_2 |
2021-09-18 08:20:56.622 |
136493192 |
hs_predictivescoringtier |
tier_3 |
2019-11-31 18:20:22.851 |
... |
... |
... |
... |
Exemple 2
Récupérez le nom actuel de tous les contacts qui ont eu une phase du cycle de vie Abonné.
Requête :
SELECT
objectId,
properties:firstname::string AS firstname,
properties:lastname::string AS lastname
FROM object_with_object_properties
WHERE object_with_object_properties.objectTypeId = '0-1'
AND objectId IN (
SELECT objectId
FROM object_properties_history
WHERE
object_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);
Renvoie :
OBJECTID | FIRSTNAME | LASTNAME |
54325098 | Hobbes | Baron |
96805401 | Milo | Gold |
6922028 | Henry | Riley |
... | ... | ... |
Requêtes d'associations
Comme les données relatives aux objets, les données relatives aux associations sont organisées de deux manières :
- Individuel : Chaque type d'association est stocké dans sa propre vue et ne contient que les enregistrements de ce type. Par exemple, les associations_contacts_to_deals ne contiennent que les enregistrements d'association entre les objets contact et deal. Il n'est donc pas nécessaire de spécifier un numéro d'association lors de l'interrogation. Ces vues sont assez faciles à interroger car il n'est pas nécessaire de connaître le modèle de données du CRM pour les utiliser. Ils renvoient également une description enrichie de l'enregistrement de l'association entre deux objets qui peut être facilement utilisée dans les jointures avec les vues objets_x. Tant que les requêtes ne sont pas lentes, il est conseillé d'utiliser ces vues pour récupérer les données d'association. Pour en savoir plus, consultez les vues d'associations_x_to_y ci-dessous.
- Combiné : Tous les enregistrements de tous les types d'association sont combinés en un seul objet de base de données. Ainsi, lors de l'interrogation, vous devez spécifier un filtre comme "WHERE combinedassociationtypeid='0-32'". Pour en savoir plus, consultez les sections associations et associations_definitions ci-dessous.
associations_x_to_y views
Chaque type d'association a sa propre vue, étiquetée associations_<association type name>, par exemple associations_contacts_to_deals. Chaque vue est un sous-ensemble des données de l'objet de base de données des associations et ne contient que les enregistrements d'un combinedassociationtypeid spécifique.
Chaque objet de la base de données renvoie les colonnes suivantes :
Nom de la colonne |
Type |
Description |
COMBINEDASSOCIATIONTYPEID |
Varchar |
L'identifiant unique de la définition d'association. |
CATÉGORIE D'ASSOCIATION |
Varchar |
La source du type d'association. Un de : HUBSPOT_DEFINED, USER_DEFINED, INTEGRATOR_DEFINED |
ASSOCIATIONTYPEID |
Varchar |
Un identifiant unique d'une définition d'association au sein d'une catégorie d'association spécifique, c'est-à-dire que deux définitions dans une catégorie d'association n'auront pas le même associationtypeid. |
FROMOBJECTTYPEID |
Varchar |
L'objecttypeid qui est la source de la définition de l'association. |
À PARTIR DU TYPE D'OBJET |
Varchar |
Le nom du type d'objet qui est la source de la définition de l'association. |
TOOBJECTTYPEID |
Varchar |
L'objecttypeid qui est la destination de la définition d'association. |
TOOBJECTTYPE |
Varchar |
Le nom du type d'objet qui est la destination de la définition d'association. |
NOM |
Varchar |
Le nom de la définition d'association. |
LABEL |
Varchar |
La description de la définition de l'association. |
ISMAINASSOCIATIONDEFINITION |
Booléen |
Indique si la définition d'association est le type principal. Si elle est vraie, la définition est celle que HubSpot affiche sur les enregistrements associés |
[FROMOBJECTTYPE_OBJECTID]. Par exemple, pour la vue ASSOCIATIONS_CONTACTS_TO_DEALS, ce sera CONTACT_OBJECTID |
Nombre |
L'ID d'objet de l'objet source. |
[TOOBJECTTYPE_OBJECTID] Par exemple, pour la vue ASSOCIATIONS_CONTACTS_TO_DEALS, ce sera DEAL_OBJECTID |
Nombre |
L'ID d'objet de l'objet de destination. |
INGESTEDAT |
Horodatage |
La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérer tous les contrats associés à un contact spécifique.
Requête :
SELECT
contact_objectId, deal_objectId
FROM associations_contacts_to_deals
WHERE contactId = 6626541373
Les retours :
CONTACT_OBJECTID |
DEAL_OBJECTID |
6626541373 |
233620335 |
6626541373 |
90253678 |
Exemple 2
Récupérez tous les contacts basés dans le fuseau horaire de Bogota et leurs transactions.
SELECT contacts.property_firstname contact_firstname,
contacts.property_lastname contact_lastname,
contacts.property_email contact_email,
deals.property_dealtype deal_type, deals.property_amount deal_amount
FROM objects_contacts contacts
JOIN associations_contacts_to_deals assoc on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
where contacts.property_hs_timezone='america_slash_bogota'
CONTACT_FIRSTNAME |
CONTACT_LASTNAME |
CONTACT_EMAIL |
DEAL_TYPE |
DEAL_AMOUNT |
David |
Magalhães |
dmagalhaes@example.com |
nouvelle entreprise |
74848 |
Fernanda |
Rodrigues |
fmoreira@example.com |
nouvelle entreprise |
253530 |
Exemple 3
Récupérez tous les contacts et la somme totale du montant de l'affaire conclue pour chaque contact.
SELECT SUM(deals.property_hs_closed_amount) AS
total_closed_amount, contacts.property_email contact_email
FROM objects_contacts contacts
JOIN associations_contacts_to_deals assoc on
assoc.contact_objectid=contacts.objectid
JOIN objects_deals deals on deals.objectid=assoc.deal_objectid
GROUP BY contact_email
ORDER BY total_closed_amount DESC
Renvoie :
MONTANT TOTAL_FERMÉ |
CONTACT_EMAIL |
98474793 |
johndoe@riverview.com |
849488 |
sallymae@longford.com |
association_definitions
Cet objet de base de données contient des informations sur tous les types d'association disponibles entre les objets HubSpot, avec une ligne par type d'association. Vous pouvez joindre cet objet de base de données à l'objet de base de données des associations pour obtenir des informations d'association spécifiques aux enregistrements.
Il peut y avoir plusieurs types d'association entre les mêmes objets. Le type d'association principal, qui est le type affiché dans HubSpot lorsque des enregistrements sont associés, utilise l'association où ISMAINASSOCIATIONDEFINITION
est true
.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
COMBINEDASSOCIATIONTYPEID |
Varchar | L'identifiant unique de la définition d'association. |
CATEGORY |
Varchar | La source du type d'association. L'un d'entre eux : HUBSPOT_DEFINED , USER_DEFINED , INTEGRATOR_DEFINED |
ID |
Nombre | L'identifiant de la définition d'association dans sa catégorie. |
FROMOBJECTTYPEID |
Varchar | L'ID de l'objet qui est la source de la définition d'association. |
TOOBJECTTYPEID |
Varchar | L'ID de l'objet qui est la destination de la définition d'association. |
NAME |
Varchar | Le nom de la définition d'association. |
LABEL |
Varchar | Le libellé de la définition d'association. |
ISMAINASSOCIATIONDEFINITION |
Booléen | Indique si la définition d'association est le type principal. Lorsque la valeur est true, la définition est celle affichée par HubSpot sur les fiches d'informations associées. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. Découvrez-en davantage sur les types de données des colonnes. |
Exemple 1
Récupérez les types d'association disponibles dans votre compte.
Requête :
SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY category, id
Renvoie :
COMBINEDASSOCIATIONTYPE | FROMOBJECTTYPEID | TOOBJECTTYPEID | NAME |
0-1 | 0-1 | 0-2 | CONTACT_TO_COMPANY |
0-2 | 0-2 | 0-1 | COMPANY_TO_CONTACT |
0-3 | 0-3 | 0-1 | DEAL_TO_CONTACT |
0-4 | 0-1 | 0-3 | CONTACT_TO_DEAL |
0-5 | 0-3 | 0-2 | DEAL_TO_COMPANY |
... | ... | ... | ... |
Exemple 2
Récupérez les types d'association disponibles entre les transactions et les entreprises.
Requête :
SELECT
combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = trueORDER BY category, id
Renvoie :
COMBINEDASSOCIATIONTYPE | FROMOBJECTTYPEID | TOOBJECTTYPEID | NAME |
0-5 | 0-3 | 0-2 | DEAL_TO_COMPANY |
0-271 | 0-3 | 0-2 | DEAL_TO_PRIMARY_COMPANY |
0-341 | 0-3 | 0-2 | DEAL_TO_COMAPNY_UNLABELED |
associations
Cet objet de base de données contient des informations sur les associations entre des enregistrements spécifiques. Chaque ligne représente une association d'un objet à un autre objet. Des rangées peuvent être présentes dans cet objet de base de données même si les enregistrements référencés ont été supprimés. Vous pouvez vérifier si des fiches d'informations existent en faisant une association avec le tableau objects.
Cet objet de base de données peut être utile si votre compte contient une grande quantité de données, ce qui entraîne une lenteur des vues associations_x_to_y.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
COMBINEDASSOCIATIONTYPEID |
Varchar | L'identifiant unique de la définition d'association. |
FROMOBJECTID |
Nombre | L'ID de l'objet source. |
TOOBJECTID |
Nombre | L'ID de l'objet de destination. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. Découvrez-en davantage sur les types de données des colonnes. |
Exemple 1
Récupérez tous les contacts et transactions associés.
Requête :
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE combinedAssociationTypeId = '0-5'
Renvoie :
TYPE D'ASSOCIATION COMBINÉ | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
0-5 | 6616436082 | 3866015468 |
0-5 | 6690805943 | 7132752747 |
... | ... | ... |
Exemple 2
Récupérez tous les tickets associés à un contact spécifique (ID du contact : 3005).
Requête :
SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;
Renvoie :
COMBINEDASSOCIATIONTYPE | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
Exemple 3
Récupérez toute les transactions associées à un contact spécifique (ID du contact : 4464). Au lieu d'un codage en dur combinedAssociationTypeId, une association avec association_definitions est réalisée dans cet exemple.
Requête :
SELECT combinedAssociationTypeId, fromObjectId toObjectId,
FROM associations
WHERE
combinedAssociationTypeId = (
SELECT combinedAssociationTypeId
FROM association_definitions
WHERE fromObjectTypeId = '0-1'
AND toObjectTypeId = '0-3'
AND isMainAssociationDefinition)
AND fromObjectId = 4464;
Renvoie :
TYPE D'ASSOCIATION COMBINÉ | FROMOBJECTID | TOOBJECTID |
0-5 | 6626541373 | 233620335 |
Requêtes du propriétaire
owners
Cet objet de base de données contient des informations sur les utilisateurs de votre compte HubSpot. Les utilisateurs peuvent être définis comme des propriétaires de fiches d'informations et contiennent à la fois un ID d'utilisateur et un ID de propriétaire, selon le contexte. Les deux identifiants peuvent être utilisés pour identifier le même utilisateur.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OWNERID |
Nombre | L'ID du propriétaire de l'utilisateur. |
USERID |
Nombre | L'ID d'utilisateur de l'utilisateur. Les utilisateurs avec la valeur NULL dans cette colonne ont été supprimés. |
EMAIL |
Varchar | L'adresse e-mail de l'utilisateur. |
FIRSTNAME |
Varchar | Le prénom de l'utilisateur. |
LASTNAME |
Varchar | Le nom de l'utilisateur. |
ARCHIVED |
Booléen | Indique si l'utilisateur a été supprimé. |
CREATEDAT |
Horodatage | La date et l'heure auxquelles l'utilisateur a été créé dans HubSpot. Consultez les types de données de colonne pour plus d'informations. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour de l'utilisateur dans HubSpot. Consultez les types de données de colonne pour plus d'informations. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez tous les utilisateurs. Le filtre WHERE archived = false
supprime les utilisateurs supprimés des données renvoyées.
Requête :
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE archived = false
Renvoie :
OWNERID | USERID | FIRSTNAME | LASTNAME | |
29584574 | 4621126 | 233620335 | Hobbes | Baron |
30532717 | 4874784 | 3866015468 | Pablo | Walters |
30580321 | 925511 | 7132752747 | Milo | Gold |
... | ... | ... | ... | ... |
Exemple 2
Récupérez un propriétaire spécifique avec son ID.
Requête :
SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE ownerId = 29584574;
Renvoie :
OWNERID | USERID | FIRSTNAME | LASTNAME | |
29584574 | 4621126 | 233620335 | Hobbes | Baron |
Exemple 3
Récupérez tous les contacts et leurs propriétaires. Cette requête associe les fiches d'informations aux propriétaires avec object_with_object_properties et la propriété de contact hubspot_owner_id.
SELECT
contact.objectId contactid,
contact.property_firstname contact_firstname,
contact.property_lastname contact_lastname,
contact.property_hubspot_owner_id contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM objects_contacts contact
JOIN owners owner ON contact.property_hubspot_owner_id = owner.ownerId
Renvoie :
CONTACTID | CONTACT_FIRSTNAME | CONTACT_LASTNAME | CONTACT_HUBSPOT_OWNER_ID | OWNER_FIRSTNAME | OWNER_LASTNAME | OWNER_EMAIL |
113834202 | Richard | Greenfield | 29584574 | Hobbes | Baron | hobbes.b@business.com |
53540801 | Ford | Karl | 30241125 | Milo | Gold | milo.g@business.com |
... | ... | ... | ... | ... | ... | ... |
Questions de l'équipe
Équipes
Dans HubSpot, les équipes vous permettent de regrouper les utilisateurs afin de faciliter leur gestion et le reporting. Vous pouvez avoir une équipe principale et des équipes supplémentaires.
Cet objet de base de données contient des informations sur les équipes de votre compte HubSpot.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
TEAMID | Nombre | L'ID de l'équipe. |
TEAMNAME | Varchar | Le nom de l'équipe. |
PARENTTEAMID | Nombre | L'identifiant de l'équipe parent. |
CHILDTEAMIDS | Tableau | Les ID des équipes enfants. |
PRIMARYOWNERMEMBERS | Varchar | Les ID de propriétaire des membres principaux de l'équipe. |
SECONDARYOWNERMEMBERS | Booléen | Les ID de propriétaire des membres secondaires de l'équipe. |
INGESTEDAT | Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez toutes les équipes.
Requête :
SELECT teamId,teamName, primaryOwnerMembers
FROM teams
Renvoie :
TEAMID | TEAMNAME | PRIMARYOWNERMEMBERS |
29584574 | Équipe secondaire | [233620335, 1234553] |
30532717 | Super équipe | [3866015468, 126221873] |
3058032 | Équipe A | [7132752747] |
owner_team
Cet objet de base de données contient des informations sur les propriétaires correspondant à chaque équipe dans votre compte HubSpot.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
TEAMID | Nombre | L'ID de l'équipe. |
OWNERID | Nombre | L'ID du propriétaire de l'utilisateur. |
ISTEAMPRIMARY | Booléen | Si cette équipe est l'équipe principale de ce propriétaire. |
INGESTEDAT | Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez tous les propriétaires dans une équipe donnée.
Requête :
SELECT teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574
Retours
TEAMID | TEAMNAME | ISTEAMPRIMARY |
29584574 | 233620335 | TRUE |
29584574 | 1234553 | TRUE |
Requêtes sur les pipelines
Découvrez ci-dessous comment interroger les données de sur le pipeline de transactions et de tickets, telles que les pipelines disponibles dans votre compte et leurs phases.
Vous pouvez joindre ces données à celles des objets pour obtenir une vue complète de vos pipelines. Par exemple, vous pouvez interroger les transactions et leurs pipelines en joignant les données object_deals_view ou object_with_object_properties aux données pipelines.
pipelines
Cet objet de base de données contient des informations sur les pipelines de transactions et de tickets dans votre compte. Chaque ligne retournée représente un seul pipeline.
Ce tableau peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
PIPELINEID |
Varchar | L'ID du pipeline. |
OBJECTTYPEID |
Varchar | L'ID de l'objet que le pipeline peut contenir (par exemple, des transactions ou des tickets). |
LABEL |
Varchar | Le nom du pipeline dans HubSpot. |
DISPLAYORDER |
Nombre | L'ordre dans lequel le pipeline est affiché dans HubSpot. |
ARCHIVED |
Booléen | Indique si le pipeline a été supprimé. |
CREATEDAT |
Horodatage | La date de création du pipeline. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour des données dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez tous les pipelines de transactions disponibles, classés selon l'ordre dans HubSpot.
Requête :
SELECT objectTypeId, pipelineId, label
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;
Renvoie :
OBJECTTYPEID | PIPELINEID | LABEL |
0-3 | 1bed503c-37f7-4f51-9d40-7598902673f4 | Pipeline de vente |
0-3 | 12659678 | Pipeline de licences |
0-3 | 75e28846-ad0d-4be2-a027-5e1da6590b98 | Nouvelle entreprise |
... | ... | ... |
Exemple 2
Récupérez les transactions dans un pipeline spécifique. Associez object_with_object_properties et pipelines.
Requête :
SELECT
deals.objectId deal_id,
deals.property_dealname deal_name,
pipelines.label pipeline_name
FROM objects_deals deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.property_pipeline = pipelines.pipelineid
WHERE pipelines.objectTypeId = '0-3';
Renvoie :
DEAL_ID | DEAL_NAME | PIPELINE_NAME |
605140072 | Meowmix Global Signup | Pipeline de vente |
605457408 | Friskies Rebrand | Pipeline de vente |
604362473 | Fresh Step App Build | Pipeline de vente |
Exemple 3
Comme l'exemple 2 ci-dessus, récupérer les transactions dans un pipeline spécifique. Mais au lieu d'utiliser objects_deals dans les jointures, utilisez object_with_object_properties.
SELECT
deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
WHERE deals.objecttypeId = '0-3'
AND pipelines.objectTypeId = '0-3';
Renvoie :
DEAL_ID |
DEAL_NAME |
PIPELINE_NAME |
605140072 |
Meowmix Global Signup |
Pipeline de vente |
605457408 |
Friskies Rebrand |
Pipeline de vente |
604362473 |
Fresh Step App Build |
Pipeline de vente |
pipeline_stages
Cette vue contient des informations sur les phases individuelles d'un pipeline. Identifiez les phases en combinant l'ID du type d'objet, l'ID du pipeline et l'ID de la phase. Lorsque vous associez pipelines, utilisez l'ID du type d'objet et l'ID du pipeline.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
PIPELINEID |
Varchar | L'ID du pipeline. |
STAGEID |
Varchar | L'identification de la scène. |
LABEL |
Varchar | Le nom de l'étape dans HubSpot. |
DISPLAYORDER |
Nombre | L'ordre dans lequel le pipeline est affiché dans HubSpot. |
METADATA |
Variante | Une description au format JSON de l'étape, indiquant si elle est ouverte ou fermée |
ARCHIVED |
Booléen | Si l'étape du pipeline a été supprimée. |
CREATEDAT |
Horodatage | Quand l'étape du pipeline a été créée. |
Exemple 1
Récupérez les phases d'un pipeline de transactions intitulé Pipeline de vente.
Requête :
SELECT pipelineid, stageId, label
FROM pipeline_stages
WHERE not archived
AND pipelineid = (
SELECT pipelineid
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
AND label = 'Sales Pipeline' LIMIT 1)
ORDER BY displayorder;
Renvoie :
PIPELINEID | STAGEID | LABEL |
941650 | 941651 | Contacté |
941650 | 941652 | Rendez-vous planifié |
941650 | 941653 | Facture envoyée |
... | ... | ... |
Exemple 2
Récupérez toutes les transactions et leurs phases respectives dans le pipeline.
Requête :
SELECTdeals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label pipeline_name,
pipeline_stages.label pipeline_stage_name
FROM object_with_object_properties deals
INNER JOIN pipelines
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
INNER JOIN pipeline_stages
ON deals.objectTypeId = pipeline_stages.pipelineid
AND deals.properties:pipeline::string = pipeline_stages.pipelineid
AND deals.properties:dealstage::string = pipeline_stages.stageId
WHERE deals.objecttypeId = '0-3';
Renvoie :
DEAL_ID | DEAL_NAME | PIPELINE_NAME | PIPELINE_STAGE_NAME |
605140072 | Meowmix Global Signup | Pipeline de vente | Contrat envoyé |
605457408 | Friskies Rebrand | Pipeline de vente | Rendez-vous planifié |
604362473 | Fresh Step App Build | Pipeline de vente | Contrat signé |
... | ... | ... | ... |
Demandes de renseignements sur la propriété
property_definitions
Cet objet de base de données contient des informations sur les propriétés de l'objet et les propriétés des événements de votre compte HubSpot. Les propriétés d'événement comprennent des événements standards, tels que les ouvertures d'e-mails et les clics ainsi que des propriétés sur des événements comportementaux personnalisés. Ces informations peuvent être utiles pour définir les valeurs trouvées dans les données object_properties et events.
Vous pouvez identifier des propriétés spécifiques en combinant l'ID du type d'objet et le nom. Vous pouvez récupérer des informations similaires via l'API des propriétés.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTYPEID |
Varchar | L'ID de l'objet auquel l'événement se rapporte. |
NAME |
Varchar | Le nom interne de la propriété. |
LABEL |
Varchar | Le libellé de la propriété. |
DESCRIPTION |
Varchar | La description de la propriété. |
TYPE |
Varchar | Le type de la propriété (par exemple, chaîne, nombre, date). |
FIELDTYPE |
Varchar | Le type de champ de la propriété. Définit l'affichage de la propriété dans HubSpot et sur les formulaires (par exemple, case à cocher, sélection). |
DISPLAYORDER |
Nombre | L'ordre d'affichage de la propriété. |
GROUPNAME |
Varchar | Le nom de groupe de la propriété. |
OPTIONS |
Variante | Pour les propriétés d'énumération, un tableau des options de la propriété, y compris les attributs de chaque option (par exemple, ordre d'affichage, libellé). |
CREATEDUSERID |
Nombre | L'ID de l'utilisateur qui a créé la propriété. |
REFERENCEDOBJECTTYPE |
Varchar | Le type d'objet auquel la propriété fait référence. Les propriétés de propriétaire renverront une valeur de type OWNER. |
CALCULATED |
Booléen | Si la propriété est une propriété calculée . |
EXTERNALOPTIONS |
Booléen | Indique si les options de la propriété sont définies dans un système externe. |
HASUNIQUEVALUE |
Booléen | Indique si les valeurs de la propriété sont uniques. Ne peut être défini que lors de la création d'une propriété via l'API. |
HIDDEN |
Booléen | Indique si la propriété est masquée dans HubSpot. |
SHOWCURRENCYSYMBOL |
Booléen | Indique si la valeur d'une propriété numérique est formatée comme une devise. |
FORMFIELD |
Booléen | Indique si cette propriété peut être utilisée dans les formulaires. |
READONLYDEFINITION |
Booléen | Indique si la propriété peut être modifiée dans HubSpot. |
READONLYVALUE |
Booléen | Indique si la valeur de la propriété peut être modifiée dans HubSpot. |
HUBSPOTDEFINED |
Booléen | Indique si la propriété a été créée par HubSpot ou par un utilisateur. |
ARCHIVED |
Booléen | Indique si la propriété a été supprimée. |
CREATEDAT |
Horodatage | La date et l'heure de création de la propriété dans HubSpot. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour des données dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez les noms et les descriptions de toutes les propriétés de contact.
Requête :
SELECT name, label, description
FROM property_definitions
WHERE objectTypeId = '0-1'
Renvoie :
NAME | LABEL | DESCRIPTION |
entered_sql_stage | Phase SQL saisie | Début de la phase SQL. |
first_conversion_event_name
|
Première conversion | Le premier formulaire soumis par ce contact. |
address | Adresse postale | L'adresse du contact. |
Requêtes d'événement
Les événements comprennent les événements standards de HubSpot ainsi que des événements comportementaux personnalisés. Chaque événement possède son propre objet de base de données, libellé comme suit events_eventname
.
Nom | |
ÉVÉNEMENTS_AD_INTERACTION | Détails d'une annonce et des contacts qui ont interagi avec elle en cliquant sur l'annonce ou en soumettant un formulaire après y avoir participé. |
ÉVÉNEMENTS_AD_METRICS_IMPORTED_V0 |
Détails d'une annonce et de ses mesures de performance. |
APPEL_ÉVÉNEMENTS_MOT_CLÉ MENTIONNÉ | Détails des mots-clés mentionnés par tous les participants aux appels. |
EVENTS_CLICKED_LINK_IN_EMAIL_V2 |
Liens cliqués par les contacts dans les e-mails de marketing. |
EVENTS_CLICKED_LINK_IN_TRACKED_INBOX_EMAIL_V8 | Liens cliqués dans les e-mails de la boîte de réception des conversations par les contacts. |
ÉVÉNEMENTS_COOKIE_BANNER_CLICKED | Contacts qui ont cliqué sur les cookies. |
ÉVÉNEMENTS_COOKIE_BANNER_VIEWED | Contacts qui ont consulté les cookies. |
ÉVÉNEMENTS_DOCUMENT_PARTAGÉS_V2 | Documents de vente partagés avec les contacts via un lien externe. |
ÉVÉNEMENTS_DOCUMENT_VIEWED_V2 | Documents de vente et contacts qu'ils ont consultés en ouvrant le lien externe. |
ÉVÉNEMENTS_DOCUMENT_COMPLÉTÉ_V2 | Documents de vente et contacts qui les ont clôturés. |
ÉVÉNEMENTS_HS_SCHEDULED_EMAIL_V2 | Les e-mails soumis aux serveurs de messagerie de HubSpot pour être envoyés. |
ÉVÉNEMENTS_MTA_MAIL_DÉLIVRÉ_V2 | E-mails délivrés avec succès aux destinataires par les serveurs de messagerie HubSpot. |
EVENTS_MTA_BOUNCED_EMAIL_V2 | Tentatives de livraison d'e-mails qui ont été rejetées par le serveur de messagerie du destinataire. |
ÉVÉNEMENTS_OUVERTS_EMAIL_V2 | Ouverture des e-mails par les contacts. |
ÉVÉNEMENTS_REPORTÉS_SPAM_EMAIL_V2 | E-mails marqués comme spam par les destinataires. |
EVENTS_UPDATED_EMAIL_SUBSCRIPTION_STATUS_V2 | Modifications des abonnements aux e-mails par les destinataires. |
ÉVÉNEMENTS_OUVERTS_TRAÇÉS_BOÎTE_EMAIL_V8 | Ouverture de la boîte de réception des conservations par les contacts. |
ÉVÉNEMENTS_MB_MEDIA_PLAYED | Activité de lecture vidéo par contacts. |
EVENTS_V2_CONTACT_BOOKED_MEETING_THROUGH_SEQUENCE | Le contact a réservé une réunion par le biais de la séquence. |
EVENTS_V2_CONTACT_ENROLLED_IN_SEQUENCE | Contact inscrit à partir de la séquence. |
ÉVÉNEMENTS_V2_CONTACT_FINISHED_SEQUENCE | Séquence terminée. |
EVENTS_V2_CONTACT_REPLIED_SEQUENCE_EMAIL | Le contact a répondu à l'e-mail (ou à l'e-mail entrant qui a désinscrit la séquence) envoyé par la séquence. |
ÉVÉNEMENTS_V2_CONTACT_NON INSCRITS_DE_LA_SÉQUENCE | Contact non inscrit dans la séquence. |
EVENTS_V2_CONTACT_UNENROLLED_MANUALLY_FROM_SEQUENCE | L'utilisateur a manuellement désinscrit le contact. |
EVENTS_V2_CONTACT_UNSUBSCRIBED_SEQUENCE_EMAIL | Le contact s'est désinscrit, désinscrivant la séquence. |
ÉVÉNEMENTS_V2_SÉQUENCE_EMAIL_ANNONCÉ | L'e-mail de la séquence a été rejeté. |
ÉVÉNEMENTS_V2_SÉQUENCE_ERRORED | Séquence erronée causant une désinscription. |
ÉVÉNEMENTS_V2_SÉQUENCE_ÉTAPE_EXÉCUTÉE | Étape de la séquence exécutée. |
ÉVÉNEMENTS_VISITÉS_PAGE | Visites de pages du site web par les contacts. |
EVENEMENTS_<INTEGRATION_EVENT_NAME>_<UNIQUE_IDENTIFIER> | Événements d'intégration (Timeline V3 API) |
Vous pouvez identifier les événements en combinant leur ID de type d'événement et leur ID. Les colonnes qui sont retournées dépendent de l'événement, avec une colonne pour chaque propriété d'événement. Tous les événements renverront les colonnes suivantes :
Remarque : HubSpot a ajouté deux nouvelles colonnes à tous les tableaux et vues d'événements :
occurredat
occurredatdateint
Ces colonnes remplaceront les colonnes occuredat
et occuredatdateint
le 24 novembre 2022. À cette date, les anciennes colonnes ne seront plus disponibles, et les tentatives d'interrogation d'un objet de base de données ou d'une vue contenant des événements utilisant ces colonnes échoueront. En savoir plus sur les changements de rupture pour les partages de données Snowflake.
Nom de la colonne | Type | Description |
EVENTTYPEID |
Varchar | L'ID du type d'événement, similaire à OBJECTTYPEID Vous pouvez interroger tous les ID des types d'événements dans votre compte en utilisant la vue object_and_event_type_definitions. |
ID |
Varchar | Ceci est unique par type d'événement. Il n'y aura pas deux enregistrements ayant le même ID pour un type d'événement donné. |
OBJECTTYPEID |
Varchar | Le type d'objet qui a réalisé l'événement. Par exemple, un clic sur un lien d'e-mail affichera 0-1 dans cette colonne, représentant l'objet contacts. |
OBJECTID |
Nombre | L'ID de la fiche d'informations qui a terminé l'événement. |
OCCURREDAT |
Horodatage | Date à laquelle l'événement s'est produit. |
|
Nombre | Un nombre représentant la date à laquelle l'événement s'est produit. Contient les mêmes données que la colonne OCCURREDAT mais formatées sous forme de nombre et utilisées comme partie de la clé de cluster de la table. Dans la plupart des cas, vous pouvez utiliser l'horodatage OCCURREDAT au lieu de ce nombre. |
|
Nombre | Un nombre représentant la date à laquelle l'événement s'est produit. Contient les mêmes données que la colonne Veuillez noter que cette colonne sera obsolète le 24 novembre 2022 et remplacée par |
OCCUREDAT |
Horodatage | Date à laquelle l'événement s'est produit. Veuillez noter que cette colonne sera obsolète le 24 novembre 2022 et remplacée par |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. Découvrez-en davantage sur les types de données des colonnes. |
PROPERTY_* |
Varchar | Colonnes individuelles contenant les propriétés de l'événement. |
Exemple 1
Récupérez les 50 événements de clics d'e-mails les plus récents.
Requête :
SELECT eventTypeId, objectTypeId, objectId, occuredAt, property_hs_click_raw_url AS hs_click_raw_url
FROM events_clicked_link_in_email_V2
ORDER BY occuredAt
LIMIT 50;
Renvoie :
EVENTTYPEID | OBJECTTYPEID | OBJECTID | OCCUREDAT | HS_CLICK_RAW_URL |
4-666288 | 0-1 | 45318579 | 2016-08-04 21:06:43.245 | http://website.com?utm_campaign=Launch&utm_source=hs_email&utm_medium=email&utm_content=32520579 |
4-666288 | 0-1 | 80701 | 2016-08-29 13:31:28.622 | https://cta-image-cms2.hubspot.com/ctas/v2/public/cs/ci/?pg=f0f6585a-08fc-4273-8422-49cfaddcf8d8&pid=1976760&ecid=ACsprvtkRbakerw4BiWlaDL7xFAqau1aW9m1_PAqSfQiWarY69X7Ds42zHGYTCfhHJLrXUP86Nxu |
4-666288 | 0-1 | 26146013 | 2017-11-30 14:27:11.528 | https://hubs.ly/H097a37a?utm_source=hs_email&utm_medium=email&utm_content=58251290 |
... | ... | ... | ... | ... |
Exemple 2
Récupérez tous les événements de clics d'emails en fonction des contacts qui ont cliqué. Cette requête permet d'obtenir des données de contact en associant objet_with_object_properties.
Requête :
SELECT
click.occuredat click_timestamp,
click.property_hs_click_raw_url click_url,
contact.properties:firstname::varchar contact_firstname,
contact.properties:lastname::varchar contact_lastname
FROM events_clicked_link_in_email_V2 click
LEFT JOIN object_with_object_properties contact
ON click.objectTypeId = contact.objectTypeId
AND click.objectId = contact.objectId
LIMIT 3;
Renvoie :
CLICK_TIMESTAMP | CLICK_URL | CONTACT_FIRSTNAME | CONTACT_LASTNAME |
2019-01-04 22:35:41.264 | https://help.website.com/articles/tutorial/how-to-use-email-tool?utm_source=hs_automation&utm_medium=email&utm_content=68042217 | Hobbes | Baron |
2018-11-08 09:37:41.400 | https://www.website.net/webinar-case-study?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | Milo | Gold |
2018-11-08 09:37:41.426 | https://www.website.net/contact?utm_source=hs_automation&utm_medium=email&utm_content=55200164 | Harlow | Villari |
... | ... | ... | ... |
Requêtes de type objet et événement
object_and_event_type_definitions
Cet objet de base de données contient les définitions de tous les objets et événements disponibles dans votre compte HubSpot.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | L'ID de l'objet que le pipeline peut contenir (par exemple, des transactions ou des tickets). |
FULLYQUALIFIEDNAME |
Varchar | Le nom de l'objet ou de l'événement. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. Découvrez-en davantage sur les types de données des colonnes. |
Exemple 1
Récupérez tous les objets et événements disponibles dans votre compte HubSpot.
Requête :
SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;
Renvoie :
OBJECTTYPEID | FULLYQUALIFIEDNAME |
0-1 | COÛT DE |
0-8 | LINE_ITEM |
0-11 | CONVERSATION |
... | ... |
Listes de requêtes
Découvrez ci-dessous comment interroger vos listes, telles que les listes de contacts et d'entreprises disponibles ainsi que les fiches d'informations incluses dans ces listes.
lists
Cet objet de base de données contient des informations sur les listes de contacts et de sociétés de HubSpot. Renvoie une ligne par liste, et les colonnes comprennent les détails de la liste tels que son nom, sa taille et le type d'objet des fiches d'informations qu'elle contient. Ne contient pas d'informations sur les fiches d'informations individuelles de chaque liste (voir list_membership).
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
OBJECTTYPEID |
Varchar | Le type de fiches d'informations dans la liste (par exemple, contact ou entreprise). |
LISTID |
Nombre | L'ID de la liste. Associe la colonne LISTID dans le tableau list-memberships. |
CLASSICLISTID |
Nombre | Un ID spécifique aux listes de contacts qui correspond à l'ID dans l'URL de la liste dans HubSpot. |
NAME |
Varchar | Le nom de la liste. |
SIZE |
Nombre | Le nombre de fiches d'informations dans la liste. |
CREATEDBYUSERID |
Nombre | L'ID de l'utilisateur qui a créé la liste. |
CREATEDAT |
Horodatage | La date de création de la liste dans HubSpot. |
UPDATEDBYUSERID |
Nombre | L'ID de l'utilisateur qui a mis à jour la liste le plus récemment. |
UPDATEDAT |
Horodatage | La date et l'heure auxquelles la date a été mise à jour pour la dernière fois dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez toutes les listes de contacts.
Requête :
SELECT listId, name, size
FROM lists
WHERE objectTypeId = '0-1';
Renvoie :
LISTID | NAME | TAILLE |
118131 | Prise de contact hebdomadaire | 103 |
66156 | Abonnés au blog | 455 |
771852 | Principaux abonnés 2021 | 37 |
... | ... | ... |
Exemple 2
Récupérez une liste de contacts spécifique nommée Principaux abonnés 2021.
Requête :
SELECT listId, name, size
FROM lists
WHERE objectTypeId = '0-1'
ANDlistName = 'Top subscribers 2021';
Renvoie :
LISTID | NAME | TAILLE |
771852 | Principaux abonnés 2021 | 37 |
list_membership
Cet objet de base de données contient des informations sur les enregistrements qui sont membres de telle ou telle liste, avec une ligne par enregistrement dans la liste. Par exemple, si vous avez une liste d'abonnés à un blog contenant 500 contacts, l'objet de base de données renverra 500 lignes.
Seuls les ID de liste et d'objet sont inclus. Pour plus d'informations, rejoignez l'objet de la base de données des listes.
Cet objet de base de données peut renvoyer les colonnes suivantes :
Nom de la colonne | Type | Description |
LISTID |
Varchar | L'ID de la liste. Associe la colonne HS_LIST_ID dans le tableau lists. |
OBJECTID |
Varchar | L'ID de la fiche d'informations dans la liste. |
UPDATEDAT |
Horodatage | La date et l'heure de la dernière mise à jour des données dans HubSpot. Découvrez-en davantage sur les types de données des colonnes. |
INGESTEDAT |
Horodatage | La date et l'heure auxquelles les données ont été ingérées pour la dernière fois par Snowflake. |
Exemple 1
Récupérez les ID de tous les contacts de la liste Principaux abonnés 2021.
Requête :
SELECT objectId
FROM list_memberships
INNER JOIN lists
ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021';
Renvoie :
OBJECTID |
54325098 |
96805401 |
6922028 |
... |
Exemple 2
Récupérer les noms et prénoms des détails des contacts de la liste Top subscribers 2021.
Requête :
SELECT
contact.objectId AS objectid,
contact.property_firstname AS firstname,
contact.property_lastname AS lastname
FROM objects_contacts contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021'
Renvoie :
OBJECTID |
PRÉNOM |
NOM |
54325098 |
Hobbes |
Baron |
96805401 |
Milo |
Gold |
6922028 |
Henry |
Riley |
... |
... |
... |
FAQ
Je souhaite générer des données similaires à un rapport de l'application HubSpot, comment dois-je procéder ?
En règle générale, il convient d'examiner les colonnes présentes dans le rapport avec lequel vous effectuez la comparaison. Si la visualisation du rapport n'est pas tabulaire, sa conversion en tableau permettra d'afficher les colonnes que le rapport contient. Ces colonnes sont généralement des propriétés d'un type d'objet ou d'un type d'événement. L'étape suivante consiste à déterminer à quelle propriété de type d'objet correspond une colonne. Vous pouvez le faire en interrogeant les property_definitions :
SELECT * FROM property_definitions
WHERE label = [column_name]
Il se peut qu'il n'y ait pas toujours une correspondance exacte, car certains noms de propriétés sont généralement remplacés par un nom plus descriptif dans l'application. Dans ce cas, une recherche floue utilisant l'opérateur LIKE sur la colonne label (ou même description) dans property_definitions peut être utile.
Après avoir déterminé à quel type d'objet appartient la colonne, il est trivial de déterminer ce qu'il faut interroger. Vous pouvez simplement rechercher la vue object_x qui correspond au type.
Comment lier ce type de données à un autre, par exemple comment récupérer des formulaires pour une campagne ?
Si vous devez relier deux ou plusieurs enregistrements différents les uns aux autres, jetez un coup d'œil aux associations. Les associations représentent les relations entre les objets dans le CRM HubSpot. Toutes les différentes associations possibles entre deux types sont répertoriées dans association_definitions. Dans l'exemple ci-dessus, pour connaître tous les formulaires auxquels une campagne est liée, vous devez interroger ASSOCIATIONS_CAMPAIGNS_TO_FORMS :
SELECT campaigns.property_hs_name campaign_name,
forms.property_hs_form_id form_id
FROM objects_campaigns campaigns
JOIN associations_campaigns_to_forms assoc ON
assoc.campaign_objectid=campaigns.objectid
JOIN objects_forms forms ON forms.objectid=assoc.form_objectid
GROUP BY campaign_name
Comment puis-je connaître l'état de l'intégration HubSpot-Snowflake ?
Pour recevoir des mises à jour en temps réel sur les pannes et les problèmes de l'intégration HubSpot-Snowflake, interrogez la vue data_share_status :
SELECT * FROM hubspot.public.data_share_status
LIMIT 1
Les résultats s'affichent comme suit :
STATUT | MESSAGE | UPDATEDAT |
OK | AUCUN INCIDENT CONNU POUR LE MOMENT | 2023-04-10 09:21:20.123 |
Cette vue fournit des mises à jour sur l'état de l'intégration dans l'ordre chronologique inverse. L'intégration des résultats de cette requête dans un tableau de bord vous aidera à suivre les performances de l'intégration et à détecter les temps d'arrêt ou les pannes.