PythonでGoogle AnalyticsのデータをPostgreSQLへロードする

Google Analytics(GA)のデータを機械学習の勉強用に使えないかなと思ったことがきっかけです。まずは、Pythonで扱いやすくするために、GAのデータをローカルのPostgreSQLにロードさせてみました。

3ステップでデータを持ってきます。

GAのAPIの有効化
APIからpandas.DataFrameへのロード
DataFrameからテーブルへのロード

Google Analytics Reporting APIの有効化

最初にGAのデータを取得するために、Google Analytics Reporting APIを有効化する必要があります。

はじめてのアナリティクス Reporting API v4: サービスアカウント向け Python クイックスタート | アナリティクス Reporting API v4 | Google Developers

設定手順としては3段階で、上のGoogleのガイドの通り進めます。

Google Cloud Platformでプロジェクトを(無ければ)作成します
IAM管理コンソールでサービスアカウント(役割は閲覧者)を作成し、認証情報をJSONファイルで取得します
Google Analytics Webコンソールの管理画面のユーザ管理にて、作成したサービスアカウントのメールアドレスを「表示と分析」権限でアクセスを許可します

設定したら、Pythonサンプルの実行に必要なパッケージをインストールします。

pip install google-api-client-python

次に上記ガイドのサンプルPythonコードをコピー・実行します。

KEY_FILE_LOCATIONは取得したjsonファイルのパス、SERVICE_ACCOUNT_EMAILは設定3.で追加したメールアドレスを設定します
VIEW_IDはGAの画面から確認できます
python 2系のコードなので、3系で実行する場合はprintを関数形式に書き換えます
サンプルではp12ファイルを読み込んでいますので、jsonファイルを読み込むように書き換えます

# credentials = ServiceAccountCredentials.from_p12_keyfile(SERVICE_ACCOUNT_EMAIL, KEY_FILE_LOCATION, scopes=SCOPES)
credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)

うまくいくと、過去7日分のセッション数が表示されます。

Date range (0)
ga:sessions: 811

PythonからGoogle Analytics APIへ接続

metricsやdimensionsで取得可能なデータは以下で説明されています。

Dimensions & Metrics Explorer | Analytics Reporting API v4 | Google Developers

今回は、セッション内のページビュー(ga:pageviews)など3種類のmetricsと、アクセスしたパス(ga:pagePath)など9種類のdimensionsを、過去3ヶ月(2017/4〜2017/6)取得します。

APIの制約で1回のリクエストで取得可能な個数が決まっており、超過した場合は400が返ってきます

analytics = initialize_analyticsreporting()

report_3months = analytics.reports().batchGet(
            body={
            'reportRequests': [
                {
                    'viewId': '123456789',
                    'dateRanges': [
                        {'startDate': '2017-04-01', 'endDate': '2017-06-30'}
                    ],
                    'pageSize': 10000,
                    'metrics': [
                        {'expression': 'ga:pageviews'}, 
                        {'expression': 'ga:sessionDuration'},
                        {'expression': 'ga:timeOnPage'},
                    ],
                    'dimensions': [
                        {'name': 'ga:dateHourMinute'},
                        {'name': 'ga:sessionCount'},
                        {'name': 'ga:daysSinceLastSession'},
                        {'name': 'ga:sourceMedium'},
                        {'name': 'ga:operatingSystem'},
                        {'name': 'ga:deviceCategory'},
                        {'name': 'ga:pagePath'},
                        {'name': 'ga:previousPagePath'},
                        {'name': 'ga:exitPagePath'},
                    ],
                    'orderBys': [
                        {'fieldName': 'ga:dateHourMinute'},
                    ]
                }]
        }
    ).execute()
report_3months

実行すると以下のようなディクショナリが返ってきます。 rowsにmetricsとdimensionsが入っています。

{'reports': [{'columnHeader': {'dimensions': ['ga:dateHourMinute',
     'ga:sessionCount',
     'ga:daysSinceLastSession',
     'ga:sourceMedium',
     'ga:operatingSystem',
     'ga:deviceCategory',
     'ga:city',
     'ga:pagePath',
     'ga:pageDepth'],
    'metricHeader': {'metricHeaderEntries': [{'name': 'ga:pageViews',
       'type': 'INTEGER'},
      {'name': 'ga:sessionDuration', 'type': 'TIME'},
      {'name': 'ga:bounces', 'type': 'INTEGER'},
      {'name': 'ga:pageViews', 'type': 'INTEGER'},
      {'name': 'ga:timeOnPage', 'type': 'TIME'}]}},
   'data': {'maximums': [{'values': ['7', '5295.0', '2', '7', '1795.0']}],
    'minimums': [{'values': ['0', '0.0', '0', '0', '0.0']}],
    'rowCount': 7785,
    'rows': [{'dimensions': ['201704010009',
       '1',
       '0',
       'google / organic',
       'iOS',
       'mobile',
       '/entry/2016/12/03/231909',
       '(entrance)',
       '/entry/2016/12/03/231909'],
      'metrics': [{'values': ['1', '0.0', '0.0']}]},
     ...],
    'totals': [{'values': ['7876',
       '344302.0',
       '6208',
       '7876',
       '344286.0']}]}}]}

APIからDataFrameへロード

次に、取得したディクショナリからmetricsとdimensionsの値をDataFrameへロードします。

DataFrameは、0開始の連番をインデックスにしてます。
また、デフォルトでは文字列型として読み込まれ、この後のPostgreSQLへの投入もやりづらくなるので、時刻や数値については引数dtypeで明示的にnumpyの型を指定しています。ちなみに、dateHourMinuteは一般的ではないフォーマット(yyyyMMddHHmm、例えば"201704120715")ですが、pandas.to_datetimeメソッドが柔軟に型変換してくれます。

import numpy as np
import pandas as pd

rows = report_3months['reports'][0]['data']['rows']

# 0からの連番をインデックスに設定した空のDataFrameを作成
ga_df = pd.DataFrame(index=range(len(rows)), columns=[])

# dimensions
ga_df['date_hour_minute'] = pd.to_datetime(pd.Series([r['dimensions'][0] for r in rows]))
ga_df['session_count'] = pd.Series([r['dimensions'][1] for r in rows], dtype=np.int32)
ga_df['days_since_last_session'] = pd.Series([r['dimensions'][2] for r in rows], dtype=np.int32)
ga_df['source_medium'] = pd.Series([r['dimensions'][3] for r in rows])
ga_df['operating_system'] = pd.Series([r['dimensions'][4] for r in rows])
ga_df['device_category'] = pd.Series([r['dimensions'][5] for r in rows])
ga_df['page_path'] = pd.Series([r['dimensions'][6] for r in rows])
ga_df['previous_page_path'] = pd.Series([r['dimensions'][7] for r in rows])
ga_df['exit_page_path'] = pd.Series([r['dimensions'][8] for r in rows])

# metrics
ga_df['pageviews'] = pd.Series([r['metrics'][0]['values'][0] for r in rows], dtype=np.int32)
ga_df['session_duration'] = pd.Series([r['metrics'][0]['values'][1] for r in rows], dtype=np.float64)
ga_df['time_on_page'] = pd.Series([r['metrics'][0]['values'][2] for r in rows], dtype=np.float64)

ga_df

f:id:ohke:20170707093545p:plain

DataFrameからPostgreSQLへロード

最後にDataFrameをPostgreSQLへロードさせます。

pandas.DataFrame.to_sqlメソッドでDataFrameをそのままDBのテーブルを作成できます。ただし、DB-APIによる接続はsqlite3以外でサポートされていないため、SQLAlchemyのEngineを引数として渡す必要があります。

[python] pandasのDataFrameからpostgresにテーブルを作成 - Qiita

from sqlalchemy import create_engine
engine = create_engine("postgresql://ohke:ohke@localhost:5432/test")
ga_df.to_sql("ga_report", engine, if_exists='replace')

以上でPostgreSQLにテーブルが作成され、GAのデータが投入されます。

f:id:ohke:20170707093712p:plain

次回は、このデータを加工して機械学習に使っていきます。