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

SnowflakeでHubSpotのデータを照会

更新日時 2022年 1月 4日

対象製品

Operations Hub Enterprise

SnowflakeをHubSpotアカウントに接続した後、SnowflakeのData Shareを使って、SnowflakeアカウントからHubSpotのデータを照会することができます。

クエリはSQLを使用して構築され、以下のようなHubSpotのデータを取得するために使用できます。

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

詳細はこちらHubSpotのCRMデータモデル.

Snowflakeの環境を整える

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

ワークシートレベルでウェアハウス、データベース、スキーマを選択するには

  • ワークシートの一番上の行で、ワークシート・アクション・バーをクリックします。

    スノーフレークワークシート設定0
  • ポップアップウィンドウで
    • Warehouseドロップダウンメニューをクリックして、倉庫を選択します。
    • Database」ドロップダウンメニューをクリックし、HubSpotのデータベースを選択します。
    • Schema」ドロップダウンメニューをクリックして、使用するスキーマを選択します。
      • V2_LIVE:HubSpotデータのSecure Viewsに基づいたライブスキーマです。このスキーマのデータは最大で15分ごとに更新されます。大規模なデータにクエリを実行する場合、クエリが遅くなることがあります。このような場合は、まずETL処理で自分のテーブルにデータをコピーし、そのテーブルにクエリをかけることをお勧めします。 
      • V2_DAILY(BETA):テーブルに基づいてデータを照会するためのデイリースキーマです。このスキーマのデータは1日1回更新されます。このスキーマを使用すると、クエリが高速化され、Snowflakeで直接クエリを行う場合に推奨されます。

      スノーフレークワークシート設定ウィンドウ0

環境が設定されると、データベースやスキーマタイプを手動でクエリに追加しなくても、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の連絡先および企業リスト(BETA)。

詳細はこちらHubSpotのCRMデータモデル.

返却されたデータの制限と順序付け

クエリを作成する際、LIMITORDER BYをクエリに含めることで、返されるデータの量や順番をコントロールすることができます。例えば、すべての連絡先とそのプロパティを照会し、3つの結果のみを返し、連絡先のID順に並べる場合、次のようにクエリを構築することができます。


SELECT objectTypeId, objectId, properties
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-12345object_and_event_type_definitionsの詳細はこちら。

オブジェクトのプロパティを持つオブジェクト

このビューには、HubSpotのレコードのデータが含まれています。このテーブルへのクエリは、オブジェクト内の各レコードに対して1行を返します(例:contactsオブジェクト内のすべてのコンタクトレコード)。すべてのプロパティは、Propertiesカラムに格納されており、これはスノーフレーク バリアント / JSON列に表示されます。 

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTTYPEID バーチャ オブジェクトタイプのID(例:contactsは0-1)。
OBJECTID 数値 レコードのID (例:ID123752のコンタクトレコード)。
プロパティー バリエーション レコードの資産価値
UPDATEDAT タイムスタンプ レコードがHubSpotで最後に更新された日付と時間です。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeに取り込まれた日付と時間。 

例1

すべての連絡先とそのプロパティを取得します。

問い合わせ。


SELECT objectTypeId, objectId, properties
Object_with_object_propertiesより
WHERE objectTypeId = '0-1'


戻ります。

OBJECTTYPEID OBJECTID プロパティー
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 オブジェクト_ウィズ_オブジェクト_プロパティーズ
WHERE objectTypeId = '0-3'
LIMIT 3;


戻ります。

OBJECTID DEALNAME ディールステージ DEALAMOUNT
38960439 案件名1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
321740651 案件名1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219
98392101 案件名1
2021-10-12 13:49:35.219
2021-10-12 13:49:35.219

オブジェクト

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

このビューは、アカウントに大量のデータがあり、object_with_object_propertiesのパフォーマンスが低下している場合に便利です。このテーブルとobject_propertiesテーブルを併用することで、object_with_object_propertiesのデータを複製しつつ、どのプロパティを返すかをコントロールすることができます。

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTTYPEID バーチャ オブジェクトタイプのID(例:contactsは0-1)。
OBJECTID 数値 レコードのID (例:ID123752のコンタクトレコード)。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 カラムのデータタイプについてはこちらをご覧ください。

例1

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

問い合わせ。


SELECT objectTypeId, objectId
オブジェクトから
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 バーチャ オブジェクトタイプのID(例:contactsは0-1)。
OBJECTID 数値 レコードのID (例:ID123752のコンタクトレコード)。
名前 バーチャ プロパティの名前です。
VALUE タイムスタンプ プロパティの値です。
UPDATEDAT タイムスタンプ レコードがHubSpotで最後に更新された日付と時間です。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データが最後にSnowflakeに取り込まれた日付と時間。 

例1

すべての連絡先について、名前、姓、都市のプロパティを取得します。

問い合わせ。


SELECT objectTypeId, objectId, name, value
FROM オブジェクト_プロパティ
WHERE objectTypeId = '0-1'
AND name IN ('firstname', 'lastname', 'city')
ORDER BY objectId;


戻ります。

OBJECTTYPEID OBJECTID NAME VALUE
0-1 10401 firstname ブライアン
0-1 10401 市区町村 ダブリン
0-1 10401 ギャラガー
0-1 23451 firstname フェルナンド
0-1 23451 フィエロ
0-1 25751 firstname チャールズ
... ... ... ...

例 2

すべての連絡先の名字、名前、ライフサイクルステージを取得し、連絡先ごとに1つの行にまとめています。この例では、SQL PIVOT構文を使用しています。

問い合わせ。


SELECT objectId, firstname, lastname, lifecyclestage
から
(
SELECT objectId, name, value
オブジェクトプロパティー
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 ファーストネーム ラストネーム LIFECYCLESTAGE
0-1 54325098 ホッブズ バロン リード
0-1 96805401 ミロ Gold リード
0-1 6922028 ヘンリー ライリー リード
... ... ... ... ...

object_properties_history

このビューには、過去のプロパティ値が含まれています。連絡先については、最新の45個の値が含まれています。その他のオブジェクトについては、最新の20個の値が含まれています。履歴プロパティの値ごとに行を表示します。プロパティの現在の値は、プロパティの履歴における最新の UPDATEDATタイムスタンプによって決定されます。

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTTYPEID バーチャ オブジェクトタイプのID(例:contactsは0-1)。
OBJECTID 数値 レコードのID (例:ID123752のコンタクトレコード)。
名前 バーチャ プロパティの名前です。
VALUE タイムスタンプ プロパティの値です。
UPDATEDAT タイムスタンプ データがHubSpotで最後に更新された日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 

例1

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

問い合わせ。


SELECT objectId, name, value, timestamp
FROM オブジェクト_プロパティ_ヒストリー
WHERE objectTypeId = '0-1'
AND オブジェクトID = 585
AND 名前 = 'hs_predictivescoringtier'
ORDER BY timestamp DESC;

   
戻ります。

OBJECTTYPEID OBJECTID ファーストネーム ラストネーム LIFECYCLESTAGE
0-1 54325098 ホッブズ バロン リード
0-1 96805401 ミロ Gold リード
0-1 6922028 ヘンリー ライリー リード
... ... ... ... ...

例 2

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

問い合わせ。


SELECT
objectId,
properties:firstname::string AS firstname,
property:lastname::string AS lastname
オブジェクトのプロパティから
WHERE object_with_object_properties.objectTypeId = '0-1'
AND objectId IN (
SELECT objectId
オブジェクトプロパティヒストリーから
の中に入っています。object_properties_history.objectTypeId = '0-1'
AND object_properties_history.name = 'lifecyclestage'
AND object_properties_history.value = 'subscriber'
);


戻ります。

OBJECTID ファーストネーム ラストネーム
54325098 ホッブズ バロン
96805401 ミロ Gold
6922028 ヘンリー ライリー
... ... ...

協会への問い合わせ

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

アソシエーションの定義

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

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
複合アソシエーションタイプID バーチャ アソシエーション定義のユニークな識別子です。
CATEGORY バーチャ アソシエーションタイプのソースです。 HUBSPOT_DEFINEDUSER_DEFINEDINTEGRATOR_DEFINEDのいずれか。
ID 数値 そのカテゴリ内の関連定義の識別子です。
fromobjecttypeid バーチャ 関連定義のソースとなるオブジェクトのID。
TOOBJECTTYPEID バーチャ 関連付け定義の宛先となるオブジェクトのIDです。
名前 バーチャ アソシエーション定義の名前。
LABEL バーチャ 協会定義のラベル。
メインアソシエーションの定義 ブール型 アソシエーションの定義がメインタイプであるかどうか。trueの場合、この定義はHubSpotが関連レコードに表示するものです。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 カラムのデータタイプについてはこちらをご覧ください。

例1 

お客様のアカウントで利用可能なアソシエーションの種類を取得します。

問い合わせ。


SELECT combinedAssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM association_definitions
ORDER BY カテゴリ, ID

  
戻ります。

複合アソシエーションタイプ fromobjecttypeid TOOBJECTTYPEID NAME
0-1 0-1 0-2 会社への連絡
0-2 0-2 0-1 会社の連絡先
0-3 0-3 0-1 連絡先
0-4 0-1 0-3 お問い合わせ
0-5 0-3 0-2 deal_to_company
... ... ... ...

例 2

案件と企業の間の利用可能な関連タイプを取得します。

問い合わせ。


SELECT複合AssociationTypeId, fromObjectTypeId, toObjectTypeId, name
FROM ASSOCIATION_DEFINITION
WHERE fromObjectTypeId = '0-3' AND toObjectTypeId = '0-2'
AND isMainAssociationDefinition = true

ORDER BY カテゴリ, ID


戻ります。

複合アソシエーションタイプ fromobjecttypeid TOOBJECTTYPEID NAME
0-5 0-3 0-2 deal_to_company
0-271 0-3 0-2 プライマリーカンパニーへの取引
0-341 0-3 0-2 deal_to_comapny_unlabeled

関連付け

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
複合アソシエーションタイプID バーチャ アソシエーション定義のユニークな識別子です。
FROMOBJECTID 数値 ソースオブジェクトのIDです。
TOOBJECTID 数値 デスティネーションオブジェクトのIDです。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 カラムのデータタイプについてはこちらをご覧ください。

例1

関連するすべての取引と連絡先を取得します。

問い合わせ。


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
団体から
WHERE combinedAssociationTypeId = '0-5'

  
戻ります。

複合アソシエーションタイプ FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335
0-5 6616436082 3866015468
0-5 6690805943 7132752747
... ... ...

例 2 

特定の連絡先(連絡先ID:3005)に関連するすべてのチケットを取得します。

問い合わせ。


SELECT combinedAssociationTypeId, fromObjectId, toObjectId
団体から
WHERE associationCategory = 'HUBSPOT_DEFINED'
AND combinedAssociationTypeId = '0-15'
AND fromObjectId = 3005;


戻ります。

複合アソシエーションタイプ FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

例3 

特定の連絡先(連絡先ID:4464)に関連するすべての案件を取得します。 combinedAssociationTypeIdをハードコーディングする代わりに、この例ではassociation_definitionsで結合しています。

問い合わせ。


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


戻ります。

複合アソシエーションタイプ FROMOBJECTID TOOBJECTID
0-5 6626541373 233620335

オーナー

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OWNERID 数値 ユーザーのオーナーIDです。
USERID 数値 ユーザーのユーザーIDです。この欄がNULLのユーザーは削除されました。
Eメール バーチャ ユーザーのメールアドレスです。
ファーストネーム バーチャ ユーザーのファーストネームです。
ラストネーム バーチャ ユーザーの姓名。
ISACTIVE ブール型 ユーザーが削除されたかどうか。
CREATEDAT タイムスタンプ ユーザーがHubSpotで作成された日付と時間。詳細は、「カラムのデータタイプ」を参照してください。
UPDATEDAT タイムスタンプ ユーザーがHubSpotで最後に更新された日時です。詳細は、「カラムのデータタイプ」を参照してください。

例 1 

すべてのユーザーを取得します。 WHERE isActive = trueフィルターは、削除されたユーザーを返すデータから削除します。

問い合わせ。


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

  
戻ります。

OWNERID ACTIVEUSERID EMAIL ファーストネーム ラストネーム
29584574 4621126 233620335 ホッブズ バロン
30532717 4874784 3866015468 パブロ ウォルターズ
30580321 925511 7132752747 ミロ Gold
... ... ... ... ...

例 2

オーナーIDで特定のオーナーを検索します。

問い合わせ。


SELECT ownerId, userId, email, firstname, lastname
オーナーから
WHERE ownerId = 29584574 ;


リターンズです。

OWNERID ACTIVEUSERID EMAIL ファーストネーム ラストネーム
29584574 4621126 233620335 ホッブズ バロン

例3

すべての連絡先とその所有者を取得します。このクエリは、object_with_object_propertieshubspot_owner_idというコンタクト・プロパティを持つオーナーにレコードを結合します。


SELECT
contact.objectId contact_id,
contact.properties:firstname::string contact_firstname,
contact.properties:lastname::string contact_lastname,
contact.properties:hubspot_owner_id::数字 contact_hubspot_owner_id,
オーナー.ファーストネーム owner_firstname,
オーナー.ラストネーム owner_lastname,
オーナー.メール オーナー.メール
FROM オブジェクトのプロパティを持つオブジェクト contact
インナージョイン オーナー on contact.properties:hubspot_owner_id::number = owner.ownerId
ここでobjectTypeId = '0-1';


リターンズです。

CONTACTID contact_firstname contact_lastname contact_hubspot_owner_id owner_firstname OWNER_LASTNAME OWNER_EMAIL
113834202 リチャード グリーンフィールド 29584574 ホッブズ バロン hobbes.b@business.com
53540801 フォード カール 30241125 ミロ Gold milo.g@business.com
... ... ... ... ... ... ...

パイプラインクエリ

以下では、アカウントで利用可能なパイプラインやそのステージなど、案件やチケットのパイプラインデータを照会する方法をご紹介します。 

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

管理用パイプライン

このビューには、お客様のアカウントにある案件やチケットのパイプラインに関する情報が含まれます。返された各行は、1つのパイプラインを表しています。

このテーブルは、以下の列を返すことができます。

コラム名 タイプ 説明
パイプラインイド(PIPELINEID) バーチャ パイプラインのIDです。
OBJECTTYPEID バーチャ パイプラインが含むことができるオブジェクトのID(例:ディールやチケット)。
LABEL バーチャ HubSpotでのパイプラインの名前です。
DISPLAYORDER 数値 パイプラインがHubSpotで表示される順番です。
アーカイヴ ブール型 パイプラインが削除されたかどうか。
CREATEDAT タイムスタンプ パイプラインが作成されたとき
UPDATEDAT タイムスタンプ データがHubSpotで最後に更新された日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 

例1

利用可能なすべての案件パイプラインを、HubSpotでの注文順に取得します。

問い合わせ。


SELECT objectTypeId, pipelineId, label
パイプラインから
WHERE objectTypeId = '0-3'
かつアーカイブされていない
ORDER BY 表示順。

  
戻ります。

OBJECTTYPEID パイプラインイド(PIPELINEID) LABEL
0-3 1bed503c-37f7-4f51-9d40-7598902673f4 営業パイプライン
0-3 12659678 ライセンス パイプライン
0-3 75e28846-ad0d-4be2-a027-5e1da6590b98 新しいビジネス
... ... ...

例 2

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

問い合わせ。


SELECT

deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label パイプライン名
FROM オブジェクト_プロパティ付きディールズ
INNER JOIN パイプライン
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 営業パイプライン
605457408 フリスキーのリブランド 営業パイプライン
604362473 フレッシュ・ステップ・アプリ・ビルド 営業パイプライン

pipeline_stages

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

このテーブルは、以下の列を返すことができます。

コラム名 タイプ 説明
パイプラインイド(PIPELINEID) バーチャ パイプラインのIDです。
OBJECTTYPEID バーチャ パイプラインが含むことができるオブジェクトのID(例:ディールやチケット)。
LABEL バーチャ HubSpotでのパイプラインの名前です。
DISPLAYORDER 数値 パイプラインがHubSpotで表示される順番です。
アーカイヴ ブール型 パイプラインが削除されたかどうか。
UPDATEDAT タイムスタンプ データがHubSpotで最後に更新された日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 

例 1 

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

問い合わせ。


SELECT pipelineid, stageId, label
FROM pipeline_stages
アーカイブされていない場合
AND pipelineid = (
SELECT pipelineid
パイプラインから
WHERE objectTypeId = '0-3'
かつアーカイブされていない
AND label = 'Sales Pipeline' LIMIT 1)
ORDER BY displayorder;

  
戻ります。

パイプラインイド(PIPELINEID) STAGEID LABEL
941650 941651 連絡した相手
941650 941652 予約済み
941650 941653 請求書の送付
... ... ...

例 2

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

問い合わせ。


SELECT

deals.objectId deal_id,
deals.properties:dealname::varchar deal_name,
pipelines.label パイプライン名。
パイプライン・ステージ・ラベル パイプライン・ステージ名
FROM オブジェクト_オブジェクト_プロパティを持つディールズ
INNER JOIN パイプライン
ON deals.objectTypeId = pipelines.objectTypeId
AND deals.properties:pipeline::string = pipelines.pipelineid
INNER JOIN パイプライン_ステージ
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(パイプライン名 パイプライン・ステージ名
605140072 Meowmix Global Signup 営業パイプライン 送信した契約
605457408 フリスキーのリブランド 営業パイプライン 予約済み
604362473 フレッシュ・ステップ・アプリ・ビルド 営業パイプライン 契約締結
... ... ... ...

property_definitions

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

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTYPEID バーチャ イベントが関連するオブジェクトのID。
名前 バーチャ プロパティの内部名称。
LABEL バーチャ プロパティのラベルです。
説明 バーチャ 物件の説明をします。
タイプ バーチャ プロパティのタイプ(例:文字列、数値、日時)。
FIELDTYPE バーチャ プロパティのフィールドタイプ。プロパティがHubSpotやフォーム上でどのように表示されるかを定義します(例:チェックボックス、セレクト)。
DISPLAYORDER 数値 プロパティの表示順を示します。
グループ名 バーチャ プロパティのグループ名。
OPTIONS バリエーション 列挙型プロパティの場合は、プロパティのオプションの配列で、各オプションの属性(表示順、ラベルなど)を含みます。
CREATEDUSERID 数値 プロパティを作成したユーザーのID。
参照オブジェクトタイプ バーチャ プロパティが参照するオブジェクトのタイプ。オーナーのプロパティはOWNERという値を返します。
CALCULATED ブール型 プロパティが計算されたプロパティであるかどうか。
外部オプション ブール型 物件のオプションが外部システムで定義されているかどうか。
HASUNIQUEVALUE ブール型 プロパティの値がユニークであるかどうか。 APIでプロパティを作成する際にのみ設定できます。
HIDDEN ブール型 プロパティがHubSpotで非表示になっているかどうか。
ショーケースのシンボルマーク ブール型 数値プロパティの値を通貨としてフォーマットするかどうか。
FORMFIELD ブール型 このプロパティをフォームで使用できるかどうか。
readonlydefinition ブール型 プロパティをHubSpotで編集できるかどうか。
READONLYVALUE ブール型 プロパティの値をHubSpotで編集できるかどうか。
HUBSPOTDEFINED ブール型 プロパティがHubSpotによって作成されたのか、ユーザーによって作成されたのかを示します。
アーカイヴ ブール型 プロパティが削除されたかどうか。
CREATEDAT タイムスタンプ プロパティがHubSpotで作成された日付と時間。
UPDATEDAT タイムスタンプ データがHubSpotで最後に更新された日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 

例1

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

問い合わせ。


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

   
戻ります。

NAME LABEL 説明
入力された_sql_stage SQLステージに突入 SQLステージの日数を開始する。
first_conversion_event_name
初回コンバージョン このコンタクトが最初に送信したフォーム
アドレス 番地 連絡先のアドレスです。

イベントクエリ

イベントには、HubSpotの標準的なイベントとカスタムのビヘイビアイベントがあります。各イベントには、Events_eventnameというラベルの付いた独自のビューがあります。

利用可能なイベントは以下の通りです。

  • 広告のクリックイベント(events_ad_clicked
  • メールの開封やクリック(events_opened_email_V2 , events_clicked_link_in_email_v2など)
  • ウェブ解析(events_visited_page

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

コラム名 タイプ 説明
EVENTTYPEID バーチャ パイプラインのIDです。
ID バーチャ パイプラインが含むことができるオブジェクトのID(例:ディールやチケット)。
OBJECTTYPEID バーチャ イベントを完了したオブジェクトの種類。例えば、メールのリンクをクリックした場合、この列には0~1が表示され、連絡先オブジェクトを表します。
OBJECTID 数値 イベントを完了したレコードのIDです。
日付が変わった時に発生した 数値 イベントが発生した時期を表す数字。 OCCUREDAT列と同じデータを含みますが、数値としてフォーマットされており、テーブルのクラスタキーの一部として使用されます。ほとんどの場合、この番号の代わりにOCCUREDATのタイムスタンプを使用することができます。
OCCUREDAT タイムスタンプ イベントの発生時期
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
PROPERTY_* バーチャ イベントのプロパティを含む個々の列。

例1

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

問い合わせ。


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

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

問い合わせ。


SELECT
click.timestamp click_timestamp,
click.property_hs_click_raw_url click_url,
contact.property:firstname::varchar contact_firstname,
contact.property: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 ホッブズ バロン
2018-11-08 09:37:41.400 https://www.website.net/webinar-case-study?utm_source=hs_automation&utm_medium=email&utm_content=55200164 ミロ Gold
2018-11-08 09:37:41.426 https://www.website.net/contact?utm_source=hs_automation&utm_medium=email&utm_content=55200164 ハーロー ビラリ
... ... ... ...

オブジェクトとイベントタイプの定義

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTTYPEID バーチャ パイプラインが含むことができるオブジェクトのID(例:ディールやチケット)。
完全修飾名 バーチャ オブジェクトまたはイベントの名前です。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 カラムのデータタイプについてはこちらをご覧ください。

例 1

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

問い合わせ。


SELECT objectTypeId, fullyQualifiedName
FROM object_and_event_type_definitions;

  
戻ります。

OBJECTTYPEID 完全修飾名
0-1 コンタクト
0-2 会社
0-3 取引
0-4 エンゲージメント
0-5 チケット
0-8 LINE_ITEM
0~11件 コミュニケーション
... ...

リスト・クエリ(BETA

以下では、利用可能な連絡先リストや企業リスト、それらのリストに含まれるレコードなど、リストを照会する方法について説明します。

リスト

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

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
OBJECTTYPEID バーチャ リスト内のレコードの種類(例:連絡先、会社)。
LISTID 数値 リストのIDです。 list-membershipsテーブルのLISTIDカラムにジョインします。
CLASSICLISTID 数値 コンタクトリストに固有のIDで、HubSpotのリストのURLにあるIDと一致します。
LISTNAME バーチャ リストの名前です。
LISTSIZE 数値 リストのレコード数です。
created by userid 数値 リストを作成したユーザーのIDです。
CREATEDAT タイムスタンプ リストがHubSpotで作成されたとき。
アップデートされたユーザーID 数値 一番最近にリストを更新したユーザーのIDです。
UPDATEDAT タイムスタンプ HubSpotで日付が最後に更新された日時です。 カラムのデータタイプについてはこちらをご覧ください。 
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。

例 1

すべての連絡先リストを取得します。

問い合わせ。


SELECT listId, listName, listSize
リストから
WHERE objectTypeId = '0-1';

  
戻ります。

LISTID LISTNAME LISTSIZE
118131 毎週のアウトリーチ 103
66156 ブログのサブスクライバー数 455
771852 トップ・サブスクライバー 2021年 37
... ... ...

例 2

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

問い合わせ。


SELECT listId, listName, listSize
リストから
WHERE objectTypeId = '0-1'
AND
listName = 'Top subscribers 2021';


リターンズです。

LISTID LISTNAME LISTSIZE
771852 トップ・サブスクライバー 2021年 37

list_membership

このビューには、どのレコードがどのリストのメンバーであるかという情報が、リストのレコードごとに1行ずつ表示されます。例えば、Blogの購読者リストに500人の連絡先がある場合、テーブルは500行を返します。 

リストIDとオブジェクトIDのみを含みます。詳細については、一覧表でご確認ください。

このビューは、以下の列を返すことができます。

コラム名 タイプ 説明
LISTID バーチャ リストのIDです。 listsテーブルのHS_LIST_IDカラムにジョインします。
OBJECTID バーチャ リスト内のレコードのIDです。
UPDATEDAT タイムスタンプ データがHubSpotで最後に更新された日付と時間。 カラムのデータタイプについてはこちらをご覧ください。
INGESTEDAT タイムスタンプ データがSnowflakeに最後に取り込まれた日付と時間。 

例1

Top subscribers 2021」に登録されているすべての連絡先のIDを取得します。

問い合わせ。


SELECT objectId
FROM list_memberships
INNER JOIN リスト
ON list_memberships.listId = lists.listId
WHERE lists.objectTypeId = '0-1'
AND lists.name = 'Top Subscribers 2021';

  
戻ります。

OBJECTID
54325098
96805401
6922028
...

例 2

Top subscribers 2021」に登録されている連絡先の詳細情報の姓名を取得する。

問い合わせ。


SELECT
contact.objectId AS objectid,
contact.properties:firstname::string AS firstname,
contact.properties:lastname::string AS lastname
FROM オブジェクト_ウィズ_オブジェクト_プロパティー 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 ファーストネーム ラストネーム
54325098 ホッブズ バロン
96805401 ミロ Gold
6922028 ヘンリー ライリー
... ... ...

カラムのデータタイプ

Snowflakeでデータをクエリする場合、返されるカラムはクエリするデータによって異なります。Snowflakeでは、左サイドバーでビューを選択すると、指定したテーブルやビューで利用可能な列を表示できます。

スノーフレークサイドバー・ビュー・コラム・ディテール0


特定のカラムを返すには、クエリのSELECTフィールドにそのカラムを含めます。例えば、次のクエリはFROMOBJECTIDTOOBJECTIDカラムのみを返します。


SELECT
フロムオブジェクトタイプID, トゥオブジェクトタイプID
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プロセスを構築する際には、取り込みのタイムスタンプに基づいてクエリを行うことをお勧めします。また、毎日データをコピーするフローの場合は、遅延を考慮して前々日のデータをコピーすることをお勧めします。
  • CREATEDATおよびUPDATEDAT:データがHubSpotで作成または更新されたときの論理的なタイムスタンプで、TIMESTAMP_NTZ 値で表されます。これらのタイムスタンプは、HubSpotのタイムスタンプと一致します。例えば、レコードの作成日にHubSpotで表示される時間は、SnowflakeのCREATEDAT列のタイムスタンプと同じになります。これらはインジェストのタイムスタンプとは直接関係がないため、これらのプロパティに基づいてETL処理を行うことは推奨されません。
  • COMBINEDASSOCIATIONTYPE:アソシエーション定義を一意に識別するためのVARCHAR値。