ブログ記事の品質を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.、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 が固まってきたら、込み入った集計クエリを書こうかなと思います。

参考

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

以上です。

コメント

カテゴリー

最近の投稿