Search Consoleのデータを自動でスプレッドシートに連携する方法
業務効率化公開日:2024年12月13日 更新日:2024年12月13日

SEO対策をする上で、検索結果のパフォーマンスをモニタリングしたり、ウェブサイトの問題点を見つけるためにはSearch Consoleは欠かせないツールの1つです。
しかし、手動でデータをダウンロードして分析する作業は手間がかかり、スピード感ある意思決定の妨げになることがあります。
今回は、そんなSearch Consoleのデータをスプレッドシートに自動連携する方法を紹介します。
※今回紹介する方法は、ちょっとだけお金がかかってしまいます。
目次
Search Consoleを自動連携する方法
Search ConsoleをGoogleスプレッドシートなどに自動連携する代表的な方法は2つあります。
- BigQueryから取得する方法
- Search Console APIから取得する方法
この記事では「1. BigQueryからインポートする方法」について解説をします。
BigQueryから取得する方法
BigQueryからの取得は、クエリ(SQL)さえ書けてしまえば比較的簡単に実装ができます。
またSearch Consoleのデータを扱うテーブル以外のデータと連結(JOIN)させることで、複雑なデータ処理も容易に行うことができるのもメリットの1つです。
またスプレッドシート上でクエリを作成・編集することもできるため、データ編集の自由度も高いです。
一方、デメリットとしてはBigQueryの利用料(と言っても規模が大きくなければ月額数十円程度)がかかってしまうことや、GCPやSQLの知識がある程度必要であることが挙げられます。
Search Console APIから取得する方法
Search Console APIを使う場合は、Google App Scriptで操作することが一般的だと思います。
BigQueryからインポートする方法とは異なり、SQLに関する知識は不要ではありますが、そのかわりGoogle App Script(JavaScript)の知識が必要になります。
またAPIでも複雑なフィルターをかけることはできなくはないですが、そのフィルターの規模が大きくなければなるほど、コードの複雑さが増します。
そのため、ざっくりとした日別・月別・注力キーワード別の限られた範囲のレポートには適していますが、実装難易度や拡張性の観点からBigQueryを使ったほうが優れているため、この記事ではBigQueryを使った自動連携方法を紹介します。
BigQueryを使って自動連携をする方法
BigQueryを使ってSearch Consoleのデータを、スプレッドシートに自動連携する手順は大きく分けて5つのステップを踏みます。
- Google Cloud Platformのプロジェクトを作成する
- IAMで権限を付与する
- Search ConsoleのデータをBigQueryに連携する
- BigQueryとスプレッドシートを連携する
- スプレッドシートで定期取得する
費用が発生するのは、「BigQueryの使用」に対してなので、BigQueryが備わっているGoogle Cloud Platform内の課金設定でクレジットカード等の設定が必要になります。
この方法の概要は下図を参考にして下さい。
Google Cloud Platformのプロジェクトを作成する
すでにGoogle Cloud Platformを利用しており、かつ今回の取組に適したプロジェクトが既に存在している場合は、このプロセスは不要になります。
BigQueryはGoogle Cloud Platformにある機能の1つであるため、BigQueryを利用するためにはGoogle Cloud Platformの登録も必須になります。
Google Cloud Platformを初めて利用する場合、300ドル(日本円で約45,000円)分の無料クレジットが付与されます。
先述の通り、料金がかかると言ってもせいぜい月数十円程度であるため、この無料クレジットの範囲でBigQueryを利用することができます。
まずはGoogle Cloud Platformのアカウントを作成し、Search Consoleのレポートを扱うプロジェクトを作成しましょう。(My First Projectでも問題なし)
IAMで権限を付与する
Google Cloud Platformのプロジェクトが作成できたら、そのままGoogle Cloud Platformの「IAMと管理」に移動します。
「IAMと管理」に移動したら、「アクセスを許可」をクリックします。
この作業は、Search Consoleが、このプロジェクトにアクセス & BigQueryの操作をできるようにするためのものです。
プリンシパルの追加画面では、以下の内容を追加します。
新しいプリンシパル:search-console-data-export@system.gserviceaccount.com
ロールの割り当て1:BigQuery ジョブユーザー
ロールの割り当て2:BigQuery データ編集者
上記設定ができたら保存をして、Google Cloud Platformの設定は完了です。
Search ConsoleのデータをBigQueryに連携する
IAMでの権限付与が完了したら、次はSearch Consoleに移動します。
Search Consoleの左メニュー「設定」から、「一括データエクスポート」を選択します。
そうすると、Google Cloud PlatformのプロジェクトID、データセット名、データセットの場所を指定するモーダルが表示されます。
データセット名や、データセットの場所は、特にこだわりがなければデフォルトのままで大丈夫です。
Google Cloud PlatformのプロジェクトIDは、Google Cloud Platform内で確認をすることができます。
Google Cloud Platformコンソールの左上のプルダウンから、プロジェクト一覧を表示させ、指定のプロジェクトにおけるID列に記載があります。
設定ができたら「続行」ボタンを押し、最終確認の画面に入ります。
設定内容に問題がなければ、「エクスポートを設定」ボタンを押してエクスポートを開始します。
これでBigQueryとSearch Consoleの連携は完了です。
48時間以内にSearch ConsoleのデータがBigQueryに蓄積し始めるので、蓄積が開始したら次の手順に進みます。
BigQueryとスプレッドシートを連携する
まずはBigQueryと連携したいスプレッドシートを用意します。
そのスプレッドシートの上部メニュー「データ」>「データコネクタ」>「BigQueryと接続」を指定します。
先ほど作成したGoogle Cloud PlatformのプロジェクトIDがあるので、そのプロジェクトを選択します。
次に、データセットの選択画面に移動しますが、このとき「searchconsole」を指定して進んでも良いのですが、オススメは保存したクエリとクエリエディタをクリックします。
このまま進むと、BigQueryに連携した全てのデータが出力されてしまうので、データ量が大きくなり、扱いが難しくなります。(しかも接続設定を編集できません)
クエリエディタが表示されたら、試しに以下のクエリを指定してみて下さい。
SELECT
DATE_TRUNC(s.data_date, MONTH) AS Month,
s.query,
SUM(s.impressions) AS Imp,
SUM(s.clicks) AS Click,
ROUND((SUM(s.sum_top_position) / SUM(s.impressions)) + 1, 2) AS AvgPosition
FROM
`{プロジェクトID}.{データセット}.searchdata_site_impression` s
GROUP BY
Month, s.query
ORDER BY
Month
{プロジェクトID}には、先ほどSearch Consoleに連携した際に利用したプロジェクトIDを、{データセット}には、任意で指定したデータセット名を入れます。(デフォルトだとsearchconsole)
これで、月別のレポートがスプレッドシートに反映されます。
スプレッドシートで定期取得する
最後に、連携できたBigQueryのデータを、定期的にスプレッドシートに反映をする設定をします。
BigQueryのデータを連携したシートの「更新オプション」から、定期実行の設定ができます。
例えば毎日、同じ時間にデータを更新する場合は以下のような設定になります。
繰り返す感覚:1日
繰り返す時間:6:00-7:00(任意の時間)
これで設定すれば、毎日決められた時間にBigQueryのデータがスプレッドシートに自動連携されます。
特定のキーワードを大量に指定する
ここからは少し応用なのですが、例えば特定のクエリのみを取得したい場合は、WHERE分でクエリを指定すればよいのですが、その数が数十・数百になるとWHERE文で指定しきるには無理があります。
このような場合は、SQLのJOINを使って乗り切りましょう。
まずBigQueryで、注力キーワードを指定するテーブルを作成します。
そしてこのテーブルと、Search ConsoleのテーブルをJOINすることで、レポートしたいキーワードを大量に指定することができます。
SELECT
DATE_TRUNC(s.data_date, MONTH) AS Month,
s.query,
SUM(s.impressions) AS Imp,
SUM(s.clicks) AS Click,
ROUND((SUM(s.sum_top_position) / SUM(s.impressions)) + 1, 2) AS AvgPosition
FROM
`{プロジェクトID}.{データセット}.searchdata_site_impression` s
JOIN
`{プロジェクトID}.{データセット}.{キーワードを指定するテーブル}` k
ON
s.query = k.keyword
GROUP BY
Month, s.query
ORDER BY
Month
このようにモニタリング対象のキーワードやURLを管理するテーブルを用意しておくことで、煩雑なクエリを回避することができ、かつ効率的にモニタリング対象を管理することができます。
他にも応用の方法や、クエリの効率化は如何様にもできるので、ぜひSearch ConsoleのデータをBigQueryで管理することを始めてみて下さい。