データサイエンティストのためのHiveQL分析クエリテンプレート その6
本記事は移転しました。新サイトにリダイレクトします。
*トレジャーデータはデータ収集、保存、分析のためのエンドツーエンドでサポートされたクラウドサービスです。
データサイエンティストのためのHiveQL分析クエリテンプレートシリーズ:
E. リテンション分析
最後はリテンションに関するクエリテンプレートです。
パターン E-1:直帰率
※ ここでは同日内に1回しかログインしなかったユーザーを「直帰」と見なします。
SELECT t2.d AS d,
COUNT(1)/SUM(t2.cnt)*100 AS bounce_rate
FROM
(
SELECT
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
uid,
COUNT(1) AS cnt
FROM login
GROUP BY
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'),
uid
) t1
RIGHT OUTER JOIN
(
SELECT
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
COUNT(DISTINCT uid) AS cnt
FROM login
GROUP BY
TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
) t2
ON ( t1.d=t2.d )
WHERE t1.cnt < 2 AND 0 < t2.cnt
GROUP BY t2.d
ORDER BY d ASC
Result :
+------------+-----------------------+
| d | bounce_rate |
+------------+-----------------------+
| 2011-11-30 | 0.009402030838661151 |
| 2011-12-01 | 0.008819119851838787 |
| 2011-12-02 | 0.005880277549100318 |
| 2011-12-03 | 0.00893734918223255 |
| 2011-12-04 | 0.0070531809846240655 |
| 2011-12-05 | 0.01663616702711695 |...
パターン E-2:(インストール後の)復帰ユーザー
直近1週間以内にアクセスのあったユーザーの内,登録後の最初のアクセスだったユーザー数を数えます。例えば直近1週間で何らかのイベントを行っており,登録後アクセスの無かったユーザーが関心を抱いてアクセス(復帰)するのを期待している場合はこの指標を参照します。
SELECT recent_term, COUNT(1) AS cnt
FROM
(
SELECT uid, MIN(term) AS recent_term
FROM
(
SELECT access_table.uid AS uid, datediff(access_table.access_day, install_table.install_day) AS term
FROM
(
SELECT uid, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS access_day
FROM login
WHERE TD_TIME_RANGE( time, TD_TIME_ADD(TD_SCHEDULED_TIME(), '-7d'), TD_SCHEDULED_TIME(), 'JST')
GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), uid
) access_table
JOIN
(
SELECT uid, TD_TIME_FORMAT(MIN(time) , 'yyyy-MM-dd', 'JST') AS install_day
FROM login
GROUP BY uid
) install_table
ON
( access_table.uid=install_table.uid )
WHERE install_table.install_day < access_table.access_day
) t1
GROUP BY uid
) t2
GROUP BY recent_term
ORDER BY recent_term
Result :
+-------------+-------+
| recent_term | cnt |
+-------------+-------+
| 1 | 12897 |
| 2 | 8051 |
| 3 | 3569 |
| 4 | 2735 |
| 5 | 1886 |
| 6 | 1727 |
| 7 | 1216 |...
| 121 | 1 |
| 123 | 2 |
| 124 | 3 |
| 125 | 2 |
| 126 | 1 |
+-------------+-------+
パターン E-3:( '2011-12-31' を起点として) 5日連続ログインユーザーのリストアップ
※ 今日を基準に今日までに5日連続アクセスしてくれたユーザーは,'2012-12-31' をTD_SCHEDULED_TIME() (現在のタイムスタンプを取得する)に書き換えます。
SELECT uid, access_count
FROM
(
SELECT uid, COUNT(1) AS access_count
FROM
(
SELECT uid, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS access_day
FROM login
WHERE TD_TIME_RANGE( time, TD_TIME_ADD('2011-12-31', '-5d'), TD_SCHEDULED_TIME(), 'JST')
GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), uid
) access_table
GROUP BY uid
) t
WHERE access_count = 5
Result :
+------------+--------------+
| uid | access_count |
+------------+--------------+
| 1011012442 | 5 |
| 101458562 | 5 |
| 101484563 | 5 |
| 1015623037 | 5 |...
パターン E-4:今日までの過去1週間で週4日アクセス(高頻度)ユーザー
SELECT uid, access_count
FROM
(
SELECT uid, COUNT(1) AS access_count
FROM
(
SELECT uid, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS access_day
FROM login
WHERE TD_TIME_RANGE( time, TD_TIME_ADD(TD_SCHEDULED_TIME(), '-7d'), TD_SCHEDULED_TIME(), 'JST')
GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), uid
) access_table
GROUP BY uid
) t
WHERE 3 < access_count
ORDER BY access_count DESC
Result :
+------------+--------------+
| uid | access_count |
+------------+--------------+
| 10004870 | 7 |
| 10005082 | 7 |
| 1000577024 | 6 |
| 1001436069 | 6 |
| 100230707 | 6 |...
| 1002452958 | 4 |
| 100324514 | 4 |
| 1003377375 | 4 |
トレジャーデータに関するお問い合わせは support@treasure-data.com まで。