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

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

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

UserEngagement事務局

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