簡単なセッションテーブルからパス解析のテーブルを作成する手順

目的

前回作った[ログからセッション情報の作成]
(http://qiita.com/itochu0523/items/80fcde77b4b1a8867baf)を用いて、
広告用のセグメントを作成に利用したり、行動分析のためのセッション詳細
テーブルを作成してみます。

前回作ったテーブルは

• session_idごとログテーブル（アクセス単位）
• session集計サマリ（セッションごとのカウントなど）
  の２つを作成しました。
  これを利用して様々な細かい軸で集計を作成してみます。

集計テーブル概要

テーブル名：session
テーブル内容：セッションIDに対してセッション内のアクセス別のデータを格納

カラム名	意味	サンプルデータ
time	時間（unixtime）	1434600335
session_id	セッション番号	ABAB1212-1111-AAAA-BBBB-XXXXXXXXXXXX
td_ip	ipアドレス	222.111.44.55
td_path	URLパス	/JP/UnsubscribePage.html
td_client_id	ユーザ番号	11111111-4555-4444-AAAA-cCCCCCCcccDD
td_title	ページタイトル	キャンペーン
td_browser	ブラウザ情報	Chrome
td_color	カラー数	32-bit
td_os_version	OSバージョン	4.4.2
td_browser_version	ブラウザバージョン	30.0.0.0
td_referrer	リファラ	http://ja.wikipedia.org/wiki/%E3%83%91%E3%83%AD%E3%82%A2
td_screen	スクリーンサイズ	1280x1024
td_os	OS	iOS
td_host	ドメイン名	www.AAAAA.com
td_url	URL	https://www.AAAAA.com/jp/UnsubscribePage.html
td_language	Lang	ja-jp

追加予定カラム

カラム名	意味	サンプルデータ
search_word	検索サイトの経由の検索ワード	スポーツ
mobile_flag	mobileかPCか（mobileの場合1）	0 or 1
page_id	マスタページのページ番号	21
conversion_flag	コンバージョンセッションかそうでないか？	0 or 1
session_data	ページ番号をハイフンで並べた値	21-1-456-444

テーブル名：session_summary
テーブル内容：sessionテーブルを集計したテーブル

カラム名	意味	サンプルデータ
time	時間（unixtime）	1434600335
session_id	セッション番号	ABAB1212-1111-AAAA-BBBB-XXXXXXXXXXXX
td_client_id	ユーザ番号	11111111-4555-4444-AAAA-cCCCCCCcccDD
session_start_time	セッション開始時間	2015-05-07 08:53:12
session_end_time	セッション終了時間	2015-05-07 09:50:02
session_stay_time	セッション滞在時間（秒）	3410
session_cnt	１セッション内のアクセス回数	7

• １日ごとのセッション数

SELECT
  TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'yyyy-MM-dd') as date
 ,count(distinct session_id) as session_cnt
FROM
 session_summary
GROUP BY
 TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'yyyy-MM-dd')
ORDER BY 
 TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'yyyy-MM-dd')

ページマスタの作成方法

• 過去分のマスタ作成方法
  
  概要：ページ番号をrow_numberを利用し番号を振る。
  
  作成テーブル：page_master

-- hive or presto
SELECT
 a.td_url as td_url
,ROW_NUMBER() OVER ( ORDER BY a.cnt DESC ) AS rownum
FROM
(
SELECT 
 regexp_replace(td_url, '(http://|https://)', '') as td_url
,count(1) as cnt
FROM access
-- 2ヶ月間のマスタを作成
WHERE TD_TIME_RANGE(time, '2015-04-01', '2015-05-31', 'JST')
GROUP BY regexp_replace(td_url, '(http://|https://)', '')
) a

これはある一定期間を１発でマスタ作成する方法のため、
毎日の運用では差分を追加するような仕組みに変更。

• 1日分のページマスタを作成
  
  概要：tmp_page_masterに１日分作成
  
  作成テーブル：tmp_page_master

SELECT
 a.td_url as td_url
,ROW_NUMBER() OVER ( ORDER BY a.cnt DESC ) AS rownum
FROM
(
SELECT 
 regexp_replace(td_url, '(http://|https://)', '') as td_url
,count(1) as cnt
FROM access
WHERE TD_TIME_RANGE(time, '2015-06-01', TD_TIME_ADD('2015-06-01','1d'), 'JST')
GROUP BY regexp_replace(td_url, '(http://|https://)', '')
) a

• 1日分の差分を作成し、元のpage_masterに追記する方法。
  
  概要：差分にrownumを振り、元のテーブルに追記する。
  
  作成テーブル：page_master

-- hive or presto
SELECT
  c.td_url as td_url
 ,(d.max_rownum + c.rownum) as rownum
FROM
(
 select  
  a.td_url as td_url
 ,ROW_NUMBER() OVER ( ORDER BY b.rownum DESC ) AS rownum
 from 
  tmp_page_master a
 LEFT OUTER JOIN
  page_master b
 ON 
  a.td_url = b.td_url
 WHERE b.rownum is null
) c
, 
( select max(rownum) as max_rownum from page_master ) d

この２つを毎日走らせることにより、新規ページを
マスタに追記し、ページ番号を振ることが可能となる。

• page_masterテーブルにConversionフラグを追加
  
  概要：コンバージョンページに1のフラグを立てる
  
  作成テーブル：page_master_detail

SELECT
  td_url
, rownum as page_id
,(CASE 
  -- 以下の行に足していく...
  WHEN td_url like '%thank%' THEN 1
  WHEN td_url LIKE '%THANK%' THEN 1
  WHEN td_url LIKE '%Thank%' THEN 1
  ELSE 0
  END) as conversion_flag
FROM page_master

セッション詳細テーブルの作成する

概要：追加予定カラムをsessionテーブルに追加する。
　　　追加カラムは、フラグ（モバイル、コンバージョン）、ページ番号
　　　リファラからの検索ワードなどを追加する。
作成テーブル：session_detail

SELECT
  b.session_id as session_id
, b.time as time
, b.td_ip as td_ip
, b.td_path as td_path
, b.td_client_id as td_client_id
, b.td_title as td_title
, b.mobile_flag as mobile_flag
, b.td_browser as td_browser
, b.td_color as td_color
, b.td_os_version as td_os_version
, b.td_browser_version as td_browser_version
, b.td_referrer as td_referrer 
, b.search_word as search_word
, b.td_screen as td_screen 
, b.td_os as td_os
, b.td_host as td_host 
, b.td_url as td_url
, (CASE WHEN c.conversion_flag = 1 THEN 1 ELSE 0 END) as conversion_flag
, c.page_id as page_id
, b.td_language as td_language
FROM
(
 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
 , regexp_replace(td_url, '(http://|https://)', '') as url
 , td_language
 , (CASE 
    WHEN td_referrer like '%google%'   THEN parse_url(TD_URL_DECODE(td_referrer),'QUERY','q')
    WHEN td_referrer like '%bing.com%' THEN parse_url(TD_URL_DECODE(td_referrer),'QUERY','q')
    WHEN td_referrer like '%yahoo%'    THEN parse_url(TD_URL_DECODE(td_referrer),'QUERY','p')
    ELSE parse_url(TD_URL_DECODE(td_referrer),'QUERY','p')
    END) as search_word
 , (CASE WHEN td_os IN ('iOS','BlackBerry OS','Android','Windows Phone') THEN 1 ELSE 0 END) as mobile_flag
 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 
) b
,page_master_detail c
WHERE b.url = c.td_url

ここからは上記のsession_detailから
セッションごとのパスを作成してみよう。

パス分析

概要：セッション番号ごとにアクセスされた順のページ番号を配列に入れる。
　　　同様にセッションカウントも表示する。
作成テーブル：session_path
テーブル構成：

カラム名	意味	サンプルデータ
time	時間（unixtime）	1434600335
session_id	セッション番号	ABAB1212-1111-AAAA-BBBB-XXXXXXXXXXXX
td_client_id	ユーザ番号	11111111-4555-4444-AAAA-cCCCCCCcccDD
path	パス（ページ番号をアクセス順に並べる）	["1","14","14","14","20","20","15","19",,"27"]
session_page_cnt	１セッション内のアクセス回数	7

SELECT
    session_id
  , td_client_id
  , collect_list(CAST(page_id as STRING)) as path
  , count(1) as page_cnt
FROM session_detail
GROUP BY
  session_id, td_client_id

session_pathへのアクセス方法

以下のように簡単に展開し、これから再度集計を行う。
同様にROW_NUMBER関数より、パス内の順番を示すことも可能です。

SELECT 
  session_id,
  pid
FROM
  session_path
LATERAL VIEW
  explode(PATH) pageTable AS pid

パス分析の詳細を作成することで各ユーザがどのようなアクセスを行っているかが
より明確になる。