前段の記事：Search Consoleの過去データもBigQueryに蓄積する_1.全体像
前記事の以下の点について記載していく。
Search Console の API で取得したデータと、公式エクスポート機能で入っているデータの形式は異なる。前者のデータをどのように整形したら後者に寄せられるか。
※ ここでいう公式エクスポート機能は以下のページの内容を指す
About bulk data export of Search Console data to BigQuery – Search Console Help
まず、公式エクスポート機能で入るデータの形式を確認する。
テーブルの種類
Table guidelines and reference – Search Console Help に記載の通り、以下の3つのテーブルが作成される。
- ExportLog テーブル：エクスポートのログ
- searchdata_site_impression テーブル：プロパティごとに集計されたデータ
- searchdata_url_impression テーブル：URL別に集計されたデータ
テーブルの集計単位（プロパティ・URL）の違いについて
テーブル searchdata_site_impression と searchdata_url_impression の集計単位（プロパティ・URL）は以下のような違いがある。
例えば https://example.com/a とhttps://example.com/b が1回ずつ検索結果に表示された場合
・プロパティごと：1回のインプレッションとしてカウント
・URLごと：2回のインプレッションとしてカウント（URLごとに1回とカウント）
参考：Performance report (Search) – Search Console Help
※ プロパティは Search Console の用語で、対象とするウェブサイトとして指定したドメインまたはURLプレフィックスのこと。
例えば、https://example.com/ というサイトの場合、 （ドメインプロパティを使わなければ）以下は別々のプロパティと認識される。
・https://example.com/
・http://example.com/
・https://www.example.com/
参考：Add a website property to Search Console – Search Console Help
公式エクスポート機能で生成されるテーブルがわかったところで、各々のテーブルの中身を確認していく。
searchdata_site_impression テーブルの詳細
公式エクスポート機能でBigQueryに生成される searchdata_site_impression テーブルと、Search Console のコンソールを比較すると以下の図のようになる。多くのカラムは Search Console の コンソールにある絞り込み機能と一致するため理解しやすい。
searchdata_site_impression テーブルで見慣れず調べたカラムについて記載する。
引用部分は Table guidelines and reference – Search Console Help より
- is_anonymous
まれなクエリ（匿名化されたクエリ）は、このブール値でマークされます。このフィールドが true の場合、クエリを行ったユーザーのプライバシーを保護するため、クエリフィールドは null になります。
検索数の少ないクエリについては、クエリが非表示となる（Search Console のコンソールでも非表示となる）。裏を返すと、is_anonymous is false の場合は、query カラムに何らかの文字列が入ることになる。
- sum_top_position
そのテーブルの行の各インプレッションに対する、検索結果におけるサイトの最上位の掲載順位の合計（0 は結果における最上位の掲載順位です）。平均掲載順位（1 ベース）求めるには、
SUM(sum_top_position)/SUM(impressions) + 1を計算します。
例えば以下の図のような順位（0位、1位、8位 ※ 最上位を0位と数える）で3回表示された場合、上で示した図（impressions = 3, sum_top_position = 9, 掲載順位 = 4）の結果となる。
searchdata_url_impression テーブルの詳細
searchdata_site_impression テーブルに比べ、こちらの方が見慣れないカラムが多く、データの意味するところが捉えづらい。公式エクスポート機能で BigQuery に生成される searchdata_url_impression テーブルと、Search Console の コンソール を比較すると以下の図のようになる。
searchdata_url_impression テーブルで見慣れず調べたカラムについて記載する。
引用部分は Table guidelines and reference – Search Console Help より
- is_anonymized_discover
データ行が Discover の匿名化しきい値を下回っているかどうかを示します。しきい値を下回っている場合、ユーザーのプライバシー保護のため、他の一部のフィールド（url や country など）は表示されなくなります。
匿名化クエリ（is_anonymous カラム）のDiscover版といえるか？ 詳細の記載は見つけられなかったが、件数が少ない場合はurl等のカラムがnullになる模様。
- その他の is_~カラム
検索での見え方のタイプを示すのに使用されるブール値フィールドがいくつかあります（is_amp_top_stories、is_job_listing、is_job_details など）。特定のリッチリザルトで問題の行が表示される場合、フィールドは true になります。
この「検索での見え方」は以下に挙げられている、例えば商品スニペットやレシピギャラリーなど、特別な検索結果の表示の仕方を指している。
参照：Performance report (Search) – Search Console Help
Search Console のコンソールにもそれ用のタブがある。
- sum_position
クエリの検索結果における URL の最上位の掲載順位を示す 0 ベースの数値（0 は結果における最上位の掲載順位です）。平均掲載順位（1 ベース）を求めるには、
SUM(sum_position)/SUM(impressions) + 1を計算します。
例えば以下の図のような順位（44位、9位、0位、… ※ 最上位を0位と数える）で 20回表示されたとしたら、上で示した図（impressions = 20, 掲載順位 = 13.4）の結果となる。
ここまで、公式エクスポート機能で BigQuery に入るデータを Search Console のコンソール上のデータの集計結果と比較しながら把握した。
次に、Search Console のAPIから取得可能なデータを確認し、公式エクスポート機能に寄せた集計をするにはどうしたら良いか考えていく。
Search Consoleの過去データもBigQueryに蓄積する_3.APIからの取得結果の整形
