Google Search ConsoleのデータをAmazon Redshiftに自動同期し、Lookerで可視化する

概要

Google Search Console（サーチコンソール）はGoogleが提供するWeb上での検索結果を分析するツールです。サーチコンソールを利用することで自分のWebサイトのGoogle検索での表示回数・掲載順位を把握したり、その結果からSEO上の問題点を修正してWebサイトの注目度を高めることができます。
サーチコンソールのデータを分析するには標準のレポート機能を使用する方が多いと思いますが、データの量・質ともに高度な分析を行う際はサーチコンソールのデータを別途データの高速処理に長けたDWH（データウェアハウス）へ統合し、DWHと連携したBIツールで可視化するという流れが有効になります。一方このような高度なデータ分析はデータエンジニアリングの知識が必要となり、一般的にサーチコンソールを利用すると思われるマーケティング部門に携わる方には少しハードルが高いのではないでしょうか。

そこで今回はtrocco®️というデータ分析基盤構築サービスを使い、サーチコンソールのデータ抽出、DWH（今回はAmazon Redshiftを使用）への統合、Looker利用した可視化までやってみようと思います。

今回、データの転送手段として使用するtrocco®️は、サーチコンソールの他にも、様々な広告・CRM・DBなどのデータソースにも対応しています。

ゴール

サーチコンソールのデータを下のようにAmazon Redshiftに統合し

Lookerで可視化します(作成後は自動で最新値に更新することも可能です）

こんな人におすすめ

• サーチコンソールのデータをAmazon Redshiftに自動的に蓄積させたい
• Google検索での表示回数・掲載順位について自動的に分析できるようにしたい
• サーチコンソールをデータ元としてデータ分析を行いたい

1. trocco®でGoogle Search Console → Amazon Redshiftの転送自動化

1-0. 事前準備

まずはtrocco®のアカウント、サーチコンソールを操作するGoogleアカウントが必要になります。

またtrocco®は現在無料のトライアルを実施しているため、ぜひ事前に申し込み・登録をしておきましょう。

https://trocco.io/lp/index.html

（申し込みの際にこちらの記事を見たという旨を記載して頂ければその後のご案内がスムーズに行なえます。）

1-1. 転送元・転送先を決定

trocco®にアクセスして、ダッシュボード画面から「転送設定を作成」のボタンを押します。

転送元に「Google Search Console」を指定し、転送先に「Amazon Redshift」を選択して転送設定作成ボタンを押します。

「この内容で作成」のボタンを押すと設定画面になるので必要な情報を入力していきます。

1-2. Google Search Consoleとの連携設定

あとで見たときに自分で分かるように転送設定の名前とメモを入力します。

次に「転送元Google Search Consoleの設定」内の「接続情報を追加」ボタンを押します。

別のタブでサーチコンソールの接続情報の新規作成画面が開きます。
接続情報を作成するためにJSON Keyが必要になります。

問題がなければ保存します。再度転送設定画面に戻り、接続情報の「接続情報を読み込む」ボタンを押すと、先ほど作成した接続情報が選択できるようになります。

1-3. Google Search Consoleからのデータ抽出設定

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

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

• サイトURL
  • データを転送したいサイトのURLを指定します。
• サーチタイプ
  • 「web」「image」「video」の３タイプから指定します。
  • 今回はwebページ(https://trocco.io/）の分析を行いますので、「web」を選択します。
• データ取得期間
  • Google Search Consoleの仕様上、データが取得できる期間は今日から3日前のデータに限られます。
• ディメンション
  • データの取得結果をグループ化するための項目です。
  • 「date」「country」「device」「page」「query」「searchAppearance」の指定が可能です。

1-4. 転送先Amazon Redshiftの設定

転送元と同様に設定していきます。まず、Amazon Redshiftの接続情報を作成していきます。

転送先となるAmazon Redshiftの接続先ホスト、接続先ポート、ユーザー名、パスワードを設定し、AWSのアクセスキーIDとシークレットアクセスキーを設定する必要があります。
AWSのアクセスキーとシークレットアクセスキーの取得方法はこちらをご覧ください。

1.IAM コンソール を開きます。
2.ナビゲーションメニューで [ユーザー] を選択します。
3.IAM ユーザー名 (チェックボックスではありません) を選択します。
4.[認証情報] タブを開き、次に [アクセスキーの作成] を選択します。
5.新しいアクセスキーを表示するには、[表示] を選択します。認証情報は次のようになります。
6.アクセスキー ID: AKIAIOSFODNN7EXAMPLE
シークレットアクセスキー: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
キーペアをダウンロードするには、[.csv ファイルのダウンロード] を選択します。キーを含む .csv ファイルを安全な場所に保存します。

先ほど作成した接続情報を選択し、データベース名、スキーマ、テーブルなど必要な情報を設定していきます。（データベース、スキーマ、テーブルは事前にAmazon Redshiftで作成しておく必要があります。）

これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。

1-5. データのプレビュー

サーチコンソールからどのようにデータが取得できるかプレビューが確認できます。もし取得したいデータが表示されていなければ再度設定画面に戻り、設定をし直します。

今回は転送したいデータが取れているので、設定を完了します。
転送設定の一覧から作成した設定を選び「スケジュール・通知設定」に進みます。

1-6. スケジュール・通知設定

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

「スケジュールを追加」ボタンを押すと、以下のようなモーダルが出てきます。ここで実行スケジュールを設定することで、転送を定期的に実行し自動化することが出来ます。

1-7. データ転送ジョブの実行

設定は以上です。最後に、手動で転送ジョブを実行し、Amazon Redshiftにデータを送ります。
手動で実行する場合はジョブ詳細画面の「実行」ボタンを押します。

これで転送は完了です。

2. Amazon Redshiftの設定

Amazon Redshift側で特に操作は必要ありませんが、コンソール画面からプレビューを確認してみると確かにデータが転送されています。

3. Lookerで可視化

最後にLookerでデータの可視化を行います。まずAmazon RedshiftとLookerを接続します。 管理タブを開いて「データベース」の「接続」を開きます。

接続しているデータベース一覧が表示されています。ここで「Add Connection」を選択し、接続設定を行う画面が表示れるので、情報を入力していきます。

Amazon Redshiftのデータベースに接続できたら、次はデータを可視化するために必要なLookMLプロジェクトを作成します。 開発タブを開いて「LookMLプロジェクトの管理」に移動します。

「New LookML Project」からLookMLプロジェクトを作成します。

「Create Project」を押したら、エディタでmodelとviewを定義します。 後々必要になるので、modelの中ではexploreを設定しておきましょう。 (書き方が分からない場合はLookerの公式ドキュメントを参照してください）

これで下準備が整いました。 トップページに戻って「New」からDashboardを作成します。

白紙のダッシュボードが作成されるので「Qiita_Searchconsole_Redshift」と名前をつけます。
そして「Dashborardの編集」→「タイルの追加」を選択し、先ほどのmodel内で定義したExploreを選択します。

ディメンションとメジャーを設定し、Tileに表示したいデータをプロットします。例えば、リンクにおける広告の表示回数について分析したいとき、ディメンションに「リンク」とメジャーに「インプレッション数」を選択します。

今回はクリック数、インプレッション数を分析したいのでダッシュボードにまとめていきます。

これで完成です。

まとめ

いかがでしたでしょうか。trocco®を使うことで管理画面での操作をせずにサーチコンソールのデータをAmazon Redshiftに統合することができました。また同様にして100万規模の大きなデータからそうではない小さなデータまで複雑なコーディングをせずtrocco®の画面上の設定のみでデータの転送が可能になります。

実際にtrocco®を試してみたい場合は、クレジットカード不要のフリープランや無料の製品資料を提供しているので、この機会にぜひ一度お試しください。