メンバーアクティビティに関する分析

このページでは，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') などの集計も容易です。

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

（図）月別×日別のPV推移を表示

UU：アクティブ（ユニーク）ユーザー数

1. 日別

• アクティブユーザー数は同日（または同月，同年）の一人のユーザーの複数回のアクセスを1回と見なした指標です。
• UUは月次のUUは日次のUUの和では無い事に注意して下さい。
• また分散処理中に，ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので，データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

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

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

2. 月別

• アクティブユーザー数は同日（または同月，同年）の一人のユーザーの複数回のアクセスを1回と見なした指標です。
• UUは月次のUUは日次のUUの和では無い事に注意して下さい。
• また分散処理中に，ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので，データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

（図）全年を通じて，UUが大きかった月を示した棒グラフ

（図）全年を通じて，UUが大きかった月を示したツリーマップ

平均アクセス回数

1. 日別

• PV / UU で計算される，一人当たりの一日の平均アクセス回数です。
• クエリは，PVとUUを同時に求めて比率を持ったものになります。
• また分散処理中に，ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので，データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。

（図）月別×日別の平均アクセス回数

2. 月別

• PV / UU で計算される，一人当たりの一月の平均アクセス回数です。
• クエリは，PVとUUを同時に求めて比率を持ったものになります。
• また分散処理中に，ノード間の中間データ受け渡しに「値」でなくユーザーの「リスト」を使うことになりますので，データ中継をメモリ上で行う Presto ではメモリキャパシティ不足となる可能性があります。Hive での実行が無難です。 

 

（図）月ごとの平均アクセス回数の推移

新規ユーザー数

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

（図）日別の新規ユーザー数の推移

（図）月別×日別の新規ユーザー数

Recency

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

• Recency は，特定の日付を基準日として，各メンバーのラストアクセスが何日前（日単位）だったのかを求める指標です。今回は基準日を '2014-01-01' としています。
• 今回は意味のある集計結果を出すため，loginテーブルではなくlogin_smallテーブルを使用しました。
• クエリでは，0日前（連続アクセス），1日前，2日前，3日前，7日（1週間）前，14日（2週間）前，21（3週間）日前，30日（1ヶ月）前，60日（2ヶ月）前，...と明示的に分類しています。

 

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

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

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 としてください。

（図）ラストログイン日の月別分布

Frequency

1. 月別のログイン頻度

• 1月辺りにメンバーは平均何回ログインしてくれているのか，Frequency は Recency と同様にメンバーのアクティビティを見るのに有効な手法です。
• 月別・ログイン頻度（freq）ごとにカウントします。

 

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

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

• 2011年1月と，2011年2月でのメンバーのログイン頻度の変化を調べます。メンバーの2月と1月のログイン頻度の差の分布を見ます。
• クエリ内では明示的に1月と2月のカラムを分けています。

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

継続期間 

1. 週単位

• 継続期間は，初回ログイン日からラストログイン日までの差分（期間）をとったものです。まずは期間を週単位でとったものを見ていきます。
• 意味のある集計結果を出すため，loginテーブルではなくlogin_smallテーブルを使用しました。

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

 

（図）継続期間（週単位，1週間以内は数が多いのでここでは除外）の分布

2. 日単位

• 継続期間は，初回ログイン日からラストログイン日までの差分（期間）をとったものです。次に期間を日単位でとったものを見ていきます。
• 意味のある集計結果を出すため，loginテーブルではなくlogin_smallテーブルを使用しました。

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

直帰率

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

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

 

（図）2011年の直帰率の推移。

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

• 基準日（ここでは '2012-04-04'）までに n 日連続ログインしたメンバーの数を数えます。今回は n = 5 としています。
• 意味のある集計結果を出すため，loginテーブルではなくlogin_smallテーブルを使用しました。

member_id	login_count
1362061	5

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

• 基準日（ここでは '2012-04-04'）までの直近1週間で，n 回以上ログインしているメンバーの数を求めます。ここでは n=3 としています。
• 意味のある集計結果を出すため，loginテーブルではなくlogin_smallテーブルを使用しました。