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

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

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

• その１
• その２
• その３
• その4
• その５
• その６

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 |