Google Analytics

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

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Excel

    コホート分析（Cohort Analysis）って何？〜Tableau/GA/Excel/Sprea…

    はじめにさぁ、ようやく皆さんお待ちかねの「コホート分析」です。我々…

  2. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い③ カスタム計測のされ方の違い…

    こんにちは。CEOの原田です。今回もAAとGAの違いを述べていきま…

  3. Google Analytics

    Google Analytics 4 + BigQueryでよく使う基本的なSQL例

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

  4. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータをフラット変換するS…

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

  5. Google Cloud Platform

    GCSへのSnowflake Open Catalogによるデータレイクハウス導入チュートリアル

    はじめにこんにちは、エクスチュアの石原です。今回は1…

  6. Adobe Analytics

    【超わかる】WEB解析の全貌

    はじめにこんにちは！インターン生の與那覇（ヨナハ）です今回は時代の…

最近の記事

  1. 回帰分析はかく語りき Part3 ロジスティック回帰
  2. GCSへのSnowflake Open Catalogによる…
  3. VPC Service Controlsで「NO_MATCH…
  4. モダンデータスタックなワークフローオーケストレーションツール…
  5. Streamlit in Snowflakeによるダッシュボ…

  1. Adobe Analytics

    Adobe Analytics: RSIDを間違えてしまったデータを正しいRSに…

  2. IT用語集

    プロパティ（Property）って何？

  3. ChatGPT

    LangChainって何？: 次世代AIアプリケーション構築 その2

  4. Adobe Analytics

    ページの深さとは-Adobe Analyticsの指標説明

  5. Google Apps Script(GAS)

    GASを利用してWebスクレイピングをやってみよう
PAGE TOP