Search Consoleの過去データもBigQueryに蓄積する_2.公式エクスポート機能の構成

前段の記事: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からの取得結果の整形