Search ConsoleをBigQueryにつないで、SQL・AI検索まで見てみた！3本の実践記事をまとめて紹介
SEOをやっていると、Google Search Consoleはほぼ必ず見るツールだと思います。
検索クエリ、クリック数、表示回数、CTR、平均掲載順位。
普段のSEO分析で必要なデータはかなり揃っています。
一方で、運用を続けていると、
「もっと長期間のデータを自由に分析したい」
「数千、数万URLをまとめて見たい」
「GA4のデータと組み合わせたい」
「AI OverviewsやAI Modeで、自社サイトがどれくらい表示されているのか知りたい」
といったことも気になってきます。
そこで最近、運営しているSEGOでSearch Console周りを一通り整理しました。
具体的には、
① Search ConsoleとBigQueryを連携する
② BigQuery上のデータをSQLとAIで分析する
③ Search Consoleの生成AIレポートでAI検索の露出を確認する
という3段階です。
それぞれ個別の記事にしていますが、今回は3本を一本の流れとしてまとめて紹介します。
1. まずSearch ConsoleのデータをBigQueryに蓄積する
最初に取り組んだのが、Search Consoleの一括データエクスポートです。
Search Consoleは管理画面上でも十分便利なのですが、データ分析を本格的に行おうとすると制約もあります。
たとえば、
大量のURLを横断して分析したい
長期間のデータを蓄積したい
SQLで条件を自由に変えたい
GA4など他のデータと組み合わせたい
といった用途です。
そこでSearch ConsoleのデータをBigQueryへ自動的に蓄積する環境を作りました。
設定自体は、一度構築してしまえば毎日データが追加されていきます。
ざっくり流れを書くと、
Google Cloudでプロジェクトを用意
BigQuery用の権限を設定
Search Console側で一括データエクスポートを設定
BigQueryにデータセットを作成
日次でデータが蓄積されることを確認
という形です。
SEGOでは、Search Console用のデータセットを作り、site_impressionやurl_impressionなどのテーブルが生成されるところまで確認しました。
ここまでできると、Search Consoleを「見るための管理画面」だけではなく、分析用データベースの入口として使えるようになります。
設定手順は以下の記事にまとめています。
Search ConsoleとBigQueryの連携方法｜一括データエクスポートの設定手順
2. BigQueryに入れたデータをSQLで分析する
次にやったのが、実際の分析です。
BigQueryにデータが溜まっても、眺めているだけでは意味がありません。
そこで、
日別のクリック数・表示回数
検索クエリ別の実績
URL別の検索パフォーマンス
表示回数は多いがCTRが低いページ
などをSQLで抽出してみました。
たとえばSEOでよく使いやすいのが、
「表示回数は多いのに、クリック率が低いページ」
の抽出です。
この条件でページを出せれば、
「すでに検索結果には出ている」
「でもクリックされていない」
というURLを見つけられます。
タイトルやディスクリプション、検索意図とのズレを確認する候補として使いやすいです。
BigQueryの良いところは、条件を変えて何度でも分析できることです。
ただ、ここで一つ問題があります。
SQLを書けないと少しハードルが高い。
そこで今回はChatGPTやClaudeにもSQLを書いてもらいました。
たとえば、
「URL別にクリック数と表示回数を集計して」
「表示回数100以上、CTR2%未満のページを出して」
といった形で指示すると、SQLのたたき台をかなり簡単に作れます。
実際、今回もAIにSQLを書かせています。
ただし、AIが生成したSQLが必ず一発で動くわけではありません。
集計処理の書き方やカラム名によってエラーになることもありました。
その場合は、
AIにSQLを書かせる
↓
BigQueryで実行する
↓
エラーを確認する
↓
エラー内容をAIに戻す
↓
修正版を実行する
という流れで進めています。
個人的には、SQLを完全に暗記するよりも、この使い方の方が現実的だと思っています。
重要なのはAIが出したコードをそのまま信用するのではなく、最終的な数値や集計条件は人間側で確認することです。
この分析方法については第2回で詳しくまとめています。
Search Console×BigQueryの使い方｜SQLとAIでSEOデータを分析する方法
3. Search ConsoleでAI検索の表示状況も確認する
そして今回、3本目としてまとめたのがSearch Consoleの生成AIレポートです。
最近は、
「自社サイトはAI Overviewsに出ているのか」
「AI Modeで表示されているのか」
「AI検索対策をしても、そもそも計測できるのか」
という相談も増えてきました。
Search Consoleでは、生成AI機能で自社サイトがどれくらい表示されているかを確認できます。
SEGOで直近3か月を確認したところ、
生成AI機能：4.32K impressions
となっていました。
同じ期間のWeb検索全体は、
38.9K impressions
です。
生成AI機能の表示回数は、Web検索全体の約11%に相当する規模でした。
ただし、ここは注意が必要です。
生成AIのデータは通常のWeb検索タイプに含まれているため、
38.9K + 4.32K
と足し合わせるものではありません。
あくまで、
「Web検索全体の中で、生成AI機能でもこれだけ露出していた」
という見方です。
生成AIレポートでは、単純な合計だけでなく、
ページ別
国別
デバイス別
日別
でも確認できます。
SEGOでは、JSON-LDやFAQ構造化データなど、技術SEO系の記事が比較的多く表示されていました。
もちろん、
「構造化データを書けばAI検索に出る」
という意味ではありません。
ただ、どんなテーマやページがAI検索上で露出しているのかを観測できるようになったこと自体は、今後のSEOやLLMOを考えるうえでかなり大きいと思います。
詳しい画面の見方は3本目にまとめました。
Search Consoleの生成AIレポートの見方｜AI Overviews・AI Modeの表示回数を分析
SEOもAI検索も、まず「観測できる状態」を作る
今回3本の記事を作ってみて、改めて感じたのは、
施策を増やす前に、まず観測できる環境を作ることが大事
ということです。
SEOでもLLMOでも、
「順位が上がった」
「AIに引用された」
「アクセスが増えた」
だけを単発で見ると、なかなか次の施策につながりません。
一方で、
Search Console
↓
BigQuery
↓
SQL
↓
GA4
↓
生成AIレポート
という形でデータをつなげていくと、
「どの検索で表示されているのか」
「どのページがクリックされているのか」
「どこに改善余地があるのか」
「AI検索ではどのページが露出しているのか」
まで少しずつ見えるようになります。
個人的には、これからのSEOでは「検索順位を見る」だけではなく、
通常検索とAI検索の両方を継続的に観測する
ことがかなり重要になると思っています。
ChatGPTやClaudeがあることで、分析のハードルもかなり下がった
もう一つ大きく感じたのが、AIによって分析環境を作るハードルが下がったことです。
以前なら、
「BigQueryを使う」
「SQLを書く」
という時点で、SEO担当者には少し遠い領域に感じることもありました。
今はChatGPTやClaudeに、
「このデータから○○を抽出したい」
と伝えれば、SQLのたたき台まで作ってくれます。
もちろん最終確認は必要ですが、
SEO担当者が自分でデータを触れる範囲はかなり広がった
と感じています。
今回のSearch Consoleシリーズも、単にツールの設定方法を紹介するというより、
SEO担当者がAIを使いながら、自分で分析環境を作る方法
として読んでもらえると嬉しいです。
今回のSearch Consoleシリーズ
最後に3本をまとめておきます。
① Search ConsoleとBigQueryをつなぐ
Search ConsoleとBigQueryの連携方法｜一括データエクスポートの設定手順
② SQLとAIでデータを分析する
Search Console×BigQueryの使い方｜SQLとAIでSEOデータを分析する方法
③ AI検索での露出を確認する
Search Consoleの生成AIレポートの見方｜AI Overviews・AI Modeの表示回数を分析
Search Consoleを普段の順位・クリック確認だけで使っている方は、まずBigQuery連携か生成AIレポートの確認から試してみると面白いと思います。
今後もSEGOでは、実際のデータを使いながら、SEO・LLMO・生成AI周りを検証していきます。
