Search ConsoleのBigQueryをClaudeで分析 — 順位の急落を検知する

Search Consoleの一括データエクスポートで毎日BigQueryに溜まるデータは、Claude Codeから読み取り専用で問い合わせられます。集計はSQL側で済ませ、Claudeには「先週と比べて急落・急伸したクエリ」だけを読ませる。この分担にすると、行数上限や課金を抑えたまま異常変動のレポートまで任せられます。

この記事では、エクスポートの前提条件、テーブルの読み方、異常検知用のSQL、Claude Codeに渡す指示と権限、定期実行の選び方を順に扱います。BigQuery MCPサーバー自体の接続手順はClaude BigQuery連携の解説にあり、ここでは省きます。

一括データエクスポートは誰に向くか

Search Consoleの一括データエクスポートは、日次の成果データをBigQueryのプロジェクトへ継続的に書き出す機能です。匿名化クエリ(プライバシー保護で除外される稀なクエリ)を除く成果データが対象で、UIやAPIにある1日あたりの行数上限の影響を受けません。

Googleは、数万ページ規模のサイトや、1日に数万クエリの流入があるサイトに特に向くと説明しています。小〜中規模のサイトは、Looker Studioコネクタ(旧Data Studio)やSearch Analytics APIで全データに届くため、無理に導入する必要はありません。

Claudeで日々の変動を追う用途なら、次の条件がそろうと効果が出ます。

• 監視したいクエリが数百〜数千あり、目視のチェックが追いつかない
• ページ×クエリの単位で順位の推移を見たい
• 過去の履歴をBigQuery側に溜めて、後から自由な切り口で集計したい

エクスポートを始める前の準備

設定できるのはプロパティの所有者だけです。作業はGoogle CloudとSearch Consoleの2か所にまたがります。

次の3点は、後から困りやすいので先に押さえておきます。

• 設定前の履歴は溜まらない。過去分が要るときはSearch Consoleのレポートや、Search Console APIで取得する
• テーブルとパーティションは既定で無期限に保持される。パーティションに有効期限を付けるなら14日以上にする
• テーブルのスキーマを変更しない。列を足すだけでエクスポートが失敗する

BigQueryの保存料金とクエリ料金は自分のプロジェクトにかかります。無料枠はありますが、超えた分は課金されます。

テーブルと列の読み方

書き出されるのは3つのテーブルです。

異常検知で使う主な列は次のとおりです。

順位の計算には癖があります。列は順位の合計値で、しかも最上位を0とする0始まりです。平均掲載順位はSUM(sum_top_position) / SUM(impressions) + 1で求めます。この+ 1を落とすと、すべての順位が1つ良く見えます。

もう1つの癖は、行が「日付×クエリ」で1行にまとまっているとは限らない点です。同じキーの行が複数あり、圧縮もされないまま書き出されます。公式ガイドも、指標は必ずSUMなどで集計するよう求めています。集計を忘れたSQLは、一番人気のクエリでさえ正しく返せません。

急落・急伸クエリを拾うSQL

直近7日と、その前の7日を比べるSQLの例です。公式サンプルの集計の書き方(SUMで集計、匿名化クエリの除外、日付範囲の限定)に沿って組んだ形なので、自分のデータセットで一度実行して列名と結果を確認してから使ってください。

WITH anchor AS (
  -- 最新の取り込み済みの日付を基準日にする
  SELECT MAX(data_date) AS d
  FROM searchconsole.searchdata_site_impression
  WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)
),
base AS (
  SELECT
    s.query,
    s.impressions,
    s.clicks,
    s.sum_top_position,
    s.data_date > DATE_SUB(a.d, INTERVAL 7 DAY) AS is_cur
  FROM searchconsole.searchdata_site_impression AS s
  CROSS JOIN anchor AS a
  WHERE LOWER(s.search_type) = 'web'
    AND NOT s.is_anonymized_query  -- 匿名化クエリを除外
    AND s.data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 16 DAY)
    AND s.data_date > DATE_SUB(a.d, INTERVAL 14 DAY)
)
SELECT
  query,
  SUM(IF(is_cur, clicks, 0)) AS clicks_cur,
  SUM(IF(NOT is_cur, clicks, 0)) AS clicks_prev,
  SAFE_DIVIDE(SUM(IF(is_cur, sum_top_position, 0)),
              SUM(IF(is_cur, impressions, 0))) + 1 AS pos_cur,
  SAFE_DIVIDE(SUM(IF(NOT is_cur, sum_top_position, 0)),
              SUM(IF(NOT is_cur, impressions, 0))) + 1 AS pos_prev,
  SUM(IF(NOT is_cur, impressions, 0)) AS imp_prev
FROM base
GROUP BY query
HAVING imp_prev >= 100
ORDER BY ABS(clicks_cur - clicks_prev) DESC
LIMIT 50

設計のポイントは5つあります。

• data_dateの範囲をCURRENT_DATE()基準で絞り、パーティションのスキャン量を減らす(公式ガイドがクエリ費用の抑え方として挙げている方法)
• 比較の窓は基準日から数える。data_dateは太平洋時間なので、日本時間の「昨日」と一致しない日がある
• 前の期間の表示回数が少ないクエリは、順位のぶれが大きいのでHAVINGで外す
• search_typeの値は、テーブルのスキーマ説明ではweb、公式のサンプルSQLでは'WEB'と書かれています。LOWER()で比べれば、どちらの表記でも通ります
• 匿名化クエリも、スキーマ説明の一方ではnull、もう一方では空文字列とあります。is_anonymized_queryで除外すれば、どちらでも崩れません

ページ単位で見たいときは、テーブルをsearchdata_url_impressionに替え、順位の列をsum_positionにして、urlでもGROUP BYします。「どのページのどのクエリで落ちたか」まで絞れるので、原因調査がそのまま始められます。

Claude Codeに読み取り専用で問い合わせさせる

BigQuery MCPサーバーの追加は、次の1行で済みます。認証はOAuth 2.0です。/mcpでサインインでき、完了しないときはOAuthクライアントIDを--client-idで渡します。

claude mcp add --transport http bigquery https://bigquery.googleapis.com/mcp

Google側の権限は、MCPツールの呼び出しにroles/mcp.toolUser、ジョブ実行にroles/bigquery.jobUser、参照にroles/bigquery.dataViewerが要ります。Search Consoleのデータセットに付けるのはdataViewerまでにして、書き込み権限を渡さない構成が安全です。Googleは、MCPを使うエージェント用に別のIDを作り、アクセスを管理・監視しやすくすることを勧めています。

Claude Code側でも、ツールを絞る設定を置けます。BigQuery MCPサーバーには書き込みを含むexecute_sqlと、読み取り専用のexecute_sql_readonlyがあります。次の設定は、後者だけを許可する例です。サーバー名は追加時に付けたbigqueryです。

{
  "permissions": {
    "allow": ["mcp__bigquery__execute_sql_readonly"],
    "deny": ["mcp__bigquery__execute_sql"]
  }
}

MCPツールの権限ルールはmcp__<サーバー名>__<ツール名>の形で書きます。この設定をプロジェクトの.claude/settings.jsonに置けば、異常検知のセッションでは書き込み系のツールが使えなくなります。

MCPのサーバーやスコープの扱いはClaude Code MCP設定ガイド、読み取り専用で始める設計の考え方はMCPからデータベースに接続する方法にあります。

MCPの制限とSQLの分担

BigQuery MCPサーバーには、クエリの処理が既定で3分まで、返る結果が最大3,000行までという制限があります。Search Consoleのテーブルは大きくなりやすいので、生データをClaudeに読ませる使い方は成立しません。

行数を絞る効果は、料金にも表れます。クエリは実行すると課金され、日付範囲を絞るほど読み込む量も減るので、日付範囲を指定しない「全期間で集計して」のような指示は避けます。

指示の型をCLAUDE.mdに置く

毎回のプロンプトに条件を書くと、ぶれが出ます。判定の基準はプロジェクトのCLAUDE.mdに置いておくと、誰が呼んでも同じ観点でレポートが出ます。例えば次のような断片です。

## Search Console異常検知の手順
- データは `searchconsole` データセットのテーブルのみを使う。
- `execute_sql_readonly` だけを使う。必ず `data_date` の範囲を指定する。
- 指標は必ず SUM で集計する。平均掲載順位は SUM(sum_top_position) / SUM(impressions) + 1。
- `is_anonymized_query` が true の行(匿名化クエリ)は除外する。
- 直近7日と前7日を比べ、クリック数の増減が大きい上位のクエリを報告する。
- 前の期間の表示回数が100未満のクエリは、順位の判断に使わない。
- 結果は表にして、急落・急伸・要観察の3区分で出す。原因は推測で断定しない。

最後の1行が要です。BigQueryのデータからわかるのは「何が動いたか」までで、「なぜ動いたか」は読み取れません。原因の候補を挙げさせるのはよいのですが、断定はさせない。この線引きを指示に入れておくと、レポートが信頼できる形になります。

定期的に走らせる選択肢

異常検知は、毎日か毎週の定期実行にして初めて効きます。Claude Codeの定期実行には3つの経路があり、性質が違います。

/loopは手軽ですが、7日で消えるうえ、ターミナルを閉じれば止まります。日次のレポートを恒常的に出すなら、クラウドかデスクトップの定期実行を選びます。デスクトップでの設定はClaude Code Desktopで定期実行タスクをスケジュールするで手順を扱っています。

スクリプトから呼び出すなら、非対話のclaude -pも使えます。次は、読み取り専用ツールだけを自動承認し、結果をJSONで受ける例です。--max-budget-usdは1回の実行で使うAPI料金の上限で、v2.1.289のclaude --helpでは--printと組み合わせたときだけ効くとされています。

claude -p "searchconsoleデータセットで直近7日と前7日を比べ、\
クリック数が大きく動いたクエリを表にして" \
  --allowedTools "mcp__bigquery__execute_sql_readonly" \
  --max-budget-usd 1 \
  --output-format json | jq -r '.result'

--bareを付けると、フックやキーチェーンの読み取りに加えて、CLAUDE.mdの自動探索も省かれます。v2.1.289のclaude --helpでは、Anthropicの認証はANTHROPIC_API_KEYか--settings経由のapiKeyHelperだけで、OAuthとキーチェーンは読まないとされています。MCPサーバーは--mcp-configで、CLAUDE.mdを置いたディレクトリは--add-dirで渡します。

つまり、先ほどのCLAUDE.mdの手順は、--bareでは指定しない限り読み込まれません。BigQuery MCPの認証はGoogle側の資料ではOAuth 2.0とIAMです。Claude Codeにはヘッダーで認証を渡す方式(claude mcp add --headerやheadersHelper)もあり、サーバーがその認証を受け付けるときに使えます。無人で回す前に、同じ指示を手動で一度通し、execute_sql_readonlyが呼ばれるところまで見てください。

運用でつまずきやすい点

エクスポートが止まっていても、気づかないまま分析を続けてしまう。これが最大の落とし穴です。Search Consoleは、非一時的なエラーが起きると、その日のエクスポートを翌日まで再試行しません。欠けた日の再試行は約1週間で打ち切られ、失敗が約1か月続くとエクスポート全体が止まります。

対策として、週次のレポートの冒頭でExportLogを確認させます。publish_timeとepoch_versionを見れば、当日分が入っているか、過去分が修正されたかがわかります。epoch_versionは、Search Consoleが過去のデータを直した場合に1ずつ増える値です。

エクスポートを止める場合は、Search Consoleの設定で「Deactivate export」を押します。停止は24時間以内に反映され、その間に1回分のエクスポートが入ることがあります。既存のテーブルは削除されません。

Search Console APIとの使い分け

Search Console APIから直接取る方法は、小〜中規模のサイトなら十分です。設定不要で、過去の期間もさかのぼれます。BigQueryのエクスポートが勝つのは、履歴を自前で溜めて、SQLで自由に切れる点です。クエリ×ページ×日付の粒度で、行数上限を気にせず持てます。

一方で、エクスポートは設定した日から先のデータしか溜まりません。今すぐ過去の動きを見たいなら、API側から取るのが近道です。両者を併用し、履歴の起点まではAPI、以降はBigQueryという構成も成立します。

BigQueryを分析全般に使う場面は、BigQueryをClaudeで自然言語のまま分析するにまとめています。

まとめ

Search ConsoleのBigQueryエクスポートを、Claudeが読める形にするコツは、役割の切り分けです。集計と比較はSQL、要約と区分けと報告はClaude。権限はexecute_sql_readonlyに絞り、基準はCLAUDE.mdに置き、定期実行は/loopではなくクラウドかデスクトップに任せます。

順位の+ 1、匿名化クエリの除外、SUMによる集計。この3つを守れば、Claudeが出す急落・急伸の表は、Search Consoleの画面と突き合わせても崩れにくくなります。