SnowflakeでHubSpotデータを照会する

SnowflakeをHubSpotアカウントに連携すると、Snowflakeの「Data Share」機能を使用して、SnowflakeアカウントからHubSpotのデータをクエリできるようになります。

データラグとSnowflakeのアカウントリージョン

15分ごとに更新されるV2_LIVEスキーマと、毎日更新されるV2_DAILYスキーマにアクセスすることができます。

注： V2_LIVEスキーマの以下の表示条件は、毎日更新されます： association_definitions、owners、pipelines、pipeline_stages。

Snowflake環境を設定する

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

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

1. ワークシートの最上部の行で、［Worksheet actions］バーをクリックします。
   
   
2. ポップアップウィンドウで、次の操作を行います。
   • ［Warehouse］ドロップダウンメニューをクリックして、ウェアハウスを選択します。
   • ［Database］ドロップダウンメニューをクリックして、HubSpotデータベースを選択します。
   • ［Schema］ドロップダウンメニューをクリックして、使用するスキーマを選択します。
     • V2_DAILY: テーブルに基づいたデータを照会するための日次スキーマ。このスキーマのデータは1日1回更新されます。このスキーマを使用すると、クエリーが高速になります。Snowflakeで直接照会する場合に推奨されます。
     • V2_LIVE： HubSpotデータのSecure Viewsに基づくライブスキーマです。このスキーマのデータは最大で15分ごとに更新されます。大規模なデータセットを照会する場合、クエリーに時間がかかることがあります。このような場合は、 まずETLプロセスで自分のテーブルにデータをコピーし、そのテーブルを照会することをお勧めします。
     
     
     

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

データベースとスキーマの種類を手動でクエリーに追加するには、データベース名の後にスキーマを追加して、クエリーの先頭に追加します。FROMのフィールドを使用します。例えば、次のような使い方が考えられます。

データの概要

クエリーはSQLで構築され、以下のようなHubSpotのデータを取得することが可能です：

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

HubSpotのCRMデータモデルに関する詳細はこちらをご覧ください。

注：データシェアを進化させるにあたり、変更点を導入し、中には破壊的な変更もあります。HubSpot の開発者向け変更ログを配信登録することを強くお勧めします。これらの変更点は、アップデートに先立ち、常に変更履歴に掲載されます。

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

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

列のデータ型

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

ご注意ください。このドキュメントの残りの部分では、テーブルとビューの両方に適用されるコンテキストでは、データベースオブジェクトという用語を使用します。

特定の列を返すには、クエリーの SELECT フィールドにその列を含めます。たとえば、次のクエリでは、OBJECTIDおよびUpdatedAt列のみを返します


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

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

データ型を変換する

VARCHAR列を持つクエリーから、別の型にした方が望ましいデータが返ってくることがあります。このような場合、Snowflakeの関数を使用してVARCHARデータ型を目的の型に変換することをお勧めします。例えば、オブジェクトレコードのプロパティーは、ほとんどのデータベースオブジェクトではVARCHARデータ型として格納されるが、時には数値やdatetime値を含むこともある。
try_to_number , try_to_timestamp_ntzなどの関数で、VARCHAR データをそれぞれNUMBER型、DATETIME型に変換することができる：


SELECT objectid, value AS amount_str, try_to_number(value) AS amount
FROM object_properties
WHERE objecttypeid='0-3' AND name='amount'
LIMIT 1



オブジェクトのクエリー

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

オブジェクトデータは、共有データベースオブジェクトで利用可能です。特定のオブジェクトタイプを取得するには、objectTypeId特定のオブジェクトタイプを取得します。各レコードの現在のプロパティが必要な場合は、OBJECT_WITH_OBJECT_PROPERTIES各レコードの現在のプロパティーが必要な場合に使用します。プロパティーの値は、propertiesJ SON 列に格納されます。コアレコード属性のOBJECTSをコアレコードの属性に、OBJECT_PROPERTIESまたはOBJECT_PROPERTIES_HISTORYプロパティーごとのデータには``を使用します。

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

詳細は、下記のobject_with_object_properties、object_properties、object_properties_historyをご覧ください。

object_with_object_properties

このデータベースオブジェクトには、HubSpotのレコードのデータが含まれています。このデータベースオブジェクトへの問い合わせは、オブジェクトタイプ内の各レコードに対して1行を返します（例：contactsオブジェクトタイプ内のすべてのcontactレコード）。全てのプロパティーは、Snowflake Variant / JSON列であるProperties列に格納されます。 

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

ご注意：2022年1月25日、object_with_object_propertiesテーブルおよびビューのtimestampという列は、HubSpotでオブジェクトレコードが最後に更新された日時を表し、updateatになります。これは、残りのデータ共有で、datetime データがどのように表現されているかを合わせるためです。

これは画期的な変更であるため、新しい列 updatedatがテーブルとビューに追加されまし た。今後90日間、この2つの新しい列は、置き換えようとしている列であるtimestampと共存することになる。

90日後の2022年1月25日に、テーブルとビューから列のタイムスタンプが削除されます。この日付以降、タイムスタンプ列を使用してobject_with_object_propertiesテーブルまたはビューを照会しようとすると、失敗します。

例1

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

クエリー：

戻り値：

例2

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

クエリー：

戻り値：

objects

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

このデータベースオブジェクトには、プロパティー値を除いた現在のオブジェクトデータが含まれています。コアレコードの属性のみが必要な場合はこれを使用し、特定のプロパティーが必要な場合は OBJECT_PROPERTIES と結合して使用してください。このデータベース・オブジェクトを object_properties データベース・オブジェクトと一緒に使うことで、 object_with_object_properties のデータを複製しつつ、どのプロパティーが返されるかを 制御することが可能になります。

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

戻り値：

object_properties

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

戻り値：

例2

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

クエリー：

戻り値：

object_properties_history

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

 
戻り値：

例2

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

クエリー：

戻り値：

関連付けのクエリー

関連付けデータは以下の場所から入手可能ですASSOCIATIONS .特定の関連付けタイプを取得するには、combinedAssociationTypeId特定の関連付けタイプを取得します。参加するASSOCIATIONSを使用してASSOCIATION_DEFINITIONSと組み合わせて、関連するオブジェクト型と関連付け定義を特定します。

association_definitions

このデータベースオブジェクトには、HubSpotオブジェクト間で利用可能なすべての関連タイプに関する情報が含まれており、関連タイプごとに1行ずつあります。このデータベースオブジェクトとassociationsデータベースオブジェクトを結合して、レコード固有の関連情報を取得することができます。

同じオブジェクトの間に複数のタイプの関連付けを設定できます。メインの関連付けタイプは、レコードが関連付けされたときにHubSpotに表示されるタイプで、以下のような関連付けを使用します。ISMAINASSOCIATIONDEFINITIONですtrue .

このデータベースオブジェクトは、以下の列を返すことができる。

例1 

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

クエリー：

 
戻り値：

例2

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

クエリー：

戻り値：

関連付け

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

このデータベースオブジェクトには、すべての関連付けタイプに関する関連レコードが含まれています。「」で絞り込みcombinedAssociationTypeIdで絞り込み、ASSOCIATION_DEFINITIONS関連付けのメタデータを必要とします。

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

 
戻り値：

実施例 2 

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

クエリー：

戻り値：

例3 

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

クエリー：

戻り値：

担当者からの問い合わせ

owners

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1 

すべてのユーザーを取得します。WHERE archived = falseフィルターは、返されるデータから削除済みユーザーを削除します。

クエリー：

 
戻り値：

実施例 2

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

クエリー：

戻り値：

例3

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

戻り値：

チームへの問い合わせ

チーム

HubSpotのチームを使用して、組織の目的やレポートの目的に合わせてユーザーをグループに分けることができます。メインチームと追加チームを設定できます。

このデータベースオブジェクトには、HubSpotアカウントのチームに関する情報が含まれています。 

このデータベースオブジェクトは、以下の列を返すことができる。

例1

全てのチームを取得します。

クエリー：

戻り値：

owner_team

このデータベースオブジェクトには、担当者とHubSpotアカウントのチームの対応付けに関する情報が含まれています。 

このデータベースオブジェクトは、以下の列を返すことができる。

例1

指定したチームの全ての担当者を取得します。

クエリー：

SELECT teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574

返されるデータ

パイプラインのクエリー

以下では、取引パイプラインおよびチケットパイプラインデータ（アカウント内で利用可能なパイプラインやそのステージなど）をクエリする方法について解説します。

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

pipelines

このデータベースオブジェクトは、アカウント内の取引およびチケットパイプラインに関する情報を含んでいます。返される各行はそれぞれ1つのパイプラインを表しています。

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

例1

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

クエリー：

 
戻り値：

例2

object_with_object_propertiesを使用して、特定のパイプライン内の案件を取得します。

戻り値： 

pipeline_stages

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1 

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

クエリー：

 
戻り値：

例2

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

クエリー：

戻り値：

プロパティーに関するお問い合わせ

property_definitions

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

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

 
戻り値：

イベントクエリー

イベントには、HubSpotの標準イベントとカスタム行動イベントが含まれます。各イベントには、それぞれデータベース オブジェクトがあり、ラベルは次のようになっています。events_eventname .

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

例1

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

クエリー：

戻り値：

実施例 2

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

クエリー：

戻り値：

オブジェクトとイベントタイプのクエリ

object_and_event_type_definitions

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

このデータベースオブジェクトは、以下の列を返すことができる。

例1

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

クエリー：

 
戻り値：

セグメントクエリ

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

セグメント

このデータベースオブジェクトには、HubSpotのコンタクトおよび会社のセグメントに関する情報が含まれています。セグメントごとに1つの行を返します。列にはセグメントの詳細(セグメントの名前、サイズ、含まれるレコードのオブジェクトタイプなど)が含まれます。各セグメント内の個々のレコードに関する情報は含まれません( segment_membershipを参照)。 

このデータベースオブジェクトは、以下の列を返すことができる。

例1

すべてのコンタクトセグメントを取得します。

クエリー：

 
戻り値：

例2

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

クエリー：

戻り値：

segment_membership

このデータベース オブジェクトには、どのレコードがどのセグメントのメンバーであるかという情報が含まれており、セグメント内のレコードごとに 1 つの行があります。例えば、500件のコンタクトを含むブログ登録読者セグメントがある場合、データベースオブジェクトは500行を返します。 

含まれるのはセグメントIDとオブジェクトIDのみです。詳細については、 segments データベースオブジェクトと結合してください。

このデータベースオブジェクトは、以下の列を返すことができる。

例1

「Top subscribers 2021 」セグメントに含まれる全てのコンタクトのIDを取得します。

クエリー：

 
戻り値：

例2

「Top subscribers 2021 」セグメントに含まれるコンタクトの詳細の名前と姓を取得します。

クエリー：

戻り値：

よくある質問

HubSpotアプリからレポートと同様のデータを作成したいのですが、どのようにすればよいですか？

一般的に、比較対象とするレポートに含まれる列を確認することをお勧めします。レポートのグラフ化が表形式でない場合、表に変換すると、レポートに含まれる列が表示されます。これらの列は通常、オブジェクト・タイプまたはイベント・タイプのプロパティーである。次のステップは、列が対応するオブジェクトタイプのプロパティーを把握することです。property_definitionsに問い合わせることで可能です。

一部のプロパティー名は、通常、アプリ上でより説明的な名前に解決されるため、必ずしも完全に一致しない場合があります。このような場合、property_definitionsのlabel（あるいはdescription）列でLIKE演算子を使ったあいまい検索が有効です。

列がどのオブジェクトタイプに属するかを決定した後、何を問い合わせるかを決定するのは簡単なことです。クエリobject_with_object_propertiesおよびobjectTypeIdそのタイプに対してフィルタリングします。properties JSON 列から必要なプロパティー値を読み取ります。

このデータ型を別のデータ型にリンクさせるにはどうすればよいですか（例：キャンペーン用のフォームを取得する方法）？

各キャンペーンに関連付けられたフォームを取得するには、OBJECT_WITH_OBJECT_PROPERTIES、ASSOCIATIONS、およびASSOCIATION_DEFINITIONSを結合します。

HubSpotとSnowflakeの連携状況を知るにはどうすればよいですか？

HubSpotとSnowflakeの連携の障害や問題についてのリアルタイムのステータスアップデートを受け取るには、data_share_statusビューにクエリーします：

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

と表示されます：

このビューでは、連携ステータスの更新を逆時系列で提供します。このクエリーの結果をダッシュボードに連携することで、連携のパフォーマンスを追跡し、ダウンタイムや停止を検出することができます。