##はじめに
分析をするにあたって、BigQueryのテーブル構造や指標の出し方に則って、
『1レコード1セッション』にデータを持つと便利。
うまくデータ取得したはずでも『1レコード1セッション』にならないとき、
日付を跨いでいるセッションにポイントがあったのでメモ。
##前提・仮説
◯前提：
GAのセッションが切れるタイミング：
・30分に1度
・日にちを跨ぐとき
◯仮説
BigQueryでの30分に一度、日にち跨ぐとセッションIDが変わっている
#問題
rawテーブルからデータを抜いてきても、1レコード1セッションにならない！
※1意性の判断には、「ユーザーID+セッションID」の複合キーの重複なしカウントを利用します。
##発見
日にち跨ぎ前後でも同じセッションIDが使われている！
(例)
--①rawデータ抜く
SELECT
fullVisitorId,
visitId,
date,
HOUR(DATE_ADD(SEC_TO_TIMESTAMP(visitStartTime), 9, 'HOUR')) AS hour, --時刻(日本時間)
CONCAT(fullVisitorId,STRING(visitId)) AS uniqSessionId --ユニークのセッションIDと定義
FROM (TABLE_DATE_RANGE( [dataset.table_],TIMESTAMP('2018-01-01'), TIMESTAMP('2018-01-02')))
--②日にち跨ぎセッションのユニークIDを指定して、データを確認
SELECT *
FROM ①で作成したテーブル WHERE uniqSessionId = 'aaaaaaaaaaaaaaaaaaabbbbbbbbbb'
| fullVisitorId | visitId | date | hour | uniqSessionId |
|---|---|---|---|---|
| aaaaaaaaaaaaaaaa… | bbbbbbbbbb | 20180101 | 23 | aaaaaaaaaaaaaaaaaaabbbbbbbbbb |
| aaaaaaaaaaaaaaaa… | bbbbbbbbbb | 20180102 | 0 | aaaaaaaaaaaaaaaaaaabbbbbbbbbb |
##解決策
Dateの箇所だけ初日側に寄せ、『1レコード1セッション』にする。
(例)ユニークのセッションID毎にDATEを元にSEQをふる。⇒次のテーブルでSEQが1のものだけ対象にする。
--SEQを振る
SELECT
uniqSessionId,
COUNT(*) OVER (PARTITION BY uniqSessionId ORDER BY date) AS SEQ,
CONCAT(fullVisitorId,STRING(visitId)) AS uniqSessionId --ユニークのセッションIDと定義
FROM dataset.table
※来訪時セッションを取るのはこれでOK
集約が必要なデータ(ヒット数、CV数など)については、集約関数を利用するべき。
来訪時セッション用と、集約セッション用でテーブルを作成し、新たにテーブルにまとめる。
##注意点
どうしても毀損させたくない・クレンジングしたくない要件がある場合には、
違うやり方をやってみる必要があるかも。