Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

目的

前回作ったログからセッション情報の作成を用いて、
広告用のセグメントを作成に利用したり、行動分析のためのセッション詳細
テーブルを作成してみます。

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

集計テーブル概要

テーブル名: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 1セッション内のアクセス回数 7
  • 1日ごとのセッション数
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発でマスタ作成する方法のため、
毎日の運用では差分を追加するような仕組みに変更。

  • 1日分のページマスタを作成
    概要:tmp_page_masterに1日分作成
    作成テーブル: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

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

  • 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 1セッション内のアクセス回数 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

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

itochu0523
今何もやってないので、 今後自分で何かやろうかな...
treasuredata
Customer Data Platformの開発・提供をしています。
https://www.treasuredata.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away