Search Console × BigQueryで全量データ分析

Retty（レッティ）のプロダクトマネージャーをしている松田です。

はじめに

Search Consoleではエクスポート行数に1000行までの制限がある、遡れる期間が16ヶ月前まで、など何かと物足りないポイントがあります。

BigQueryにデータをエクスポートすることで、エクスポート開始以降はデータ量に制限がなく全量データを分析できるようになり、意思決定の幅が広がります。

ここではエクスポートしたデータの見方と、簡単な分析例をご紹介します。

はじめに
Search ConsoleのデータをBigQueryにエクスポートする方法
searchdata_url_impressionテーブルの見方
sum_positionの扱い方
分析例：特定のURL群に絞ったデータを見る
分析例：すべての検索クエリを見る
まとめ

Search ConsoleのデータをBigQueryにエクスポートする方法

エクスポート方法については、Search Consoleのヘルプに動画付きの解説や日本語記事が複数あるので、ここでは割愛します。新しい一括データのエクスポートを開始する - Search Console ヘルプ

searchdata_url_impressionテーブルの見方

使用頻度の高いフィールドを抜粋しています。すべてのフィールドについては「テーブルのガイドラインとリファレンス」に記載があります。

フィールド名	種類	説明
data_date	DATE	日付（JST）
url	STRING	ページのURL
query	STRING	検索クエリの文字列
search_type	STRING	サーチコンソールの「検索タイプ」に相当します。テーブルのガイドラインとリファレンスでは小文字になっていますが、実際のデータは大文字（例: WEB）です。
device	STRING	DESKTOP, MOBILE, TABLETのいずれか
impressions	INTEGER	インプレッション数
clicks	INTEGER	クリック数
sum_position	INTEGER	順位のもとになるデータが入っています。単体では利用できません。

sum_positionの扱い方

平均順位は SUM(sum_position)/SUM(impressions) + 1で計算した値を利用します。

単体では利用できずsum_positionに10というデータが入っているレコードがあったとしても、「10位に表示された」ということを意味しないので注意です。

分析例：特定のURL群に絞ったデータを見る

Search Consoleではページに含まれるURLを指定することはできるものの、特定のセグメントごとにデータを出して比較することは難しいです。

例として、料理ジャンル（居酒屋・イタリアンなど）ごとのデータを集計してみます。 RettyではURLに料理ジャンルが入っていて、例えば https://retty.me/area/PRE13/ARE1/LCAT1/CAT350/ では CAT350 が居酒屋を示しています。

期間を指定して料理ジャンルごとに月間の表示回数・クリック回数・平均順位を出してみます。自然検索の結果だけを見るために search_type に WEBを指定、デバイスによって検索順位は異なるのでMOBILEに絞っています。

select
  safe_cast(regexp_extract(url, r'/(?:CAT)(\d+)/') as int64) as category_id
  , category_name
  , sum(impressions) as impressions
  , sum(clicks) as clicks
  , round(safe_divide(sum(sum_position),sum(impressions)) + 1, 2) as avg_position
from project.dataset.searchdata_url_impression
  left join categories on safe_cast(regexp_extract(url, r'/(?:CAT)(\d+)/') as int64) = categories.category_id
where
  data_date between '2024-11-01' and '2024-11-30'
  and search_type = 'WEB'
  and device = 'MOBILE'
group by 1,2
order by 1,2

このような形で、料理ジャンルごとの結果を集計することができます。 Search Consoleでは比較でも2種類までしか見ることができませんが、BigQueryを使うことで見通しが良くなりました。

URLベースではありますが任意のセグメントに分けて分析することで、Search Consoleで表示される限られたデータからでは把握できない情報を得ることができます。

分析例：すべての検索クエリを見る

サーチコンソールではクエリ例が1000行しか出ないため、すべての検索クエリを見ることはできません。また、例として表示されるものは表示回数やクリック数が一定ある検索クエリが多く、抽象的なワードが多くなります。

細かなニーズまで読み取るには、すべての検索クエリを見るのが一番です。このページを例として、すべての検索クエリを見てみます。
新宿のウマい居酒屋20選〜人気店から穴場まで〜 - Retty（レッティ）

select
  query
from project.dataset.searchdata_url_impression
where
  data_date between '2024-11-01' and '2024-11-30'
  and search_type = 'WEB'
  and device = 'MOBILE'
  and url = 'https://retty.me/area/PRE13/ARE1/LCAT1/CAT350/'
group by 1
having sum(clicks) > 0
order by sum(impressions) desc