GSC (サーチコンソール) × BigQuery 活用時によくあるトラブルと解決策【サンプルSQLつき】

Google Search Console、通称GSC (サーチコンソール)のBulk Export機能を使うと、日次の検索データをBigQueryに蓄積して分析・活用することができます。

ただし、元のデータ構造やUIとの計算の違いなどから、悪意無く誤った分析をしてしまうことがあるので注意注意が必要です。

この記事は、BigQueryでGSC Bulk Exportデータを扱う際のテーブル構造、評価順位計算の正しい形、よくある誤差の原因などをまとめたガイドです。

※ GSC Bulk Exportとは？
GSCの「Bulk Export」は、GSCの検索パフォーマンスデータを日次でBigQueryに自動エクスポートできる機能です。GSCの画面のような表示件数制限がなく、大量のクエリデータやページデータをもとに、より詳細なSEO分析が可能になります。

1. 使用データの構造

BigQuery内のGSCデータセットにおいて、主に使用するテーブルは searchdata_url_impressionです。

GA4のデータ構造に比べると非常にシンプルでわかりやすいのですが、カラム名に多少クセがあるので注意が必要です。
私自身、初めてこのテーブルを使ったときにはdata_dateをdateに、urlをpageに、sum_positionをtotal_positionに誤って記述したためエラーが続き、小一時間つまづいた経験があります。カラム名のミスは地味ですが影響が大きいため、ぜひ下記の一覧表を確認しながら作業を進めてください。

それと、必ず一度はBigQueryのプレビュー画面で、データの中身を目視確認されることをお勧めします。

2. 実務で使う常用アウトプット

GSCのBulk Exportデータを使った日次分析で、最も基本となる集計パターンです。
CTR（クリック率）や平均掲載順位の取得にも対応しており、初期分析やLooker Studio連携のベースとして活用できます。

SELECT
  data_date AS date,
  query,  -- (1) NULLクエリを除外対象にする前提(※後述)
  url AS page,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0), 4) AS ctr,
  ROUND(SUM(sum_position) / NULLIF(SUM(impressions), 0), 2) AS avg_position
FROM `project_id.searchconsole.searchdata_url_impression`
WHERE query IS NOT NULL  -- (1) NULLクエリの除外(※後述)
GROUP BY date, query, page
ORDER BY total_clicks DESC

3. よくある誤差とその原因

GSC Bulk Export データは便利な反面、集計方法を誤るとUIとの数値のズレや意図しない平均値のブレが発生することがあります。ここでは、代表的な誤差とその背景をまとめています。

4. 平均掲載順位を正しく計算するベストプラクティス

平均掲載順位を出すとき、下記のようにAVGでやりたくなってしまいますが（やったこともありますが）、これは誤りです。

AVG(sum_position / impressions)

AVGを使ってしまうと、1行ごとの sum_position ÷ impressions の値を平均する形になってしまい、行ごとのばらつきや極端な値があったときに大きな誤差が出てしまいます。そのため、実態とは異なる「ゆがんだ平均順位」になってしまうのです。

正しくは以下の式を使うようにしてください。

ROUND(SUM(sum_position) / NULLIF(SUM(impressions), 0), 2)

この式を使うと、全体での合計をもとに計算するため、より安定した指標になります。

5. 集計データをクリーン化するコツ

GSC Bulk Export データを活用する際、クエリの欠損やノイズURL、極端なCTRなど、分析に不要な行が混入していることがあります。ここでは、そうしたノイズを取り除いて、安定したデータを得るための工夫を紹介します。

• query IS NULLを除外して、CTRや平均順位のブレを減らす (1)
• HAVING total_clicks > 0を使うことでノイズデータを減らし、平均値を安定させる (2)
• Looker Studioのカスタムクエリ用に IFNULL(..., '(not provided)') を仕込んでおくと便利 (3)

1）実施例：クリーンな集計を行うSQL

以下のSQLは、先ほどの3つのコツをすべて反映した実装例です。

SELECT
  IFNULL(query, '(not provided)') AS query,  -- (3) NULL補完（Looker Studio向け）
  url AS page,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  ROUND(SUM(clicks) / NULLIF(SUM(impressions), 0), 4) AS ctr,
  ROUND(SUM(sum_position) / NULLIF(SUM(impressions), 0), 2) AS avg_position
FROM `project_id.searchconsole.searchdata_url_impression`
WHERE query IS NOT NULL
GROUP BY query, page
HAVING total_clicks > 0
  AND total_impressions > 50
  AND ctr > 0.01  -- (3) ノイズとなる低CTR行を除外
ORDER BY total_clicks DESC

このようにフィルタ条件を加えることで、ノイズを抑えた安定した傾向分析が可能になります。

先ほどご紹介した「実務で使う常用アウトプット」のサンプルスクリプトでも使用しています。

6. 高精度な分析のためのSQL Tips

以下のクエリは、誤差対策やノイズ除去を考慮した、より実務的な集計パターンです。実際のレポート作成や可視化用データとしても、そのまま使える形式になっています。

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,
  ROUND(SUM(sum_position) / NULLIF(SUM(impressions), 0), 2) AS avg_position
FROM `project_id.searchconsole.searchdata_url_impression`
WHERE query IS NOT NULL
  AND ROUND(SUM(impressions), 0) > 10
GROUP BY date, query, page
ORDER BY total_clicks DESC

7. おわりに

GSC Bulk Exportデータは非常に有用ですが、分析の技術によって評価は大きく変わります。
誤差を防ぐSQLの考え方や、NULL値への配慮は、計算の基礎であり、より信頼性の高いレポートに繋がります。

本記事を参考に、実務の分析フローに精度を持たせていただければ幸いです。
また、本記事に掲載しているSQLが上手く動かなかったときは、SNSなどでお気軽にお声がけください。（※初回相談【1時間以内】無料です）

8. 参考リンク

• Google公式ドキュメント：Search Console Bulk data export
• BigQuery スキーマ：searchdata_url_impression テーブル構造
• Search Console ヘルプ：パフォーマンスレポートの定義