Server-side GTMのGAビーコンログをBigQueryにエクスポートして分解する

ホーム
Google Analytics, Google Cloud Platform, Web解析
Server-side GTMのGAビーコンログをBigQueryにエクスポートして分解する

Google Analytics

2020.08.18

Server-side GTMのGAビーコンログをBigQueryにエクスポートして分解する

こんにちは、エクスチュアの権泳東(權泳東/コン・ヨンドン)です。

前回の記事ではServer-side GTMをGoogle AppEngineにデプロイしてGoogleAnalyticsのユニーバサルアナリティクス計測を行う方法を説明しました。

Server-side GTM を Google AppEngine にデプロイする

今回は続編というか「おまけ」でして、Server-side GTMをホストするGoogleAppEngineに届いたanalytics.jsのビーコン生ログをBigQueryにエクスポートした上で分解する方法について説明します。

1.Stackdriverログをエクスポート

GoogleAppEngineのログは、GCPのロギングサービスであるStackdriverにデフォルトで7日間記録されます。

そして、このログにはanalytics.jsからGoogleAnalyticsに送信されるべきビーコンURLのパラメータも全て記録されます。
Measurement Protocolで使うあの形式のログです。

Measurement Protocol の操作 | アナリティクス Measurement Protocol

ではこのStackdriverログをBigQueryにエクスポートします。

まず事前準備として、BigQueryにデータセットを作っておきます。
データセットを作ったら、Stackdriver画面上部の[CREATE SINK]をクリックします。

CREATE SINKからエクスポート

続いて、[BigQuery dataset]を選んで[NEXT]をクリックします。

BigQuery datasetを選ぶ

画面右側にシンク編集ウィンドウが出てくるので、適宜名前をつけてから先程作ったBigQueryデータセットを選択します。

BigQueryデータセットを選択

設定後、エクスポートが開始されるまでしばらく待つ必要があるので私は一晩放ったらかしましたｗ

翌日以降にBigQueryを見ると、Stackdriverログが「appengine_googleapis_com_request_log_」というプレフィックスを持つ日別テーブルにエクスポートされてました。

Server-side GTMのログが出てた

collect?v=1というお馴染みの文字列を含むあのビーコンURL、ペイロードとか呼んだりもしますけどアレが[protoPayload.resource]カラムに記録されてます。

ペイロード以外にも:

protoPayload.ip (IPアドレス)
protoPayload.userAgent (ユーザーエージェント)
timestamp (タイムスタンプ)

などがログに残ってます。

2.ビーコンログを分解

ビーコンのペイロードそのままだと全く何も分からないので、SQLで分解して別テーブルを作ります。
分解用SQLが長ったらしいけどキニシナイ！

CREATE TEMP FUNCTION decode(s string)
returns string
language js as """
var ret = '';
try {
  ret = decodeURIComponent(s);
}catch(e){}
return ret;
""";
 
WITH base as (
SELECT
timestamp
, DATETIME(timestamp, 'Asia/Tokyo') as datetime_jpn
, DATE(timestamp, 'Asia/Tokyo') as date_jpn
, protoPayload.ip as ip
, protoPayload.userAgent as userAgent
, REGEXP_EXTRACT(protoPayload.resource, "cid=([^&]+)") as client_id
, REGEXP_EXTRACT(protoPayload.resource, "t=([^&]+)") as hit_type
, decode(REGEXP_EXTRACT(protoPayload.resource, "ec=([^&]+)")) as event_category
, decode(REGEXP_EXTRACT(protoPayload.resource, "ea=([^&]+)")) as event_action
, decode(REGEXP_EXTRACT(protoPayload.resource, "el=([^&]+)")) as event_label
, REGEXP_EXTRACT(protoPayload.resource, "ev=([^&]+)") as event_value
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd1=([^&]+)")) as custom_dimension_1
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd2=([^&]+)")) as custom_dimension_2
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd3=([^&]+)")) as custom_dimension_3
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd4=([^&]+)")) as custom_dimension_4
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd5=([^&]+)")) as custom_dimension_5
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd6=([^&]+)")) as custom_dimension_6
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd7=([^&]+)")) as custom_dimension_7
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd8=([^&]+)")) as custom_dimension_8
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd9=([^&]+)")) as custom_dimension_9
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd10=([^&]+)")) as custom_dimension_10
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd11=([^&]+)")) as custom_dimension_11
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd12=([^&]+)")) as custom_dimension_12
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd13=([^&]+)")) as custom_dimension_13
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd14=([^&]+)")) as custom_dimension_14
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd15=([^&]+)")) as custom_dimension_15
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd16=([^&]+)")) as custom_dimension_16
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd17=([^&]+)")) as custom_dimension_17
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd18=([^&]+)")) as custom_dimension_18
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd19=([^&]+)")) as custom_dimension_19
, decode(REGEXP_EXTRACT(protoPayload.resource, "cd20=([^&]+)")) as custom_dimension_20
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm1=([^&]+)")) as custom_metric_1
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm2=([^&]+)")) as custom_metric_2
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm3=([^&]+)")) as custom_metric_3
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm4=([^&]+)")) as custom_metric_4
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm5=([^&]+)")) as custom_metric_5
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm6=([^&]+)")) as custom_metric_6
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm7=([^&]+)")) as custom_metric_7
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm8=([^&]+)")) as custom_metric_8
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm9=([^&]+)")) as custom_metric_9
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm10=([^&]+)")) as custom_metric_10
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm11=([^&]+)")) as custom_metric_11
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm12=([^&]+)")) as custom_metric_12
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm13=([^&]+)")) as custom_metric_13
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm14=([^&]+)")) as custom_metric_14
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm15=([^&]+)")) as custom_metric_15
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm16=([^&]+)")) as custom_metric_16
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm17=([^&]+)")) as custom_metric_17
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm18=([^&]+)")) as custom_metric_18
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm19=([^&]+)")) as custom_metric_19
, decode(REGEXP_EXTRACT(protoPayload.resource, "cm20=([^&]+)")) as custom_metric_20
, decode(REGEXP_EXTRACT(protoPayload.resource, "cg1=([^&]+)")) as content_grouping_1
, decode(REGEXP_EXTRACT(protoPayload.resource, "cg2=([^&]+)")) as content_grouping_2
, decode(REGEXP_EXTRACT(protoPayload.resource, "cg3=([^&]+)")) as content_grouping_3
, decode(REGEXP_EXTRACT(protoPayload.resource, "cg4=([^&]+)")) as content_grouping_4
, decode(REGEXP_EXTRACT(protoPayload.resource, "cg5=([^&]+)")) as content_grouping_5
, decode(REGEXP_EXTRACT(protoPayload.resource, "dh=([^&]+)")) as hostname
, decode(REGEXP_EXTRACT(protoPayload.resource, "dl=([^&]+)")) as page_url
, decode(REGEXP_EXTRACT(protoPayload.resource, "dp=([^&]+)")) as page_path
, decode(REGEXP_EXTRACT(protoPayload.resource, "dr=([^&]+)")) as referrer_source
, decode(REGEXP_EXTRACT(protoPayload.resource, "dt=([^&]+)")) as page_title
, REGEXP_EXTRACT(protoPayload.resource, "uid=([^&]+)") as user_id
, REGEXP_EXTRACT(protoPayload.resource, "gclid=([^&]+)") as google_ads_id
, REGEXP_EXTRACT(protoPayload.resource, "dclid=([^&]+)") as display_ads_id  
, REGEXP_EXTRACT(protoPayload.resource, "ni=([^&]+)") as non_interaction_hit
, decode(REGEXP_EXTRACT(protoPayload.resource, "de=([^&]+)")) as document_encoding
, decode(REGEXP_EXTRACT(protoPayload.resource, "ul=([^&]+)")) as user_language
, REGEXP_EXTRACT(protoPayload.resource, "_v=([^&]+)") as protocol_version
, REGEXP_EXTRACT(protoPayload.resource, "je=([^&]+)") as java_enable
, REGEXP_EXTRACT(protoPayload.resource, "sd=([^&]+)") as screen_color
, REGEXP_EXTRACT(protoPayload.resource, "sr=([^&]+)") as screen_resolution
, decode(REGEXP_EXTRACT(protoPayload.resource, "tid=([^&]+)")) as ga_property_id
, REGEXP_EXTRACT(protoPayload.resource, "vp=([^&]+)") as view_port
, REGEXP_EXTRACT(protoPayload.resource, "_gid=([^&]+)") as _gid
, REGEXP_EXTRACT(protoPayload.resource, "gtm=([^&]+)") as gtm_id
FROM
ga_server_log.appengine_googleapis_com_request_log_20200817
WHERE
1=1
AND protoPayload.resource like '%/collect?v=%'
)
  
, history AS (
SELECT *
,LAG(timestamp, 1) OVER (PARTITION BY client_id ORDER BY timestamp) AS last_hit
FROM base
)
  
, log AS (
SELECT *
, CASE WHEN TIMESTAMP_DIFF(timestamp, last_hit, second) > 1800 THEN 1
       WHEN last_hit IS NULL THEN 1 ELSE 0 END AS session_start
FROM history
)
    
SELECT *
, SUM(session_start) OVER (PARTITION BY client_id ORDER BY timestamp) AS session_num
FROM log

すると結果はこうなりました。

GA生ビーコンを分解

analytics.jsの生ビーコンが見やすいカラムに分割されました！
client_idも抽出されてますね。

ビーコンに含まれないタイムスタンプとIPアドレス、ユーザーエージェントはStackdriverのログにあるものを抽出しており、セッションIDはSQLで生成して追加してます。

3.検討事項

生ビーコン/ペイロードにはセッション情報が含まれてません。
そのため、上記のSQLではclient_id毎に「直前のヒットから30分以上経過した場合は別セッション」としてセッション番号を振るようにしてます。
しかしこの方法だと、通算のセッション回数ではなく「集計期間内のセッション回数」しか分からないので、ブラウザのセッションクッキーに値をランダムで書き込んでそれをカスタムディメンションに渡す方法も有効です。

function(){
    var ret = '';
    var c = 'ga_ssnum';
	var cn = c + '=';
	var allc = document.cookie;
	var pos = allc.indexOf(cn);
	if (pos != -1) {
		var si = pos + cn.length;
		var ei = allc.indexOf(';', si);
		if (ei == -1) {
			ei = allc.length;
		}
		ret = decodeURIComponent(allc.substring(si, ei));
	}
    if (!ret) {
        ret = encodeURIComponent(Math.random().toString().substr(2));
        val = cn + ret  + '; path=/;'
        document.cookie = val;
    }
    return ret;
}

これをWeb側のGTMで、カスタムJavascript変数を使ってカスタムディメンション21番以降にセットしましょう。
なぜかというと、GoogleAnalytics無料版スタンダードだと、カスタムディメンションは20個しか使えない。

でも、Stackdriverログにさえ入れば良いので、cd21以降にセットすればGoogleAnalyticsには計測されなくてもStackdriver→BigQueryには入るという裏技です。

その場合は上記SQLを修正して、cd21も抽出するようにしましょう。

TL;DR

今回はServer-side GTMがStackdriverに残すanalytics.jsの生ビーコンログをBigQueryにエクスポートする方法について説明しました。
セッションIDはSQLで生成するか、ブラウザで生成するかは要検討です。
以前からGTMのCustomTaskを使ってGAビーコン/ペイロードを横流しする方法がありましたが、今回説明した方法も新たな代替手段のひとつです。

~~SafariITPが騒ぎを起こしてくれたおかげで、~~マーテックツールの導入において様々なテクニックを使う時代がやって参りました。

弊社ではGoogleAnalytics/AdobeAnalyticsなどの各Martechツールの導入実装コンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ。