GoogleスプレッドシートとData Studioを使用して、自動化されたHubspotカスタムダッシュボードを作成する

Hubspot、Google Spreadsheet、Google Datastudioを使用した自動ダッシュボードの最終結果

前書き

この記事は、技術チームを必要とせずにMVPのようなものを自動化したいマネージャーを対象としています。この場合、GoogleスプレッドシートとGoogle Data Studioを使用して、自動化されたHubspotカスタムダッシュボードをゼロから作成します。

Make it Groupの創設者の1人であるAlexです。 IoTでスタートアップを構築します。ここからチェックしてください:makeit-group.com

私のコミットメント:

  • それは無料です
  • 簡単です
  • 仕事を終わらせる

コードをコピーして貼り付けるだけでJavascriptを知っている必要はありませんが、ダッシュボードをさらにカスタマイズすることはプラスになります。

Hubspotから連絡先を取得してGoogleスプレッドシートに書き込む方法の簡単な例を探している場合は、私が書いた次の記事を読むことをお勧めします:App Scriptを使用してGoogleスプレッドシートでHubspotから連絡先を取得する方法

Hubspotを使用せずにカスタムダッシュボードを作成する場合は、以前の記事「GoogleスプレッドシートとRaspberry Piを使用してスタートアップのKPI用のカスタムダッシュボードを作成する方法」を確認してください。

状況

当社は、ブランド、製品、またはサービスを世に出すことに多額の投資をしています。しかし、最も適格なリードを生成するものは何ですか?リードはどの時点で転換または消滅しますか?結局のところ、最も効果的なチャネルに投資したいのですが、賢明な決定を下すにはそのデータが必要です。

以前は、指標を使用していませんでしたが、決定は直観に基づいていましたが、ある程度までは問題ありません。しかし、数字が私たちのビジネスについて教えてくれることに驚くかもしれませんし、多くの場合、私たちの直感は私たちに誤ったシグナルを与えます。

掘り下げましょう

ミッションを達成するには、次のことを行う必要があります。

  1. Hubspotで開発アカウントを作成する
  2. Googleスプレッドシートを使用して、そのAPIを使用してHubspotを認証します
  3. APIを使用してHubspotから取引を取得します
  4. Googleスプレッドシートを使用して、必要に応じてデータを準備します
  5. GoogleスプレッドシートをGoogle Data Studioに接続してレポートを作成します

行こう。

Hubspotで開発アカウントを作成する

Hubspotで開発者アカウントを作成することにより、これは非常に簡単です。

アカウントの準備ができたら、クライアントIDとクライアントシークレットを取得するアプリケーションを作成する必要があります。これらは、GoogleスプレッドシートとHubspot CRMアカウントを接続するために使用されます。

以下に示すように、Hubspotアプリの設定で正しいスコープを必ず指定してください。

Hubspotアプリケーションで適切なスコープを示します

Googleスプレッドシートを使用して、そのAPIを使用してHubspotを認証します

さらに情報が必要な場合は、このチュートリアルを使用しましたが、各ステップを実行します。

まず、Googleドライブに新しいGoogleスプレッドシートを作成する必要があります。スプレッドシートが開いたら、[ツール]> [スクリプトエディター]に移動して、ブラウザーに新しいタブを開きます。

OAuth2を使用して認証するためのライブラリを追加する必要があります。

  1. メニュー項目[リソース]> [ライブラリ...]をクリックします
  2. [ライブラリの検索]テキストボックスに、スクリプトID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDFを入力し、[選択]ボタンをクリックします。これはプロジェクトにライブラリを追加する最も簡単な方法ですが、プロジェクトにコードを手動で追加する場合はこの記事をご覧ください。
  3. ドロップダウンボックスでバージョンを選択します(通常、最新バージョンを選択するのが最善です)。
  4. [保存]ボタンをクリックします。

それでは、少しずつコードを書いてみましょう。 JSの専門知識を持っている技術者は、ここで完全なコードを読むだけでいいかもしれません。他のものについては、単に手順に従ってください。

最初に、認証部分にいくつかの変数を定義する必要があります。

var CLIENT_ID = '…'; //クライアントIDを入力します
var CLIENT_SECRET = '…'; //クライアントシークレットを入力します
var SCOPE = 'contacts';
var AUTH_URL = 'https://app.hubspot.com/oauth/authorize';
var TOKEN_URL = 'https://api.hubapi.com/oauth/v1/token';
var API_URL = 'https://api.hubapi.com';

認証部分を処理する次の関数を追加しましょう。

関数getService(){
   OAuth2.createService( 'hubspot')を返します
      .setTokenUrl(TOKEN_URL)
      .setAuthorizationBaseUrl(AUTH_URL)
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)
      .setCallbackFunction( 'authCallback')
      .setPropertyStore(PropertiesService.getUserProperties())
      .setScope(SCOPE);
}
関数authCallback(request){
   var service = getService();
   var authorized = service.handleCallback(request);
   if(authorized){
      return HtmlService.createHtmlOutput( 'Success!');
   } else {
      return HtmlService.createHtmlOutput( 'Denied。');
   }
}

次に、認証を実行する関数が必要です。

関数authenticate(){
   var service = getService();
   if(service.hasAccess()){
      //…ここで何をする必要があるか…
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log( '次のURLを開き、スクリプトを再実行します:%s'、authorizationUrl);
   }
}

認証部分をテストしましょう。クライアントIDとクライアントシークレットを入力し、認証機能を実行する場合、以下を行う必要があります。

  1. ログ(表示>ログ)に表示される認証URLを取得します。
  2. ブラウザでそのURLを開くと、Hubspotアカウントが表示されます。
  3. スプレッドシートに接続するHubspotアカウントを選択します

お疲れ様でした!

APIを使用してHubspotから取引を取得します

シンプルにしましょう。そのデータを取得してスプレッドシートに入力するために最低限のことをしましょう。次に、Googleスプレッドシートのツールを使用して、必要に応じてデータを準備します。

(開発者であれば、夢中になってJavascriptでデータを操作したくなるでしょうが、車輪を再発明せずにGoogleスプレッドシートの組み込み機能を使用することをお勧めします。)

パイプラインステージを取得することから始めましょう。次の関数をコピーして、既存のコードの下のスクリプトエディターに貼り付けることができます。

関数getStages(){
  // Hubspotへの認証を準備します
  var service = getService();
  var headers = {headers:{'Authorization': 'Bearer' + service.getAccessToken()}};
  
  // APIリクエスト
  var pipeline_id = "default"; //ここにパイプラインIDを入力します。
  var url = API_URL + "/ crm-pipelines / v1 / pipelines / deals";
  var response = UrlFetchApp.fetch(url、headers);
  var result = JSON.parse(response.getContentText());
  var stage = Array();
  
  // Hubspotにある可能性のあるさまざまなパイプラインをループします
  result.results.forEach(function(item){
    if(item.pipelineId == pipeline_id){
      var result_stages = item.stages;
      // displayOrderでステージをソートしましょう
      result_stages.sort(function(a、b){
        return a.displayOrder-b.displayOrder;
      });
  
      //使用されているすべてのステージ(IDとラベル)を配列に入れましょう
      result_stages.forEach(function(stage){
        stage.push([stage.stageId、stage.label]);
      });
    }
  });
  
  戻りステージ;
}

このコードは次のことを行います。

  1. Hubspotに接続します。
  2. ステージを含む完全なデフォルト*パイプライン構成を取得します。
  3. それに応じてステージがソートされます。
  4. 結果を後で使用できる配列に入れます。

* Hubspot CRMでは、複数のパイプラインを構成できます。セットアップした他のパイプラインIDによってデフォルトを変更する必要があります。 HubspotのCRMパイプラインAPIに関する詳細情報が必要な場合は、このページをご覧ください。

これで、パイプラインを流れるすべての取引を取得することもできます。次のコードがそれを処理します。

注:Hubspot構成の取引にカスタムフィールド「ソース」を追加しました。これはHubspotのデフォルトのフィールドではありません。そのため、これを取引に追加するか、取引に必要な他のカスタムフィールドによって「ソース」を変更することをお勧めします。

関数getDeals(){
   // Hubspotへの認証を準備します
   var service = getService();
   var headers = {headers:{'Authorization': 'Bearer' + service.getAccessToken()}};
   //ページネーションを準備します
   // Hubspotでは、リクエストごとに最大250件の取引を行うことができます。
   //すべての取引を取得するまで、複数のリクエストを行う必要があります。
   var keep_going = true;
   var offset = 0;
   var Deals = Array();
   while(keep_going){
      //取引から3つのプロパティを取得します:取引のソース、ステージ、金額
      var url = API_URL + "/ deals / v1 / deal / paged?properties = dealstage&properties = source&properties = amount&limit = 250&offset =" + offset;
      var response = UrlFetchApp.fetch(url、headers);
      var result = JSON.parse(response.getContentText());
      //ページネーションを停止する必要があります
      keep_going = result.hasMore;
      オフセット= result.offset;
      //取引ごとに、stageId、source、およびamountを取得します
      result.deals.forEach(function(deal){
         var stageId =(deal.properties.hasOwnProperty( "dealstage"))? deal.properties.dealstage.value: "不明";
         var source =(deal.properties.hasOwnProperty( "source"))? deal.properties.source.value: "unknown";
         var amount =(deal.properties.hasOwnProperty( "amount"))? deal.properties.amount.value:0;
         deal.push([stageId、source、amount]);
      });
   }
   リターン取引;
}

このコードは次のことを行います。

  1. Hubspotに接続します。
  2. すべての取引を取得し、次のプロパティを追加します:取引のステージ、ソース、および金額。
  3. 結果を後で使用できる配列に入れます。

取引を取得する方法の詳細については、こちらをご覧ください。

OK、配列のデータがありますが、Googleスプレッドシートを使用してそれらを操作できるようにするには、データをシートに印刷する必要があります。最初にスプレッドシートに2つのシートを作成し、それらをステージと取引と呼びましょう。

var sheetNameStages = "Stages";
var sheetNameDeals = "Deals";
関数writeStages(stages){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(sheetNameStages);
   //ヘッダーを入れて、テーブルにステージを追加しましょう
   var matrix = Array(["StageID"、 "Label"]);
   マトリックス= matrix.concat(stages);
   //表をスプレッドシートに書き込む
   var range = sheet.getRange(1,1、matrix.length、matrix [0] .length);
   range.setValues(matrix);
}
関数writeDeals(deals){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(sheetNameDeals);
   //ヘッダーを追加して、取引をテーブルに追加しましょう
   var matrix = Array(["StageID"、 "Source"、 "Amount"]);
   マトリックス= matrix.concat(deals);
   //表をスプレッドシートに書き込む
   var range = sheet.getRange(1,1、matrix.length、matrix [0] .length);
   range.setValues(matrix);
}

このコードは、それぞれ次の手順でステージと取引を適切なシートに書き込みます。

  1. 右のシートに接続します。
  2. 適切な列を持つテーブルを準備します。
  3. テーブルにデータを追加します。
  4. データをシートに書き込む。

それでおしまい!ほとんどのコードは完了しました。プロジェクトトリガーを使用して繰り返し呼び出すことができる単純なルーチンを作成するだけです。

関数refresh(){
   var service = getService();
   if(service.hasAccess()){
      var stage = getStages();
      writeStages(stages);
      var Deals = getDeals();
      writeDeals(deals);
   } else {
      var authorizationUrl = service.getAuthorizationUrl();
      Logger.log( '次のURLを開き、スクリプトを再実行します:%s'、authorizationUrl);
   }
}

このルーチンは:

  1. Hubspotに接続してみてください
  2. 成功すると、データを取得して適切なシートに印刷します
  3. 成功しない場合(おそらくスクリプトを初めて実行した場合)、ログに認証URLを出力し(表示>ログ)、ブラウザにコピーして貼り付ける必要があります。

これで、最新のデータを取得し続けるために、1時間ごとにrefresh()関数を実行するようスクリプトに指示できます。 [編集]> [現在のプロジェクトのトリガー]に移動できます。

Googleスプレッドシートでプロジェクトトリガーを準備して、データを繰り返し更新する

Googleスプレッドシートだけでなく、便利なようにすべてのコードをここで見つけることができます。

スクリプトをテストして、何が起こるかを見てみましょう。スクリプトを初めて実行するときは、前に説明したようにHubspotに対して認証する必要があることを忘れないでください。次に、コードがエラーなしで実行された場合(私が仕事を適切に行った場合はそうなるはずです;-))、Googleスプレッドシートに移動して、適切なシートにデータが入力されていることを確認できます。

Googleスプレッドシートを使用して、必要に応じてデータを準備します

上記のスクリプトを使用して、Hubspotからデータを取得できました。これで、Googleスプレッドシートを使用して、Google Data Studioに送信する前に必要なデータを準備できます。だから、私は何を知りたいですか?

  • パイプラインにボトルネックはありますか?つまり、パイプラインのステージごとの現在の取引数はいくらですか。
  • キャッシュフローを予測できるように、パイプラインにお金が十分にプロビジョニングされていますか?つまり、各段階での現在の金額はいくらですか。
  • どのチャネルが最適に機能し、どのチャネルにさらに投資すべきですか?言い換えると、ソースごとに生成されるコンバージョン率と金額はいくらですか。

これらすべての数値と、Hubspotから取得したデータについては、いくつかのピボットテーブルを作成するだけです。作成するには、Googleスプレッドシートに移動し、[データ]> [ピボットテーブル]をクリックします。

ただし、最初のピボットテーブルを作成する前に、ステージシートを取引シートにリンクする必要があります。実際、取引シートにはStageIDが含まれていますが、ステージのラベルも含む完全なテーブルが必要です。そうでない場合、レポートには実際には使いにくいIDのみが表示されます。

Dealsシートで、D列に移動し、列の最初のセルに次の行を追加します。

= ARRAYFORMULA(IFERROR(VLOOKUP(A:A、Stages!A:B、2、FALSE)、 "Unknown"))

この行を分解しましょう:

  1. VLOOKUP(A:A、Stages!A:B、2、FALSE):A列をStagesのテーブルと一致させ、2列目の値を取得してください。
  2. IFERROR:一致するものが見つからない場合は、「不明」としてお知らせください
  3. ARRAYFORMULA:このルールをすべての列に適用します。
VLOOKUPを使用してステージをStageIDにリンクし、適切なラベルを取得する

さて、準備ができました。最初のピボットテーブルを作成して、ステージごとの現在の取引数を取得しましょう。

新しいピボットテーブルを作成すると、ステージでカウントを変更できる新しいシートが生成されます:カウント。ピボットテーブルエディターで次の手順を実行します。

  1. データ範囲を選択します。データ範囲は、取引シートのデータ範囲全体にする必要があります。
  2. 行を追加して、StageNameを選択します
  3. 値を追加して、StageIDを選択し、集計方法を設定します:COUNTA

これで、ステージごとの取引額を含む表が作成されました。 Google Data Studioレポートでこの表を使用します。

Googleスプレッドシートでピボットテーブルを作成して、パイプラインのステージごとの取引数を取得する

同じことを各段階で金額を取得するために行うことができます。新しいピボットテーブルを作成しますが、StageIDを値として選択する代わりに、金額を取得し、集計方法:SUMを設定します。

Googleスプレッドシートでピボットテーブルを作成して、パイプラインのステージごとの取引量を取得する

ソースごとのコンバージョン率を取得するには、次のパラメーターを使用して次のピボットテーブルを作成します。

  • 行:ソース
  • 列:StageName
  • 値:StageID Sumcountize by COUNTA

次に、ピボットテーブルの最後に列を追加して、コンバージョン率を計算します。これはステージによって異なりますが、私の場合、コンバージョンが発生するステージはクローズドウォンと呼ばれます。したがって、Closed Won列をGrand Total列で割ることにより、コンバージョン率を計算します。

Googleスプレッドシートでピボットテーブルを作成してコンバージョン率を計算する

最後に、ソースによって生成された金額を取得するために、次のパラメーターを使用して最後のピボットテーブルを作成します。

  • 行:ソース
  • 列:StageName
  • 値:合計金額

Closed Won列は、各ソースによって生成された金額を示します。

これまでに必要なすべてのデータセットを用意しました。最後の部分については、Google Data Studioに行きましょう;-)

GoogleスプレッドシートをGoogle Data Studioに接続してレポートを作成します

上記の手順では、OAuth2を使用するAPIを使用してHubspotからデータを取得しました。次に、Googleスプレッドシートでデータを準備しました。これで、Google Data Studioを使用して素敵なカスタムダッシュボードにそのデータを表示する準備ができました。

それは非常に簡単なので、私はあまり詳細に立ち入るつもりはありません。始めるための基本をあなたに教えます。まず、新しいレポートを作成する必要があります。次に、データソースを接続する必要があります。 Data Studioでは、さまざまなデータソース、特にGoogleスプレッドシートを選択できます。これは明らかに必要です。

もちろん、特にGoogleアナリティクス、Adwords、または既にビジネスで使用している他のツールもすべてチェックする必要があります。コミュニティコネクタもあります(残念ながら、現在までHubspotコネクタはありません)。

ソースごとのコンバージョン率を示す最初の棒グラフを作成しましょう。

プロットを追加すると、データソースを編集できるメニューが右側に表示されます。この時点で、変換率を含むピボットテーブルを作成した[ソース:カウントと変換率]シートからデータソースを追加する必要があります。

Googleスプレッドシートデータソースを追加すると、選択したシートから検出されるさまざまなフィールドを設定できます。通常、数値を検出すると、集計列にSUMが自動入力されます。ほとんどの場合、これらのすべてのフィールドを「なし」に切り替えます。

データソースをGoogle Data Studioに接続するGoogle Data Studioで棒グラフを作成する

接続が確立されたら、適切なディメンションとメトリックを追加する必要があります。たとえば、ディメンションとコンバージョン率のソースをメトリックとして設定して、上記のグラフを取得できます。

Googleスプレッドシートで作成した各シートまたはピボットテーブルのデータソースをData Studioに追加する必要があります。すべてのデータソースを追加したら、上記の手順を複製して、カスタムダッシュボードに追加するすべてのグラフ、スコアカード、またはその他の凝ったツールを作成できます。

おまけ:日々のパフォーマンスの進化を見るために、時間をかけてデータを取得するのはどうですか?

たとえば、リードが時間とともにどのように進化するか、チームが改善または不足している場所、数日後にリードを送信しなかったためにレビューするチャネル、または最後に販売パイプラインに与えた影響を知りたい先週、チームの営業担当者の1人が病気になりました。

経時的にデータを記録する簡単な方法があります。これを実現するためにスクリプトに追加できるコードは次のとおりです。

関数logSources(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName( "Sources:Count&Conversion Rates");
   var getRange = sheet.getRange( "M3:M13");
   var row = getRange.getValues();
   row.unshift(新しい日付);
   var matrix = [row];
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName( "Log:Sources");
   var lastRow = sheet.getLastRow();
   var lastCol = sheet.getLastColumn();
   //スプレッドシートの最後に書く
   var setRange = sheet.getRange(lastRow + 1,1,1、row.length);
   setRange.setValues(matrix);
}

以下がその機能です。

  1. 右側のシートの右側の列をコピーし、配列に保存します。
  2. 配列の先頭に日付を追加します。
  3. Log:Sourcesシートの最後の行のデータを選択して貼り付けます。

ステージでも同じことができます。

最後に、データが1日に1回記録されるようにプロジェクトトリガーを追加できます。

その後、上記で説明したのと同様に、新しいデータをGoogle Data Studioにプラグインできます。

最終結果

上記の手順を実行すると、最終的なカスタムダッシュボードのスクリーンショットが以下に表示されます。それは非常に簡単で、すでに販売パイプラインで何が起こっているのかについて非常に素晴らしい概要を得ることができます。数日待って、時間の経過とともに進化を確認し、営業チームのパフォーマンスを確認する必要があります。

あなたの便宜のために、以下の使用されたドキュメントを見つけてください:

  • 完全なGoogle Appスクリプト:ここをクリック
  • Googleスプレッドシートのサンプル:ここをクリック
  • Google Data Studioダッシュボードのサンプル:ここをクリック

次のステップ ?

このチュートリアルを書いている時点では、Hubspot CRMをDatastudioに接続するためのコミュニティコネクタはありません。他の人がそれから利益を得ることができるように、実際に構築することは良いことかもしれません。ただし、Googleスプレッドシートを中間ステップとして使用すると、ニーズに合わせて柔軟に対応できるようになります。 Hubspotがデータソースであり、Data Studioがダッシュボードである場合、データを送信してData Studioで表示する前に、スプレッドシートを「頭脳」として解釈してデータを解釈することを検討できます。

あなたの洞察を得るためにコメントしてください。

質問や提案? LinkedInで私に連絡するか、こちらでチェックしてください。