ブログ記事の
今回は、
Google Analytics 有料版 は
前提
以下の
OS
% sw_vers ProductName: Mac OS X ProductVersion: 10.13.6 BuildVersion: 17G65
Python の
Version % python3 -V Python 3.6.5
データ量に ついて
6月分の
取得期間
2018年3月18-2018年9月15日
レコード数
システム表から
クエリ
SELECT SUM(row_count) FROM `GA_Statistics.__TABLES__`
結果
[ { "f0_": "100116" } ]
100116件です。
データサイズ
これも
クエリ
データサイズはバイトなので、 MB 変換を して 合計します。 SELECT SUM(size_bytes)/1024/1024 FROM `GA_Statistics.__TABLES__`
結果
88.80 MB です。[ { "f0_": "88.80032157897949" } ]
全データを取得する クエリを 投げようとすると、 SELECT * FROM `GA_Statistics.*`
この<wbr>クエリを<wbr>実行すると、<wbr>88.8 MB が<wbr>処理されます。<wbr>
と表示されるので、 この 算出法で 問題ないかと 思います。
登録、 登録後の 検索までの 手順
以下のような
- 事前準備
- Google Analytics から
データを 取得、 取得した データを SQLite に 登録する。 - SQLite に
登録した データを Pandas で 結合。 結合した 結果を CSV に して、 Google Cloud Storage に 登録する。 - Google Cloud Storage に
登録した CSV を、 Big Query に インポートする。 - 使い方
- 検索クエリを
実行する。
それぞれ
1. 事前準備
Google Cloud Storage の
Python の
動かすと
Python で Google Cloud Storage と、
2.Google Analytics から データを 取得、 取得した データを SQLite に 登録する。
作成した
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 に 登録する。
作成した
merge_data_to_gcs.py
2.
で
軸テーブルと
結合方法に ついて
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 に インポートする。
作成した
load_data_to_bigquery_from_gcs.py
以下、
改行の
扱い
ダブルクォートで括られた カラム内の 改行は デフォルトでは カラム値と 認識されず、 行の 終わりを 意味します。
これは、allow_quoted_newlines = True
で振る 舞いを コントロールできます。 Google BigQueryのjob_config.allow_quoted_newlines = True
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
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'
上記クエリが
感想
- Google Analytics は、
月々の 費用を 考えなければ、 有料版が 良いと 思う。 - 無理を
する、 且つ、 分析軸が しっかりしていれば、 無料版で 無理矢理データを 取得するのもいいかもしれない。 - 同じことを
2度書くが、 テーブルの 再作成等なかなか 手間が かかるので、 分析軸を しっかり 持った ほうが いい。 - 企業利用する
場合は、 このやり方だと 方言が 強く 出てしまうので、 有料版で 自動エクスポートされてる スキーマに 対して クエリを 投げる ことを 覚えた ほうが、 いざ 有料に する 時の 覚えな おしもないし、 従業員的には 転職時の 潰しも 効くのでやはり 有料版が 良いと 思う。
以上です。
コメント