トレジャーデータのJSSDKで収集したログでユーザーセグメントを作成する方法 #TreasureData

1,データの準備

TreasureDataのJavaScriptSDKを利用してデータを収集。　　
参考ドキュメント　　
収集したデータ

2,セグメント作成

作成するセグメントを定義してみる。

セグメント名	意味
Weekly_Over5_Access	１週間以内に５回アクセスしたユーザ
Hour_Over5_Access	時間別で５回以上アクセスしたユーザ
Weekday_Over5_Access	曜日別で５回アクセスしたユーザ
Conversion_User	ある期間にコンバージョンページを踏んだ人
Smartphone_User	iOSかAndroidのユーザ
Iphone_User	iPhoneを使っているユーザ
Windows_User	Windowsを使っているユーザ

上記のセグメントは他のセグメントとの掛け合わせも可能である。

以下はSQLで上記の定義したセグメントをSQLで表現しています。
それでは１つ１つ見ていきます。

アクセス頻度系（Freqency）

端末別１週間内に５回以上アクセスしたユーザ：Weekly_Over5_Access


-- Presto
SELECT
  a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Weekly_Over5_Accesses' as segment_name
FROM
(
 SELECT
  td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-05-24 00:00:00','2015-05-31 00:00:00','JST')
 GROUP BY td_client_id,td_os
 HAVING count(1) >= 5
) as a

２０時〜２４時に５回以上アクセスしている人：Hour_Over5_Access

-- Presto
SELECT
  a.hour as hour
 ,a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Hour_Over5_Access' as segment_name
FROM
(
 SELECT
  td_time_format(time,'HH') as hour
 ,td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
 GROUP BY 
  td_time_format(time,'HH')
 ,td_client_id
 ,td_os
 HAVING count(1) >= 5 
) as a
WHERE
 a.hour BETWEEN '20' AND '23'

曜日別アクセスで５回以上アクセスしている人：Weekday_Over5-Access

-- Hive
SELECT
  a.weekday as weekday
 ,a.td_client_id as td_client_id
 ,a.td_os as td_os
 ,'Weekday_Over5_Access' as segment_name
FROM
(
 SELECT
  weekofyear(td_time_format(time,'yyyy-MM-dd')) as weekday
 ,td_client_id
 ,td_os
 ,count(1) as cnt
 FROM 
  access
 WHERE
     TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
 GROUP BY 
 weekofyear(td_time_format(time,'yyyy-MM-dd'))
,td_client_id,td_os
 HAVING count(1) >= 5 
) as a

今回のセグメントは１つの条件ですが、
SQLで表現が可能であれば掛け合わせも
可能となる。

コンバージョン系

* ある期間コンバージョンしたユーザ：Conversion_User
コンバージョンページ：'/201506FreeUserSeminar_marketo-summit-2015-thankyou.html'

SELECT 
  td_client_id,
  td_os,
  COUNT(1) AS cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
  AND td_path = '/201506FreeUserSeminar_marketo-summit-2015-thankyou.html'
GROUP BY
  td_client_id,
  td_os
ORDER BY
  cnt DESC

キャリア別

smartphoneユーザー：Smartphone_User

 WHERE td_os in ('iOS','Android')

iphoneユーザ：Iphone_User

td_os in ('iOS')

windowsユーザ：Windows_User

 td_os like '%Windows%'

同じユーザが複数端末を持った場合の集約方法

ID集約：PCとsmartphoneその他タブレットなどを利用しているユーザを集約

SELECT
  td_ip
 ,count(distinct(td_client_id)) as cnt
FROM
  access
WHERE
  TD_TIME_RANGE(time,'2015-04-01 00:00:00','2015-06-01 00:00:00','JST')
GROUP BY 
  td_ip
HAVING
  count(distinct(td_client_id)) > 1

* 同じIPで違うIDの場合は集約していいのか？　　

ここの議論に関しては、同じwifiのIPを利用していることから
同じ人の端末と判断しで集約している例である。

今後はセグメント作成をSQLでの＜応用編＞や＜拡張編＞をまとめます。