Zenn
🐡

BigQuery×SearchConsole 正規表現以外のグルーピング方法

2024/12/26に公開
BigQuery
Search Console
tech

はじめに

Google Search Console（サーチコンソール）のデータを BigQuery で分析することで、より詳細な SEO 分析が可能になります。この記事では以下の内容を解説します：

  1. サーチコンソールデータの基本構造と集計方法の違い
  2. 平均順位の計算方法
  3. ページグループを使った分析手法

前提知識

  • BigQuery の基本的な操作方法
  • Google Search Console の基本的な理解
  • SQL の基本文法（SELECT, JOIN, GROUP BY など）

サーチコンソールデータの構造

テーブルの種類と集計方法の違い

この記事を書くにあたり、サーチコンソールのブラウザでの数値とbigqueryでの数値の差異について調べ直しました。その差異は二つのテーブルのデータの集計方法にあるということを知ったので簡単に記載します。

サーチコンソールのデータは、BigQuery に主に 2 つのテーブルとしてエクスポートされます。これらのテーブルは集計方法が異なるため、同じ取り出し方でも異なる結果になることがあります：

  1. searchdata_site_impression

    • プロパティ（サイト）全体のパフォーマンスデータ
    • クエリ、国、デバイスなどの集計データを含む
    • 重要: 同一クエリで複数のページが表示された場合、表示回数は 1 として集計
    • 一番良い順位のページのデータのみが記録される

  2. searchdata_url_impression

    • URL 単位での詳細なパフォーマンスデータ
    • 個別 URL のインプレッション数、クリック数、順位情報を含む
    • 重要: 同一クエリで複数のページが表示された場合、各ページごとに個別に集計

集計方法の違いの具体例

例えば、ユーザーが「葬儀 口コミ」で検索した際に、以下のように複数のページが表示されたケースを考えてみましょう：

  1. /review/123 (3 位)
  2. /area/tokyo (5 位)
  3. /company (8 位)

この場合、以下のように集計されます：

  • searchdata_site_impression の場合：

    • インプレッション数: 1 回
    • 順位: 3 位（最も良い順位のみ記録）

  • searchdata_url_impression の場合：

    • /review/123: インプレッション 1 回、順位 3 位
    • /area/tokyo: インプレッション 1 回、順位 5 位
    • /company: インプレッション 1 回、順位 8 位

使い分けのポイント

それぞれのテーブルの特性を活かした使い分けが重要です：

  • searchdata_site_impression の活用シーン

    • サイト全体のパフォーマンス把握
    • 検索クエリ全体の傾向分析
    • 競合サイトとの比較

  • searchdata_url_impression の活用シーン

    • 個別ページの詳細な分析
    • コンテンツ改善の効果測定
    • URL 構造ごとのパフォーマンス比較

主要なフィールド

今回の分析では主に searchdata_url_impression テーブルを使用します。このテーブルの主要なフィールドは：

  • data_date: データの日付
  • url: 検索結果に表示された URL
  • impressions: インプレッション数
  • clicks: クリック数
  • sum_position: 検索順位の合計値（平均順位の計算に使用）

平均順位の計算方法

サーチコンソールの平均順位は、sum_position と impressions を使用して計算します。Google Search Console では検索順位の 1 位が 0 として記録されるため、実際の順位に変換するために 1 を加算する必要があります。

SELECT
    data_date,
    SUM(sum_position) / SUM(impressions) + 1 AS average_rank
FROM
    `<project_id>.searchconsole.searchdata_url_impression`
WHERE
    url = 'https://example.com/some-page'
    AND data_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    data_date
ORDER BY
    data_date

注意点:

  • data_date でパーティション分割されているため、日付範囲を指定することでクエリのパフォーマンスが向上します
  • sum_position は各インプレッションでの順位の合計値です
  • 平均順位を求めるには、必ず SUM(sum_position) / SUM(impressions) の形式で計算する必要があります

クエリの実行結果例

上記のクエリを実行すると、以下のような結果が得られます：

data_date average_rank
2024-01-01 12.5
2024-01-02 13.2
2024-01-03 14.1
... ...

この結果から、指定した URL の平均順位の日次推移を確認できます。

ページグループごとの分析

テーブル構造の説明

葬儀の口コミでは、さまざまな種類のページが存在し、正規表現だけではグルーピングが難しいケースがあります。そこで、ページグループのテーブルを BigQuery に作成して管理しています。

※ ページのグルーピングはもちろん正規表現で行ったり、looker studio でカスタムフィールドを作ったり、データソースに直接追加したりなどの方法があります。しかし、ページ数が多すぎるとクエリの可読性が損なわれたり、looker studio の処理が重くなりすぎて使い物にならなくなります。なので私はこの方法でページのグルーピングを行っています。

以下の 2 つのテーブルを使用します：

  1. <project_id>.<dataset_name>.url_grouping テーブル
    • URL とページグループの対応関係を管理
    • 例：
group_id url
1 https://example.com/review/123
2 https://example.com/company/456
3 https://example.com/service/789
... ...
  1. <project_id>.<dataset_name>.group_name テーブル
    • グループ ID と名前の対応関係を管理
    • 例：
id group_name
1 口コミページ
2 市区町村
3 都道府県ページ
... ...

このようなテーブル設計により、以下のメリットがあります：

  • 正規表現では捉えきれない複雑なグルーピングが可能
  • テーブルの更新だけで柔軟に分類を変更可能
  • 過去に遡って分析が可能

グループごとの平均順位を計算するクエリ

WITH
  PageRanks AS (
    SELECT
        data_date,
        url,
        SUM(sum_position) / SUM(impressions) + 1 AS average_rank
    FROM
        `<project_id>.searchconsole.searchdata_url_impression`
    WHERE
        data_date BETWEEN '2024-01-01' AND '2024-01-31'
    GROUP BY
        data_date, url
  ),
  URLGroups AS (
    SELECT
        ug.url,
        gn.group_name
    FROM
        `<project_id>.<dataset_name>.url_grouping` AS ug
    INNER JOIN
        `<project_id>.<dataset_name>.group_name` AS gn
    ON
        ug.group_id = gn.id
  )
SELECT
    pr.data_date,
    ug.group_name,
    AVG(pr.average_rank) AS group_average_rank
FROM
    PageRanks pr
INNER JOIN
    URLGroups ug
ON
    pr.url = ug.url
GROUP BY
    pr.data_date, ug.group_name
ORDER BY
    pr.data_date, group_average_rank

このクエリにより、以下のような結果が得られます：

data_date group_name group_average_rank
2024-01-01 口コミページ 12.5
2024-01-01 市区町村ページ 15.2
2024-01-02 口コミページ 11.8
2024-01-02 市区町村ページ 14.9

この結果から、各ページグループの平均順位の推移を確認でき、コンテンツタイプごとの SEO パフォーマンスを把握できます。

まとめ

  • ページグループのテーブルを作成することで、柔軟な分析が可能になります
  • BigQuery では外部データの取り込みも可能なため、自社データやスプレッドシートのデータと組み合わせた分析も検討できます
  • 平均順位の計算では、必ず合計値同士の除算を行い、最後に 1 を加算する点に注意が必要です

参考文献

Discussion