読者です 読者をやめる 読者になる 読者になる

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

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

Login(アクセス)ログからわかる12の指標 その3

バッチクエリ(Hive) アドホッククエリ(TQA) クエリ集 実践シリーズ

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

「Login(アクセス)ログからわかる12の指標 シリーズ」

前回(その2)の続きです。

10. 直近のログインユーザーの,(i) 登録時からのプレイ期間,および (ii) 登録日,の分布

直近1週間内にログインのあったユーザーが,いつ登録してくれたユーザーなのか, (i) 初回ログインから最新ログイン日までの期間,および (ii) 初回ログイン日の分布を調べます。

* 現在のタイムスタンプを調べるのは TD_SCHEDULED_TIME() を使いますが,今回のサンプルログデータは 2012-04-04 が最大の日付なのでこの日を直近と定義しています。

(i) 登録時からのプレイ期間の分布
SELECT term, COUNT(1) AS cnt
FROM
(
  SELECT access_table.uid AS uid,
    datediff(access_table.most_recent_day, install_table.install_day) AS term
  FROM
  (
    SELECT uid, MAX(TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')) AS most_recent_day
    FROM login
    WHERE TD_TIME_RANGE(
      time,
      TD_TIME_ADD(TD_TIME_PARSE('2012-04-04'), '-7d'),
      TD_TIME_PARSE('2012-04-04'),
      'JST'
    )
    GROUP BY 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.most_recent_day
) t
GROUP BY term
ORDER BY term

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

(ii) 登録日の分布
SELECT install_day, COUNT(1) AS cnt
FROM
(
  SELECT access_table.uid AS uid, install_table.install_day AS install_day
  FROM
  (
    SELECT uid, MAX(TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')) AS most_recent_day
    FROM login
    WHERE TD_TIME_RANGE(
      time,
      TD_TIME_ADD(TD_TIME_PARSE('2012-04-04'), '-7d'),
      TD_TIME_PARSE('2012-04-04'),
      'JST'
    )
    GROUP BY 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.most_recent_day
) t
GROUP BY install_day
ORDER BY install_day

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

Metric Insights

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

トレジャーデータでは上記の指標を一覧するためのダッシュボード:Metric InsightsをOEM提供しています。

Treasure Data Platform で始めるデータ分析入門 〜7. Data Visualization 〜 Metric Insights - Treasure Data JP 公式ブログ 

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