2017.05.18
第6回 訪問種別を定義して成長指数を集計する
第5回「アクション回数に応じた定着率を集計する」に引き続き、今回もユーザーのサービス利用に対する時系列の変化を数値化し可視化することで、現状の把握、施策の効果、今後の計画に役立つ糸口となるレポートと集計するSQLを解説します。
→第5回「アクション回数に応じた定着率を集計する」を読む
サービス運営では、ユーザー登録をはじめ、ユーザーの継続利用やリテンションのための施策を担当し、サービス成長を加速させることを担当するチームがあります。ユーザー獲得チームまたはグロースハックチームなどと呼ばれます。本項では、サービスの成長を指標化したり、グロースハックチームの成果を指標化する方法の1 つ、成長指数と呼ばれる指標を紹介します。
成長指数
成長指数はユーザーのサービス利用に関する状態変化を数値化して、サービスが成長しているかを示す指標です。この成長指数が1以上の時はサービスが成長している状態を示し、0を下回るとサービスが衰退している状態を意味します。
下表に、サービスの成長指数を算出するために必要となる、サービス利用に関する状態変化のパターンを示します。
ユーザーのサービス登録からサービス退会までの利用状況から、成長指数を算出するため、どのように各指標を判定するかを下表に示します。
10/1に登録したので「signup」と判定します。
10/2は利用していないので、アクティブユーザーから非アクティブになったと言うことで「Deactivation」と判定します。
10/3は引き続き利用しなかったので、状態変化がないので、どの指標にも分類されません。
10/4は利用があったので、非アクティブからアクティブユーザーへ状態変化したことで、「Reactivation」と判定します。
10/5は利用していないので、アクティブユーザーから非アクティブになったと言うことで「Deactivation」と判定します。
10/6は利用していますが、この日に退会したことによって、「Reactivation」ではなく「Exit」と判定します。
上記の判定結果を用いて、成長指数を下記の通りに計算します。
「利用するようになったユーザー」と「去っていったユーザー」を集計して、どちらのユーザーが多いのか数値化するものが成長指数です。利用を継続しているユーザーやずっと利用していないユーザー、当日に登録して当日に退会したユーザーは、成長指数の増減には影響しません。この成長指数を集計して、その推移を記録したものが冒頭に紹介した、前図です。
この通り、サービスの成長指数をシンプルに定義することで、マーケティング以外の担当者も理解可能になります。その結果、成長指数を改善するには、下記2点に注力すれば良いと考えるようになります。
1 SignupとReactivationを増やすことを考える。
2 Deactivationを下げることを考える。
成長指数を集計する
成長指数を算出する目的のユーザー状態として、新規登録したか(is_new)、退会したか(is_exit)、その日サービスにアクセスしたか(is_access)、前日にサービスにアクセスしたか(was_access)を、日別に判定するクエリを、下記のコード例に示します。
WITH unique_action_log AS ( -- 同じ日付のログを2重にカウントしないよう、アクセスログから日付の重複を排除する SELECT DISTINCT user_id , substring(stamp, 1, 10) AS action_date -- ■ BigQueryの場合、下記を用いる -- , substr(stamp, 1, 10) AS action_date FROM action_log ) , mst_calendar AS ( -- 集計したい期間のカレンダーテーブルを用意する -- generate_series等で動的に作成も可能 SELECT '2016-10-01' AS dt UNION ALL SELECT '2016-10-02' AS dt UNION ALL SELECT '2016-10-03' AS dt -- 省略 UNION ALL SELECT '2016-11-04' AS dt ) , target_date_with_user AS ( -- ユーザーマスタに対して、カレンダーテーブルの全日付をtarget_dateとして付与する SELECT c.dt AS target_date , u.user_id , u.register_date , u.withdraw_date FROM mst_users AS u CROSS JOIN mst_calendar AS c ) , user_status_log AS ( SELECT u.target_date , u.user_id , u.register_date , u.withdraw_date , a.action_date , CASE WHEN u.register_date = a.action_date THEN 1 ELSE 0 END AS is_new , CASE WHEN u.withdraw_date = a.action_date THEN 1 ELSE 0 END AS is_exit , CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END AS is_access , LAG(CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END) OVER( PARTITION BY u.user_id ORDER BY u.target_date -- ■ SparkSQLの場合、下記フレーム指定を追記する -- ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS was_access FROM target_date_with_user AS u LEFT JOIN unique_action_log AS a ON u.user_id = a.user_id AND u.target_date = a.action_date WHERE -- 集計期間を登録日以降の日付に絞り込む u.register_date <= u.target_date -- 退会日が入っている場合は、集計期間を退会日以前の日付に絞り込む AND ( u.withdraw_date IS NULL OR u.target_date <= u.withdraw_date ) ) SELECT target_date , user_id , is_new , is_exit , is_access , was_access FROM user_status_log ;
target_date | user_id | is_new | is_exit | is_access | was_access -------------+---------+--------+---------+-----------+------------ 2016-10-01 | U001 | 1 | 0 | 1 | 2016-10-02 | U001 | 0 | 0 | 1 | 1 2016-10-03 | U001 | 0 | 0 | 1 | 1 2016-10-04 | U001 | 0 | 0 | 1 | 1 2016-10-05 | U001 | 0 | 0 | 0 | 1 2016-10-06 | U001 | 0 | 0 | 0 | 0 ... 2016-10-01 | U002 | 1 | 0 | 1 | 2016-10-02 | U002 | 0 | 0 | 0 | 1
次のステップ 日別のユーザー状態が判定できたら、成長指数を計算するためのパターンを計算し、最終的な成長指数を計算します。下記コード例に、signupとreactivation、deactivation、exit、growth indexの各値を日次で計算するクエリを示します。
WITH unique_action_log AS ( -- 略 ) , mst_calendar AS ( -- 略 ) , target_date_with_user AS ( -- 略 ) , user_status_log AS ( -- 略 ) , user_growth_index AS ( SELECT * , CASE -- ある日付に新規登録または退会している場合は、signupまたはexitと判定する WHEN is_new + is_exit = 1 THEN CASE WHEN is_new = 1 THEN 'signup' WHEN is_exit = 1 THEN 'exit' END -- 新規登録も退会もしていない場合は、reactivationまたはdeactivationと判定する -- ただし、reactivation, deactivationの定義に当てはまらない場合はNULLとする WHEN is_new + is_exit = 0 THEN CASE WHEN was_access = 0 AND is_access = 1 THEN 'reactivation' WHEN was_access = 1 AND is_access = 0 THEN 'deactivation' END -- ある日付に新規登録と退会を同時に行っている(is_new + is_exit = 2)場合はNULLとする END AS growth_index FROM user_status_log ) SELECT target_date , SUM(CASE growth_index WHEN 'signup' THEN 1 ELSE 0 END) AS signup , SUM(CASE growth_index WHEN 'reactivation' THEN 1 ELSE 0 END) AS reactivation , SUM(CASE growth_index WHEN 'deactivation' THEN -1 ELSE 0 END) AS deactivation , SUM(CASE growth_index WHEN 'exit' THEN -1 ELSE 0 END) AS exit -- 成長指数の定義に基づき値を集計する , SUM( CASE growth_index WHEN 'signup' THEN 1 WHEN 'reactivation' THEN 1 WHEN 'deactivation' THEN -1 WHEN 'exit' THEN -1 ELSE 0 END ) AS growth_index FROM user_growth_index GROUP BY target_date ORDER BY target_date ;
target_date | signup | reactivation | deactivation | exit | growth_index -------------+--------+--------------+--------------+------+-------------- 2016-10-01 | 234 | 0 | 0 | 0 | 234 2016-10-02 | 346 | 12 | -123 | -3 | 232 2016-10-03 | 568 | 13 | -165 | -2 | 414 2016-10-04 | 836 | 32 | -489 | -3 | 376 2016-10-05 | 932 | 25 | -742 | -5 | 210 2016-10-06 | 722 | 42 | -976 | -12 | -224 2016-10-07 | 675 | 37 | -568 | -23 | 121 2016-10-08 | 462 | 24 | -254 | -45 | 187 2016-10-09 | 572 | 57 | -432 | -54 | 143 2016-10-10 | 468 | 63 | -269 | -62 | 200 2016-10-11 | 346 | 24 | -178 | -45 | 147 2016-10-12 | 435 | 37 | -156 | -42 | 274 2016-10-13 | 348 | 39 | -218 | -74 | 95 2016-10-14 | 435 | 26 | -146 | -52 | 263 2016-10-15 | 386 | 28 | -287 | -59 | 68 2016-10-16 | 538 | 31 | -143 | -61 | 365 2016-10-17 | 476 | 28 | -287 | -45 | 172
上記のコード例では、ユーザー状態から、signup、reactivation、deactivation、exitを判定して、日付ごとに各ユーザー数をカウントしています。ユーザー数をカウントする際、signupとreactivationは正の数、deactivationとexitは負の数としてカウントして、これらの値を合計したものが成長指数となります。
サービスローンチ時からの成長指数の推移
サービスの成長指数がローンチ時からどのように推移するのか、執筆陣の経験を元にまとめます。また、成長指数を向上させるために考えるべきポイントを紹介します。
サービスのローンチ直後は、事前に計画していたプレスリリースや集客施策に加え、新サービスを紹介するメディアに掲載されることなどによって、登録数が一気に伸びます(図①のタイミング)。しかし、しばらくすると8割ぐらいが非アクティブなユーザーとなるでしょう。
ローンチ直後の飛躍的な成長が一段落した時点で、どのような方法でどのようなユーザー層を獲得できるのか、そして、どのようにすれば継続利用を確保できるかを検討します(図②~③のタイミング)。
例えば、ローンチのタイミングに合わせて仕込んだメディアによるユーザー獲得などは、容易に考え付きますが、あくまでも一時的なカンフル剤の役目しか果たせず、成長を継続させるものではないケースが多々あります。
メディアによるユーザー獲得ではなく、サービス(プロダクト)を通じて、ユーザーを獲得し継続して利用してもらうことを重視することで、安定かつ継続した成長が見込めるようになります。継続的に施策を検討し、成長指数を成長させることが、チームにおける目標であると同時に成果となります。
ワンポイント
日常的な利用を期待するサービス(ニュースサイトやソーシャルゲームなど)、何らかの目的が発生した際に利用を期待するサービス(飲食店検索サービスやECサイト)では、利用頻度が異なります。成長指数を計算するために使用する指標の集計は、サービスの特性に応じて、日次や週次、月次などから適切な集計期間を選択しましょう。