以下の点について引き続き記載していく。
Search Console の API で取得したデータと、公式エクスポート機能で入っているデータの形式は異なる。前者のデータをどのように整形したら後者に寄せられるか。
※ ここでいう公式エクスポート機能は以下のページの内容を指す
About bulk data export of Search Console data to BigQuery – Search Console Help
公式エクスポート機能で BigQuery に入るデータの形式については前記事で確認した。
Search Consoleの過去データもBigQueryに蓄積する_3.APIからの取得結果の整形
この記事では、Search Console APIから取得したデータを公式エクスポート機能で BigQuery に入るデータに寄せた集計をする方法を検討する。
Search Console APIから取得できるデータ
Search Console の検索パフォーマンスのデータの取得は query メソッドを利用する。
参照：Search Analytics: query | Search Console API
searchdata_site_impression テーブルに近づけて集計する
例えば2024-02-20のwebのデータを取得する場合、以下のような形式でリクエストを送る。
query_request = {
"startDate": "2024-02-20",
"endDate": "2024-02-20",
"dimensions": ["query", "country", "device", "date"],
"type": "web",
"aggregationType": "byProperty",
}
search_console_client.searchanalytics().query(
siteUrl="your_site_url",
body=query_request
).execute()
このリクエストによる集計と公式エクスポート機能で BigQuery に入るデータを比較した図
このリクエストでは匿名化したクエリの結果は集計されない（= searchdata_site_impression テーブルの is_anonymous カラムがTrueのデータは集計されない）。
ただ、is_anonymous カラムがTrueの場合、query カラムはnullになる。そのため、分析上大きな問題にならない場合が多いと判断し、集計から除くこととした。
※ 匿名化したクエリ：
匿名化されたクエリは、2～3 か月間に数十人未満のユーザーが発行したクエリです。プライバシー保護のため、実際の検索語句は検索のパフォーマンス データに表示されません
A deep dive into Search Console performance data filtering and limits | Google Search Central Blog
リクエストに対するレスポンスは以下のような形式で返ってくるので、searchdata_site_impression テーブルのカラム名に合わせて整形する。
[
{
'type': 'WEB',
'data': {
'rows': [
{
'keys': [
'bigquery 日付 比較',
'jpn',
'DESKTOP',
'2024-01-29'
],
'clicks': 1,
'impressions': 3,
'ctr': 0.3,
'position': 9
}, …(略)
],
'responseAggregationType': 'byProperty'
}
}, { …(略)
]
※ コードは以下
https://github.com/yrarchi/search_console_to_bigquery
searchdata_url_impression テーブルに近づけて集計する
例えば2024-02-20のwebのデータを取得する場合、以下のような形式でリクエストを送る。（searchdata_site_impression の場合とほぼ同じだが、dimensions と aggregationType が変化している）
query_request = {
"startDate": "2024-02-20",
"endDate": "2024-02-20",
"dimensions": ["query", "page", "country", "device", "date"],
"type": "web",
"aggregationType": "byPage",
}
search_console_client.searchanalytics().query(
siteUrl="your_site_url",
body=query_request
).execute()
このリクエストによる集計と公式エクスポート機能で BigQuery に入るデータを比較した図
このリクエストでは searchdata_site_impression テーブル同様、匿名化したクエリの結果は集計されない（= is_anonymous カラムがTrueのデータは集計されない）が、同様に問題としないこととした。is_anonymized_discover カラムについても類似した要因でTrueのデータ（url や country などがnullとなる）も集計結果に入れていない。
また、今回対象としたサイト（このサイト）は検索での見え方のタイプに該当するようなデータがなかったため、それらの is_~ カラム（検索での見え方のタイプを示すのに使用されるブール値フィールド）ごとの集計も行わなかった。
リクエストに対するレスポンスは以下のような形式で返ってくるので、searchdata_url_impression テーブルのカラム名に合わせて整形する。
[
{
'type': 'WEB',
'data': {
'rows': [
{
'keys': [
'bigquery timestamp',
'<https://yrarchi.net/managing_learning_with_notion/>',
'jpn',
'DESKTOP',
'2024-01-29'
],
'clicks': 0,
'impressions': 4,
'ctr': 0.25,
'position': 44
}, { …（略）
],
'responseAggregationType': 'byPage'
}
}, {…（略）
]
※ コードは以下
https://github.com/yrarchi/search_console_to_bigquery
以上で、一部カラムは集計対象から除いたものの公式エクスポート機能に寄せた集計を Search Console APIで行えるようになった。
最後に、これを複数日に渡って実行できるよう、Cloud Functions に組み込む。
