トレジャーデータ(Treasure Data)公式ブログ

トレジャーデータ(Treasure Data)公式ブログです。

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

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

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

D. Activeness

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

Login(アクセス)ログからわかる12の指標 その1 - 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

f:id:treasure-data:20140430171111p:plain

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

f:id:treasure-data:20140430172257p:plain

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

f:id:treasure-data:20140430172444p:plain

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

f:id:treasure-data:20140430172705p:plain

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

f:id:treasure-data:20140430172838p:plain

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

f:id:treasure-data:20140430173000p:plain

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