【データ活用初心者マーケターへ】BigQueryとtrocco®でGoogle Search Consoleのデータを手軽に可視化する方法 #初心者

はじめに

この記事は、データ可視化初心者マーケターによる、データ可視化初心者マーケターのための記事です。

Google Search Consoleのデータは、ウェブサイトのパフォーマンスを評価し、SEOの戦略を策定する上で欠かせないツールです。
Google Search Consoleには標準のレポート機能があり、WebサイトのGoogle検索結果ページへの表示回数やページのクリック数、掲載順位の推移、どのようなクエリがサイト流入につながっているのか、といったことを確認できます。

Google Search Consoleはとても便利なツールではありますが、

「数値の推移はわかった…！
でも、それ、月次・週次単位で比較したいんや…！」

と思ったことがあるマーケティング担当の方は少なくないのではないでしょうか。

また、各数値を月次・週次で見るために、日々手動でデータをインポートし、スプレッドシートに張り付けて、グラフ化して…と頑張っている方もいるでしょう。

そこで今回は、Google BigQueryとtrocco®、Google Looker Studio、さらにChatGPTを使い、
「Google Search Consoleを月次・週次で見えるようにしたい」
という希望を簡単に叶えるための方法を紹介します。

2023年2月にリリースされたGoogle Search Consoleの一括データエクスポート機能を使えばtrocco®を使わなくてもデータをBigQueryにエクスポートできますが、2022年のデータも可視化したかったので今回はtrocco®を使ってデータを可視化してみました。
※trocco®は無料で利用できます。

なお、今回紹介する方法は、データベースやデータエンジニアリングに関する専門的な知識がない方でも行えるものです。
この記事を参考に、ぜひデータの可視化に挑戦してみてください。

こんな人におすすめ

データ集計を効率化したいと思っているマーケティング担当者
SEO担当者・WebサイトのKPIを追っている方
BIツールの活用に興味がある方

データ可視化のゴール

今回はGoogle Search Consoleにあるデータ（imp、click、ctr）をGoogle BigQueryに転送し、Google Looker Studioで可視化することを目標にします。（下画像）

データ転送をするための事前準備

trocco®アカウント（無料）の作成

Google Search Consoleのデータを転送するにあたり、分析基盤構築・運用の支援SaaS「trocco®」を利用します。

trocco®にはクレジットカード不要・月額利用料0円のフリープランがあります。
事前に申し込みをしてアカウントを作成しておきましょう。
https://trocco.io/lp/inquiry_free.html

また、データ元のGoogle Search Consoleのアカウントや、データの転送先となるGoogle BigQueryのアカウントも事前にご準備ください。

BigQueryの接続設定

trocco®とBigQueryの接続をする

詳しい手順はこちらをご覧ください。

データセットを用意する

データの転送先情報を接続するために使用します。
BigQuery上であらかじめデータセットの作成をしておきましょう。

データセットとは
テーブルとビューへのアクセスを整理して制御するために使用される最上位のコンテナです。テーブルまたはビューはデータセットに属していなければなりません。したがって、データを BigQuery に読み込む前に、1 つ以上のデータセットを作成する必要があります。

※引用: https://cloud.google.com/bigquery/docs/datasets-intro

詳しい手順は公式ドキュメントを参考にしてください。

Google Search Console から Google BigQueryへデータ転送をする

1. 転送設定

1-1. 転送設定の新規作成

まずはtrocco®でデータ転送を行います。
はじめに、転送設定の新規作成を行います。

転送元・転送先の選択を行います。

概要設定をします。

1-2. 転送元（Google BigQuery）の設定

転送元であるGoogle Search Consoleとの連携設定をします。

Google Search Console接続情報、サーチタイプ、サイトURL、データ取得期間、ディメンションを入力しましょう。
カスタム変数は必要に応じて設定します。

1-3. 転送先（Google BigQuery）の設定

次に、転送先であるGoogle BigQueryとの接続設定をします。
Google BigQuery接続情報、データセット、テーブル等を設定していきます。
テーブルはtrocco®上で作れるので、事前にBigQuery側で作成しなくても大丈夫です。

ここまで完了したら、画面右下にある次のSTEPへというボタンを押します。
そうすると自動データ設定の選択というポップがでるので、自動データ設定を実行を選択し、決定して次へ進むを押します。

2. データのプレビュー・スケジュール設定・転送設定の実行

2-1. プレビュー

設定して取得できたサンプルデータに問題がなければ確認画面へを押します。

保存して適用を押します。

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

必要に応じてスケジュール・通知設定を行います。

2-3. データ転送ジョブの実行

転送設定が全て完了したら、画面右上の実行ボタンを押します。

次の画面に遷移するので、再び実行ボタンを押します。

実行完了と表示が出れば、データ転送の成功です！

3. データマートの作成

データ転送が終わったら、BigQueryに転送したデータをもとに、データマートを作成します。

データマートとは
データの検証や分析など特定の目的のために作成されたデータベースのことです。
通常データ分析には社内データを統合したデータウェアハウスを使用し、データマートはそのデータウェアハウス上の別テーブルにデータを切り出す形で作成されます。

※引用: https://blog.trocco.io/glossary/data-mart

データマートから設定画面を開き、新規データマート定義作成のボタンを押します。

BigQueryを選択します。

データマート定義名やGoogle BigQuery接続情報など作成に必要な情報を設定します。

クエリ設定にて、クエリを入力し、データセットやデータテーブルを指定します。
出力先テーブルはtrocco®上から作成することができるので、事前にBigQueryで作成する必要はありません。

なお、SQLのクエリはChatGPTに書いてもらいました。

以下はその時のプロンプトです。

BigQueryで使うためのSQLのクエリを書いてください。

## やりたいこと
以下の要素をdateをもとに月次・週次で集計する
・impressions				
・clicks				
・ctr			

なお、ctrはすでにBigQueryにデータがあるので、割り算をする計算はSQL上ではしなくても大丈夫です。

そして出てきたクエリがこちらです。

-- 月次集計
SELECT
  DATE_TRUNC(CAST(date AS DATE), MONTH) as month_start,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  AVG(ctr)*100 as avg_ctr
FROM your_table
GROUP BY month_start

-- 週次集計
SELECT
  DATE_TRUNC(CAST(date AS DATE), WEEK) as week_start,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  AVG(ctr)*100 as avg_ctr
FROM your_table
GROUP BY week_start

※your_tableの部分に、ご自身が使うデータセット名をいれてください。
※ctrの値をパーセンテージ表記っぽくしたかったので、SQLクエリ内でctrの値を100倍しています。

SQLを自分で書くとなると検索しながら書くことになり時間がかかるので、ChatGPTに頼れるのはとても助かりました。

最後にシンクというボタンを押し、遷移した画面右下のシンクジョブを実行というボタンを押したらデータマート設定は完了です。