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サイト）では、利用頻度が異なります。成長指数を計算するために使用する指標の集計は、サービスの特性に応じて、日次や週次、月次などから適切な集計期間を選択しましょう。
