BigQueryに連携したGoogle Search Consoleのデータ活用方法【サンプルSQLつき】

2025.04.07

Google Search Console（GSC）は、検索キーワードのパフォーマンスを確認するために便利な無料ツールですが、データ件数やフィルター条件などの制限が多く、深い分析には向いていません。

そのため、GSCのデータをBigQueryに連携し、SQLで集計したり、Looker Studioで可視化したりする活用方法が注目されています。

本記事では「とりあえず連携はしたけれど、このデータをどう活用すればいいの？」という方に向けて、実際に役立つクエリ例やLooker Studioとの連携方法などを、わかりやすく解説します。

なおBigQuery自体の初期設定については、下記のページが、
初心者でもわかるBigQuery入門｜導入方法や使い方を徹底解説！(株式会社電算システム様)

BigQueryとGSCの連携手順については下記のページがわかりやすいので、ご参照ください。
サーチコンソールとBigQueryを連携する方法 (デジマ練習帳：bluegoat様)

1. エクスポートされたSearch Consoleデータの構造

GSCと連携したBigQueryには、データセット「searchconsole」の中に、次のようなテーブルが作成されていますが、個人的には「searchdata_url_impression」ばかり使っています（下図赤枠）：

  • searchdata_site
  • searchdata_url_impression

1）主なカラム（searchdata_site）

カラム名説明
dateデータの日付（UTC）
country国コード（例：JPN）
device使用デバイス（mobile / desktop / tablet）
page対象URL
query検索クエリ
clicksクリック数
impressions表示回数
ctrクリック率（clicks ÷ impressions）
position平均掲載順位

2. よく使うクエリのサンプル集

下記のサンプルクエリを使うときは、
・「project_id」のところに皆さんのプロジェクトID
・「dataset_id」のところには「searchconsole」
を入れてください。

1）日別のCTR推移を取得する

SELECT
  data_date AS date,
  ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0), 4) AS ctr
FROM `project_id.searchconsole.searchdata_url_impression`
GROUP BY date
ORDER BY date

2）ページ別の表示回数とクリック数を抽出

SELECT
  data_date AS date,
  url AS page,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM `project_id.searchconsole.searchdata_url_impression`
GROUP BY date, page
ORDER BY total_clicks DESC
LIMIT 100

3）クエリ別の上位表示ワード（掲載順位の低い順）

SELECT
  data_date AS date,
  query,
  ROUND(AVG(sum_position), 2) AS avg_position,
  SUM(clicks) AS total_clicks
FROM `project_id.searchconsole.searchdata_url_impression`
GROUP BY date, query
HAVING total_clicks > 0
ORDER BY avg_position ASC
LIMIT 100

3. Looker StudioでBigQueryのデータをレポート化するには？

BigQuery上で取得したSearch Consoleデータは、SQLのままだと扱いにくい場面もあります。
そこでおすすめなのが、Googleの無料ダッシュボードツール「Looker Studio」の「カスタムクエリ接続」機能です。

Looker Studioのカスタムクエリ機能を使うことには以下のようなメリットがあります。

  • Googleアカウントさえあれば、データ接続のための追加認証が不要
  • SQLで事前に必要なデータのみに絞り込むことができるため、Looker Studio側での細かい設定が減る
  • Looker Studioの「データ統合」機能を使えば、GA4データとの連携も可能（※やや上級向け）

1）カスタムクエリ接続の手順

  1. Looker Studioを開く
  2. 「データを追加」→「BigQuery」を選択
  3. 「カスタムクエリ」をクリックし、カスタムクエリ入力欄にSQLを貼り付ける（下図参照）
  4. 接続後、レポートにグラフや表として表示

例：検索クエリ × ページ別のCTRを可視化するクエリ

SELECT
  data_date AS date,
  query,
  url AS page,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0), 4) AS ctr
FROM `project_id.searchconsole.searchdata_url_impression`
GROUP BY date, query, page
ORDER BY total_impressions DESC
LIMIT 1000

この形式で集計しておけば、「キーワード × ランディングページ別」のパフォーマンスを表で一望できます。

4. まとめ

Search Consoleの標準機能だけでは、どうしても分析や可視化に限界があります。
BigQueryとの連携により、CTRやクエリ・ページ単位での詳細分析や、柔軟なフィルタ設計が可能になります。

GA4のBigQuery連携と同様に、「GSC連携 → SQLで集計 → Looker Studioで可視化」という流れを覚えて、SEOレポートや改善施策の裏付けなどに活かしてください。

関連：GA4のデータをBigQueryで扱う方法を知りたい方はこちら
BigQueryでGA4データを扱う準備とは？初期設定と基本クエリまでを解説｜サンプルSQLつき

5. 参考リンク集

[1]: Search Consoleでは「クエリ」「ページ」に対してのみ正規表現が使えます。
[2]: UI上でのCSVエクスポートでは最大1,000行までが上限となっています。