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

これまでSearch Consoleの検索パフォーマンスデータをエクスポートするためにはSearch Consoleの管理画面からエクスポートを行うかAPIで取得する必要がありましたが、[2023年2月末よりSearch Consoleの一括エクスポート機能がリリース](https://developers.google.com/search/blog/2023/02/bulk-data-export?hl=ja)されたことにより検索パフォーマンスの詳細な分析や定点観測が行いやすくなりました。

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

## 対応するデータ
「検索結果のパフォーマンス」ページで「検索タイプ : ウェブ」の状態でエクスポートしたCSVファイル
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103957/5a9a0986-291b-419f-1e67-e09fae69dd3c.png)

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

### クエリ
```sql
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文字分までしか出力されない
### ページ
```sql
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件より多い情報が欲しいときはこの行を削除
```
### 国
```sql
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](https://ja.wikipedia.org/wiki/ISO_3166-1)）で出力
searchdata_url_impressionテーブルから集計すると数が合わないので注意
### デバイス
```sql
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テーブルから集計すると数が合わないので注意
### 検索での見え方
```sql
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の「検索での見え方」の日本語版ヘルプ・英語版ヘルプを照らし合わせて確認

https://support.google.com/webmasters/answer/7576553?hl=ja#by_search_appearance


https://support.google.com/webmasters/answer/7576553?hl=en#by_search_appearance

### 日付
```sql
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日前の情報がエクスポートされることが多いものの固定されていない。

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

## その他参考になるリンク
**BigQueryへの一括エクスポート設定の開始方法**

https://support.google.com/webmasters/answer/12917675?hl=ja

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

https://support.google.com/webmasters/answer/12917991?hl=ja

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

https://developers.google.com/search/blog/2023/06/bigquery-efficiency-tips?hl=ja