トレジャーデータ(Treasure Data)ブログ

トレジャーデータ(Treasure Data)ブログです。

本サイトは移転しました。新サイトにリダイレクトします。

データサイエンティストのための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 まで。