Claude Media
Search ConsoleのBigQueryをClaudeで分析 — 順位の急落を検知する
Claude CodeMCPClaude連携チュートリアル

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

Search Consoleの一括データエクスポートで溜めたBigQueryのテーブルを、Claude Codeから読み取り専用で問い合わせ、急落・急伸クエリを拾う手順。SQLの型と定期実行の選び方も載せます。

読了目安 約21分

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側の作業に分かれます。

Google Cloud側では、請求先を設定したプロジェクトでBigQuery APIとBigQuery Storage APIを有効にします。そのうえで、次のサービスアカウントをIAMに追加し、2つのロールを付与します。

項目値
サービスアカウント値search-console-data-export@system.gserviceaccount.com
付与するロール値BigQuery Job User(bigquery.jobUser)とBigQuery Data Editor(bigquery.dataEditor)

Search Console側では、対象プロパティの「設定 > 一括データエクスポート」を開き、Cloudプロジェクトを入力します。ここに入れるのはプロジェクト番号ではなく、プロジェクトIDです。データセット名は既定でsearchconsoleで、名前を変えても先頭はsearchconsoleになります。1つのプロジェクトに複数プロパティを書き出すなら、プロパティごとに別名を付けます。

データセットの場所は、最初のエクスポートで作成された後は簡単には変えられません。最初のエクスポートは設定の成功から最大48時間以内で、その日のデータを含みます。

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

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

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

テーブルと列の読み方

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

テーブル中身
searchdata_site_impression中身プロパティ単位で集計された成果データ(クエリ・国・検索タイプ・デバイス)
searchdata_url_impression中身URL単位で集計された成果データ。検索結果の見え方(リッチリザルト)のフラグも持つ
ExportLog中身成功したエクスポートの記録。失敗した試行は残らない

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

列意味
data_date意味データの日付。太平洋時間
query意味検索クエリ。匿名化クエリのときは空文字列(またはnull)
search_type意味web、image、video、news、discover、googleNews
impressions / clicks意味表示回数とクリック数
sum_top_position(site表)/ sum_position(url表)意味掲載順位の合計。0始まり

順位の計算には癖があります。列は順位の合計値で、しかも最上位を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 s.search_type = 'WEB'
    AND s.query != ''  -- 匿名化クエリ(空文字列・null)を除外
    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

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

  • data_dateの範囲をCURRENT_DATE()基準で絞り、パーティションのスキャン量を減らす(公式ガイドがクエリ費用の抑え方として挙げている方法)
  • 比較の窓は基準日から数える。data_dateは太平洋時間なので、日本時間の「昨日」と一致しない日がある
  • 前の期間の表示回数が少ないクエリは、順位のぶれが大きいのでHAVINGで外す

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

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

BigQuery MCPサーバーの追加は、次の1行で済みます。認証は/mcpでOAuthのサインインを済ませます。

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までにして、書き込み権限を渡さない構成が安全です。

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に読ませる使い方は成立しません。

分担は単純です。集計・比較・絞り込みはSQLに寄せ、Claudeには上位50行前後の結果だけを渡します。先ほどのSQLがLIMIT 50で終わるのはこのためです。行数を絞る効果は、料金にも表れます。オンデマンド課金では読み込んだバイト数で決まるため、日付範囲を指定しない「全期間で集計して」のような指示は避けます。

指示の型をCLAUDE.mdに置く

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

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

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

定期的に走らせる選択肢

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

経路実行場所使い分け
クラウドの定期実行(Routines)実行場所Anthropic管理のクラウド。自分のマシンは不要使い分け最小間隔は1時間。実行時は新規クローン、MCPはタスクごとにコネクタを設定
デスクトップの定期実行実行場所自分のマシン使い分けローカルのファイルと設定ファイルのMCPサーバーを使える
/loop実行場所自分のマシン(セッション内)使い分けセッションが開いている間だけ動く。繰り返しタスクは作成から7日で自動的に期限切れ

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

スクリプトから呼び出すなら、非対話のclaude -pも使えます。次は、読み取り専用ツールだけを自動承認し、結果をJSONで受ける例です。

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

--bareを付けると、.mcp.jsonのMCPサーバーを含む自動読み込みがすべて省かれます。その場合は--mcp-configでサーバーを渡す必要があり、認証もサブスクのログインではなくANTHROPIC_API_KEYが前提になります。無人実行でBigQuery MCPのOAuthトークンが使われるかどうかは、公式の資料に説明が見当たりません。手元で一度動かして確かめてから運用に乗せてください。

運用でつまずきやすい点

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

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

つまずき症状対処
平均掲載順位が1つ良く出る症状手計算の値と合わない対処+ 1を付ける。順位は0始まり
クエリの上位が空欄になる症状一番多いクエリが空文字列対処匿名化クエリをquery != ''で除外する
数字が合わない症状同じクエリの行が複数ある対処必ずSUMで集計する
エクスポートが失敗する症状テーブルに列を足した対処スキーマを戻す。パーティション期限は14日以上
結果が途中で切れる症状3,000行で打ち切られる対処SQL側で上位N件に絞る

エクスポートを止める場合は、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の画面と突き合わせても崩れにくくなります。

この記事を共有:XはてブLinkedIn

関連する記事

Claude Code をもっと見る →