はじめに
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すごい。