Search ConsoleとBigQueryの連携方法｜一括エクスポート・保存確認・SQL集計

こんにちは、中都です。

「2年前のお正月キャンペーンでは、どんなキーワードから人が来ていたんだろう」。そう思ってSearch Consoleを開いても、保存期間を過ぎた検索データは見返せません。

Search Consoleの「一括データエクスポート」をBigQueryにつなぐと、検索データを日次で自動保存できます。この記事では、最初の設定から保存の確認、日別のクリック数・表示回数を集計するところまで説明します。

過去の施策を比較したいのに、検索データが残っていない

SEOを振り返るとき、こんなところで困っていませんか？

• 2年前のキャンペーンで、どんな検索語から人が来たのか分からない。
• 毎回ファイルをダウンロードして保存する作業が続かない。
• BigQueryへ連携したいけれど、Google Cloudの権限や設定で止まっている。

読み終わったら、次の3つを順に進められます。

1. Google Cloudの保存先と権限を整え、自動エクスポートを開始する。
2. BigQueryのデータとログを見て、実際に保存できたか確かめる。
3. 掲載したSQLを使い、日別のクリック数・表示回数を表にする。

最初の設定は、正直ちょっと大変です。でも、一度設定すれば毎日データが溜まっていく。この仕組み化ができるのは、めっちゃいいですよね。あとから比較したいと思ったときに、振り返る材料を残しておけます。

ただし、連携前の過去データが自動で取り込まれるわけではありません。これからの履歴を残すための設定として、進めていきましょう。

Search ConsoleとBigQueryを連携すると、何が変わる？

Search Consoleは、Google検索での表示回数やクリック数を確認するツールです。BigQueryは、そのデータを保存して、SQLという指示文で集計できるGoogle Cloudのサービスです。

こういうツールが無料で使えるのは、めちゃめちゃありがたいですよね。ただ、保存期間や画面に表示できる件数には制限があります。

Search Consoleの検索パフォーマンスは、通常、過去16か月を振り返る範囲です。また、画面の表には最大1,000行という制限があります。BigQueryへの一括エクスポートは、画面の表を毎日コピーする作業とは別の仕組みで、検索パフォーマンスデータを日次で保存できます。Google公式の期間の説明とデータの制限も参照してください。

過去の数字を自社に残しておくと、キャンペーンや記事更新の前後を、同じ条件で振り返る材料になります。

設定前に確認するアカウント・権限・料金

用意するものは、対象サイトのSearch Consoleと、出力先のGoogle Cloudプロジェクトです。Search Consoleでは対象プロパティの所有者（オーナー）権限が必要です。普段レポートを見られるだけでは、一括エクスポートを開始できない場合があります。Google Cloud側では、プロジェクトの作成・選択、請求先の設定、APIの有効化、IAM権限の付与ができる担当者と進めてください。

この連携では、Google Cloudプロジェクトへの請求先アカウントの設定が必要です。BigQueryには無料枠がありますが、保存量とクエリで処理するデータ量によって費用が発生します。無料枠の対象と、枠を超えた場合の単価は、BigQueryの料金で利用条件を確認しましょう。

以下の画像は解説時の操作画面です。現在のメニュー表記が少し違う場合も、設定する項目は本文とGoogle公式の設定手順を照らし合わせてください。

手順1．Google Cloudで保存先と権限を準備する

1-1．プロジェクトを作成・選択する

Google Cloudコンソールを開き、上部のプロジェクト選択を押します。新しく用意する場合は「新しいプロジェクト」を選び、用途が分かる名前を入力します。作成画面のプロジェクトIDも確認してから、「作成」を押してください。たとえば「SEOデータ保存用」としておくと、後で見分けやすくなります。

作成が終わったら、そのプロジェクトに切り替えます。すでに会社で使っている保存先がある場合は、管理者に確認して既存のものを選んで構いません。後で入力するのは表示名や数字だけのプロジェクト番号ではなく、プロジェクトIDです。プロジェクト選択画面のIDをコピーして控えておきます。Search Console側にも、このIDを入力します。

プロジェクトIDは作成後に変えられないので、作成前に確認しておきましょう。表示用のプロジェクト名とは別の項目です。プロジェクト名とIDの公式説明

1-2．請求先アカウントを設定する

同じプロジェクトで「お支払い」を開き、利用する請求先アカウントをリンクします。BigQueryに「サンドボックス」と表示される場合は、「アップグレード」から請求先を設定する画面にも進めます。初めてGoogle Cloudを使う場合は、先に請求先アカウントと支払い方法の登録が必要です。

設定後は「お支払い」で、選んだプロジェクトが意図した請求先につながっているか確かめます。サンドボックスのままではテーブルなどが60日で期限切れになるため、長期保存の前提にできません。請求を有効にした後も、データセット・テーブル・パーティションに有効期限が残っていないか確認してください。サンドボックスの制限とアップグレード

1-3．2つのBigQuery APIが有効か確認する

左上のメニューから「APIとサービス」→「有効なAPIとサービス」を開き、BigQuery APIとBigQuery Storage APIがあるか確認します。最初から有効になっている場合もあるので、まず一覧を見てみましょう。両方とも有効なら、追加の操作は不要です。

見つからない場合は「APIとサービスを有効にする」を押し、それぞれの名前を検索して詳細画面の「有効にする」を押します。2つとも有効なAPIの一覧に載れば、この作業は完了です。

1-4．Search Consoleに書き込み権限を付ける

メニューから「IAMと管理」→「IAM」を開き、「アクセスを許可」を押します。「新しいプリンシパル」に、次のGoogleのサービスアカウントを貼り付けます。自分のメールアドレスを入れる欄ではありません。

search-console-data-export@system.gserviceaccount.com

ロールを次の2つにします。「別のロールを追加」で2つ目を選び、「保存」を押してください。

• BigQuery ジョブユーザー（roles/bigquery.jobUser）
• BigQuery データ編集者（roles/bigquery.dataEditor）

IAM一覧にこのサービスアカウントが追加され、2つのロールが表示されれば完了です。これはSearch Consoleがデータを書き込むための設定なので、サービスアカウントのJSONキーを作成したり、配布したりする必要はありません。

「ドメインで制限された共有」などで保存できないときは、エラー内容と上記のサービスアカウント・必要ロールを組織の管理者へ伝えてください。会社全体の共有制限を一律に解除するのではなく、対象アカウントを許可する方法を判断してもらいます。Google Cloudのドメイン制限の説明

手順2．Search Consoleで一括エクスポートを開始する

2-1．対象サイトの「一括データエクスポート」を開く

Search Consoleを開き、左上で保存したいプロパティを選びます。「設定」→「一括データエクスポート」を押してください。複数サイトを管理している方は、ここで別のサイトを選んでいないか確認します。

2-2．プロジェクトID・データセット名・場所を入力する

「CloudプロジェクトID」には、手順1で控えたIDを入力します。「データセット名」は標準のsearchconsoleでも構いません。名前を変える場合も、先頭をsearchconsoleにします。たとえばsearchconsole_exportです。複数のプロパティを同じCloudプロジェクトへ出すときは、プロパティごとに別のデータセット名を使います。

続いて「データセットの場所」を選びます。これはデータを保存する地域です。僕は東京を使っていますが、会社の保存方針や、後で組み合わせるGA4などの保存先を先に確認してください。画像は入力途中で「米国（US）」になっている状態なので、そのまま選ぶ必要はありません。

場所は開始後に気軽に切り替えられる設定ではありません。別の場所への移行には追加作業が必要になるため、確定する前に保存先をそろえると後で困りにくくなります。

2-3．確認してエクスポートを設定する

「続行」を押し、確認画面のプロジェクトID・データセット名・場所を見直して「エクスポートを設定」を押します。「一括データエクスポートは有効です」と表示されれば、設定が受け付けられています。

ただし、この表示だけでデータ保存まで終わったわけではありません。最初のエクスポートは、正しく設定してから最大48時間以内に行われます。すぐにBigQueryへデータが出なくても、何度も設定を作り直す前に次の確認へ進んでください。

手順3．BigQueryに保存されたデータを確認する

Google Cloudのメニューから「BigQuery」→「Studio」を開き、エクスプローラで設定したプロジェクトを展開します。その下のsearchconsole、または自分で指定したデータセットを開いてください。初回出力が終わると、次のテーブルを確認できます。

まずsearchdata_url_impressionを選び、「プレビュー」を開きます。日付、URL、検索語、クリック数、表示回数などの列にデータが入っているかを見てください。下の画像では、検索語と国・検索タイプ・デバイスの列が確認できます。

ExportLogも開き、data_date（データの日付）、namespace（出力対象）、publish_time（出力完了日時）を見ます。データテーブルごとに出力時刻が異なることがあります。設定画面が有効で、データの行があり、出力ログが記録されているところまで確認します。設定の受付と、実際に保存されたことは分けて確かめましょう。テーブルの公式リファレンス

毎日のデータには処理の遅れがあり、当日分がすぐそろうわけではありません。日付は太平洋時間を基準にしているため、日本時間の別レポートと突き合わせるときにも注意が必要です。匿名化された検索語は空欄などになり、BigQueryに保存しても検索語の文字列を復元することはできません。

また、連携前の過去データは一括エクスポートで自動補完されません。今ある履歴も残したい場合は、取得可能な期間についてSearch ConsoleのレポートやAPIから別途保存します。後で分析するときは、一括エクスポートのデータと出どころを分けて扱ってください。

手順4．日別クリック数・表示回数をSQLで集計する

データが入ったら、BigQuery Studioの「新しいクエリ」または「＋」からSQLエディタを開きます。最初は、日別のクリック数と表示回数を1つの表にするところから試してみましょう。

以下は、この記事用の補足SQLです。動画で案内している配布SQLとは別の例で、プロパティ単位のテーブルを使い、直近28日の日付範囲に絞っています。your-project-idとsearchconsoleを、自分のプロジェクトID・データセット名に置き換えてください。

SELECT
  data_date,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1
    AS average_position
FROM `your-project-id.searchconsole.searchdata_site_impression`
WHERE search_type = 'WEB'
  AND data_date BETWEEN
    DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 28 DAY)
    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
GROUP BY data_date
ORDER BY data_date DESC;

貼り付けたら、参照先と処理するデータ量の見積もりを確認して「実行」を押します。下部の「クエリ結果」に日付・クリック数・表示回数が並べば、集計できています。下の実演画像は、日別の結果表が返ってきた例です。

エクスポートには同じ日付・検索語などの行が複数含まれることがあるため、SUMでまとめます。CTRは行ごとの値の平均ではなく「クリック合計÷表示回数合計」。0.05なら5％です。平均掲載順位は元の位置が0から始まるため、最後に1を足して1位基準にしています。集計方法と公式SQLの例

結果を照合するときは、Search Consoleでも同じプロパティ・日付・検索タイプに合わせます。保存開始が最近なら28日分がそろっていないのは自然です。最新日が未出力の場合も、空欄を勝手に0として補わず、実際に保存されている日付から読み始めてください。

日別集計ができたら、URL別の比較や、自社名を含む検索語と含まない検索語の比較にも広げられます。検索語の比率を出す場合は、匿名化された検索語を分類できないため、見えている検索語だけの比率なのか、全体に対する値なのかを分けて説明します。

日別の数字を確認する作業は、AIとの会話でも進められます。普段の分析をもっと手軽にしたい方は、末尾のSynapse MCPを使った実例も参考にしてみてください。

連携できない・データが出ないときの確認先

「一括データエクスポートに失敗しました」とメールが届いたら

設定後でも、一括エクスポートが失敗することがあります。一時的でないエラーが発生すると、Search Consoleからプロパティの所有者・フルユーザーへ通知メールが届きます。対象の日付・テーブル・エラーの内容を確認しましょう。下は実際に届いた通知です。

この例の原因は「Cloud プロジェクトが存在しません」です。メールの「クラウド プロジェクトの管理」やGoogle Cloudコンソールから対象を開き、Search Consoleに入力したプロジェクトIDと一致しているか確認します。表示名やプロジェクト番号を入れていないか、コピー元のIDを見直してください。

別のエラーなら、その内容に合わせて請求先・API・IAM権限などを直します。通知のエラーを起点に、該当する設定を確認すると、あちこち触らずに原因を絞れます。対応内容はGoogle公式のエラー一覧でも確認できます。

修正したら、Search Consoleの「設定」→「一括データエクスポート」に戻り、「レポートをテスト」で設定を確認します。ただし、テストに通ってもすぐに再出力されるわけではありません。約1日後に最新の状態とBigQueryのデータを確認してください。設定テストの成功だけで、データ保存まで復旧したとは判断しないようにしましょう。成功した出力はExportLogにも記録されます。

エラーの修正を後回しにしないことも大切です。同じ日付の出力に約1週間失敗し続けると、その日付の再試行が止まります。さらに約1か月失敗が続くと一括エクスポート自体も停止します。修正後にすべての欠損が埋まるとは限らないので、通知が来たら早めに確認しましょう。Google公式の監視・復旧手順

保存用の元テーブルに列を追加したり、名前を変えたりするのも避けてください。独自の加工をしたいときは、別のテーブルやビューに分けます。

まず保存を始めて、最初のデータを確かめましょう

SQLが難しそうに感じても、必要なデータを先に蓄積しておく価値はあると思っています。後でAIに分析を手伝ってもらうとしても、残していない自社の過去データは取り戻せないからです。

最初に目指すのは、設定を終え、BigQueryにデータが入り、日別の表を1回出せること。出力エラーの通知にも目を通しながら、あとで振り返れるデータを残していきましょう。

日々の検索データは、Synapse MCPでAIに聞いてみましょう

データを保存できたら、次は「最近、検索からのクリックは増えているのか」も気になりますよね。毎回管理画面を開いて集計する代わりに、AIとの会話で確認する方法もあります。

僕が提供しているSynapse MCPは、Search ConsoleやGA4のデータをClaude・ChatGPTなどのAIから取得するためのツールです。Search Consoleを接続すれば、自然言語でクリック数・表示回数を尋ねられます。

Search Consoleのクリック数・表示回数を、会話の中で確認する

たとえば、最初はこのように聞いてみてください。

接続したサイトのSearch Consoleから、昨日までの7日間について、ウェブ検索の日別クリック数・表示回数を表にして。対象サイトと集計期間も示して。未取得の日があれば、0と区別して教えて。

このように、まず表で数字を確認し、気になった日やページについて質問を続けられます。長期の履歴はBigQueryに蓄積し、日々の確認はSearch ConsoleからAIへ取得する、という使い分けです。この接続では、今回BigQueryに保存した履歴そのものは読み出しません。

GA4もつなげば、PVやよく読まれる記事を聞ける

Search Consoleのクリック数は、サイト全体のPVとは別の数字です。サイト内で何が読まれたかも知りたい場合は、同じサイトのGA4を追加で接続します。

GA4を接続すると、探索レポートの作り方を知らなくても、自然言語でPVを集計できます。「先月と今月を比較したい」「どの記事がよく読まれている？」という疑問を、たとえば次のように伝えます。

今月1日から昨日までと、先月の同じ日数のPVを比較して。対象のGA4プロパティと集計期間を示し、差分と増減率を表にして。

先月、よく読まれたページをPVの多い順に10件出して。ページのタイトルとURL、PVを表にして。

月の途中で前月全体と比べると日数が違うので、比較期間をそろえるのがコツです。Search ConsoleもGA4もデータの処理に時間がかかるため、返ってきた対象・期間・指標を確認してから数字を読みましょう。

ここで紹介した実演の流れは、GA4とSearch ConsoleでAIレポートを作る記事で詳しく説明しています。GA4も試したい方は、GA4をAIにつなぐ手順も参考にしてください。

自分のサイトの数字を、AIに1回聞いてみる

まずはSearch Consoleだけの接続で始められます。Synapse MCPにログインし、対象の店舗・案件で「サービスを追加」からSearch Consoleを選びます。Google認証でサイトを選び、同じ店舗・案件のMCPリンクを発行して、利用するAIに登録してください。

自分のサイトのクリック数・表示回数が、AIの会話に返ってくるところが最初のゴールです。下のリンクからログインして、最初に載せたSearch Consoleの日別集計の質問を試してみてください。接続に迷ったら、画像付きの接続手順を開きながら進められます。

Search Consoleをつないで、AIにクリック数を聞いてみる →

新規フリー契約は14日間、3サービス・各サービス1アカウントまでで、自動課金はありません。料金と接続条件、利用するAI側の外部接続機能・プランの条件を確認して始めてください。