このページでは,member_id と time という,たった2つの項目を持つ login ログに対する KPI テンプレートです。この KPI は EC 業界に限らず,あらゆる業界に対して有用な KPI です。

PV:ページビュー

  • ページビューは一日のアクセス回数を表す最も基本的な指標です。

  • TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') で日付のフォーマットを指定します。

  • 年次:TD_TIME_FORMAT(time, 'yyyy-01-01', 'JST') や月次:TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST') などの集計も容易です。

SELECT
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
  COUNT(1) AS pv
FROM login
GROUP BY
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
ORDER BY d ASC 

(図)日別のPV推移を,年・月ごとに表示。同時に移動平均を掲載

(図)月別×日別のPV推移を表示

UU:アクティブ(ユニーク)ユーザー数

1. 日別

  • アクティブユーザー数は同日(または同月,同年)の一人のユーザーの複数回のアクセスを1回と見なした指標です。

  • UUは月次のUUは日次のUUの和では無い事に注意して下さい。

  • また分散処理中に,ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので,データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

SELECT
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
  COUNT(DISTINCT(member_id)) AS uu
FROM login
GROUP BY
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
ORDER BY d ASC

(図)日別のUU推移を,年・月ごとに表示。同時に移動平均を掲載

(図)月別×日別のUU推移をカラーテーブルで表示

2. 月別

  • アクティブユーザー数は同日(または同月,同年)の一人のユーザーの複数回のアクセスを1回と見なした指標です。

  • UUは月次のUUは日次のUUの和では無い事に注意して下さい。

  • また分散処理中に,ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので,データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

SELECT TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST') AS d, COUNT(DISTINCT(member_id)) AS uu
FROM login
GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST')
ORDER BY d ASC

(図)全年を通じて,UUが大きかった月を示した棒グラフ

(図)全年を通じて,UUが大きかった月を示したツリーマップ

平均アクセス回数

1. 日別

  • PV / UU で計算される,一人当たりの一日の平均アクセス回数です。

  • クエリは,PVとUUを同時に求めて比率を持ったものになります。

  • また分散処理中に,ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので,データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

SELECT
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
  COUNT(1)*1.0 / COUNT(DISTINCT(member_id)) AS login_times
FROM login
GROUP BY
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
ORDER BY d ASC

(図)月別×日別の平均アクセス回数

2. 月別

  • PV / UU で計算される,一人当たりの一月の平均アクセス回数です。

  • クエリは,PVとUUを同時に求めて比率を持ったものになります。

  • また分散処理中に,ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので,データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。 

SELECT
  TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST') AS d,
  COUNT(1) AS pv, COUNT(DISTINCT(member_id)) AS uu,
  COUNT(1)*1.0 / COUNT(DISTINCT(member_id)) AS avg_login_times
FROM login
GROUP BY
  TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST')
ORDER BY d ASC

(図)月ごとの平均アクセス回数の推移

新規ユーザー数

  • 新規ユーザー数は,初めてサイトにログインしたユーザーを日別にカウントしたものです。

  • 新規ユーザー数は日毎の推移を眺めると同時に,日にちごとに積み上げていくスタックバーチャートは,その月のある時点でどれだけ新規ユーザーが積み上がってきたのかを見る有効な手法です。

SELECT
  TD_TIME_FORMAT(first_login_time, 'yyyy-MM-dd', 'JST') AS d,
  COUNT(1) AS new_user
FROM
(
  SELECT member_id, MIN(time) AS first_login_time
  FROM login
  GROUP BY member_id
) t1
GROUP BY
  TD_TIME_FORMAT(first_login_time, 'yyyy-MM-dd', 'JST')
ORDER BY d ASC

(図)日別の新規ユーザー数の推移

(図)月別×日別の新規ユーザー数

Recency

1. 直近と最終ログイン日までの期間(日単位)

  • Recency は,特定の日付を基準日として,各メンバーのラストアクセスが何日前(日単位)だったのかを求める指標です。今回は基準日を '2014-01-01' としています。

  • 今回は意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

  • クエリでは,0日前(連続アクセス),1日前,2日前,3日前,7日(1週間)前,14日(2週間)前,21(3週間)日前,30日(1ヶ月)前,60日(2ヶ月)前,...と明示的に分類しています。

SELECT term_category, COUNT(1) AS cnt
FROM
(
  SELECT
    member_id,
    CASE
      WHEN term_daily=0 THEN 0
      WHEN term_daily=1 THEN 1
      WHEN term_daily=2 THEN 2
      WHEN term_daily=3 THEN 3
      WHEN  3<term_daily AND term_daily<=7  THEN 7
      WHEN  7<term_daily AND term_daily<=14 THEN 14
      WHEN 14<term_daily AND term_daily<=21 THEN 21
      WHEN 21<term_daily AND term_daily<=28 THEN 30
      WHEN 28<term_daily AND term_daily<=60 THEN 60
      WHEN 61<term_daily AND term_daily<=60 THEN 90
      WHEN 91<term_daily AND term_daily<=120 THEN 120
      WHEN 121<term_daily AND term_daily<=150 THEN 150
      WHEN 151<term_daily AND term_daily<=180 THEN 180
      WHEN 181<term_daily AND term_daily<=365 THEN 365
      WHEN 365<term_daily     THEN 366
    END AS term_category
  FROM
  (
    SELECT member_id,
      CAST(
        (TD_TIME_PARSE('2014-01-01')-MAX(time)) / (60*60*24)
      AS BIGINT ) AS term_daily
    FROM login_small
    GROUP BY member_id
    HAVING MAX(time) < TD_TIME_PARSE('2014-01-01')
  ) t1
) t2
GROUP BY term_category
ORDER BY term_category ASC

(図)直近のログイン日が何日前かを,14日,30日,…とカテゴライズし,各々人数を確認。下の円グラフより,30日までにアクティビティのあったユーザーは15%程度。

  • 次のクエリでは,期間別に細かく出しています。

SELECT term_daily, COUNT(1) AS cnt
FROM
(
  SELECT
    member_id, term_daily
  FROM
  (
    SELECT member_id,
      CAST(
        (TD_TIME_PARSE('2014-01-01')-MAX(time)) / (60*60*24)
      AS BIGINT ) AS term_daily
    FROM sales_slip
    GROUP BY member_id
    HAVING MAX(time) < TD_TIME_PARSE('2014-01-01')
  ) t1
) t2
GROUP BY term_daily
ORDER BY term_daily ASC

2. 最終ログイン月の分布

  • こちらは各メンバーのラストアクセス日ごとに集計し,分布をみるものになります。基準日を '2014-12-31' とし,それより前の(2014年中の)ラストアクセス日を取得しています。

  • 今回は意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

  • もちろん日別での Recency を見る事も可能です。

  • TD_TIME_FORMAT(MAX(time), 'yyyy-MM-01', 'JST') AS m を TD_TIME_FORMAT(MAX(time), 'yyyy-MM-dd', 'JST') AS d としてください。

SELECT m, COUNT(1) AS cnt
FROM
(
  SELECT
    member_id,
    TD_TIME_FORMAT(MAX(time), 'yyyy-MM-01', 'JST') AS m
  FROM login_small
  GROUP BY member_id
  HAVING MAX(time) < TD_TIME_PARSE('2014-12-31')
) t
GROUP BY m
ORDER BY m ASC

(図)ラストログイン日の月別分布

Frequency

1. 月別のログイン頻度

  • 1月辺りにメンバーは平均何回ログインしてくれているのか,Frequency は Recency と同様にメンバーのアクティビティを見るのに有効な手法です。

  • 月別・ログイン頻度(freq)ごとにカウントします。

SELECT freq, m, COUNT(1) AS cnt
FROM
(
  SELECT
    TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST') AS m,
    member_id,
    COUNT(1) AS freq
  FROM login
  GROUP BY member_id, TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST')
) t
GROUP BY m, freq
ORDER BY m ASC, freq ASC

(図)2011年の月・日別 Frequency の分布。Tableau 得意の月×日の棒グラフ付きテーブルがわかりやすい可視化例です。

2. 当月と先月のログイン頻度の比較プロット

  • 2011年1月と,2011年2月でのメンバーのログイン頻度の変化を調べます。メンバーの2月と1月のログイン頻度の差の分布を見ます。

  • クエリ内では明示的に1月と2月のカラムを分けています。

SELECT cnt_feb-cnt_jan AS diff, COUNT(1) AS cnt
FROM
(
  SELECT
    member_id,
    SUM(
      IF(TD_TIME_FORMAT(time,'yyyy-MM-01','JST')='2011-01-01',1,0)
    ) AS cnt_jan,
    SUM(
      IF(TD_TIME_FORMAT(time,'yyyy-MM-01','JST')='2011-02-01',1,0)
    ) AS cnt_feb
  FROM login
  GROUP BY member_id, TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST')
  HAVING
  (
    TD_TIME_FORMAT(time,'yyyy-MM-01','JST') = '2011-01-01' OR TD_TIME_FORMAT(time,'yyyy-MM-01','JST') = '2011-02-01'
  )
) t
GROUP BY (cnt_feb-cnt_jan)
ORDER BY diff ASC

(図)2月と1月のログイン頻度の差分の分布。ほとんどのメンバーは ±5日 の差で収まっている。

継続期間 

1. 週単位

  • 継続期間は,初回ログイン日からラストログイン日までの差分(期間)をとったものです。まずは期間を週単位でとったものを見ていきます。

  • 意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

  • 継続期間が1週間以内の人は期間=0(週)として扱われます。

SELECT
  CAST( play_term/7 AS BIGINT ) AS play_term_by_week,
  COUNT(1) AS uu
FROM
( SELECT member_id,
    CAST( (MAX(time) - MIN(time)) / 86400 AS BIGINT ) AS play_term
  FROM login_small
  GROUP BY member_id
)t1
GROUP BY
  CAST( play_term/7 AS BIGINT )
ORDER BY play_term_by_week ASC

(図)継続期間(週単位,1週間以内は数が多いのでここでは除外)の分布

2. 日単位

  • 継続期間は,初回ログイン日からラストログイン日までの差分(期間)をとったものです。次に期間を日単位でとったものを見ていきます。

  • 意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

SELECT
  play_term,
  COUNT(1) AS uu
FROM
( SELECT member_id,
    CAST( (MAX(time) - MIN(time)) / 86400 AS BIGINT ) AS play_term
  FROM login_small
  GROUP BY member_id
)t1
GROUP BY play_term
ORDER BY play_term ASC

(図)継続期間(日単位)の分布。0日は数多いので除外。

直帰率

  • 直帰はログインはしたものの,他のページに移動せずに去ってしまったものおよび,同日に再ログインを行わなかったメンバーの数を数えます。 

  • 今回はログインページの直帰率しか見ていませんが,直帰率は本来ページごとに求められる指標です。

SELECT t2.d AS d,
  COUNT(1)*1.0/SUM(t2.cnt)*100 AS bounce_rate
FROM
(
  SELECT
    TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
    member_id,
    COUNT(1) AS cnt
  FROM login
  GROUP BY
    TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'),
    member_id
) t1
RIGHT OUTER JOIN
(
  SELECT
    TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS d,
    COUNT(DISTINCT member_id) AS cnt
  FROM login
  GROUP BY
    TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')
) t2
ON ( t1.d=t2.d )
WHERE t1.cnt < 2 AND 0 < t2.cnt
GROUP BY t2.d
ORDER BY d ASC

(図)2011年の直帰率の推移。

Streak 5:5日連続ログインユーザーリスト

  • 基準日(ここでは '2012-04-04')までに n 日連続ログインしたメンバーの数を数えます。今回は n = 5 としています。

  • 意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

SELECT member_id, login_count
FROM
(
  SELECT member_id, COUNT(1) AS login_count
  FROM
  (
    SELECT member_id, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS login_day
    FROM login_small
    WHERE TD_TIME_RANGE(
      time,
      TD_TIME_ADD(TD_TIME_PARSE('2012-04-04'), '-5d'),
      TD_TIME_PARSE('2012-04-04'), 'JST'
    )
    GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), member_id
  ) login_table
  GROUP BY member_id
) t
WHERE login_count = 5

member_id

login_count

1362061

5

3回/週:高頻度ログインユーザー数

  • 基準日(ここでは '2012-04-04')までの直近1週間で,n 回以上ログインしているメンバーの数を求めます。ここでは n=3 としています。

  • 意味のある集計結果を出すため,loginテーブルではなくlogin_smallテーブルを使用しました。

SELECT member_id, login_count
FROM
(
  SELECT member_id, COUNT(1) AS login_count
  FROM
  (
    SELECT member_id, TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS login_day
    FROM login_small
    WHERE TD_TIME_RANGE(
      time,
      TD_TIME_ADD( TD_TIME_PARSE('2012-04-04'), '-6d' ),
      TD_TIME_PARSE('2012-04-04')
    )
    GROUP BY TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'), member_id
  ) login_table
  GROUP BY member_id
) t
WHERE 3 < login_count
ORDER BY login_count DESC

  • No labels