Google Search Console の データを Google Cloud Storage 経由で BigQuery に登録する

ブログ記事の品質をRedpenの警告と文書の統計量を使って可視化する | Monotalk の続きで、文書の解析結果や、Google Analytics の 情報 と Google Search Console の情報を統合して分析したいので、BigQuery に 一律データを登録して解析します。

今回は、Google Search Console のデータを登録した結果を記載します。

前提

以下の環境で動作確認は実施しています。

• OS
  

  % sw_vers 
  ProductName:    Mac OS X
  ProductVersion: 10.13.6
  BuildVersion:   17G65
  

• Python の Version
  

  % python3 -V
  Python 3.6.5
  

課金について

CSV インポート、クエリ発行については現在、無料枠に収まる範囲の使用です。
1年分の Google Search Console のデータで、600MB 程度で、1年分のデータ集計を実施するとすると、1600 回程度で 1 TB となります。
1月真面目に使うとこのくらいの回数は到達しそうで、意識はしておいたほうがいいかと思います。
ストレージの格納は 10GB まで無料です。今のところこれは問題がなさそうです。
料金  |  BigQuery  |  Google Cloud

登録、登録後の検索までの手順

以下のような手順になります。

1. 事前準備
2. Google Search Console からデータを取得する。
3. 取得したデータを CSV に変換し、Google Cloud Storage にアップロードする。
4. Google Cloud Storage に登録した CSV を、BigQuery インポートする。
5. 使い方
6. 検索クエリを実行する。

それぞれ説明していきます。

1. 事前準備

Google Cloud Storage の セットアップや、Python のライブラリのインストール等があります。
Python のライブラリのインストール後、幾つかエラーに遭遇したので、以下の記事にまとめました。
動かすところまでうまくいっていない方はご確認ください。
Python で Google Cloud Storage と、BigQuery を使う過程でのエラー | Monotalk

2.、3. を実施するスクリプト

Python スクリプトを、2.、<wbr>3. のまとまりで作成しました。
作成したスクリプトは、Google Search Console のデータを BigQuery に登録する Python スクリプト に UP しました。

ここでは、スクリプトを抜粋して説明します。

• Google Search Console の データ取得、CSV 変換について
  joshcarty/google-searchconsole: A wrapper for the Google Search Console API. を使用して、Pandas の DataFrame に変換し、その後に CSV に変換しています。
  Branch に Service Accounts 使うための修正が入っているので、Branch の ライブラリを インストールします。
  以下の Issue から Branch の URL は辿れます。
  Add support for Service Accounts · Issue #2 · joshcarty/google-searchconsole

• DataFrame の URL のデコード
  

       df['page'] = df['page'].apply(lambda x: urllib.parse.unquote(x))
  

  で URL のデコードを行なっています。
  理由は URL に全角文字が含まれる場合、Google Analytics と Google Search Console での戻り値が異なるためです。

• CSV には DataFrame のインデックスは出力しない
  CSV の 出力時に index=False を指定して

      df.to_csv(temp_file_name, index=False)     
  

• Google Cloud Storage の GOOGLE_APPLICATION_CREDENTIALS 設定について
  

      from google.cloud import storage
      os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = GOOGLE_APPLICATION_CREDENTIALS_PATH  
  

  で、GOOGLE_APPLICATION_CREDENTIALS の設定を行いましたが、Authentication — google-cloud e1a7cfe documentation のように、client = Client.from_service_account_json('/path/to/keyfile.json') でもできるのかもしれませんが、試しておりません。

4 を実施するスクリプト

作成したスクリプトは、Google Search Console のデータを BigQuery に登録する Python スクリプト に UP しました。

以下、説明します。

• Pandas から BigQuery への登録方法について
  pandas.io.gbq を使うと直接 DataFrame を BigQuery に登録できます。
  何かで、Google Cloud Storage から、CSVファイルをインポートしたほうが速度的に有利というのを見かけたので、Google Cloud Storage からファイルをアップロードする方式にしました。ただ、そこまで大きいデータではなかったので、直接登録でも良かった気がします。
  以下スライドに CSV のアップロードと、INSERT の違いについての記載があります。
  Google Cloud ベストプラクティス：Google BigQuery 編 - 02 : データ処理 / クエリ / データ抽出

• テーブルの削除について
  更新の際も使用するかなと思って、テーブル削除処理を追加。
  登録時はエラーになるので、エラーを無視して登録を行うようにしました。
  

      try:
          client.delete_table(table_ref)  # API request
      except:
          pass
  

5.使い方

以下のように、日付を指定して実行します。
以下は、2017-05-18 のデータを Google Search Console から 取得して CSV を Google Cloud Storage にアップロードし、BigQuery にインポートする例です。

python3 gsc_to_gcs.py 2017-05-18
python3 load_data_to_bigquery_from_gcs.py 2017-05-18

6.検索クエリを実行する。

• API経由でないと取得できないデータについて
  ページ、クエリ両方をディメンションとして指定したデータは API 経由でないと、取得ができません。
  この形式でデータを取得して、分析するのが良いのかと思います。

• 直近 1年分のデータを取得する
  

  SELECT
    *
  FROM
    `GSC_Statistics.gsc_*`
  WHERE
    _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL  365 day) AS string), '-', '')
    AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')
   ORDER BY date ASC
  

• 日付指定
  

  SELECT
    *
  FROM
    `GSC_Statistics.gsc_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20180501'
    AND '20180610'
   ORDER BY date ASC     
  

日付条件でデータを取り出して、DataStudio で加工する使い方をして、集計、見たい VIEW が固まってきたら、込み入った集計クエリを書こうかなと思います。

参考

投稿時に確認していた。記事になります。

• Google Search Console Data into Google BigQuery – Ben Powis – Medium
  作成後に、検索したらヒットしましたが、この記事と実装方法は違いますが同じことができます。
  Google-Search-Console-bulk-query-to-GBQ の説明が記載されています。

• benpowis/Google-Search-Console-bulk-query-to-GBQ: A script to iterate through the available filters on Google Search Console, pulling large amounts of historical data and uploading it into Google BigQuery.
  pandas.io.gbq でデータの登録を行うツールです。
  私は登録の速さから gcs を経由しましたが、正直 データ あまり多くなかったので、直接登録のほうがよかったかもしれません。

• A Simple Tool For Saving Google Search Console Data To BigQuery - Adapt
  gsc-logger の説明が記載されています。

• jroakes/gsc-logger: Google Search Console Logger for Google App Engine
  gcloud のアプリケーションとして動作可能な Google Search Console > Big Query のデータ連携ツールです。
  Google App Engine の無料枠で動かせるのかもしれません。結構便利かなと思います。

• BigQueryでテーブルを日付ごとに分ける必要がなくなったり、古いテーブルのストレージ代が安くなったりするらしい - 唯物是真 @Scaled_Wurm
  BigQuery のクエリの検索方法を参考にさせてもらいました。

• BigQueryで有り金全部溶かさないように、テーブルの日付ごとのパーティション(partition)機能について調べてみた - 唯物是真 @Scaled_Wurm
  BigQuery のクエリの検索方法を参考にさせてもらいました。

• ５分でわかる！BigQuery Tips集 - Gunosyデータ分析ブログ
  _TABLE_SUFFIX を使って検索を実行する際に参考にしました。

以上です。