fbpx
HOW TO TD(User Engagement)Treasure Data User Engagement

データサイエンティストのためのHiveQL分析クエリテンプレート その6

ホーム » データサイエンティストのためのHiveQL分析クエリテンプレート その6

この記事は最終更新から3年以上が経過しています。最新情報は担当のカスタマーサクセスにご確認ください。

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

UserEngagement事務局

ユーザーの皆さまへのお知らせや、Treasure Data UserEngagementのサイト運営を担当する事務局アカウント。
Back to top button