Google Analytics のデータを Google Cloud Storage 経由で BigQuery に登録する

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

今回は、Google Analytics のデータを登録した結果を記載します。
Google Analytics 有料版は BigQuery へのエクスポート機能があり、それはそれは便利そうなのですが、無料版にはエクスポート機能がなく、無理矢理登録しております。

前提

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

OS

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

Python の Version
```
% python3 -V
Python 3.6.5
```

データ量について

6月分の Google Analytics のデータを Export して、BigQuery に登録しました。

取得期間

2018年3月18-2018年9月15日

レコード数

システム表から SQL で取得します。

クエリ

SELECT SUM(row_count)
FROM `GA_Statistics.__TABLES__`

結果
```
[
  {
    "f0_": "100116"
  }
]
```

100116件です。

データサイズ

これもシステム表から SQL で取得します。

クエリ
データサイズはバイトなので、MB 変換をして合計します。
```
SELECT SUM(size_bytes)/1024/1024 FROM `GA_Statistics.__TABLES__`
```
結果
```
[
  {
    "f0_": "88.80032157897949"
  }
]
```
88.80 MB です。
全データを取得するクエリを投げようとすると、
```
SELECT * FROM `GA_Statistics.*`
```
このクエリを実行すると、88.8 MB が処理されます。 と表示されるので、この算出法で問題ないかと思います。

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

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

事前準備
Google Analytics からデータを取得、取得したデータを SQLite に登録する。
SQLite に登録したデータを Pandas で結合。結合した結果を CSV にして、Google Cloud Storage に登録する。
Google Cloud Storage に登録した CSV を、Big Query にインポートする。
使い方
検索クエリを実行する。

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

1. 事前準備

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

2.Google Analytics からデータを取得、取得したデータを SQLite に登録する。

作成したスクリプトは、Gist にアップしました。
ga_to_sqlite.py
ここでは、スクリプトを抜粋して説明します。

選択しているディメンションについて
スクリプトの先頭に、以下のように取得ディメンションを記載しています。
```
USER_DIMENSIONS = [{'name': 'ga:userType'},
                   {'name': 'ga:sessionCount'},
                   {'name': 'ga:daysSinceLastSession'},
                   {'name': 'ga:userDefinedValue'},
                   {'name': 'ga:userBucket'}]
```
取得ディメンションはDimensions & Metrics Explorer | アナリティクス Reporting API v4 | Google Developers を参考に見たいものを抽出しています。
Adwords 関連の項目等、個人的に使っていないものは除外しています。
Hit単位でのレコードを取得する
Hit単位のレコードを取得するため、カスタムディメンションに clientId と、TimeStamp を設定しています。
dimension8、dimension10 がそれにあたるので、API 実行時は必ず設定するようにしています。
Google Analytics StandardのデータをBigQueryで分析するための力技 | エクスチュア株式会社ブログのように、HitID として2つの項目を繋げたほうがディメンション数が少なくて済むので設定しておいたほうが、Google Analytics 上で閲覧するのに便利かなと思います。
UNIX 時間の変換
dimennsion10 には TimeStamp を設定していますが、訳あって、UNIX 時間と yyyy-MM-dd hh:mm:ss の日付が混在しています。
このため、以下の記述で、UNIX 時間を yyyy-MM-dd hh:mm:ss 形式に変換しています。
```
    df['dimension10'] = df['dimension10'].apply(lambda x: convert_datetime(x))
```

Google Analytics からデータが Export できない場合は、空テーブルを作成する
google2pandas ですが、クエリの実行結果が 0 件だと例外が発生します。
スクリプトの都合ですが、CSV のカラム数を固定したかったので、例外発生時は、通常取得時と同じカラム数になるようにレコードのない Dataframe を作成するようにしました。

    try:
        conn = GoogleAnalyticsQueryV4(secrets=GA_CLIENT_JSON)
        # table名生成
        table_name = "ga_sessions_" + date.replace("-", "") + "_" + category_name
        df = conn.execute_query(query)
        df['dimension10'] = df['dimension10'].apply(lambda x: convert_datetime(x))

    except TypeError:
        print("TypeError... table_name[", table_name, "]", "create empty table.")
        import pandas as pd
        columns = []
        for i in query_dimensions:
            for value in i.values():
                columns.append(value.replace("ga:", ""))
        for i in metrics:
            for value in i.values():
                columns.append(value.replace("ga:", ""))
        df = pd.DataFrame(columns=columns)

ディメンションの数の指定
Google Analytics Reporting V4 API の制限で、指定できるディメンションの数は 7つまでです。
各カテゴリごとのディメンション数は 8以上ある場合もあり、設定ディメンションは2つはヒット単位で取得するため5つまでしか設定できず、5つ以下になるように List を分割して API を複数回実行しました。
```
    # -------------------------------------------
    # PAGE_TRACKING_DIMENSIONS
    # -------------------------------------------
    sublist = divide_list(PAGE_TRACKING_DIMENSIONS, 4)
    for i, elem in enumerate(sublist):
        store_ga_to_sqlite(args.date, elem, PAGE_TRACKING_METRICS, "PAGE_TRACKING" + str(i))
```

作成されるテーブルについて
スクリプトを実行すると以下の日付のついたテーブルが作成されます。

"ga_sessions_20180318_USER"
"ga_sessions_20180318_SESSION"
"ga_sessions_20180318_TRAFFIC0"
"ga_sessions_20180318_TRAFFIC1"
"ga_sessions_20180318_TRAFFIC2"
"ga_sessions_20180318_GOAL_CONVERSIONS"
"ga_sessions_20180318_GOAL_CONVERSIONS1"
"ga_sessions_20180318_GOAL_CONVERSIONS2"
"ga_sessions_20180318_GOAL_CONVERSIONS3"
"ga_sessions_20180318_GOAL_CONVERSIONS4"
"ga_sessions_20180318_GOAL_CONVERSIONS5"
"ga_sessions_20180318_PLATFORM_OR_DEVICE0"
"ga_sessions_20180318_PLATFORM_OR_DEVICE1"
"ga_sessions_20180318_PLATFORM_OR_DEVICE2"
"ga_sessions_20180318_GEO_NETWORK0"
"ga_sessions_20180318_GEO_NETWORK1"
"ga_sessions_20180318_GEO_NETWORK2"
"ga_sessions_20180318_GEO_NETWORK3"
"ga_sessions_20180318_SYSTEM0"
"ga_sessions_20180318_SYSTEM1"
"ga_sessions_20180318_PAGE_TRACKING0"
"ga_sessions_20180318_PAGE_TRACKING1"
"ga_sessions_20180318_PAGE_TRACKING2"
"ga_sessions_20180318_PAGE_TRACKING3"
"ga_sessions_20180318_EVENT_TRACKING"
"ga_sessions_20180318_CUSTOM_VARIABLES_OR_COLUMNS0"
"ga_sessions_20180318_CUSTOM_VARIABLES_OR_COLUMNS1"
"ga_sessions_20180318_CUSTOM_VARIABLES_OR_COLUMNS2"
"ga_sessions_20180318_CUSTOM_VARIABLES_OR_COLUMNS3"
"ga_sessions_20180318_CUSTOM_VARIABLES_OR_COLUMNS4"

3.SQLite に登録したデータを Pandas で結合。結合した結果を CSV にして、Google Cloud Storage に登録する。

作成したスクリプトは、Gist にアップしました。
merge_data_to_gcs.py
2. で作成したテーブルからデータを取得し、結果をマージ、マージした CSV を Google Cloud Storage に登録します。

軸テーブルと結合方法について
ga_sessions_YYYYMMDD_SESSION を軸テーブルにしています。これは、この取得項目がga:hit を含み、まず取得できないことがないためです。
このデータを軸に残りのテーブルを LEFT OUTER で結合しています。
```
    df = pd.merge(df, get_table_data_as_dataframe(args.date, "GEO_NETWORK1"), how='left')
```
ページのPath にドメインを付与する
以下のコードで、pathPath ディメンションに DOMAIN_NAME = "https://www.monotalk.xyz" のドメイン名を付与して、fullPagePath とし、DataFrame に追加しています。
```
    df["fullPagePath"] = df['pagePath'].apply(lambda x: DOMAIN_NAME + str(x))
```
これは、Google Search Console で取得できるパスがドメインとプロトコルを含むパスのため、Google Search Console から取得したデータセットと結合するために付与しています。
Google Cloud Storage に登録
一時的に、temp.csv という名称で CSV を作成し、その CSV を Google Cloud Storage にアップロードしています。
アップロード方法についてはGoogle Search Console のデータを Google Cloud Storage 経由で BigQuery に登録する | Monotalk に記載したので、よろしれければご確認ください。

    temp_file_name = 'temp.csv'
    df.to_csv(temp_file_name, index=False)
    file_name = CSV_PREFIX + args.date.replace("-", "") + ".csv"

    import os
    from google.cloud import storage
    os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = GOOGLE_APPLICATION_CREDENTIALS_PATH
    client = storage.Client()
    # https://console.cloud.google.com/storage/browser/[bucket-id]/
    bucket = client.get_bucket(BUCKET_NAME)
    blob = bucket.blob(FILE_DIR_NAME + file_name)
    blob.upload_from_filename(filename=temp_file_name)

    if os.path.exists(temp_file_name): os.remove(temp_file_name)

4. Google Cloud Storage に登録した CSV を、Big Query にインポートする。

作成したスクリプトは、Gist にアップしました。
load_data_to_bigquery_from_gcs.py
以下、抜粋して説明していきます。

改行の扱い
ダブルクォートで括られたカラム内の改行はデフォルトではカラム値と認識されず、行の終わりを意味します。
これは、allow_quoted_newlines = True で振る舞いをコントロールできます。
```
    job_config.allow_quoted_newlines = True
```
Google BigQueryのCSVロードでMissing close double quote (“) character. に記載されている内容と同じエラーが発生して、このオプションをつけることで解消しました。

Schema 定義の指定
autodetect = True とすることで、カラムの型を有る程度自動判別できますが、Google Analytics のデータ1日分をテーブル化する限りではこの性質が仇になり、カラムの型定義が値の偏りにより、String型になったり、Int型になったりして、テーブルを UNION で結合する際に、型が違うことでエラーが発生しました。
このため予め型定義をして、自動判別でカラム型にブレが生じないようにしました。

    job_config.source_format = bigquery.SourceFormat.CSV
    job_config.schema = [
        bigquery.SchemaField('avgSessionDuration','FLOAT','NULLABLE'),
        bigquery.SchemaField('bounceRate','FLOAT','NULLABLE'),
        bigquery.SchemaField('bounces','INTEGER','NULLABLE'),
        bigquery.SchemaField('dimension10', 'TIMESTAMP', 'NULLABLE'),
        bigquery.SchemaField('dimension8', 'STRING', 'NULLABLE'),
        bigquery.SchemaField('hits', 'INTEGER', 'NULLABLE'),
        bigquery.SchemaField('sessionDuration', 'FLOAT', 'NULLABLE'),
        bigquery.SchemaField('sessionDurationBucket', 'INTEGER', 'NULLABLE'),
        bigquery.SchemaField('sessions', 'INTEGER', 'NULLABLE'),
        ...
        ]

どうやってスキーマ定義を作ったかですが、一度自動判別でテーブルを作り、変化する型定義を微調整して作成しました。

データロード時の例外
データロード時に、何度か例外になりましたが、詳細メッセージが拾えなかったため、例外が発生した際は詳細メッセージを出力するようにしました。
ループ内の error.get("message") で詳細メッセージを出力することができます。
```
    try:
        load_job.result()  # Waits for table load to complete.
    except Exception as e:
        for error in load_job.errors:
            print("Error detail [message]", error.get("message"))
        raise e
```

5.使い方

以下のように、日付を指定して実行します。

python3 ga_to_sqlite.py 2018-03-18
python3 merge_data_to_gcs.py 2018-03-18
python3 load_data_to_bigquery_from_gcs.py 2018-03-18

これで 2018-03-18 のデータが BigQuery に保存されます。

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

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

select * from `GA_Statistics.ga_sessions_*` 
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), '-', '')

日付指定

select * from `GA_Statistics.ga_sessions_*` 
WHERE
  _TABLE_SUFFIX BETWEEN '20180501'
  AND '20180610'

上記クエリが軸になり、集計関数を書いたり、Where 句で条件をより絞り込み分析をしていく形になります。

感想

Google Analytics は、月々の費用を考えなければ、有料版が良いと思う。
無理をする、且つ、分析軸がしっかりしていれば、無料版で無理矢理データを取得するのもいいかもしれない。
同じことを2度書くが、テーブルの再作成等なかなか手間がかかるので、分析軸をしっかり持ったほうがいい。
企業利用する場合は、このやり方だと方言が強く出てしまうので、有料版で自動エクスポートされてるスキーマに対してクエリを投げることを覚えたほうが、いざ有料にする時の覚えなおしもないし、従業員的には転職時の潰しも効くのでやはり有料版が良いと思う。

以上です。

カテゴリー

Google Analytics

Google Analytics のデータを Google Cloud Storage 経由で BigQuery に登録する

前提

データ量について

取得期間

レコード数

データサイズ

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

1. 事前準備

2.Google Analytics からデータを取得、取得したデータを SQLite に登録する。

3.SQLite に登録したデータを Pandas で結合。結合した結果を CSV にして、Google Cloud Storage に登録する。

4. Google Cloud Storage に登録した CSV を、Big Query にインポートする。

5.使い方

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

感想

コメント

カテゴリー

最近の投稿

前提

データ量について

取得期間

レコード数

データサイズ

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

1. 事前準備

2.Google Analytics からデータを取得、取得したデータを SQLite に登録する。

3.SQLite に登録したデータを Pandas で結合。結合した結果を CSV にして、Google Cloud Storage に登録する。

4. Google Cloud Storage に登録した CSV を、Big Query にインポートする。

5.使い方

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

感想

コメント

カテゴリー

関連投稿

最近の投稿