執筆背景
データアナリストとして従事している現職で、そう言えばBigQueryからSearch Consoleの集計をできる人が自分以外にいないことを気づいた2025年10月。
私が事故って2ヶ月入院にでもなったらどうするんだ...
こりゃいかん!!!!!!!
ということで、search consoleの基本的な事と、使用頻度の高そうなSQLを簡単、分かり易く (当社比) まとめてみた。
1. なぜ、BigQueryにSearch Consoleのデータをエクスポートするのか
誤解を恐れず一言で言うと、「長期間・大規模なデータを自由に分析・活用できるから。」
Search Console UIはとても操作し易く、誰でも使える設計になっていると個人的には思う。反面、16ヶ月しか遡れない、自社データとジョインして深い分析をするニーズには応えることができない。
まとめてみると以下のようなPros/Consがある。
| 項目 | Search Console UI | BigQuery エクスポートデータ |
|---|---|---|
| 利点 | - 操作が簡単 - 可視化されている - フィルタや期間指定が直感的 - GSC管理画面から直接アクセス可能 |
- 生データが取得できる - 大規模データ分析に強い - データ保持期間が無制限 - 他のデータと結合しやすい |
| 欠点 | - データ保持期間は最大16か月 - データ量の制限（1000行） - 高度な分析が難しい |
- 初期設定が必要（リンク設定など） - SQLの知識が必要 - 2~3日差でのデータ反映 - エクスポート設定した日からしかデータが取れない |
2. URL Impression と Site Impression の違いと使い方
エクスポート設定が完了した後にぶち当たる壁。
URL impressionとSite impression、2つのデータセットの使い分けが珍紛漢紛であること。
ここでは端的すぎる説明でまとめてみる。
定義
| 種類 | 説明 |
|---|---|
| URL Impression | 検索結果に表示された特定の ページ単位（URL）での表示回数。 URLごとに記録されるため、同じクエリでも複数のURLが対象になることもある。 |
| Site Impression | 検索結果でドメイン単位での表示。 クエリ×日などで サイト全体として 1インプレッションとカウントされる。 同じクエリで複数URLが表示されても1とカウントされる。 |
使い分け
- URL Impression：ページごとのパフォーマンスを確認したい時（SEO施策の効果測定など）
- Site Impression：クエリや検索ワードがどれだけの人にリーチしているかを大まかに把握したい時
3. よく使うSQL集計パターン
ここでは、多くの人がまとめている集計を、何番煎じか分からないがまとめてみる。
① クエリ別のインプレッション、クリック数、CTR、掲載順位
SELECT
query,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
AVG(position) AS avg_position
FROM
`project.dataset.table`
WHERE
date BETWEEN '2025-09-01' AND '2025-09-30'
GROUP BY
query
ORDER BY
total_impressions DESC
LIMIT 100;
② ページ（URL）ごとのパフォーマンス
SELECT
page,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
AVG(position) AS avg_position
FROM
`project.dataset.table`
GROUP BY
page
ORDER BY
impressions DESC
LIMIT 100;
③ 日別のインプレッション・クリック推移
SELECT
date,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
FROM
`project.dataset.table`
GROUP BY
date
ORDER BY
date;
④ 特定クエリのランディングページ分析
SELECT
query,
page,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
AVG(position) AS avg_position
FROM
`project.dataset.table`
WHERE
query = '〇〇' -- 対象クエリを入れる
GROUP BY
query, page
ORDER BY
impressions DESC;
⑤ デバイス別のパフォーマンス比較
SELECT
device,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
AVG(position) AS avg_position
FROM
`project.dataset.table`
GROUP BY
device
ORDER BY
impressions DESC;
補足：BigQuery用 Search Console データの構造（例）
| カラム名 | 内容 |
|---|---|
| date | 日付 |
| query | 検索クエリ |
| page | 表示されたURL |
| country | 国 |
| device | 使用デバイス（DESKTOP, MOBILE, TABLET） |
| clicks | クリック数 |
| impressions | 表示回数 |
| ctr | クリック率（clicks ÷ impressions） |
| position | 掲載順位（平均） |
あとがき
とりあえず簡単にまとめてみたはいいものの、全然親切ではない気がした。
あとでも少し補足だったり、改訂版を出そうと思う。
特にクエリは現職ではほんの少し複雑なので、今後誰かの役に立つのを願って、掲載が許される範囲でまとめてみようと思う。