Search Console検索結果のパフォーマンスレポートとほぼ同じ情報をBQから取得する #BigQuery

これまでSearch Consoleの検索パフォーマンスデータをエクスポートするためにはSearch Consoleの管理画面からエクスポートを行うかAPIで取得する必要がありましたが、2023年2月末よりSearch Consoleの一括エクスポート機能がリリースされたことにより検索パフォーマンスの詳細な分析や定点観測が行いやすくなりました。

本記事ではこれまで「検索結果のパフォーマンス」からCSVエクスポートで取得していたデータとほぼ一致するデータをBigQueryにエクスポートされたデータから取得できるクエリをご紹介します。ダッシュボード作成などにご活用ください。

対応するデータ

「検索結果のパフォーマンス」ページで「検索タイプ : ウェブ」の状態でエクスポートしたCSVファイル

CSVシートの各シートに対応するBigQueryのクエリ

クエリ中のプロジェクト名YOUR-PROJECT-IDとデータセット名searchconsoleはご自身のエクスポート設定に合わせて変更してご利用ください。

クエリ

SELECT
  query AS `上位のクエリ`
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_top_position)/SUM(impressions)+1,2) AS position
FROM `YOUR-PROJECT-ID.searchconsole.searchdata_site_impression` 
WHERE search_type = 'WEB'
AND is_anonymized_query IS NOT true  -- 匿名化クエリの情報も欲しいときはこの行を削除
AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000  -- 上位1000件より多い情報が欲しいときはこの行を削除

「上位のクエリ」列には実際のクエリの先頭250文字分までしか出力されない

ページ

SELECT
  url AS top_pages
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_position)/SUM(impressions)+1,2) AS position
FROM `YOUR-PROJECT-ID.searchconsole.searchdata_url_impression`
WHERE search_type = 'WEB'
AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000  -- 上位1000件より多い情報が欲しいときはこの行を削除

国

SELECT
  country
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_top_position)/SUM(impressions)+1,2) AS position
FROM `YOUR-PROJECT-ID.searchconsole.searchdata_site_impression` 
WHERE search_type = 'WEB'
AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
GROUP BY 1
ORDER BY 2 DESC

国名はjpn, usaのような英字3文字形式（ISO 3166-1 Alpha-3）で出力
searchdata_url_impressionテーブルから集計すると数が合わないので注意

デバイス

SELECT
  device
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_top_position)/SUM(impressions)+1,2) AS position
FROM `YOUR-PROJECT-ID.searchconsole.searchdata_site_impression` 
WHERE search_type = 'WEB'
AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
GROUP BY 1
ORDER BY 2 DESC

デバイスの出力はDESKTOP, TABLET, MOBILE
searchdata_url_impressionテーブルから集計すると数が合わないので注意

検索での見え方

SELECT 
  is_xxx AS search_appearance
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_position)/SUM(impressions)+1,2) AS position
FROM (
    SELECT
      is_amp_top_stories, is_amp_blue_link, is_amp_story, is_amp_image_result
      , is_job_listing, is_job_details
      , is_tpf_qa, is_tpf_faq, is_tpf_howto
      , is_weblite, is_action
      , is_events_listing, is_events_details
      , is_search_appearance_android_app
      , is_video, is_organic_shopping
      , is_review_snippet, is_recipe_rich_snippet
      , is_special_announcement, is_recipe_feature
      , is_subscribed_content, is_page_experience
      , is_practice_problems, is_math_solvers
      , is_translated_result, is_edu_q_and_a
      , is_product_snippets, is_merchant_listings, is_learning_videos
      , clicks, impressions, sum_position
    FROM
        `YOUR-PROJECT-ID.searchconsole.searchdata_url_impression`
    WHERE search_type = 'WEB'
      AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
)
UNPIVOT (
    is_status
    FOR is_xxx IN (
      is_amp_top_stories, is_amp_blue_link, is_amp_story, is_amp_image_result
      , is_job_listing, is_job_details
      , is_tpf_qa, is_tpf_faq, is_tpf_howto
      , is_weblite, is_action
      , is_events_listing, is_events_details
      , is_search_appearance_android_app
      , is_video, is_organic_shopping
      , is_review_snippet, is_recipe_rich_snippet
      , is_special_announcement, is_recipe_feature
      , is_subscribed_content, is_page_experience
      , is_practice_problems, is_math_solvers
      , is_translated_result, is_edu_q_and_a
    )
)
WHERE is_status = true
GROUP BY 1
ORDER BY 2 DESC;

結果の意味はSearch Consoleの「検索での見え方」の日本語版ヘルプ・英語版ヘルプを照らし合わせて確認

日付

SELECT
  data_date AS date
  , SUM(clicks) AS clicks
  , SUM(impressions) AS impressions
  , CONCAT(CAST(ROUND(SUM(clicks) / SUM(impressions)*100,2) as string),'%') AS CTR
  , ROUND(SUM(sum_top_position)/SUM(impressions)+1,2) AS position
FROM `YOUR-PROJECT-ID.searchconsole.searchdata_site_impression` 
WHERE search_type = 'WEB'
AND data_date BETWEEN "2023-04-01" AND "2023-04-30"
GROUP BY 1
ORDER BY 1 DESC

searchdata_url_impressionテーブルから集計すると数が合わないので注意

エクスポートのタイミング

不明。以下のクエリでエクスポートタイミングを調査可能。自分が見ているデータだと日本時間で朝8時-夕方5時あたりに3日前の情報がエクスポートされることが多いものの固定されていない。

SELECT 
  EXTRACT(HOUR FROM publish_time) AS hour
  , COUNT(*)
 FROM `YOUR-PROJECT-ID.searchconsole.ExportLog`
 GROUP BY 1
 ORDER BY 1

その他参考になるリンク

BigQueryへの一括エクスポート設定の開始方法

集計元テーブルについての情報

高額な費用を負担することなく一括エクスポートされたデータを活用するためのヒント