Google Search ConsoleのデータをBigQueryに自動同期し、Googleデータポータル(旧Data Studio)で可視化する


## 概要
Google Search Console（サーチコンソール）はGoogle提供のインターネット検索分析ツールです。　Google Search Consoleを使うことでGoogle検索での表示回数・掲載順位を把握したり、SEO上の問題点を修正してWebサイトの注目度を高めることができます。
しかし、絶えず集められるデータをその都度分析し、最新の状態に保つのには大変な工数がかかります。

そこで今回は[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)というデータ統合サービスを使い、Google Search Consoleデータの抽出自動化＋DWH（データウェアハウス）への統合＋可視化までやってみようと思います。

今回、データの転送手段として使用する[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)は、Google Search Consoleの他にも、様々な広告・CRM・DBなどのデータソースにも対応しています。
[trocco®️の使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2216922/3d80c3b5-83e2-e020-e41b-85410d841510.png)


## ゴール
Google Search Consoleのデータを下のようにBigQueryに統合し
![bigquery転送.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/b75f6073-00bf-d3e1-8175-76878a7ea06c.png)


データポータルで可視化します(作成後は自動で最新値に更新することも可能です）
![円グラフ完成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/08e5a8d6-bae7-dd68-408b-49b0f7fab5fb.png)







## こんな人におすすめ
・Search ConsoleのデータをBigQueryに自動的に蓄積させたい
・Google検索での表示回数・掲載順位について自動的に分析できるようにしたい
・データの件数が多すぎてGoogle Search Consoleの標準のレポート機能では分析ができない

## 1. DWHと同期する手段の選定
### 1-1. DWHの選定
まずはデータを集約する場所である、DWH（データウェアハウス）を選定します。

* Amazon Redshift
* Google BigQuery
* MySQLやPostgreSQL

今回はGoogle BigQueryを利用することにします。

### 1-2. Google Search ConsoleのデータをGoogle BigQueryに転送する４つの方法
Google BigQueryにデータを集約することが決まったので、続いては転送するための手段を検討します。

1. Google Search ConsoleのデータをGoogle Cloud Storage経由でBigQueryに登録する
1. BigQuery データコネクタであるコネクテッド シートを使用する（ただしGsuiteのプランがEnterprise以上である必要がある）
1.  [trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)を利用し、画面上の設定のみで転送する。

１は単発の分析でしたら可能ですが、集めたデータをその都度更新し続けるのは手間がかかります。２の方法ではGoogle BigQueryを操作するGoogleアカウントがデータ元となるGoogle Search Consoleに対して閲覧以上の権限を有する必要があります。
今回は自動での更新が可能で、一度Googleアカウントとの連携を行えば設定を保存して異なる分析にも使い回せる3の[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)を利用してみたいと思います。

## 2. troccoでGoogle Search Console→Google BigQueryの転送自動化
### 2-0. 事前準備
データの転送のためには[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)のアカウントが必要です。
フリープランがあり、アカウントは即日発行されますので、申し込みしておいてください。
[https://trocco.io/lp/inquiry_free.html](https://trocco.io/lp/inquiry_free.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)

Googleアカウントに関しては、Google BigQueryとGoogleデータポータルは同じアカウントで運用したほうが接続が簡単だと思います。

### 2-1. 転送元・転送先を決定
[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)にアクセスして、ダッシュボードから「転送設定を作成」のボタンを押します。
![trocco | 「日本発」の分析基盤向けデータ統合自動化サービス 2022-03-30 16-23-51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/b5a6deb9-bd4d-7191-3ac8-94fef2f7198d.png)
転送元に「Google Search Console」、
![転送設定の新規作成 | trocco 2022-03-30 16-25-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/08f01cc4-9cb7-c263-857d-899c18d17a0c.png)
転送先に「Google BigQuery」をそれぞれ選択します。
![転送設定の新規作成 | trocco 2022-03-30 16-25-51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/7d45fadf-f80a-c953-ada2-3f087196b152.png)
「この内容で作成」を押して次のステップに進みます。
![転送設定の新規作成 | trocco 2022-03-30 16-26-38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/f3b171c1-95f9-a2aa-1a86-bfb679585daa.png)

すると、設定画面になるので、必要な情報を入力していきます。
### 2-2. Google Search Consoleとの連携設定

まず「接続情報を追加」ボタンを押し、[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)をGoogle Search Consoleと接続させます。
![転送設定の新規作成 | trocco 2022-03-30 16-27-15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/309ebb24-4c5e-7063-f99b-92a0a8f585e8.png)


別のタブでGoogle Search Consoleの接続設定が開きます。  
連携にはJSONキーを使用します。JSONキーについては[こちらのヘルプページ](https://documents.trocco.io/docs/connection-configuration-google-search-console)を参考にしてください。
![Google Search Console接続情報の新規作成 | trocco 2022-03-30 16-27-49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/2802850a-e2e6-fbc0-6477-c8197617dbf0.png)
転送設定画面に戻り、「接続情報を読み込む」ボタンを押すと、▽ボタンから作成した接続情報が選択できるようになります。
![転送設定の新規作成 | trocco 2022-03-30 16-29-56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/a64d745d-f92e-dcaf-465d-294940712b2b.png)

### 2-3. Google Search Consoleからのデータ抽出設定
次に、どのようなデータを取得するかを設定していきます。
今回は分析したいサイトについての日付、検索クエリ、リンクに関する情報を取得していきます。


必要なデータを入力していきます。

○サイトURL
転送したいデータのサイトURLを指定してください。

○サーチタイプ
web, image, videoの中から選択します。
今回はwebページ(https://trocco.io/) の分析を行うので「web」を選びます。

○データ取得期間
Google Search Consoleの仕様上、取得できるデータは3日以上前のデータに限られるので要注意です。

○ディメンション
データの取得結果をグループ化するための項目です。
date, country, device, page, query, searchAppearanceを指定することができます。
![転送設定の新規作成 | trocco 2022-03-30 16-30-36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/4f1e6129-9b4b-2279-d28e-2216b84215f1.png)



### 2-4. 転送先Google BigQueryの設定
転送元と同様に設定していきます。Google BigQueryに関してはGoogleアカウントとの連携が必要です。（事前に転送先となるデータセットとテーブルを作成しておいてください。）
![転送設定の新規作成 | trocco 2022-03-30 16-31-05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/313b973e-737b-9b8d-87f4-eaa1eb9e55f3.png)

接続にはGoogleアカウント連携を利用します。
Google BigQueryを利用するGoogleアカウントの情報を入力し、Googleアカウント側の設定に従って二段階認証などを行います。
![BigQuery接続情報の新規作成 | trocco 2022-03-30 16-31-22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/7879f202-9086-c992-9481-9dc4d0da643c.png)
転送設定の画面に戻り、「接続情報を読み込む」ボタンを押して接続情報を呼び出します。
![転送設定の新規作成 | trocco 2022-03-30 16-31-47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/84de7a93-e411-7f4f-a042-926ff55e146a.png)
接続が正しく行われていればGoogle BigQuery上にあるデータセット、テーブルが読み込まれているため、転送先に使用するテーブルを選択します。（データセットは事前に作成しておく必要があります。テーブルは仮に存在しないテーブル名を指定しても転送時にそのテーブル名のテーブルを自動で作成して転送を行うため、必ずしも事前に作成しておく必要はありません。）
![転送設定の新規作成 | trocco 2022-03-30 16-36-40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/eee88193-d08e-bc13-fe29-44990253b760.png)
その他、データロケーションや転送方式を選択し、「次のステップへ」のボタンを押します。

これで入力は完了です。接続状況を確認し問題がなければ、「次のステップへ」をクリックし確認作業に進みましょう。

### 2-5. データのプレビュー
この画面では設定に従って転送がどのように行われるかプレビューを確認することができます。
![データプレビュー・詳細設定 | trocco 2022-03-30 17-04-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/8fe78b45-3413-a53b-7fda-c0762449b56c.png)
データはきちんと取得できているようです。このままで問題ないので「確認画面へ」ボタンを押します。
なおデータが取得できなかった際には前のステップに戻って設定を見直します。また不要なデータが転送されている、転送の際にデータのマスキング処理を行いたいという場合はプレビュー画面下部からデータの加工設定が行なえます。
確認画面では転送内容がおおまかに確認できます。また既存の転送設定を編集した際には、この画面で変更内容の差分がハイライト表示されます。
問題なければ「保存して適用」ボタンを押します。
![転送設定詳細 (Qiitaテスト) - 内容の確認 | trocco 2022-03-30 17-04-22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/4f67586f-65a5-631d-4999-f1541ec19acc.png)
転送設定の詳細画面に遷移します。4つのタブがありますが、まず「転送設定概要」では転送設定の内容が大まかに確認できます。
![転送設定詳細 (Qiitaテスト) | trocco 2022-03-30 17-04-53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/2eef21cb-17c2-37af-d40f-339327d2a05c.png)

### 2-6. スケジュール・トリガー設定
「スケジュール・トリガー設定」タブを開きます。

「スケジュール・トリガー設定」のタブを選択します。「スケジュールを追加」を押すと以下のようなモーダルが出るので、データの更新頻度や分析の頻度に応じて設定を行います。（今回は毎週月曜０時に転送を行うよう設定します。）
![転送設定詳細 (Qiitaテスト) - スケジュール・トリガー設定 | trocco 2022-03-30 17-05-24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/1e926a7f-53c4-d70a-eb9d-4519cdaa131d.png)
![転送設定詳細 (Qiitaテスト) - スケジュール・トリガー設定 | trocco 2022-03-30 17-06-00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/28e66051-4ebf-d0fb-eb6a-60cd6c909d5d.png)


### 2-7. 通知設定
必ずしも設定する必要はありませんが、転送が失敗した際は通知がほしい、誰かが転送を実行したらその通知がほしいといった場合は「通知設定」から以下のようなモーダルで通知設定をすることができます。
通知先は事前に登録したE-mail、または指定のチャンネルに通知を行うSlack通知の２つから選択できます。
![転送設定詳細 (Qiitaテスト) - 通知設定 | trocco 2022-03-30 17-06-40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/6a1dc746-1df5-6784-cfb6-3327030564f1.png)




### 2-8. データ転送ジョブの実行
以上で設定は終わりです。以後は設定したスケジュールに従って自動で転送が行われますが、今回は手動で転送を実行します。
![転送設定詳細 (Qiitaテスト) | trocco 2022-03-30 17-07-06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/108ee044-2458-f1a9-6a56-48d87579af55.png)
転送の実行に際し、その目的などをメモとして残しておくことができます。今回はそのまま「実行」を押します。
![新規転送ジョブの実行 | trocco 2022-03-30 17-08-13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/46b787a8-156f-cd56-2fc0-202139a5374b.png)
転送が実行されます。
![転送ジョブ詳細 | trocco 2022-03-30 17-08-36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/a4d9f0ef-5183-36e0-7277-30dd8709f3fb.png)
転送が成功したようです。
![転送ジョブ詳細 | trocco 2022-03-30 17-09-17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/06916d7d-3748-0eb3-7bde-ecae7fea9ef4.png)


## 3. Google BigQueryの設定
特に設定することはありません。設定で選択したテーブルにデータが転送されているので、今すぐに分析・可視化を行うことが出来ます。
データがきちんと送られているかをプレビューで確認してみます。
![SQL ワークスペース – BigQuery – trocco-ambassador-s… – Google Cloud Platform 2022-03-30 17-10-10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/99c31968-3e4c-c018-f8d4-3386284ccc3b.png)

転送されていることが確認できました！
## 4. Googleデータポータルで可視化
それでは、これらのデータをGoogleデータポータルで可視化していきます。  
まずはGoogle BigQueryとGoogleデータポータルの接続設定を行います。
Googleデータポータルを開いて、新規のレポートを作成します。
![Google データポータル 2022-03-30 17-10-55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/c01fe489-800c-4fb2-ac00-bd728368e76d.png)
コネクタ（データ元）を選択する画面になります。サービスの一覧からGoogle BigQueryを選び、先程データを転送したデータセット、テーブルを順に選択することでデータを読み込むことができます。
![無題のレポート 2022-03-30 17-11-34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/c8f6c26c-874a-f43b-feb8-059d88ecf53f.png)
データを読み込むとレポートの編集画面になります。画面の右側には読み込んだデータのカラムが一覧になっています。
![無題のレポート 2022-03-30 17-12-20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1071783/66cd0ec0-d379-aeb6-7c0f-d833c2b1f5b0.png)
これらのカラムをディメンション・指標に配置することでグラフを作成していきます。


今回は日別のデータを調べるのではなく、１日における検索クエリ、リンクそれぞれの割合を調べたいので円グラフを用いることにします。

![表作成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/949b9341-5b69-dd4c-3b87-78919d80bc08.png)
まず、円グラフの項目となるディメンションに「query」を、円グラフの各項目の値となる指標には「Record Count」を選択します。そして「グラフ＞表」から「グラフ＞円」に変更します。
![query円グラフ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/99338cce-cc98-e945-0ccd-b731af4e0e35.png)
次に、pageに関する円グラフを作ります。
「グラフを追加」をクリックして先ほど同様、円グラフを選択し、ディメンションを「page」に変更します。
![page円グラフ追加.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/a019578c-ff00-7532-9d56-5a2203e767ff.png)
最後に、説明を加えてグラフの大きさを調整します。
![円グラフ完成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1121252/c500159b-edda-4f73-ed84-764f2245a397.png)







## まとめ
いかがでしたでしょうか。[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)を使うと、Google Search Consoleのデータでも自動でGoogle BigQueryにまとめていくことができるほか、100万規模の大きなデータからそうではない小さなデータまで複雑なコーディングをせず[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)の画面上の設定のみでデータの転送が可能になります。
実際に弊社サービスの[trocco®️](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)においても、マーケティングKPI等をこのような流れで収集・分析しています。
ぜひ広告データ分析の際にはご活用ください。
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)

実際に試してみたい場合は、[フリープラン](https://trocco.io/lp/inquiry_free.html?utm_medium=social&utm_source=qiita&utm_campaign=searchconsole_to_bigquery)がありますので、この機会にぜひ一度お試しください。

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[trocco®️の使い方まとめ（CRM・広告・データベース他）](https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)