LoginSignup
17

More than 5 years have passed since last update.

BigQuery上のユーザID入りアクセスログからセッション分割したり、ユーザ別・セッション別の情報を抽出する入れ子構造のテーブルを作成する方法メモ

Posted at

はじめに

BigQuery上にユーザID付きのアクセスログ的なデータを格納しているときに「30分以上アクセスがなければ別セッションとみなす」というようなルールでセッション分割したり、Google Analyticsっぽくセッション内のアクセス数や滞在時間などを計算したいということがあると思います。

そのような計算をBigQuery上だけで行う方法のメモです。

方法

アクセスログデータの構造

アクセスログデータには以下の項目を含むとします。

  • user_id
    • ユーザID(数値でも文字列でもOK)
  • unixtime
    • unixtimestamp(1970/1/1からの秒数)とします。
    • BigQuery上のTimestamp型でも同じようにできると思います。
  • action_name, action_param, ...
    • 任意のイベント情報。複数項目あってもやり方は同じになります。

集計方法

Standard SQL Dialect で実行

ARRAY_AGG() などを使いたいので Standard SQL Dialectを使います。
WebUIから実行する場合は、 Show Options -> Use Legacy SQL のチェックを外します。
bqコマンドから実行する場合は、 --nouse_legacy_sql をつけて実行します.

Query

<YOUR_DATASET>.<YOUR_TABLE>_* から 2016XXXX ~ 2016YYYY の期間を集計するQueryは以下のようになります。
セッションを分離する間隔は1800秒(30分)だとします。

WITH
  data_source AS
  (
    SELECT
      user_id,
      unixtime,
      action_name,
      action_param,
      LAG(unixtime, 1) OVER (PARTITION BY user_id ORDER BY unixtime) as prev_unixtime
    FROM
      `<YOUR_DATASET>.<YOUR_TABLE>_*`
    WHERE _TABLE_SUFFIX BETWEEN '2016XXXX' AND '2016YYYY'
  ),

  with_session_seq AS
  (
    SELECT SUM(IF(unixtime - prev_unixtime > 1800, 1, 0)) OVER (PARTITION BY user_id ORDER BY unixtime, prev_unixtime) as session_seq, *
    FROM data_source
  ),

  groupby_session AS
  (
    SELECT
      user_id,
      session_seq,
      MIN(unixtime) as visited_time,
      MAX(unixtime) as left_time,
      COUNT(action_name) as action_count,
      MAX(unixtime) - min(unixtime) as stayed_time,
      ARRAY_AGG(STRUCT(
        unixtime,
        action_name,
        action_param
      )) as actions
    FROM with_session_seq
    GROUP BY user_id, session_seq
  ),

  session_interval_1 AS
  (
    SELECT
      LAG(left_time, 1) OVER (PARTITION BY user_id ORDER BY session_seq) as prev_left_time,
      *
    FROM groupby_session
  ),

  with_session_interval AS
  (
    SELECT
      visited_time - prev_left_time as session_interval_time,
      *
    FROM session_interval_1
  )

SELECT
  user_id,
  MAX(session_seq) as max_session_seq,
  MIN(visited_time) as first_access_time,
  MAX(left_time) as last_access_time,
  MIN(action_count) as min_action_count,
  AVG(action_count) as mean_action_count,
  MAX(action_count) as max_action_count,
  MIN(stayed_time) as min_stayed_time,
  AVG(stayed_time) as mean_stayed_time,
  MAX(stayed_time) as max_stayed_time,
  SUM(stayed_time) as total_stayed_time,
  AVG(session_interval_time) as mean_session_interval_time,
  MIN(session_interval_time) as min_session_interval_time,
  MAX(session_interval_time) as max_session_interval_time,
  ARRAY_AGG(STRUCT(
    session_seq,
    visited_time,
    left_time,
    session_interval_time,
    action_count,
    stayed_time,
    actions
  )) as sessions
FROM with_session_interval
GROUP BY user_id

好みの問題かもしれませんが、 WITHを使うとFROMの入れ子地獄が緩和され、上から下に処理を読んでいけるようになるので気に入っています。

いくつも処理が連なっていますが、元データへのアクセスは1回だけなので、データアクセス量としてはそこまで大きくはないです。

結果イメージ

user_id max_session_seq ... sessions.session_seq sessions.visited_time ... sessions.actions.unixtime ...
uid1 2 0 10000 .. 10000 ...
.. 10010 ...
1 13000 .. 13000 ...
.. 13030 ...
.. 13070 ...
2 19000 .. 19000 ...
uid2 0 0 22000 .. 22000 ...
... .. ...

Exportするときの注意

Queryの結果が大きい場合、結果をTableに保存してExportしたりすると思いますが、
その場合(入れ子構造のTableなので)CSV形式は選べません。ExportはJSON形式かAVRO形式で行うことになるのでその点だけ注意しましょう。

さいごに

やっぱBigQueryすごい。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
17