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

*トレジャーデータはデータ収集、保存、分析のためのエンドツーエンドでサポートされたクラウドサービスです。

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

• その１, その２, その３, その４, その５, その６

D. Activeness

アクティブネスはユーザーのPVやUUなどのユーザーの活況度を知るための基本的なKPIです。ここではいくつか挙げております。ここでも login ログを使っています。ここにある以上の Activeness KPI の求め方は

Login（アクセス）ログからわかる12の指標 その１ - Treasure Data JP 公式ブログ

を参考にしてください。

パターン 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

例

トレジャーデータに関するお問い合わせは  support@treasure-data.com まで。