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

## 1,データの準備
* TreasureDataのJavaScriptSDKを利用してデータを収集。　　
* 
[参考ドキュメント](http://docs.treasuredata.com/articles/javascript-sdk)　　


* 収集したデータ
<a href="https://gyazo.com/0d816d7d6a237d8197af4d6e831bd728"><img src="https://gyazo.com/0d816d7d6a237d8197af4d6e831bd728.png" alt="https://gyazo.com/0d816d7d6a237d8197af4d6e831bd728"/></a>

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

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

```sql
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
 
```sql
 WHERE td_os in ('iOS','Android')
```

 - iphoneユーザ：Iphone_User

```sql
td_os in ('iOS')
```

 - windowsユーザ：Windows_User
 
```sql
 td_os like '%Windows%'
```

<br><br><br>



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

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

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

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

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


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