概要
Webログデータの世界では、ユーザーの行動を表す指標として
・ページビュー（PV）
・セッション
・ユニークユーザー（UU）
といったものがあります。
本記事では、それぞれの指標を、生ログからSQLで計算してみることに挑戦してみます。
※SQL言語は、一番慣れているHiveQLで書いてます。
それぞれの指標の定義
ユーザーが見たページ数を示す「ページビュー」や、
ユーザーの数を示す「ユニークユーザー」数は、
直感的にわかりやすく、また、ログデータの計算方法もイメージしやすいかと思います。
一方で「セッション」の定義はGoogle Analytics公式ページの記載を引用すると
以下のようになっており、単純なSQLでは計算が難しそうです。
■セッション開始のタイミング
セッションはユーザーが次のいずれかの操作を行うと開始されます。
・アプリをフォアグラウンドで開く
・現在アクティブなセッションがないとき（以前のセッションがタイムアウトした場合など）にページまたはスクリーンを表示する
■セッション終了のタイミング
デフォルトでは、30 分間操作がなければセッションはタイムアウトのため終了します。セッションの継続時間に制限はありません。
扱うデータの紹介
今回扱うデータは、以下のようなWebログであるとします。
・user_idは、顧客を一意に識別するためのID
・time_stampは、そのログが発生した＝ページを閲覧したタイムスタンプ
・urlは、閲覧をしたページのURL
| user_id | time_stamp | url |
|---|---|---|
| a001 | 2022-12-11 09:00:12 | https://business.aidemy.net/ |
| a001 | 2022-12-11 09:02:04 | https://business.aidemy.net/downloads/business/ |
| a001 | 2022-12-11 09:03:50 | https://business.aidemy.net/downloads/complete/ |
| a001 | 2022-12-12 16:38:25 | https://business.aidemy.net/ai-can/ |
| b313 | 2022-12-11 11:58:46 | https://business.aidemy.net/ |
| b313 | 2022-12-11 13:08:11 | https://business.aidemy.net/ |
| b313 | 2022-12-11 13:10:40 | https://business.aidemy.net/ai-can/ |
| b313 | 2022-12-11 13:11:27 | https://business.aidemy.net/ai-can/seminar-dx-loadmap/ |
| b313 | 2022-12-11 13:15:53 | https://business.aidemy.net/ai-can/seminarreport-nextint001/ |
| c422 | 2022-12-12 23:39:39 | https://business.aidemy.net/ |
マーケターの端くれである私が拝察するに、
・a001さんは良い感じで資料をダウンロードしてくれて、しかも他社事例記事も読んでくれた
・b313さんは、一旦Topページに来たが、昼休みを挟んだ模様
・c422さんは、夜に一杯引っ掛けた後、どこかで間違って広告を踏んだのか...？
というような感じのログなんだと思います。まあ余談ですね笑
まずはPVとUUから計算してみよう！
PVとUUの計算は本当に簡単ですね。サクッとやってしまいましょう。
PVはログ数をそのままカウントし、UUはユニークなuser_idをカウントします。
SELECT
COUNT(*) AS pv
,COUNT(DISTINCT user_id) AS uu
FROM
DB.web_log
;
| pv | uu |
|---|---|
| 10 | 3 |
おまけ的に、ユーザーごとのPVを計算するならこんな感じでしょうか
SELECT
user_id
,COUNT(*) AS pv
FROM
DB.web_log
GROUP BY 1
ORDER BY 1 DESC
;
| user_id | pv |
|---|---|
| a001 | 4 |
| b313 | 5 |
| c422 | 1 |
はい。とっても簡単ですね（圧）
ということで、本題の「セッション」の計算に取り掛かりたいと思います。
セッションの定義をログレベルで理解する
おさらいですが、「セッション」の定義はGoogle Analytics公式ページの記載を引用すると以下のようになっており、「開始した時間」と「終了した時間」が大事なようです。
■セッション開始のタイミング
セッションはユーザーが次のいずれかの操作を行うと開始されます。
・アプリをフォアグラウンドで開く
・現在アクティブなセッションがないとき（以前のセッションがタイムアウトした場合など）にページまたはスクリーンを表示する
■セッション終了のタイミング
デフォルトでは、30 分間操作がなければセッションはタイムアウトのため終了します。セッションの継続時間に制限はありません。
これを、SQLを使ってログを整理していくという観点に落とし込むと、
・ユーザーごとのログを時間順に並べた時の「最初」と「最後」を判別したい
・「最後」とは、次のログが30分以上経過したものか、自分よりも後ろにログがないか
ということをSQLで表現できればよい、と整理できます。
「最初のログ」と「最後のログ」を見つけ出す
ここから一気に行きます。結構難易度上がるかもなので、そこはご承知おきください。
まずは
・ユーザーごとのログを時間順に並べた時の「最初」と「最後」を判別したい
という部分に取り組んでみます。
最初と最後を判別するために、私であれば、LAG関数とLEAD関数を使います
（参考：【BigQuery】LAG関数，LEAD関数の使い方）
LAG関数とLEAD関数は、引数に1を渡すと、
それぞれ「自身よりも1つ前」のものと、「自身よりも1つ後」のものを返すため、
その値がnullであれば、それらが「最初」と「最後」である、ということになります。
なので、以下のようなクエリで、最初と最後のログを引き出せることになります。
SELECT
user_id
,LAG(time_stamp, 1) OVER(PARTITION BY user_id ORDER BY time_stamp ASC) AS lag_ts
,time_stamp
,LEAD(time_stamp, 1) OVER(PARTITION BY user_id ORDER BY time_stamp ASC) AS lead_ts
FROM
DB.web_log
WHERE
user_id = 'a001'
ORDER BY 3 ASC
;
| user_id | lag_ts | time_stamp | lead_ts |
|---|---|---|---|
| a001 | null | 2022-12-11 09:00:12 | 2022-12-11 09:02:04 |
| a001 | 2022-12-11 09:00:12 | 2022-12-11 09:02:04 | 2022-12-11 09:03:50 |
| a001 | 2022-12-11 09:02:04 | 2022-12-11 09:03:50 | 2022-12-12 16:38:25 |
| a001 | 2022-12-11 09:03:50 | 2022-12-12 16:38:25 | null |
なので、上記のLAG関数の結果である、「lag_tsがnull」のカラムを抜き出せば、
セッションが何回始まったかがわかるから終わりじゃん！と思うかもしれないですが...
そうは問屋が卸さない...というわけですね...
「30分経過したらセッションが切れる」を再現する
「セッション」はユーザーの行動を一定の単位で一つのまとまりとして捉えるための指標であることから、「30分間何もしなければ切れる」という定義を採用しているため、それもSQLで再現してあげる必要があるわけです。
では、「30分経過した」を実際に導いていきたいと思いますぞ！（役割論理の論者ではない）
少し見慣れないかもしれないですが、SQLにはUNIX_TIMESTAMP()という関数があり、
これを用いることで、2点間の秒数差を計算できます。
（※そもそも、UNIX時間とは？という方はこちらのWikiをご覧ください）
例えば、ユーザーa001さんのログに登場する2つのtime_stampを例に取ってみると
SELECT
a
,b
,b - a AS dif_b_a
FROM (
SELECT
UNIX_TIMESTAMP('2022-12-11 09:00:12') AS a
,UNIX_TIMESTAMP('2022-12-11 09:03:50') AS b
FROM
DB.web_log
)sub
;
| a | b | dif_b_a |
|---|---|---|
| 1670716812 | 1670717030 | 218 |
このように、2つの地点の秒数差を計算することができます。
これを利用することで、30分すなわち、1800秒以上離れているログを算出できます。
まずは、全てのログに対して、
・自分よりも前のログがなければnullを返す
・自分よりも前のログがあれば、そのログとのUNIX時間ベースでの差分を出す
というクエリを書いてみます。
SELECT
user_id
,time_stamp
,CASE
WHEN lag_unix_ts IS null THEN null
ELSE unix_ts - lag_unix_ts
END AS dif_unix
FROM (
SELECT
user_id
,LAG(UNIX_TIMESTAMP(time_stamp), 1) OVER(PARTITION BY user_id ORDER BY time_stamp ASC) AS lag_unix_ts
,UNIX_TIMESTAMP(time_stamp) AS unix_ts
,time_stamp
FROM
DB.web_log
WHERE
user_id = 'a001'
)sub
ORDER BY 2 ASC
;
| user_id | time_stamp | dif_unix |
|---|---|---|
| a001 | 2022-12-11 09:00:12 | null |
| a001 | 2022-12-11 09:02:04 | 112 |
| a001 | 2022-12-11 09:03:50 | 218 |
| a001 | 2022-12-12 16:38:25 | 113675 |
このようになるので、あとは、最後にWHERE句のなかで、
「dif_unixが、nullまたは1800以上のもの」に絞ってあげれば、
セッションが開始された数がわかる、つまりセッション数がわかる。ということになります。
ということで、セッション数を最後に計算しますぞ！
やっと答えに辿り着きました。ここまで長かったですが、
以下のようなクエリで、セッション数が導けるということになりますね！
SELECT
COUNT(*) AS session_num
FROM (
SELECT
user_id
,time_stamp
,CASE
WHEN lag_unix_ts IS null THEN null
ELSE unix_ts - lag_unix_ts
END AS dif_unix
FROM (
SELECT
user_id
,LAG(UNIX_TIMESTAMP(time_stamp), 1) OVER(PARTITION BY user_id ORDER BY time_stamp ASC) AS lag_unix_ts
,UNIX_TIMESTAMP(time_stamp) AS unix_ts
,time_stamp
FROM
DB.web_log
)sub
WHERE
dif_unix IS null OR dif_unix >= 1800
)main
;
ということで、答えとなるセッション数は「５」なのですが
一応、最後のクエリ内でCOUNTする前のテーブルを覗くとこんな感じになってます。
| user_id | time_stamp | dif_unix |
|---|---|---|
| a001 | 2022-12-11 09:00:12 | null |
| a001 | 2022-12-12 16:38:25 | 113675 |
| b313 | 2022-12-11 11:58:46 | null |
| b313 | 2022-12-11 13:08:11 | 4165 |
| c422 | 2022-12-12 23:39:39 | null |
あとがき
みなさん、いかがだったでしょうか？
しれっとLAG関数とLEAD関数のなかで、
・PARTITION BY
・ORDER BY
を説明なしに使っていたりするのですが、window関数は非常に奥が深く、
マスターすれば分析の幅がグッと広がる関数になっているので、もしこの記事を読んで興味を持ってくださった方がいらっしゃるのであれば大変嬉しいです。
ここまでお付き合いいただきまして大変ありがとうございました！
Discussion