背景
ログ解析の分野において、セッション集計というのは主流でありますが、それを実現するには特定の業者のツールやルールによって集計されていることが多い。
なかなか自分でルールを決めて集計するということ以前は難しかったのですが、クエリで簡単に集計できるという手順を説明いたします。
準備
今回もトレジャーデータの機能を利用して試してみます。
前回同様JavaScriptSDKでデフォルトで取得できるログを利用します。
- 主に集計に利用するユーザ関数（UDF）：TD_SESSIONIZE
使い方はマニュアルを参考にしました。
- やりたいこと
- セッション集計をしてみる
- セッションごとにユーザセグメントなどを作成してみる
では実際に実行してみましょう。
集計方法
集計したテーブルはトレジャーデータのResultOutput機能を利用して
データを書き出す。
- 用意したログをセッションIDを付与する 作成した集計テーブル:session
--hive
SELECT
TD_SESSIONIZE(time, 86400, td_ip) as session_id
, time
, td_ip
, td_path
, td_client_id
, td_title
, td_browser
, td_color
, td_os_version
, td_browser_version
, td_referrer
, td_screen
, td_os
, td_host
, td_url
, td_language
FROM
(
SELECT time, td_ip, td_path, td_client_id, td_title
,td_browser, td_color, td_os_version
,td_browser_version, td_referrer, td_screen
,td_os, td_host, td_url, td_language
from access
distribute by td_ip
sort by td_ip,time
) a
* セッションIDごとに開始時間と終了時間を集計する
（そのセッションが何PVかも一緒に出力）
集計したテーブル名：session_summary
SELECT
b.session_id as session_id
, b.td_client_id as td_client_id
, TD_TIME_FORMAT(b.session_start_time, 'yyyy-MM-dd HH:mm:ss', 'JST') as session_start_time
, TD_TIME_FORMAT(b.session_end_time, 'yyyy-MM-dd HH:mm:ss', 'JST') as session_end_time
, (b.session_end_time - b.session_start_time) as session_stay_time
, b.session_cnt as session_cnt
FROM
(
SELECT
a.session_id as session_id
, a.td_client_id as td_client_id
, min(a.time) as session_start_time
, max(a.time) as session_end_time
, count(1) as session_cnt
FROM
(
SELECT
TD_SESSIONIZE(time, 86400, td_ip) as session_id
, time
, td_ip
, td_path
, td_client_id
FROM (
SELECT time, td_ip, td_path, td_client_id
from access
distribute by td_ip
sort by td_ip,time
) t
) a
GROUP BY a.session_id,a.td_client_id
ORDER BY a.session_id
) b
* 出力結果
* やってみて思うこと
- 1日分のログを集計単位とすれば、１日における一人のセッションに関して情報をまとめることが可能。
- ipが同じものをセッションと定義し集約している。他の要素でもセッションが定義できそう。
例えば、同じユーザエージェントであるという要素も加えることが可能。
簡単に作成できるので試してみてください。