HubSpotのCRM/SFAデータをLooker Studioで可視化する方法【Fivetran, BigQuery】

こんにちはGLASSのエンジニア森です。寒いのが苦手なので春が待ち遠しいこの頃です🌸

弊社ではデータに基づくWEBマーケティングを行うために様々なマーケティング指標を可視化、分析してマーケティング戦略を立案しています。指標の中の一つとしてCRM（Customer Relationship Management：顧客管理）のデータを使って顧客数の増減などをチェックすることがあります。

今回はそんなCRMでよく使われるHubSpotのデータをLooker Studioで可視化する方法をご紹介します。

※手っ取り早く、自社のHubSpotデータを可視化したい方は記事最後の弊社サービスをご活用ください。

なぜHusSpotデータをLooker Studioで可視化するのか？

HubSpotを普段使われている方は「HubSpotにも可視化機能なかったっけ？」と思われるでしょう。おっしゃる通り、HubSpotにはレポート＞ダッシュボードからデータを可視化できる機能があります。

では、なぜあえてLooker Studioで可視化するかといえば、以下の理由があります。

• HusSpotの可視化機能で物足りない部分がありカスタマイズしたい
• 他のマーケティング指標はLooker Studioで可視化しているので、HubSpotもLooker Studioに統合して一か所で確認できるレポートにしてクライアントに提出したい
• HubSpot以外のデータと結合して一貫した分析をしたい

これらに当てはまる方は、ぜひ以下の手順で可視化にチャレンジしてみてください。

どうしたらLooker Studioで可視化できるのか

Looker Studioを使ったことがある方なら分かると思いますが、Looker Studioでは最初に入力となる「データソース」を選択します。この時「データソース」の種類によって「コネクタ」を選びます。

例えばデータソースがGoogle アナリティクス（GA4）の場合、「Google アナリティクス」というコネクタが、Google公式のコネクタ（Google Connector）として用意されています。GA4であればこのコネクタを選択するだけですぐに可視化が可能です。

しかし、HubSpotはGoogle公式のコネクタが用意されていません。コネクタの検索ウィンドウで「HubSpot」と検索すると、サードバーティのコネクタ（Partner Connectors）として、いくつかのETLツールが候補に出てきます。

これらのETLツールをざっと調べましたが、利用には別途サービス契約が必要になります。そのため「Google アナリティクス」のコネクタを使うケースほど簡単ではなく、サービス契約やサービス管理画面での設定が必要になります。

今回はHubSpotに対応したETLツールとして「FiveTran」を使った方法をご紹介します。

ETLツールを使った場合の処理イメージは以下の通りです。

登場する各サービスの紹介

設定方法の説明の前に今回使う各種サービスを一度整理しておきます。

• HubSpot
  • CRM（Customer Relationship Management：顧客管理）として有名。SFA（Sales Force Automation）の機能も有している。
• FiveTran
  • ETL（Extract, Transorm, Load）ツール。様々なデータソースからデータを抽出し、変換、出力する。
• BigQuery
  • データウェアハウス。ETL（今回はFiveTran）でのデータ出力先として利用
• Looker Studio
  • 旧Google Data Portal。BigQueryに接続してデータの可視化を行う

以降ではこれらのサービスをぞれぞれ接続するために必要な設定方法をご紹介します。

①FiveTranの出力先にBigQueryを指定

HubSpotの入力指定の前にFiveTranの出力先としてBigQueryを指定しておきましょう。

マーケティングデータの集約というケースを考えると、入力元は複数、出力先は１か所ということが多いでしょう。なのでHubSpotに限らずこれから様々なデータを集約していく先として最初に出力先を指定しておくと分かりやすい良いと思います。

左メニュー「Destination」を選んで右上「Add destination」をクリック

今回はBigQueryを選択。

Project IDに利用するGCPのプロジェクト名を入力。
データロケーションはGCPのBigQueryで指定しているロケーションに合わせておくといいでしょう。
Time zoneは日本時間にしました。
途中の指示の通り、FiveTranから提供されたサービスアカウント「g-vest-cork@fivetran-production.iam.gserviceaccount.com」をGCPに追加します。手順は以下。

該当プロジェクトのIAMの画面から「アクセス権を付与」を選択。
新しいプリンシパルに先ほどのサービスアカウントを入力。
ロールは以下のようにBigQueryのサービスからBigQueryユーザーを探して保存。

最後にFiveTranの画面に戻って Save & Testをクリック

エラーなくテストが完了したらDestinationの登録は完了です！

②HubSpotのデータをFiveTranに入力

続いて、FiveTranへの入力としてHubSpotのコネクタを追加していきます。

左メニュー「Connectors」を選んで、右上の「Add Connector」をクリック

①で追加したBigQueryが「SELETCTED DESTINATION」に指定されているのを確認（複数のDestinationがある場合はDestinationの選択が必要になります）
右側の検索ウィンドウに「hubspot」と入れてコネクタを検索。「Set up」で設定画面へ。

Destination schemaはBigQueryのデータセットの名前です。自動で作成されるので、事前に作っておく必要はありません。FiveTranから出力されるデータはこのデータセット内のテーブルに格納されます。

「Authorize」をクリック。

HubSpotとの連携許可の画面が出るので、連携したいHubSpotのアカウントを選んで「アカウントを選択」をクリック

連携される内容を確認したら「アプリを接続」をクリック

完了するとAuthorizeをクリックした画面に戻ってきます。「Authentication Succeeded」が出ていれば認証は成功。画面下部に「Save & Test」があるのでクリックして接続テスト。

All connection tests passed!と出て入れば成功。

FiveTranが用意したHubSpotのデータモデルを使うか聞かれます。今回の記事はこのデータモデルを使います。「Yes, build models for me」を選んで「Continue」

「Start Initial Sync」で最初のデータ取得を行う。

しばらく待って「initial sync complete」となったらデータ取得完了。今回の試行では12分と結構かかったので、すぐに完了しなくても焦らず待つのが吉です。

Destinationで指定したGCPプロジェクトのBigQueryに指定したデータセット名でデータが入っていることを確認。ここまででHubSpot→FiveTran→BigQueryの接続は完了です。

ついでにFiveTranの設定も変えておきましょう。FiveTranでは定期的にデータ出力が行われます。

Setupのタブから「Sync Frequency」のデータ出力の同期周期も調整しておきましょう。

「Sync Frequency」の指定内容によってその下の詳細設定の選択肢が変わります。例えば「24 hours」とした場合は、何時を起点にするかを指定できます。

ただ、Setupのタブから設定・テスト中は定期的なデータ出力が不要なケースが多いと思うので、本番運用を開始するまでは、右上「ENAMBLED」の箇所をクリックして「PAUSED」にしておきましょう。必要な時だけ有効化するのがいいでしょう。

③BigQueryのデータをLooker Studioで可視化する

Looker Studioを開いて新しいレポートを作成します。

コネクタとしてBigQueryを選択します。

データソースとして②で出力したデータセットを選択してデータを可視化したいテーブルを選択します。今回はcontactを選んでコンタクト数の遷移を表すグラフを作ってみましょう。

今回は月別の新規コンタクト数の棒グラフとそれを累積した折れ線グラフにしてみます。

• グラフ種別：期間グラフ
• 期間のディメンション：property_createdate
• ディメンション：property_createdate
  • ドリルダウン：有効
  • デフォルトのドリルダウンレベル：３つ出てくる選択肢のうち、真ん中が月単位
• 指標
  • 1つ目：Record Count
  • 2つ目：Record Count
    • キャプチャでは名前を「コンタクト数」「コンタクト数（累積）」に変えています
• スタイルタブ
  • 系列１：棒グラフ、軸は左
  • 系列２：折れ線、累積にチェック、軸は右

無事、月別の新規コンタクト数のグラフが表示されましたね。

次に２テーブルを結合して可視化してみます。結合に必要な情報として、FiveTranから出力されるHubSpotのER図は以下で確認できます。

• Marketing Hub schema
• CRM and Sales Hub schema
• Service Hub schema

今回は取引（=deal）の件数を、担当者ごとと取引ステージごとに表示してみます。

取引の情報はCRM and Sales Hub schemaのDEALテーブルにあります。一方、担当者はOWNERテーブルなので、結合が必要です。今回はLookerStudioの機能でテーブルを結合します。

リソース＞追加済みリソースの管理　から「データソースを追加」を選択します。

contactの時と同様に、「deal」と「owner」をそれぞれデータソースに追加します。

追加したら「グラフを追加」から「積み上げ横棒グラフ」を選択し、レポートに設置します。
その後グラフのデータソースに「deal」を指定して「データを統合」をクリックします。

「別のテーブルを結合する」をクリックして「owner」を選択。それぞれ以下のディメンションを追加します。

• dealテーブル
  • ディメンション
    • owner_id
    • deal_pipeline_stage_id
• ownerテーブル
  • ディメンション
    • owner_id
    • last_name

最後に「結合を設定」で「owner_id」での内部結合を指定したら右下「保存」をクリックします。
グラフの設定を以下の通りに変更します。

• グラフ種別：積み上げ横棒グラフ
• ディメンション：last_name
• 内訳ディメンション：deal_pipeline_stage_id
• 指標：Record Count

２テーブルを結合する例も無事グラフを作ることができましたね。

今回は簡単のためにLooker Studioで結合を行いました。ただし、Looker Studioではテーブルの結合数の上限が５つまでであったり、複雑な結合処理ができるわけではないので注意が必要です。

内容が複雑になる場合は、事前にBigQueryでテーブルを結合するViewを作っておいて、そのViewをLooker Studioのデータソースとして指定するのが良いでしょう。

まとめ

HubSpotのデータをLooker Studioで可視化する手順をご紹介しました。

最初に挙げた通りHubSpotの機能で可視化することも可能ですが、今回のようにBigQueryに書き出すことで、他のマーケティングデータと結合してマーケティング施策のタイミングとコンタクトの増減を確認する、などの様々な活用ができるようになります。ぜひ試してみてください。

また「可視化してみたいけど設定が大変そうだ」「GA4など他のマーケ指標も併せて可視化したい」などあれば、本日ご紹介したHubSpotの可視化を含め、様々なマーケティングデータをすぐに可視化できる弊社サービス「マーケティングダッシュボードGLASS」もご活用ください