メインコンテンツにスキップ
お客さまへの大切なお知らせ:膨大なサポート情報を少しでも早くお客さまにお届けするため、本コンテンツの日本語版は人間の翻訳者を介さない自動翻訳で提供されております。正確な最新情報については本コンテンツの英語版をご覧ください。
Reports

SnowflakeでHubSpotデータを照会する

更新日時 2022年 8月 12日

対象製品

Operations Hub Enterprise

SnowflakeをHubSpotアカウントに接続したら、Snowflakeのデータ共有機能を使って、SnowflakeアカウントからHubSpotデータを照会できます。

クエリーはSQLを使用して作成され、次のHubSpotデータを取得するために使用できます。

  • オブジェクトコンタクト、会社、製品など、標準およびカスタムのオブジェクト。 
  • レコード:コンタクト会社など、個々のレコード。 
  • 関連付け利用可能なすべての関連付けタイプと現在関連付けられているレコード。
  • 所有者:レコードの所有者として設定されているHubSpotアカウントのユーザー。
  • パイプライン:取引パイプラインとチケットパイプライン(パイプラインステージを含む)。
  • プロパティー CRMレコードのプロパティーとその値(プロパティー履歴を含む)。
  • イベントメール開封などの標準的なHubSpotイベントと、カスタム行動イベントの両方のイベントデータ。
  • リスト HubSpotの連絡先と会社リスト(ベータ版)。

HubSpotのCRMデータモデルについて詳細をご確認ください。

Snowflake環境を設定する

SnowflakeでHubSpotデータを照会するには、照会元のウェアハウス、データベース、スキーマを選択する必要があります。ワークシートレベルでこれらを設定して自動的にクエリーに含めるか、または手動で追加することができます。

ワークシートレベルでウェアハウス、データベース、スキーマを選択するには、次の手順に従います。

  • ワークシートの一番上の行で、[Worksheet actions]バーをクリックします。

    snowflake-worksheet-settings0
  • ポップアップウィンドウで、次の操作を行います。
    • [Warehouse]ドロップダウンメニューをクリックして、ウェアハウスを選択します。
    • [Database]ドロップダウンメニューをクリックして、HubSpotデータベースを選択します。
    • [Schema]ドロップダウンメニューをクリックして、使用するスキーマを選択します。
        • V2_LIVE:HubSpotデータのセキュアビューに基づいたライブスキーマです。このスキーマのデータは最大で15分ごとに更新されます。大規模なデータセットを照会する場合、クエリーに時間がかかることがあります。このような場合は、まずETLプロセスで自分のテーブルにデータをコピーし、そのテーブルを照会することをお勧めします。

      注意:このスキーマは現在、HubSpotのSnowflakeアカウントの地域が一致する場合にのみ利用できます。EUデータセンターのHubSpotアカウントの場合は、EU_CENTRAL_1_AWSになります。他のすべてのHubSpotアカウントでは、US_EAST_1_AWSを意味します。

      • V2_DAILY(ベータ):テーブルに基づいてデータを照会するためのデイリースキーマです。このスキーマのデータは1日1回更新されます。このスキーマを使用すると、クエリーが高速になります。Snowflakeで直接照会する場合に推奨されます。

      snowflake-worksheet-settings-window0

設定された環境では、HubSpotデータを照会するときに、データベースやスキーマタイプを手動でクエリーに追加する必要がありません。

データベースとスキーマタイプをクエリーに手動で追加するには、FROMフィールドの先頭にデータベース名とスキーマをこの順序で追加します。次に例を示します。


SELECT objectTypeId, objectId
FROM hubspot_share_name.V2_LIVE.object_with_object_properties
WHERE objectTypeId = '0-1'

データの概要

クエリーはSQLを使用して作成され、次のHubSpotデータを取得するために使用できます。

  • オブジェクトコンタクト、会社、製品など、標準およびカスタムのオブジェクト。 
  • レコード:コンタクト会社など、個々のレコード。 
  • 関連付け利用可能なすべての関連付けタイプと現在関連付けられているレコード。
  • 所有者:レコードの所有者として設定されているHubSpotアカウントのユーザー。
  • パイプライン:取引パイプラインとチケットパイプライン(パイプラインステージを含む)。
  • プロパティー CRMレコードのプロパティーとその値(プロパティー履歴を含む)。
  • イベントメール開封などの標準的なHubSpotイベントと、カスタム行動イベントの両方のイベントデータ。
  • リスト HubSpotの連絡先と会社リスト(ベータ版)。

HubSpotのCRMデータモデルについて詳細をご確認ください。

返されるデータの制限と順序の設定

クエリーを作成するときに、返されるデータの量と、データが返される順序を制御できます。このためにはLIMITORDER BYをクエリーに追加します。例えば、すべてのコンタクトとそのプロパティーを照会するが、3件の結果だけをコンタクトIDの順で返すには、クエリーを次のように作成します。


SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'
ORDER BY objectId
LIMIT 10;

オブジェクトのクエリー

以下では、特定のHubSpotオブジェクトやレコードのデータを照会する方法と、返されるデータを説明します。 

照会時には以下のオブジェクトIDを使用できます。

オブジェクト名 ObjectTypeId
コンタクト 0-1
会社 0-2
取引 0-3
エンゲージメント 0-4
チケット 0-5
商品項目 0-8

カスタムオブジェクトには、作成時にIDが割り当てられ、「2-unique_ID」として識別されます。例えば「2-12345」などです。object_and_event_type_definitionsについて詳細をご確認ください。

object_with_object_properties

このビューには、HubSpotレコードのデータが含まれています。このテーブルに対するクエリーでは、オブジェクト内のレコードごとに1行が返されます(例:コンタクトオブジェクト内のすべてのコンタクトレコード)。すべてのプロパティーは「Properties」列(Snowflake Variant / JSON列)に格納されます。 

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar オブジェクトタイプのID(例:コンタクトの場合は「0-1」)。
OBJECTID 数値 レコードのID (例:IDが「123752」のコンタクトレコード)
PROPERTIES バリアント レコードのプロパティーの値。
アップデートデータ タイムスタンプ HubSpotでのレコードの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

すべてのコンタクトとそのプロパティーを取得します。

クエリー:


SELECT objectTypeId, objectId, properties
FROM object_with_object_properties
WHERE objectTypeId = '0-1'


戻り値:

OBJECTTYPEID OBJECTID PROPERTIES
0-1 38960439 {" createdate ":" 1504656970152 "...}
0-1 321740651 {" createdate ":" 1590183081949 "...} 
0-1 98392101 {" createdate ":" 1533566477279 "...}
... ... ...

2

最大3つの取引の取引名とステージを取得します

クエリー:


SELECT

objectId,
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;


戻り値:

OBJECTID DEALNAME DEALSTAGE DEALAMOUNT
38960439 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
321740651 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
98392101 Deal name 1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219

objects

このビューは、object_with_object_propertiesテーブルのデータのサブセットです。返される各行には、各レコードのオブジェクトIDが含まれています。このビューでは、既存のレコードのみが返されます。HubSpotでレコードが削除されると、そのレコードはこのビューから除去されます。

このビューは、アカウントに大量のデータがあり、「object_with_object_properties」の実行に時間がかかる場合に便利です。このテーブルと「object_properties」テーブルを併用することで、「object_with_object_properties」のデータを複製し、かつ返されるプロパティーを制御できます。

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar オブジェクトタイプのID(例:コンタクトの場合は「0-1」)。
OBJECTID 数値 レコードのID (例:IDが「123752」のコンタクトレコード)
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。列のデータ型について詳細をご確認ください。

例1

すべてのコンタクトレコードを取得します。

クエリー:


SELECT objectTypeId, objectId
FROM objects
WHERE objectTypeId = '0-1'


戻り値:

OBJECTTYPEID OBJECTID
0-1 36721864
0-1 103580363
0-1 56047670
... ...

object_properties

このテーブルは、object_with_object_propertiesテーブルのサブセットです。各行には、特定のレコードで設定されている現在のプロパティー値が含まれています。例えば、コンタクトの場合は「First name」プロパティーと「Last name」プロパティーに対してそれぞれ1行ずつがあります。レコードでプロパティーに値が設定されていない場合には、行には空の文字列が示されるか、または行が表示されません。

このテーブルは、アカウントに大量のデータがあり、「object_with_object_properties」の実行に時間がかかる場合に便利です。このテーブルと「object_properties」テーブルを併用することで、「object_with_object_properties」のデータを複製し、かつ返されるプロパティーを制御できます。

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar オブジェクトタイプのID(例:コンタクトの場合は「0-1」)。
OBJECTID 数値 レコードのID (例:IDが「123752」のコンタクトレコード)
NAME Varchar プロパティーの名前。
VALUE タイムスタンプ プロパティーの値。
アップデートデータ タイムスタンプ HubSpotでのレコードの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

すべてのコンタクトの名前、姓、市町村のプロパティーを取得します。

クエリー:


SELECT objectTypeId, objectId, name, value
FROM object_properties
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;


戻り値:

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

例2

すべてのコンタクトの名前、姓、ライフサイクルステージを取得し、結合してコンタクトごとに1行にまとめます。この例では、SQL PIVOT構文を使用します。

クエリー:


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

 

戻り値:

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

このビューには、過去のプロパティー値が含まれています。コンタクトの場合、最新の45個の値が含まれています。その他のすべてのオブジェクトでは、最新の20個の値が含まれています。過去のプロパティー値ごとに1行ずつ表示されます。プロパティーの現在の値は、プロパティーの履歴における最新の「UPDATEDAT」タイムスタンプによって決まります。

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar オブジェクトタイプのID(例:コンタクトの場合は「0-1」)。
OBJECTID 数値 レコードのID (例:IDが「123752」のコンタクトレコード)
NAME Varchar プロパティーの名前。
VALUE タイムスタンプ プロパティーの値。
アップデートデータ タイムスタンプ HubSpotでのデータの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

1つのコンタクトの1つのプロパティーの履歴を取得します。

クエリー:


SELECT objectId, name, value, timestamp
FROM object_properties_history
WHERE objectTypeId = '0-1'
AND objectId = 585
AND name = 'hs_predictivescoringtier'
ORDER BY timestamp DESC;

   
戻り値:

OBJECTTYPEID OBJECTID FIRSTNAME LASTNAME LIFECYCLESTAGE
0-1 54325098 Hobbes Baron lead
0-1 96805401 Milo Gold lead
0-1 6922028 Henry Riley lead
... ... ... ... ...

例2

ライフサイクルステージが「サブスクライバー」であったことがあるすべてのコンタクトの現在の名前を取得します。

クエリー:


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
WHEREobject_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);


戻り値:

OBJECTID FIRSTNAME LASTNAME
54325098 Hobbes Baron
96805401 Milo Gold
6922028 Henry Riley
... ... ...

関連付けのクエリー

以下では、オブジェクト間で利用可能な関連付けタイプや、現在関連付けられているレコードなど、関連付けを照会する方法について説明します。 

association_definitions

このビューには、HubSpotオブジェクト間で利用可能なすべての関連付けタイプに関する情報が、関連付けタイプごとに1行ずつ表示されます。このテーブルをassociationsテーブルと結合することで、レコード固有の関連付け情報を取得できます。

同じオブジェクトの間に複数のタイプの関連付けを設定できます。メインの関連付けタイプ(レコードが関連付けられたときにHubSpotに表示されるタイプ)は、ISMAINASSOCIATIONDEFINITIONtrueである関連付けを使用します。

このビューでは、以下の列が返されます。

列名 タイプ 説明
COMBINEDASSOCIATIONTYPEID Varchar 関連付け定義の一意の識別子。
カテゴリー Varchar 関連付けタイプのソース。次のいずれか: HUBSPOT_DEFINEDUSER_DEFIED、INTEGATOR_DEFIED
ID 数値 そのカテゴリー内の関連付け定義の識別子。
FROMOBJECTTYPEID Varchar 関連付け定義のソースであるオブジェクトのID。
TOOBJECTTYPEID Varchar 関連付け定義の宛先であるオブジェクトのID。
NAME Varchar 関連付け定義の名前。
LABEL Varchar 関連付け定義のラベル。
ISMAINASSOCIATIONDEFINITION ブール型 関連付け定義がメインタイプであるかどうか。trueの場合、この定義はHubSpotで関連付けられているレコードに表示される定義です。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。列のデータ型について詳細をご確認ください。

例1 

アカウントで利用可能な関連付けのタイプを取得します。

クエリー:


SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY category, id

  
戻り値:

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

例2

取引と会社の間で利用可能な関連付けタイプを取得します。

クエリー:


SELECTcombinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = true

ORDER BY category, id


戻り値:

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

このビューには、特定のレコード間の関連付けに関する情報が含まれています。各行は、オブジェクト間の関連付けを表します。参照先レコードが削除されていても、このテーブルに行が存在する場合があります。レコードが存在しているかどうかは、objectsテーブルと結合することで確認できます。

このビューでは、以下の列が返されます。

列名 タイプ 説明
COMBINEDASSOCIATIONTYPEID Varchar 関連付け定義の一意の識別子。
FROMOBJECTID 数値 ソースオブジェクトのID。
TOOBJECTID 数値 ターゲットオブジェクトのID。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。列のデータ型について詳細をご確認ください。

例1

関連付けられている取引とコンタクトをすべて取得します。

クエリー:


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE combinedAssociationTypeId = '0-5'

  
戻り値:

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335
0-5 6616436082 3866015468
0-5 6690805943 7132752747
... ... ...

例2 

特定のコンタクト(コンタクトID:「3005」)に関連付けられているチケットをすべて取得します。

クエリー:


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
FROM associations
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;


戻り値:

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

例3 

特定のコンタクト(コンタクトID:「4464」)に関連付けられている取引をすべて取得します。この例では「combinedAssociationTypeId」をハードコーディングする代わりに、association_definitionsと結合しています。

クエリー:


SELECT combinedAssociationType, fromObjectId, toObjectId,
FROM associations
WHERE
combinedAssociationTypeId = (
SELECT combinedAssociationTypeId
FROM association_definitions
WHERE fromObjectTypeId = '0-1'
AND toObjectTypeId = '0-3'
AND isMainAssociationDefinition)
AND fromObjectId = 4464;


戻り値:

COMBINEDASSOCIATIONTYPE FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

所有者の問い合わせ

owners

このビューには、HubSpotアカウントのユーザーに関する情報が含まれています。ユーザーはレコード所有者として設定できます。ユーザーには、そのコンテキストに応じてユーザーIDと所有者IDの両方が含まれます。どちらのIDも同じユーザーを識別するために使用できます。 

このビューでは、以下の列が返されます。

列名 タイプ 説明
OWNERID 数値 ユーザーの所有者ID。
ユーザーID 数値 ユーザーのユーザーID。この列が「NULL」のユーザーは削除されています。
EMAIL Varchar ユーザーのEメールアドレス。
FIRSTNAME Varchar ユーザーの名前。
LASTNAME Varchar ユーザーの姓。
ISACTIVE ブール型 ユーザーが削除されているかどうか。
CREATEDAT タイムスタンプ HubSpotでのユーザーの作成日時。詳細については、列のデータ型を参照してください。
アップデートデータ タイムスタンプ HubSpotでのユーザーの最終更新日時。詳細については、列のデータ型を参照してください。

例1 

すべてのユーザーを取得します。WHERE isActive = trueフィルターにより、返されるデータから削除済みユーザーが除去されます。

クエリー:


SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE isActive = true

  
戻り値:

OWNERID ACTIVEUSERID EMAIL FIRSTNAME LASTNAME
29584574 4621126 233620335 Hobbes Baron
30532717 4874784 3866015468 Pablo Walters
30580321 925511 7132752747 Milo Gold
... ... ... ... ...

例2

所有者IDで特定の所有者を取得します。

クエリー:


SELECT ownerId, userId, email, firstname, lastname
FROM owners
WHERE ownerId = 29584574;


戻り値:

OWNERID ACTIVEUSERID EMAIL FIRSTNAME LASTNAME
29584574 4621126 233620335 Hobbes Baron

例3

すべてのコンタクトとその所有者を取得します。このクエリーは、object_with_object_propertiesとコンタクトプロパティー「hubspot_owner_id」を使用してオーナーにレコードを結合します。


SELECT
contact.objectId contact_id,
contact.properties:firstname::string contact_firstname,
contact.properties:lastname::string contact_lastname,
contact.properties:hubspot_owner_id::number contact_hubspot_owner_id,
owner.firstname owner_firstname,
owner.lastname owner_lastname,
owner.email owner_email
FROM object_with_object_properties contact
inner join owners owner on contact.properties:hubspot_owner_id::number = owner.ownerId
where objectTypeId = '0-1';


戻り値:

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

パイプラインのクエリー

以下では、取引とチケットのパイプラインのデータ(アカウントで利用可能なパイプラインとそのステージなど)を照会する方法を説明します。 

このデータをオブジェクトデータと結合することで、パイプラインの全体像を把握できます。例えば、object_with_object_propertiesのデータとpipelinesのデータを結合することで、取引とそのパイプラインを照会できます。

pipelines

このビューには、アカウントの取引パイプラインとチケットパイプラインに関する情報が含まれます。返される各行はそれぞれ1つのパイプラインを表しています。

このテーブルでは、以下の列が返されます。

列名 タイプ 説明
PIPELINEID Varchar パイプラインのID。
OBJECTTYPEID Varchar パイプラインに含めることができるオブジェクト(取引やチケットなど)のID。
LABEL Varchar HubSpotでのパイプラインの名前。
DISPLAYORDER 数値 HubSpotでパイプラインが表示される順序。
アーカイブ済み ブール型 パイプラインが削除されているかどうか。
CREATEDAT タイムスタンプ パイプラインの作成日時。
アップデートデータ タイムスタンプ HubSpotでのデータの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

利用可能なすべての取引パイプラインを、HubSpotでの順序で取得します。

クエリー:


SELECT objectTypeId, pipelineId, label
FROM pipelines
WHERE objectTypeId = '0-3'
AND not archived
ORDER BY displayorder;

  
戻り値:

OBJECTTYPEID PIPELINEID LABEL
0-3 1bed503c-37f7-4f51-9d40-7598902673f4 Sales Pipeline
0-3 12659678 Licenses Pipeline
0-3 75e28846-ad0d-4be2-a027-5e1da6590b98 New Business
... ... ...

例2

特定のパイプライン内の取引を取得します。object_with_object_propertiespipelinesを結合します。

クエリー:


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


戻り値:

DEAL_ID DEAL_NAME PIPELINE_NAME
605140072 Meowmix Global Signup Sales Pipeline
605457408 Friskies Rebrand Sales Pipeline
604362473 Fresh Step App Build Sales Pipeline

pipeline_stages

このビューには、パイプライン内の個々のステージに関する情報が含まれています。オブジェクトタイプID、パイプラインID、ステージIDの組み合わせによりステージを識別します。pipelinesとの結合には、オブジェクトタイプIDとパイプラインIDを使用します。

このテーブルでは、以下の列が返されます。

列名 タイプ 説明
PIPELINEID Varchar パイプラインのID。
OBJECTTYPEID Varchar パイプラインに含めることができるオブジェクト(取引やチケットなど)のID。
LABEL Varchar HubSpotでのパイプラインの名前。
DISPLAYORDER 数値 HubSpotでパイプラインが表示される順序。
アーカイブ済み ブール型 パイプラインが削除されているかどうか。
アップデートデータ タイムスタンプ HubSpotでのデータの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1 

「Sales pipeline」という名前の取引パイプラインのパイプラインステージを取得します。

クエリー:


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;

  
戻り値:

PIPELINEID STAGEID LABEL
941650 941651 Contacted
941650 941652 Appt scheduled
941650 941653 Invoice sent
... ... ...

例2

すべての取引とそれぞれのパイプラインステージを取得します。

クエリー:


SELECT

deals.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';


戻り値:

DEAL_ID DEAL_NAME PIPELINE_NAME PIPELINE_STAGE_NAME
605140072 Meowmix Global Signup Sales Pipeline Contract sent
605457408 Friskies Rebrand Sales Pipeline Appt scheduled
604362473 Fresh Step App Build Sales Pipeline Contract signed
... ... ... ...

プロパティクエリ

property_definitions

このビューには、HubSpotアカウントのオブジェクトプロパティーとイベントプロパティーに関する情報が含まれています。イベントプロパティーには、メールの開封やクリックなどの標準イベントと、カスタム行動イベントのプロパティーがあります。この情報は、object_propertiesおよびeventsのデータにある値を定義する際に役立ちます。

オブジェクトタイプIDと名前を組み合わせることで、特定のプロパティーを識別できます。同様の情報をproperties APIで取得できます。

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTYPEID Varchar イベントに関連するオブジェクトのID。
NAME Varchar プロパティーの内部名。
LABEL Varchar プロパティーのラベル。
DESCRIPTION Varchar プロパティーの説明。
タイプ(T) Varchar プロパティーのタイプ(例:文字列、数値、日時)。
FIELDTYPE Varchar プロパティーのフィールドタイプ。プロパティーがHubSpotとフォームでどのように表示されるかを定義します(例:checkbox、select)。
DISPLAYORDER 数値 プロパティーの表示順。
グループ名 Varchar プロパティーのグループ名。
オプション@ INFO: WHATSTHIS バリアント 列挙プロパティーの場合は、プロパティーのオプションの配列であり、各オプションの属性(表示順、ラベルなど)が含まれます。
CREATEDUSERID 数値 プロパティーを作成したユーザーのID。
REFERENCEDOBJECTTYPE Varchar プロパティーが参照するオブジェクトのタイプ。所有者のプロパティーの場合は「OWNER」という値が返されます。
計算済み ブール型 プロパティーが計算済みプロパティーであるかどうか。
エクステルナロプチオン ブール型 プロパティーのオプションが外部システムで定義されているかどうか。
HASUNIQUEVALUE ブール型 プロパティーの値が一意であるかどうか。APIでプロパティーを作成するときにのみ設定できます。
隠された ブール型 プロパティーがHubSpotで非表示であるかどうか。
SHOWCURRENCYSYMBOL ブール型 数値プロパティーの値の形式が通貨であるかどうか。
フォームフィールド ブール型 このプロパティーをフォームで使用できるかどうか。
READONLYDEFINITION ブール型 プロパティーをHubSpotで編集できるかどうか。
READONLYVALUE ブール型 プロパティーの値をHubSpotで編集できるかどうか。
HUBSPOTDEFINED ブール型 プロパティーがHubSpotによって作成されたのか、ユーザーによって作成されたのか。
アーカイブ済み ブール型 プロパティーが削除されているかどうか。
CREATEDAT タイムスタンプ HubSpotでのプロパティーの作成日時。
アップデートデータ タイムスタンプ HubSpotでのデータの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

すべてのコンタクトプロパティーの名前と説明を取得します。

クエリー:


SELECT name, label, description
FROM property_definitions
WHERE objectTypeId = '0-1'

   
戻り値:

NAME LABEL DESCRIPTION
entered_sql_stage Entered SQL stage SQLステージ日のカウントを開始する。
first_conversion_event_name
First Conversion このコンタクトが送信した最初のフォーム。
address Street address - 番地 コンタクトの住所。

イベントクエリー

イベントには、標準的なHubSpotイベントとカスタム行動イベントがあります。各イベントには、「EVENTS_EVENTNAME」とラベル付けされた独自のビューがあります。

利用可能なイベントを以下に示します。

  • 広告クリックイベント(「events_ad_clicked」)
  • Eメールの開封とクリック(「events_opened_email_V2」、「events_clicked_link_in_email_v2」など)
  • ウェブアナリティクス(「events_visited_page」)

イベントタイプIDとIDを組み合わせることで、イベントを識別できます。返される列はイベントによって異なります。イベントのプロパティーごとに1つの列が返されます。すべてのイベントで返される列を以下に示します。

注意: HubSpotは、すべてのイベントテーブルとビューに2つの新しい列を追加しました。

  • 発生した
  • occurredatdateint

これらの列は、2022年4月24日に発生日 と発生日の列を置き換えます。この日付では、古い列は使用できなくなり、これらの列を使用してテーブルまたはイベントを含むビューをクエリしようとすると失敗します。Snowflakeデータ共有の変更を解除する方法について詳しくは、こちらをご覧ください。



列名 タイプ 説明
EVENTTYPEID Varchar パイプラインのID。
ID Varchar パイプラインに含めることができるオブジェクト(取引やチケットなど)のID。
OBJECTTYPEID Varchar イベントを完了したオブジェクトのタイプ。例えば、メールのリンクのクリックの場合、この列にはコンタクトオブジェクトを表す「0-1」が表示されます。
OBJECTID 数値 イベントを完了したレコードのID。
OCCURREDAT タイムスタンプ イベントが発生した時点。

OCCURREDATDATEINT

 

数値 イベントが発生した時点を表す数字。OCCURREDAT列と同じデータが含まれていますが、数値としてフォーマットされ、テーブルのクラスタキーの一部として使用されます。ほとんどの場合、この番号の代わりにOCCURREDATタイムスタンプを使用できます。

OCCUREDATDATEINT

数値

イベントが発生した時点を表す数字。OCCURREDAT列と同じデータが含まれていますが、数値としてフォーマットされ、テーブルのクラスタキーの一部として使用されます。ほとんどの場合、この番号の代わりにOCCURREDATタイムスタンプを使用できます。

注意:この列は2022年4月24日に廃止され、OCCURREDATDATEINTに置き換えられます

OCCUREDAT タイムスタンプ

イベントが発生した時点。

注意:この列は2022年4月24日に廃止され、OCCURREDATに置き換えられます

INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。列のデータ型について詳細をご確認ください。
プロパティ_* Varchar イベントのプロパティーを含む個々の列。

例1

最新の50件のEメールのクリックイベントを取得します。

クエリー:


SELECT eventTypeId, objectTypeId, objectId, occuredAt, hs_click_raw_url
FROM events_clicked_link_in_email_V2
ORDER BY occuredAt

LIMIT 50;


戻り値:

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

例2

クリックしたコンタクトのEメールのクリックイベントをすべて取得します。このクエリーは、object_with_object_propertiesと結合することでコンタクトデータを取得します。

クエリー:


SELECT
click.timestamp 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;


戻り値:

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

object_and_event_type_definitions

このビューには、HubSpotアカウントで利用可能なすべてのオブジェクトとイベントの定義が含まれています。

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar パイプラインに含めることができるオブジェクト(取引やチケットなど)のID。
FULLYQUALIFIEDNAME Varchar オブジェクトまたはイベントの名前。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。列のデータ型について詳細をご確認ください。

例1

HubSpotアカウントで利用可能なすべてのオブジェクトとイベントを取得します。

クエリー:


SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;

  
戻り値:

OBJECTTYPEID FULLYQUALIFIEDNAME
0-1 CONTACT
0-2 COMPANY
0-3 DEAL
0-4 ENGAGEMENT
0-5 TICKET
0-8 LINE_ITEM
0-11 CONVERSATION
... ...

リストのクエリー(ベータ)

以下では、利用可能なコンタクトリストと会社リスト、それらのリストに含まれるレコードなど、リストを照会する方法について説明します。

lists

このビューには、HubSpotのコンタクトリストと会社リストに関する情報が含まれます。1つのリストにつき1つの行を返します。列にはリストの詳細(リストの名前、サイズ、含まれるレコードのオブジェクトタイプなど)が含まれます。各リスト内の個々のレコードに関する情報は含まれません(list_membershipを参照)。 

このビューでは、以下の列が返されます。

列名 タイプ 説明
OBJECTTYPEID Varchar リスト内のレコードのタイプ(例:コンタクト、会社)。
LISTID 数値 リストのID。list-membershipsテーブルの「LISTID」に結合します。
CLASSICLISTID 数値 コンタクトリストに固有のIDで、HubSpotのリストのURLに含まれるIDと一致します。
LISTNAME Varchar リストの名前。
LISTSIZE 数値 リストのレコード数。
CREATEDBYUSERID 数値 リストを作成したユーザーのID。
CREATEDAT タイムスタンプ HubSpotでリストが作成された時点。
UPDATEDBYUSERID 数値 リストを最後に更新したユーザーのID。
アップデートデータ タイムスタンプ HubSpotでの日付の最終更新日時。列のデータ型について詳細をご確認ください。 
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。

例1

すべてのコンタクトリストを取得します。

クエリー:


SELECT listId, listName, listSize
FROM lists
WHERE objectTypeId = '0-1';

  
戻り値:

LISTID LISTNAME LISTSIZE
118131 Weekly outreach 103
66156 Blog subscribers 455
771852 Top subscribers 2021 37
... ... ...

例2

「Top subscribers 2021」という名前の特定のコンタクトリストを取得します。 

クエリー:


SELECT listId, listName, listSize 
FROM lists
WHERE objectTypeId = '0-1'
AND
listName = 'Top subscribers 2021';


戻り値:

LISTID LISTNAME LISTSIZE
771852 Top subscribers 2021 37

list_membership

このビューには、どのレコードがどのリストのメンバーであるかという情報が含まれており、リストの1レコードにつき1行ずつ返されます。例えば、Blog subscribersリストに500件のコンタクトが含まれている場合、テーブルは500行を返します。 

含まれるのはリストIDとオブジェクトIDのみです。詳細については、listsテーブルと結合してください。

このビューでは、以下の列が返されます。

列名 タイプ 説明
LISTID Varchar リストのID。listsテーブルのHS_LIST_ID列に結合します。
OBJECTID Varchar リスト内のレコードのID。
アップデートデータ タイムスタンプ HubSpotでのデータの最終更新日時。列のデータ型について詳細をご確認ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeにインジェストされた日時。 

例1

「Top subscribers 2021」リストに含まれているすべてのコンタクトのIDを取得します。

クエリー:


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

  
戻り値:

OBJECTID
54325098
96805401
6922028
...

例2

トップサブスクライバー2021リストの連絡先の詳細の姓名を取得します。

クエリー:


SELECT
contact.objectId AS objectid,
contact.properties:firstname::string AS firstname,
contact.properties:lastname::string AS lastname
FROM object_with_object_properties contact
INNER JOIN list_memberships ON contact.objectId = list_memberships.objectId
INNER JOIN lists ON list_memberships.listId = lists.listId
WHERE
contact.objectTypeId = '0-1'
AND lists.objectTypeId = '0-1'
AND lists.listName = 'Top Subscribers 2021'


戻り値:

OBJECTID FIRST NAME LAST NAME
54325098 Hobbes Baron
96805401 Milo Gold
6922028 Henry Riley
... ... ...

列のデータ型

Snowflakeでデータを照会する場合、返される列は、照会するデータによって異なります。Snowflakeで、指定したテーブルまたはビューで利用可能な列を確認するには、左側のサイドバーでビューを選択します。

snowflake-sidebar-view-column-details0


特定の列を返すには、クエリーのSELECTフィールドにその列を含めます。例えば、次のクエリーは「FROMOBJECTID」列と「TOOBJECTID」列のみを返します。


SELECT
fromObjectTypeId, toObjectTypeId
FROM association_definitions

以下に、クエリーが返す可能性のある列の一般的な定義を示します。以下に記載されていないその他の定義については、上記の各ビューの定義を参照してください。

  • OBJECTTYPEID:オブジェクトのタイプを表すVARCHAR値。例えば、「0-1」はコンタクトオブジェクト、「0-2」は会社オブジェクト、「2-1232」はアカウントのカスタムオブジェクトを表します。リストでは、リストのタイプ(コンタクトや会社など)を指します。object_and_event_type_definitionsビューを使って、アカウント内のすべてのオブジェクトとイベントのIDを照会できます。
  • OBJECTID:HubSpotアカウント内の特定タイプのオブジェクトの1つのレコードを表すBIGINT番号です。レコードを識別するには、オブジェクトタイプIDとオブジェクトIDの両方を使用する必要があります。
  • INGESTEDAT:データの行がSnowflakeに追加された時点のタイムスタンプであり、TIMESTAMP_NTZ値で表されます。HubSpotでの作成操作または更新操作の論理タイムスタンプとは関係ありません。
    • HubSpotの内部処理により、行の他の列が変更されていなくても、行のインジェスチョンタイムスタンプが更新されることがあります。
    • 最近変更されたデータをコピーするETLプロセスを作成するときには、インジェスチョンタイムスタンプに基づいてクエリーを作成することをお勧めします。また、フローで毎日データをコピーする場合は、遅延を考慮して過去2日間のデータをコピーすることをお勧めします。
  • CREATEDATおよびUPDATEDAT:データがHubSpotで作成または更新された時点の論理タイムスタンプであり、TIMESTAMP_NTZ値で表されます。これらのタイムスタンプは、HubSpotのタイムスタンプと一致します。例えば、HubSpotでレコード作成日に表示される時刻は、Snowflakeの「CREATEDAT」列のタイムスタンプと同じになります。これらはインジェスチョンタイムスタンプとは直接関係がないため、これらのプロパティーに基づいてETLプロセスを作成することは推奨されません。
  • COMBINEDASSOCIATIONTYPE:関連付け定義を一意に識別するVARCHAR値。
Was this article helpful?
This form is used for documentation feedback only. Learn how to get help with HubSpot.