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

## 目的
前回作った[ログからセッション情報の作成]
(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|




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

```sql
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')
```



## ページマスタの作成方法

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

```sql
-- 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日分のページマスタを作成<br>
概要：tmp_page_masterに１日分作成<br>
作成テーブル：tmp_page_master

```sql

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に追記する方法。<br>
概要：差分にrownumを振り、元のテーブルに追記する。<br>
作成テーブル：page_master

```sql
-- 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フラグを追加<br>
概要：コンバージョンページに1のフラグを立てる<br>
作成テーブル：page_master_detail

```sql
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

```sql

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|


```sql

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関数より、パス内の順番を示すことも可能です。

```sql

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

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