Google Analytics

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

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

Twitter:@exturekwon

前回の記事では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データセットを選択

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

翌日以降に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などのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ

関連記事

  1. Google Analytics

    Google Analytics StandardのデータをBigQueryで分析するための力技

    こんにちは、エクスチュアの権です。Twitter:@extur…

  2. Adobe Analytics

    Adobe Mobile SDK 4.xからAEP SDKに移行する

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

  3. KARTE

    CXツール「KARTE」ってどんなツール?主な機能や魅力をざっくり紹介!

    こんにちは!エクスチュアインターン生の上園…

  4. Cloud Dataflow

    Firebase AnalyticsのデータをフラットなCSVに変換する – Googl…

    こんにちは。エクスチュアCTOの権です。Twitter:@…

  5. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条件を一気に追加する方…

    Adobe Analyticsでセグメントを作るとき、「A または B…

  6. Google Cloud Platform

    Looker: LookerbotをGoogle Cloud Platformで動かす

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

最近の記事

  1. Google Analytics 4: イベントパラメータを…
  2. Google Analytics 4 + BigQueryで…
  3. Google Analytics 360: BigQuery…
  4. Google Analytics: ユニバーサルアナリティク…
  5. Server-side GTMのAppEngine設定をカス…
  1. Mouseflow

    Mouseflow: MouseflowタグをWebサイトに導入する
  2. KARTE

    KARTE:指定期間の来訪が再訪問の場合にスコアリングする
  3. Tableau

    【TC19ブログ】エクスチュアの海外カンファレンス参加支援制度
  4. GA 360 Suite

    Google Analytics 360 + BigQueryでよく使うSQL例…
  5. Tableau

    Tableau:分かりやすいLOD-FIXED編
PAGE TOP