アクセスログからセッションを作成する方法

背景

ログ解析の分野において、セッション集計というのは主流でありますが、それを実現するには特定の業者のツールやルールによって集計されていることが多い。
なかなか自分でルールを決めて集計するということ以前は難しかったのですが、クエリで簡単に集計できるという手順を説明いたします。

準備　　

今回もトレジャーデータの機能を利用して試してみます。
前回同様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が同じものをセッションと定義し集約している。他の要素でもセッションが定義できそう。
  例えば、同じユーザエージェントであるという要素も加えることが可能。

簡単に作成できるので試してみてください。