HOW TO TD(User Engagement)Treasure Data User Engagement
データサイエンティストのためのHiveQL分析クエリテンプレート その5
この記事は最終更新から3年以上が経過しています。最新情報は担当のカスタマーサクセスにご確認ください。
データサイエンティストのためのHiveQL分析クエリテンプレートシリーズ:
D. Activeness
アクティブネスはユーザーのPVやUUなどのユーザーの活況度を知るための基本的なKPIです。ここではいくつか挙げております。ここでも login ログを使っています。ここにある以上の Activeness KPI の求め方はLogin(アクセス)ログからわかる12の指標 その1を参考にしてください。
パターン D-1:PV
SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) AS d, COUNT(1) AS pv FROM login WHERE TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) > :last_measurement_time GROUP BY TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) ORDER BY d ASC
例
パターン D-2:アクティブ(ユニーク)ユーザー
SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) AS d, COUNT(DISTINCT(uid)) AS uu FROM login WHERE TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) > :last_measurement_time GROUP BY TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) ORDER BY d ASC
例
パターン D-3:新規ユーザー
SELECT TD_TIME_FORMAT(first_login_time, ‘yyyy-MM-dd’, ‘JST’) AS d, COUNT(1) AS new_user FROM ( SELECT uid, MIN(time) AS first_login_time FROM login GROUP BY uid ) t1 WHERE TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) > :last_measurement_time GROUP BY TD_TIME_FORMAT(first_login_time, ‘yyyy-MM-dd’, ‘JST’) ORDER BY d ASC
例
パターン D-4:訪問回数
SELECT TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) AS d, COUNT(1) / COUNT(DISTINCT(uid)) AS play_times FROM login GROUP BY TD_TIME_FORMAT(time, ‘yyyy-MM-dd’, ‘JST’) ORDER BY d ASC
例
パターン D-5:訪問間隔(単位:週)
SELECT TD_TIME_FORMAT(TD_SCHEDULED_TIME(), ‘yyyy-MM-dd’, ‘JST’) , CAST( play_term/7 AS BIGINT ) AS play_term_by_week, COUNT(1) AS uu FROM ( SELECT uid, CAST( (MAX(time) – MIN(time)) / 86400 AS BIGINT ) AS play_term FROM login GROUP BY uid )t1 GROUP BY CAST( play_term/7 AS BIGINT ), TD_TIME_FORMAT(TD_SCHEDULED_TIME(), ‘yyyy-MM-dd’, ‘JST’) ORDER BY play_term_by_week ASC
例
パターン D-6:訪問間隔(単位:日)
SELECT TD_TIME_FORMAT(TD_SCHEDULED_TIME(), ‘yyyy-MM-dd’, ‘JST’), play_term, COUNT(1) AS uu FROM ( SELECT uid as uid, CAST( (MAX(time) – MIN(time)) / 86400 AS BIGINT ) AS play_term FROM login GROUP BY uid )t1 GROUP BY TD_TIME_FORMAT(TD_SCHEDULED_TIME(), ‘yyyy-MM-dd’, ‘JST’) , play_term ORDER BY play_term ASC
例