旧GA(Universal Analytics)データのBigQuery移行をGoogle Analytics APIとbqコマンドで済ませられそうな話

先日2023年07月01日を以て、旧GA(Universal Analytics)はその仕様・プロパティに於ける新しいデータ生成の処理を停止しました。下記公式アナウンスではGoogle Analytics 4(以降"GA4"と記載)への移行を促しています。

• ユニバーサル アナリティクスは Google アナリティクス 4 に置き換わりました - アナリティクス ヘルプ

そんな中、弊社クラスメソッドでもこれまで旧GA(Universal Analytics、以降"旧GA(UA)"と記載)を活用してきていました。旧GA(UA)とGA4に関しては、今後の方針としては大きく以下のようなものを想定しています。

• 旧GA(UA)
  • これまで長らく使っていた。
  • 移行自体は予定していたものの、公式で展開されている移行ツール等が無い状況。
  • 引き続きデータを使用するのであれば(期限が来る前に)何らかの手段を講じる必要があった。
  • 無料アカウントでの利用。(有料の「Google アナリティクス 360」では無い)
    • 有料であればGA4同様、BigQueryへのエクスポートが行けるのだが...
    • BigQuery Export を設定する - アナリティクス ヘルプ
• GA4
  • 2021年末から別途運用を開始。
  • GA4については無料でBigQureyへのデータエクスポートが標準装備されているため、エクスポート先として選びやすい状況がある。
    • GA4は最大データ保持期間が14ヶ月のため、そもそもエクスポートが必要だという前提もある
  • Google BigQuery環境への自動(エクスポート)連携も時期を同じくして行っている。

ここでポイントとなるのが『これまで利活用していた旧GA(UA)データを、今後も活用出来るようにBigQueryに移行・退避しておく』というアクションです。『旧GA(UA)データの移行・退避』については世の中の多くの企業やユーザーが懸念している部分であり、言うても2023年末にはデータ自体吸い出せなくなっちゃうのでそれまでには何とかしないといけないなぁ(そんなことを言ってたらもうあと4ヶ月くらいしか時間残ってないぞ)...なんてことを思ったり考えたりしていることと思います。(はい、私もそうでした)

そんな思いを抱えながらGoogle Analyticsデータを利活用している担当メンバーと連携し、タイトルに記載しているように『Google Analytics API』と『bqコマンド』で作業を行い、移行を進めるための目処が立ってきた(＝作業を終えました、済ませました！と書ければ一番スマートだったのですが移行対象データの種類や規模が何ぶん大きいので、サンプルで試してみて上手く通った、連携出来た、そのサンプルを通じてある程度の見通しもより明確に立った)ので、その手順について前後の進め方と合わせて備忘録的に当エントリに残しておこうと思います。(移行作業が完了した暁にはタイトル末尾を『済ませた話』に変えておこうと思います。)

目次

• 実行環境
• Step1.移行対象の情報ヒアリング
• Step2.実行環境の用意
  • 旧GA(UA)エクスポート用
  • BigQueryコマンド(bq)用
• Step3.旧GA(UA)データのエクスポート
  • Pythonプログラムの作成
  • Pythonプログラムの動作確認
  • データエクスポート(全件)実施
• Step4.エクスポートした旧GA(UA)データをBigQueryにロード
  • BigQuery環境にテーブルを作成
  • bqコマンドを使ってデータをロード
  • ロード内容の検証
• まとめ

実行環境

当エントリの作業を進める上で用意した環境や情報は以下の通りです。アクセス制限、アクセス権限に関するエラーが発生した場合は都度管理者と調整の上、必要な設定(権限付与等)を済ませておいてください。

• Mac OS X
• Python 3.8.6
• Google Cloud SDK

  % gcloud version
  Google Cloud SDK 314.0.0
  bq 2.0.62
  core 2020.10.09
  gsutil 4.53

• エクスポート対象のGoogle Analytics 3環境にアクセスするための権限設定、サービスアカウントキーの発行
• インポート対象のGoogle BigQuery環境にアクセスするための権限設定、サービスアカウントキーの発行

Step1.移行対象の情報ヒアリング

旧GA(UA)データをBigQueryに移行するにあたって、まず確認したことは以下の情報でした。ここでの情報は旧GA(UA)のデータを取得する際に必要なパラメータとなりますので、正確な情報を漏れなく収集しておく必要があります。ボリューム感に関しては『このデータ取得で都合、どれだけプログラムを回せば良いのか＝ビュー単位で全件取得するとなるとどれくらい時間やリソースが掛かるのか』を見積もり、また移行後に於いても『必要な期間分、データが移行出来ているか』の確認ポイントにもなります。

• 移行対象となるビューは全部で幾つあるのか？
• ビュー毎の以下の情報(そのビューに対してどのような項目や条件が必要となるのか？)
  • テーブルの概要や説明
  • ビューID
  • ディメンション
    • 取得する項目には日付項目(ga:date)も合わせて追加しておくこと
  • メトリクス
  • 取得の単位
    • 期間を指定。今回は基本的に日単位での取得となった
• ビューにおけるデータのボリューム感(ビューにおける日付期間の長さは如何程か？)

取得したデータを格納するためのテーブル定義もこの時点で作成することが出来るかと思います。表形式のテーブル定義を担当者と確認・協議して仕様を確定しておくと良いでしょう。

例)ヒアリングして収集した情報：

Description
  流入元別セッション数&CV
View ID
  9xxxxx999  (※実際のビューIDがここに当たる)
Dimensions
  ga:date,ga:landingPagePath,ga:medium,ga:source,ga:campaign,ga:adContent
Metrics
  ga:sessions,...
----
ディメンションズ：日付、ランディングページURL、流入元メディア、ソース、キャンペーン、コンテンツ名 
メトリックス：セッション数、....

例)収集した情報を元に担当者と協議して決めたテーブル定義：

テーブルのデータ型に関しては下記情報等をご参照ください。

• BigQueryのテーブル作成実践(データ定義言語(DDL)) | DevelopersIO
• レガシー SQL のデータ型  |  BigQuery  |  Google Cloud

Step2.実行環境の用意

Pythonライブラリから旧GA(UA)データを取得するプログラムを実行出来る環境を用意します。

旧GA(UA)エクスポート用

旧GA(UA)のデータにアクセスするための手段として、公式から専用のライブラリが複数のプログラミング言語で提供されています。今回はその中から『API Client Library for Python』を用いる形としました。下記エントリを参考に、実行環境を整えてください。

環境が整備出来たら下記Pythonプログラムを実行。アクティブユーザーの情報が取得出来れば準備OKです。

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
import datetime
import os

SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']
KEY_FILE_LOCATION = '/path/to/xxxx.json' ## 旧GA(UA)の所定のビューにアクセス可能な権限を有するサービスアカウントキーのファイルパスを絶対パス指定で
VIEW_ID = '9xxxxx999' ## ビューID

credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)
service = build('analytics', 'v3', credentials=credentials)

# アクティブユーザーの取得例
active_users = service.data().realtime().get(
	ids='ga:' + VIEW_ID,
	metrics='rt:activeUsers').execute()
print(active_users)

参考：

BigQueryコマンド(bq)用

bqコマンドに関しては、Google Cloud SDKを所定の環境に導入することで利用出来るようになります。以下の情報を参考にインストールしてください。

gcloud versionコマンドでbqに関するバージョン情報を取得出来ていればOKです。

% gcloud version
Google Cloud SDK 314.0.0
bq 2.0.62
core 2020.10.09
gsutil 4.53
Updates are available for some Cloud SDK components.  To install them,
please run:
  $ gcloud components update

Step3.旧GA(UA)データのエクスポート

Pythonプログラムの作成

Step2で動作確認したPython実行環境下で『日付を指定して、その日付に於けるディメンションやメトリクスの指定条件に合致するデータを取得するプログラム』を作成します。サンプルコードは以下です。

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
import datetime
import os
import sys
import codecs

args = sys.argv

## Python実行に必要な情報の設定
SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']
KEY_FILE_LOCATION = '/path/to/xxxx.json' ## 旧GA(UA)の所定のビューにアクセス可能な権限を有するサービスアカウントキーのファイルパスを絶対パス指定で
credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SCOPES)
service = build('analytics', 'v3', credentials=credentials)

## 旧GA(UA)データ取得用パラメータ各種
p_view_id = "ga:9xxxxx999";  ## 対象ビューID("ga:"を接頭辞として付与した形で指定)
p_start_date = args[1]  ## 取得対象日付(開始)
p_end_date = args[2]    ## 取得対象日付(終了/基本的には開始日と同じ値を指定)
p_dimensions = "ga:date,ga:landingPagePath,ga:medium,ga:source,ga:campaign,ga:adContent"; ## 取得したいディメンションの情報を記載(複数ある場合はカンマ区切り)
p_metrics = "ga:sessions,....";  ## 取得したいメトリクスの情報を記載(複数ある場合はカンマ区切り)
p_max_results = 10000;  ## 1回で取得する件数の上限値(MAX10000件)
print("start_date: " + p_start_date);
print("end_date  : " + p_end_date);

## ファイル作成
tsvFileName = "GA3Data_" + str(p_start_date) + "to" + str(p_end_date) + ".tsv"  ## 任意のファイル名になるように引数などを使ってアレンジ
print(tsvFileName)
fout = codecs.open(tsvFileName, 'w', 'utf-8')

## ヘッダ行追記
file_contents = "dim_date" \
  + "\t" + "dim_landingPagePath" \
  + "\t" + "dim_medium" \
  + "\t" + "dim_source" \
  + "\t" + "dim_campaign" \
  + "\t" + "dim_adContent" \
  + "\t" + "met_sessions" \
     :
     :
     :
  + "\t" + "met_xxxxxxxxxxx" + "\n"
fout.write(file_contents);

## 1万件単位でデータを取得し続ける。1回の取得件数が10000件を割る(＝次の10000件は存在しない)まで継続して実行
data_is_continued = True;  ## 次の1万件は存在するか？
repeat_index = 0; ## 次のx万件を設定・活用するための変数
total_rows = 0; ## 合計行数(確認用)
while(data_is_continued):
  print("-----------------")
  print(data_is_continued)
  print((str(repeat_index + 1)) + "回目のデータ取得実施:")
  
  ## 最大1万件分のデータを取得
  results = service.data().ga().get(
    ids = p_view_id,
    start_date = p_start_date,
    end_date= p_end_date,
    start_index = 1 + (repeat_index * 10000),
    metrics = p_metrics,
    dimensions = p_dimensions,
    max_results = p_max_results
    ).execute();
  rows = results.get('rows')

  ## 取得内容をファイル出力
  for row in rows:
    ga_date = row[0];
    str_year = ga_date[0:4]
    str_month = ga_date[4:6]
    str_day = ga_date[6:8]
    ga_date = str_year + "-" + str_month + "-" + str_day
    ga_landingPagePath = row[1];
    ga_medium = row[2];
    ga_source = row[3];
    ga_campaign = row[4];    
    ga_adContent = row[5];
    ga_sessions = row[6];
      :
      :
      :
    ga_xxxxxxxxxxx = row[12];
    
    filestr = ga_date \
      + "\t" + ga_landingPagePath \
      + "\t" + ga_medium \
      + "\t" + ga_source \
      + "\t" + ga_campaign \
      + "\t" + ga_adContent \
      + "\t" + ga_sessions \
        :
        :
        :
      + "\t" + ga_xxxxxxxxxxx + "\n"
    fout.write(filestr)

  ## もう1回10000件取得を行うかどうかを件数結果から判断
  print("  " + str(len(rows)) + "件取得")
  if (len(rows) == 10000):
    data_is_continued = True;
    repeat_index += 1
    total_rows += len(rows)
  else:
    data_is_continued = False;
    repeat_index += 1
    total_rows += len(rows)
  print("  累計件数:" + str(total_rows) + "件")

print("合計件数:" + str(total_rows) + "件")

• 『旧GA(UA)データ取得用パラメータ各種』の各要素に、取得したい旧GA(UA)データの条件を設定。
• ファイル名(27行目)の部分は任意の値にアレンジしてください。
• ヘッダ行(32行目〜)、ファイル出力(67行目〜、84行目)の部分は実際に取得する内容＆定義した項目名で置き換えてください。プログラムでは一部処理を省略(":"のみの行)しています。
• ヘッダ行の項目名は後述するテーブル名のカラムと一致させる必要があります。
• APIを使ったデータ取得は1回に取得できるデータの上限が1万件だったため、指定条件で1万件を超える場合の取り回し処理にも対応させました。
• (筆者注)日付指定を範囲指定で行えるようにしてみたかったんですが、何か指定の仕方が悪かったのか意図した通りに動きませんでした。再利用性を考えるとその辺まで解決対応した形出来ればと思ったのですが、現状の単一日付指定で動作確認出来てるので十分と判断＆『このプログラム使うの、今回の移行作業だけだもんなぁ』というのもあったのでこの時点、このコード状態でリリース(公開)しちゃうでいいかな、という判断でした。

動作確認

単一日付指定(開始日と終了日に同じ日付を指定)でPythonプログラムを実行。以下のような形で結果推移が出力されます。結果内容はTSV形式での出力となります。

% python ga3pt1.py 2023-01-23 2023-01-23
start_date: 2023-01-23
end_date  : 2023-01-23
GA3Data_2023-01-23to2023-01-23.tsv
-----------------
True
1回目のデータ取得実施:
  10000件取得
  累計件数:10000件
-----------------
True
2回目のデータ取得実施:
  10000件取得
  累計件数:20000件
-----------------
True
3回目のデータ取得実施:
  7400件取得
  累計件数:27400件
合計件数:27400件

データエクスポート(全件)実施

上記の形式で数日分のデータ出力、及び後述する形でBigQuery環境へのインポートを行い、『サンプリングで、内容が正しいものであるかどうかを確認』します。検証の結果大丈夫、となればあとは同じ形で必要な期間分のデータをプログラムをぶん回して取得→BigQuery環境へ投入すれば(投入仕切れば)そのビューに於ける移行作業は完了、という流れですね。

参考までに、バッチ処理の1日あたりの実行回数上限は50000件だそうです。余程の人海戦術感で実行を仕掛けないと上限には引っ掛からなさそうな気もしますがご参考までに。

デフォルトでは、プロジェクトあたり 1 日 50,000 件のリクエストに制限されており、バッチ処理を行ってもこの割り当てに対するカウントを 減らすことはできません。

Step4.エクスポートした旧GA(UA)データをBigQueryにロード

BigQuery環境にテーブルを作成

定義しておいたテーブル定義を元に、BigQuery環境にテーブルを作成しておきます。プロジェクト名及びデータセット名は対応する環境に合わせたものに置き換えてください。

• CREATE TABLE Statement - Data definition language (DDL) statements in GoogleSQL  |  BigQuery  |  Google Cloud

DROP TABLE IF EXISTS `任意のプロジェクト名.任意のデータセット名.ga3_pt1_sessions_by_source_and_cv`;
CREATE TABLE IF NOT EXISTS `任意のプロジェクト名.任意のデータセット名.ga3_pt1_sessions_by_source_and_cv`
(
  dim_date DATE OPTIONS(description="対象日付"),
  dim_landingPagePath STRING OPTIONS(description="ランディングページURL"),
  dim_medium STRING OPTIONS(description="流入元メディア"),
  dim_source STRING OPTIONS(description="ソース"),
  dim_campaign STRING OPTIONS(description="キャンペーン"),
  dim_adContent STRING OPTIONS(description="コンテンツ名"),

  met_sessions INT64 OPTIONS(description="セッション数"),
    :
    :
    :
    :
)
OPTIONS(
  description="旧GA(UA):流入元別セッション数&CV"
);

bqコマンドを使ってデータをロード

上記で作成したBigQueryテーブルに、Pythonプログラムでエクスポートしたデータをロード(インポート)します。

• bq コマンドライン ツール リファレンス  |  BigQuery  |  Google Cloud

まずそもそも『エクスポートしたデータがテーブルにちゃんと入るのか』の確認はローカルファイルからのインポートが楽でしょう。

## ローカルファイル
bq load \
  --location=asia-northeast1 \
  --source_format=CSV \
  --field_delimiter="\t" \
  --skip_leading_rows=1 \
  --allow_quoted_newlines \
  cmdevio.ga3_pt1_sessions_by_source_and_cv \
  /xxxx/xxxx/xxxx/export-ga3data/GA3Data_2023-01-23to2023-01-23.tsv \
dim_date:DATE,\
dim_landingPagePath:STRING,\
dim_medium:STRING,\
dim_source:STRING,\
dim_campaign:STRING,\
dim_adContent:STRING,\
met_sessions:INTEGER,\
:
:
:
:
:
met_xxxxxxxxxxxx:INTEGER

検証が完了し、本格的に移行するとなった際はGoogle Cloud Storageにアップロードしたファイルをまとめてロードした方が効率的です。(bq loadコマンドでは、Google Cloud Storage経由でのロードの際はワイルドカード指定が可能です)

• Cloud Storage の URI でのワイルドカードの使用 - データのバッチ読み込み  |  BigQuery  |  Google Cloud

データが複数のファイルに分割されている場合は、アスタリスク（*）のワイルドカードを使用して複数のファイルを選択できます。アスタリスク ワイルドカードを使用する場合は、次のルールに従う必要があります。

・アスタリスクは、オブジェクト名の中や末尾に使用できます。
・複数のアスタリスクは使用できません。たとえば、パス gs://mybucket/fed-*/temp/*.csv は無効です。
・バケット名にはアスタリスクを使用できません。

## Google Cloud Strage経由
bq load \
  --location=asia-northeast1 \
  --source_format=CSV \
  --field_delimiter="\t" \
  --skip_leading_rows=1 \
  --allow_quoted_newlines \
  ga3_exported_data.ga3_pt1_sessions_by_source_and_cv \
  "gs://xxxxxxxxxxxx/ga3_pt1_sessions_by_source_and_cv/2023/GA3Data_2023-01*.tsv" \
dim_date:DATE,\
dim_landingPagePath:STRING,\
dim_medium:STRING,\
dim_source:STRING,\
dim_campaign:STRING,\
dim_adContent:STRING,\
met_sessions:INTEGER,\
:
:
:
:
:
met_xxxxxxxxxxxx:INTEGER

ロード内容の検証

ロード作業が完了したら、『データの移行がちゃんと出来ているかの検証』を行います。旧GA(UA)の管理者、担当者が別途居るのであればその方に検証作業を依頼してください。

検証方法についてはそれぞれ有識者によってやり方があるかとは思いますのでここでは割愛します。(旧GA(UA)コンソールでの内容と照らし合わせて行く、というのはあるかなとは思いますが)

まとめ

という訳で、Google Analytics APIとbqコマンドを用いた『Google Analytics 3(UA)のデータをBigQuery環境に移行・退避』する際の手順の紹介でした。

今回紹介した流れは『あるビューにおける所定の条件でのデータ移行の1パターン』のお話なので、こういったパターンが複数ある場合はそのパターン分、この作業を実施していけば...というイメージです。

いざというタイミングになって移行出来ない！間に合わない！ということが無いように、出来るところは早めに対応する形で対応したいところですね。