🔍

Google Search Console の BigQuery 一括データエクスポートのサンプルクエリ

2023/03/22に公開

SEO

BigQuery

Search Console

tech

一括データエクスポートとは

2023/2/21にGoogle Search Console のデータをBigQueryにエクスポートする機能が発表された。

ここでは、一括データエクスポートで使用するクエリのサンプルについてご紹介したい。

テーブルについて

テーブルの説明

エクスポートログ
- テーブル: <project_id>.searchconsole.Exportlog
- 一括データエクスポートの実施ログが毎日追記される。分析には使用しない。
サイトのインプレッション
- テーブル: <project_id>.searchconsole.searchdata_site_impression
- サイトのインプレッションのデータが分析できる
URLのインプレッション
- テーブル: <project_id>.searchconsole.searchdata_url_impression
- URLのインプレッションのデータが分析できる

サイトのインプレッションと URLのインプレッションの違い

ざっくり言えば、あるキーワードでGoogle検索したときに、検索結果上に自社のサイトが複数表示されていた場合に、個別URLごとに数えるか（URL）、検索のユニーク回数で数えるか（サイト）の違い。以下の記事が詳しい。

スキーマ

エクスポートログ (Exportlog)

フィールド名	種類
agenda	STRING
namespace	STRING
data_date	DATE
epoch_version	INTEGER
publish_time	TIMESTAMP

サイトのインプレッション (searchdata_site_impression)

フィールド名	種類
data_date	DATE
site_url	STRING
query	STRING
is_anonymized_query	BOOLEAN
country	STRING
search_type	STRING
device	STRING
impressions	INTEGER
clicks	INTEGER
sum_top_position	INTEGER

URLのインプレッション (searchdata_url_impression)

フィールド名	種類
data_date	DATE
site_url	STRING
url	STRING
query	STRING
is_anonymized_query	BOOLEAN
is_anonymized_discover	BOOLEAN
country	STRING
search_type	STRING
device	STRING
is_amp_top_stories	BOOLEAN
is_amp_blue_link	BOOLEAN
is_job_listing	BOOLEAN
is_job_details	BOOLEAN
is_tpf_qa	BOOLEAN
is_tpf_faq	BOOLEAN
is_tpf_howto	BOOLEAN
is_weblite	BOOLEAN
is_action	BOOLEAN
is_events_listing	BOOLEAN
is_events_details	BOOLEAN
is_search_appearance_android_app	BOOLEAN
is_amp_story	BOOLEAN
is_amp_image_result	BOOLEAN
is_video	BOOLEAN
is_organic_shopping	BOOLEAN
is_review_snippet	BOOLEAN
is_special_announcement	BOOLEAN
is_recipe_feature	BOOLEAN
is_recipe_rich_snippet	BOOLEAN
is_subscribed_content	BOOLEAN
is_page_experience	BOOLEAN
is_practice_problems	BOOLEAN
is_math_solvers	BOOLEAN
is_translated_result	BOOLEAN
is_edu_q_and_a	BOOLEAN
impressions	INTEGER
clicks	INTEGER
sum_position	INTEGER

サンプルコード

以下に細かく記載がある。

それ以外の便利そうな例を記載（随時更新）

指名・一般分類

DECLARE start_date DATE;
DECLARE end_date DATE;
 
-- 集計にあたっての基本設定
SET start_date = '2023-03-01'; -- 集計開始日YYYY-MM-DD
SET end_date = '2023-03-31'; -- 集計終了日YYYY-MM-DD

SELECT
  IF(is_anonymized_query = true, '(anonimized)', query) AS query,
  CASE
    WHEN REGEXP_CONTAINS(query, '(ブランドKW漢字|ブランドKW2ひらがな|ブランドKWカタカナ)') THEN 'brand'
    WHEN is_anonymized_query = true THEN 'anonimized'
    ELSE 'gereral' END AS query_category,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position
FROM
  `<project_id>.searchconsole.searchdata_url_impression`
WHERE
  url LIKE 'https://www.example.com/example/url/%' -- URLを絞りたい場合
  AND search_type = 'WEB'
  AND data_date BETWEEN start_date AND end_date
GROUP BY
  1,2
ORDER BY
  4 DESC, 3 DESC, 5
;

AMPページのアクセス数

DECLARE start_date DATE;
DECLARE end_date DATE;
 
-- 集計にあたっての基本設定
SET start_date = '2023-03-01'; -- 集計開始日YYYY-MM-DD
SET end_date = '2023-03-31'; -- 集計終了日YYYY-MM-DD

SELECT
  data_date,
  url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position
FROM
  `<project_id>.searchconsole.searchdata_url_impression`
WHERE
  url LIKE 'https://www.example.com/amp/%' -- AMPページのURL
  AND search_type = 'WEB'
  AND data_date BETWEEN start_date AND end_date
GROUP BY
  1,2
ORDER BY
  4 DESC, 3 DESC, 5
;

構造化データ（FAQ）の表示有無の差異分析

DECLARE start_date DATE;
DECLARE end_date DATE;
 
-- 集計にあたっての基本設定
SET start_date = '2023-03-01'; -- 集計開始日YYYY-MM-DD
SET end_date = '2023-03-31'; -- 集計終了日YYYY-MM-DD

SELECT
  url,
  IF(is_anonymized_query = true,"(anonimized)",query) AS query,
  is_tpf_faq,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(clicks) / SUM(impressions) AS ctr,
  ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position
FROM
  `<project_id>.searchconsole.searchdata_url_impression`
WHERE
  search_type = 'WEB'
  AND data_date BETWEEN start_date AND end_date
  AND url IN (SELECT url FROM `<project_id>.searchconsole.searchdata_url_impression` WHERE is_tpf_faq = true GROUP BY 1)
GROUP BY
  1,2,3
ORDER BY
  5 DESC,4 DESC

設定方法

設定のながれ

以下のヘルプを参照。

必要なGCPの権限

IAMより、以下のサービスアカウント（メールアドレス）の登録を行う。

サービスアカウント: search-console-data-export@system.gserviceaccount.com
権限: BigQuery ジョブユーザー, BigQuery データ編集者（※両方登録が必要）
GCPのアクセスにIPでのフィルタリンクをかけている場合は、上記サービスアカウントからのアクセスについてはフィルタしない設定をかける。

連携時の制約

1GCPプロジェクトに1つのGoogle Search Consoleプロパティしか連携できない。これは、データセット名がsearchconsoleで固定のためと思われる。
ゆえに、複数ドメインで展開している場合は、Google Search Consoleのドメインプロパティを構築して、連携することを強く推奨する。
https://support.google.com/webmasters/answer/10431861?hl=ja
2022-03-27 に改良され、任意のデータセット名でエクスポートできるようになりました。

一括データ エクスポート とは