TreasureDataで対象ユーザーが何回訪問したのかを調べる

データサイエンティストのためのHiveQL分析クエリテンプレート その６ - トレジャーデータ（Treasure Data）公式ブログ
これの応用例です。

社内ブログで評判が良かったのでこちらにも書きます。
リテンションの指標として

1. 週に何日トップページに来るのか
2. 1日に何セッション（ある一定の連続したアクセスの塊）トップページを訪問しているのか

をTDで調べます。HiveでもPrestoでも行けるはずです。

1. あるユーザー群が1周間に何日トップページを見ているか

1. トップページにアクセスしている限定公開ユーザが、11/22-11/29の間にアクセスしたときの、uidとアクセス日を列挙
2. 1. をuidごとにカウントして、各ユーザが何日アクセスしてるかを集計
3. 1. をアクセス日数毎にユーザ数を集計

という流れで処理をしていきます。

access_days.sql

-- 3. 2.をアクセス日数毎にユーザ数を数える
SELECT access_count, COUNT(uid) AS cnt
FROM
(
  -- 2. 1.をuidごとにカウントして、ユーザ毎に何日アクセスしてるかを数える
  SELECT uid, COUNT(1) AS access_count
  FROM
  (
    -- 1. トップページにアクセスしている対象ユーザが、11/22-11/28の間にアクセスしたときの、uidとアクセス日を列挙
    SELECT uid, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS access_day
    FROM login
    WHERE TD_TIME_RANGE( time, TD_TIME_ADD('2015-11-29', '-7d'), '2015-11-29', 'JST')
      AND
        uid IN 
          (
            -- 対象のユーザーIDを列挙する or サブクエリを発行する
          )
        AND
          path = '/' -- トップにアクセスしているユーザ
    GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), uid
  ) access_table
  GROUP BY uid
) t
GROUP BY access_count
ORDER BY access_count DESC

2. あるテストユーザー群が1日何回トップページに訪問してるか

「1日何回」というのは単純に回数をはかっても意味が無い場合がほとんどです。
なぜなら、ページ遷移でトップページに戻ってくるみたいなこともあるでしょう。そこで、アクセスログの塊をセッション化してそれを"1回訪問"とみなしてカウントしてみましょう。

ポイントはTD_SESSIONIZEという便利関数を使って、900秒以上間隔が空いたら別のセッションとみなす（≒違うアクセスと思う）ようにすることです。
ユーザー数が多いとセッション化するところの量が増えてしまうので注意してください。

TD_SESSIONIZEを一言で言うと「一連の連続したアクセスの塊に、特定のID（セッションID）を付ける便利機能」と覚えておいてもらえばいいです。

以下の流れで処理を行います。

1. 対象ユーザが11/27にアクセスしたログにセッションIDを振る
2. 1.からトップページをアクセスしたセッションに絞って、ユーザーごとに回数を数える
3. 2.をセッション回数毎にユーザー数を数える

access_sessions.sql

-- 3. 2.をセッション回数毎にユーザー数を数える
SELECT session_count, COUNT(uid) AS cnt
FROM
(
  -- 2. 1.からトップページをアクセスしたセッションに絞って、ユーザーごとに回数を数える
  SELECT uid, COUNT(session_id) AS session_count
  FROM
  (
    -- 1. 対象ユーザが11/27にアクセスしたログにセッションIDを振る
    SELECT TD_SESSIONIZE(time, 900, unique_id) AS session_id, uid, time, path
    FROM
    (  
       SELECT time, unique_id, uid, time, path
       FROM login
       distribute by unique_id -- Prestoの場合は不要
       sort by unique_id, time
    )
    WHERE TD_TIME_RANGE(time, '2015-11-27', '2015-11-28', 'JST')
      AND
        uid IN 
          (
            --対象のuser id
          )
  ) session_table
  WHERE path = '/' -- アクセスしたページ
  GROUP BY uid
) session_count_table
GROUP BY session_count
ORDER BY session_count DESC

まとめ

TD_SESSIONIZEめっちゃべんり！