2017.05.11
第5回 アクション回数に応じた定着率を集計する
ユーザーのサービス利用開始から日時が経過すると、ロイヤリティが高いユーザーに成長する、あるいは利用を中止して退会、登録は残っているものの再び利用することがない状態(休眠)になるなど、ユーザーの状態に変化が生じます。
・ユーザーが継続して利用する(リピート)
・ユーザーが利用を中止する(退会・休眠)
サービスを運営する側としては、継続して利用してもらうことを願っています。そのためには、まずどの程度のユーザーが継続して利用しているのかを把握して、目標に対する乖離をどのように埋めるか検討する必要があります。また、しばらくサービスの利用がなかったり、休眠の傾向があるユーザーに対しては、継続してもらうための工夫を常に考えたいと思っているはずです。
退会ユーザーに対する施策は実施が困難ですが、休眠ユーザーに対しては、メルマガやCM、広告などの施策で、利用を再開してもらいたいところです。
ユーザーのサービス利用に対する時系列の変化を数値化し可視化することで、現状の把握、施策の効果、今後の計画に役立つ糸口となるレポートと集計するSQLを解説するChapter5-2「ユーザー全体の時系列による状態変化を見付ける」から、5-2-4「アクション回数に応じた定着率を集計する」を紹介します。
本節では、下記に示す2つのテーブル、ユーザーマスタとアクションログを元に解説します。SNSに代表されるコミュニティサービスを想定してください。
●データ:ユーザーマスタ(mst_users)
●データ:アクションログ(action_log)
FacebookやTwitterなどに代表されるSNSの事例で、「登録後1週間以内に10人をフォローしたらユーザーはサービスを継続的に利用する」ことを耳にしたことはないでしょうか。このような調査結果から、大きな方針を示すことができれば、「○○さんは友達ではないですか?」、「人気のユーザーをフォローしましょう」などを表示するモジュールを開発したり、チュートリアルに人気ユーザーをフォローする導線を設けるなど、サービスの活性化に繋がる具体的な施策を検討できます。
本項では例として、下図に示す通り、登録日と登録日以降の7 日以内(7 日定着判定期間)に実行したアクション数に応じて、14 日定着率に変化があるかを集計します。
本項では、下図に示す通り、各アクション別に度数分布表を作成し、達成者における14日定着率を 集計するクエリを紹介します。
まず、上図に示したアクションの階級マスタを一時テーブルとして定義し、定着率を求めるためのユーザーアクションフラグとの組み合わせを、CROSS JOINで求めます。そのコード例を下記に示します。
WITH repeat_interval(index_name, interval_begin_date, interval_end_date) AS ( -- ■ PostgreSQLの場合、VALUES句で一時テーブルを作成できる -- ■ Hive, Redshift, BigQuery, SparkSQLの場合、SELECT文で代用可能 -- ※3-4-5:擬似的なテーブルを作成する参照 VALUES ('14 day retention', 8, 14) ) , action_log_with_index_date AS ( -- 略 ) , user_action_flag AS ( -- 略 ) , mst_action_bucket(action, min_count, max_count) AS ( -- アクションの階級マスタ -- ■ PostgreSQLの場合、VALUES句で一時テーブルを作成できる -- ■ Hive, Redshift, BigQuery, SparkSQLの場合、SELECT文とUNION ALLで代用可能 VALUES ('comment', 0, 0) , ('comment', 1, 5) , ('comment', 6, 10) , ('comment', 11, 9999) -- 最大値として便宜上9999を入れている , ('follow' , 0, 0) , ('follow' , 1, 5) , ('follow' , 6, 10) , ('follow' , 11, 9999) -- 最大値として便宜上9999を入れている ) , mst_user_action_bucket AS ( -- ユーザーマスタとアクション階級マスタの組み合わせを算出 SELECT u.user_id , u.register_date , a.action , a.min_count , a.max_count FROM mst_users AS u CROSS JOIN mst_action_bucket AS a ) SELECT * FROM mst_user_action_bucket ORDER BY user_id, action, min_count ;
user_id | register_date | action | min_count | max_count ---------+---------------+---------+-----------+----------- U001 | 2016-01-01 | comment | 0 | 0 U001 | 2016-01-01 | comment | 1 | 5 U001 | 2016-01-01 | comment | 6 | 10 U001 | 2016-01-01 | comment | 11 | 9999 U001 | 2016-01-01 | follow | 0 | 0 U001 | 2016-01-01 | follow | 1 | 5 U001 | 2016-01-01 | follow | 6 | 10 U001 | 2016-01-01 | follow | 11 | 9999 U002 | 2016-01-01 | comment | 0 | 0 …
次のステップ 上記コード例の出力結果に、登録後7日間のログをLEFT JOINし、登録後7日間のアクション度数を集計するクエリを、下記のコード例に示します。
なお、下記のコード例では、登録後7日間のログを結合するため、JOIN句内でBETWEEN句を用いますが、Hiveでは動作しません。別途WHERE句での絞り込みが必要となりますが割愛します。
WITH repeat_interval AS ( -- 略 ) , action_log_with_index_date AS ( -- 略 ) , user_action_flag AS ( -- 略 ) , mst_action_bucket AS ( -- 略 ) , mst_user_action_bucket AS ( -- 略 ) , register_action_flag AS ( -- 登録日から7日後までのアクション数をカウントし、 -- アクション階級と14日定着の達成フラグを算出する SELECT m.user_id , m.action , m.min_count , m.max_count , COUNT(a.action) AS action_count , CASE WHEN COUNT(a.action) BETWEEN m.min_count AND m.max_count THEN 1 ELSE 0 END AS achieve , index_name , index_date_action FROM mst_user_action_bucket AS m LEFT JOIN action_log AS a ON m.user_id = a.user_id -- 登録日当日から7日後までのアクションログを結合 -- ■ PostgreSQL, Redshiftの場合は下記 AND CAST(a.stamp AS date) BETWEEN CAST(m.register_date AS date) AND CAST(m.register_date AS date) + interval '7 days' -- ■ BigQueryの場合は下記 -- AND date(timestamp(a.stamp)) -- BETWEEN CAST(m.register_date AS date) -- AND date_add(CAST(m.register_date AS date), interval 7 day) -- ■ SparkSQLの場合は下記 -- AND CAST(a.stamp AS date) -- BETWEEN CAST(m.register_date AS date) -- AND date_add(CAST(m.register_date AS date), 7) -- ■ Hiveの場合は、JOIN句でBETWEEN句が使用できないため、WHERE句での絞り込みが必要 AND m.action = a.action LEFT JOIN user_action_flag AS f ON m.user_id = f.user_id WHERE f.index_date_action IS NOT NULL GROUP BY m.user_id , m.action , m.min_count , m.max_count , f.index_name , f.index_date_action ) SELECT * FROM register_action_flag ORDER BY user_id, action, min_count ;
-[ RECORD 1 ]-----+----------------- user_id | U001 action | comment min_count | 0 max_count | 0 action_count | 0 achieve | 1 index_name | 14 day retention index_date_action | 1 -[ RECORD 2 ]-----+----------------- user_id | U001 action | comment min_count | 1 max_count | 5 action_count | 0 achieve | 0 index_name | 14 day retention index_date_action | 1 ...
次のステップ 最後に、上記コード例で求めたアクション度数別に、14日定着率を集計します。
WITH repeat_interval AS ( -- 略 ) , action_log_with_index_date AS ( -- 略 ) , user_action_flag AS ( -- 略 ) , mst_action_bucket AS ( -- 略 ) , mst_user_action_bucket AS ( -- 略 ) , register_action_flag AS ( -- 略 ) SELECT action -- ■ PostgreSQL, Redshiftの場合は下記で文字列を連結する , min_count || ' ~ ' || max_count AS count_range -- ■ BigQueryの場合は下記で文字列を連結する -- , CONCAT(CAST(min_count AS string), ' ~ ', CAST(max_count AS string)) -- AS count_range , SUM(CASE achieve WHEN 1 THEN 1 ELSE 0 END) AS achieve , index_name , AVG(CASE achieve WHEN 1 THEN 100.0 * index_date_action END) AS achieve_index_rate FROM register_action_flag GROUP BY index_name, action, min_count, max_count ORDER BY index_name, action, min_count;
action | count_range | achieve | index_name | achieve_index_rate ---------+-------------+---------+------------------+-------------------- comment | 0 ~ 0 | 24 | 14 day retention | 4.1666 comment | 1 ~ 5 | 3 | 14 day retention | 33.3333 comment | 6 ~ 10 | 0 | 14 day retention | comment | 11 ~ 9999 | 0 | 14 day retention | follow | 0 ~ 0 | 25 | 14 day retention | 4.0000 follow | 1 ~ 5 | 2 | 14 day retention | 50.0000 follow | 6 ~ 10 | 0 | 14 day retention | follow | 11 ~ 9999 | 0 | 14 day retention |
本項ではユーザーが行ったアクションに着目して集計していますが、受けたアクション別(いいね、コメント、フォローなど)で集計すると、より活性化させるべきアクションが見えてくるはずです。