BigQuery で Google Analytics 的な session をカウントしてみる #1

何をやる？

Google Analytics のセッションの定義( https://support.google.com/analytics/answer/2731565?hl=ja )である、

• (定義1) ユーザーが何も操作を行わないまま 30 分が経過するまで持続
• (定義2) ユーザーの操作が行われたら有効期限はその時点から30分後にリセットされる
• (定義3) 1日の終わりによる期限切れ

の感じの session のカウントを BigQuery でやってみようという企画。

今回の #1 では定義1,2を行います。

準備

schema.json

[
  {"name": "datetime", "type": "TIMESTAMP", "mode": "required"},
  {"name": "user_id", "type": "string", "mode": "required"}
]

data.json

{"datetime":"2019-11-30T00:00:00","user_id":"a"}
{"datetime":"2019-11-30T00:29:59","user_id":"a"}
{"datetime":"2019-11-30T01:00:00","user_id":"a"}
{"datetime":"2019-11-30T01:30:00","user_id":"a"}
{"datetime":"2019-11-30T00:00:00","user_id":"b"}
{"datetime":"2019-11-30T00:00:10","user_id":"b"}
{"datetime":"2019-11-30T00:30:09","user_id":"b"}

テーブル作成&データロード

bq load --source_format=NEWLINE_DELIMITED_JSON YOURPROJECT:YOURDATASET.access_log ./data.json ./schema.json

SQL を書いていってみる

同一ユーザーの一つ前の access が 1800 秒以上前ならば、新しいセッションというフラグをたてる。
という Query を書いてみます。以下の様になります。

  -- #3
SELECT
  user_id,
  ((datetime - prev_seconds) / (1000*1000) )as duration_sec,
  IFNULL(datetime - prev_seconds >= 30 * 60 * 1000 * 1000, TRUE) AS start_of_session,
  datetime,
  prev_seconds
FROM (
    -- #2
  SELECT
    user_id,
    datetime,
    LAG(datetime, 1) OVER(PARTITION BY user_id ORDER BY datetime) AS prev_seconds
  FROM (
      -- #1
    SELECT
      *
    FROM
      [YOURPROJECT:YOURDATASET.access_log] ) AS activity_log)

そしてこの Query の結果は、

こんな感じになり、無事 start_of_session = true というフラグがつきました。

次回

次回は定義3をやります。