マナティ

ビッグデータ分析・活用のためのSQLレシピ

第6回 訪問種別を定義して成長指数を集計する

第5回「アクション回数に応じた定着率を集計する」に引き続き、今回もユーザーのサービス利用に対する時系列の変化を数値化し可視化することで、現状の把握、施策の効果、今後の計画に役立つ糸口となるレポートと集計するSQLを解説します。

第5回「アクション回数に応じた定着率を集計する」を読む

 サービス運営では、ユーザー登録をはじめ、ユーザーの継続利用やリテンションのための施策を担当し、サービス成長を加速させることを担当するチームがあります。ユーザー獲得チームまたはグロースハックチームなどと呼ばれます。本項では、サービスの成長を指標化したり、グロースハックチームの成果を指標化する方法の1 つ、成長指数と呼ばれる指標を紹介します。

成長指数

 成長指数はユーザーのサービス利用に関する状態変化を数値化して、サービスが成長しているかを示す指標です。この成長指数が1以上の時はサービスが成長している状態を示し、0を下回るとサービスが衰退している状態を意味します。

●図:成長指数の推移

6-1.png

 下表に、サービスの成長指数を算出するために必要となる、サービス利用に関する状態変化のパターンを示します。

●表:サービス利用に関する状態変化のパターン

6-2.png

 ユーザーのサービス登録からサービス退会までの利用状況から、成長指数を算出するため、どのように各指標を判定するかを下表に示します。

●表:ユーザーの利用と成長指数を算出する際に用いる状態変化の判定

6-3.png

10/1に登録したので「signup」と判定します。
10/2は利用していないので、アクティブユーザーから非アクティブになったと言うことで「Deactivation」と判定します。
10/3は引き続き利用しなかったので、状態変化がないので、どの指標にも分類されません。
10/4は利用があったので、非アクティブからアクティブユーザーへ状態変化したことで、「Reactivation」と判定します。
10/5は利用していないので、アクティブユーザーから非アクティブになったと言うことで「Deactivation」と判定します。
10/6は利用していますが、この日に退会したことによって、「Reactivation」ではなく「Exit」と判定します。

 上記の判定結果を用いて、成長指数を下記の通りに計算します。

6-4.png

 「利用するようになったユーザー」と「去っていったユーザー」を集計して、どちらのユーザーが多いのか数値化するものが成長指数です。利用を継続しているユーザーやずっと利用していないユーザー、当日に登録して当日に退会したユーザーは、成長指数の増減には影響しません。この成長指数を集計して、その推移を記録したものが冒頭に紹介した、前図です。

 この通り、サービスの成長指数をシンプルに定義することで、マーケティング以外の担当者も理解可能になります。その結果、成長指数を改善するには、下記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
;

sankaku.png

 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
;

sankaku.png

 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は負の数としてカウントして、これらの値を合計したものが成長指数となります。

サービスローンチ時からの成長指数の推移

 サービスの成長指数がローンチ時からどのように推移するのか、執筆陣の経験を元にまとめます。また、成長指数を向上させるために考えるべきポイントを紹介します。

●図:成長指数の推移におけるユーザー獲得チームの成果

6-5.png

 サービスのローンチ直後は、事前に計画していたプレスリリースや集客施策に加え、新サービスを紹介するメディアに掲載されることなどによって、登録数が一気に伸びます(図①のタイミング)。しかし、しばらくすると8割ぐらいが非アクティブなユーザーとなるでしょう。

 ローンチ直後の飛躍的な成長が一段落した時点で、どのような方法でどのようなユーザー層を獲得できるのか、そして、どのようにすれば継続利用を確保できるかを検討します(図②~③のタイミング)。
 例えば、ローンチのタイミングに合わせて仕込んだメディアによるユーザー獲得などは、容易に考え付きますが、あくまでも一時的なカンフル剤の役目しか果たせず、成長を継続させるものではないケースが多々あります。
 メディアによるユーザー獲得ではなく、サービス(プロダクト)を通じて、ユーザーを獲得し継続して利用してもらうことを重視することで、安定かつ継続した成長が見込めるようになります。継続的に施策を検討し、成長指数を成長させることが、チームにおける目標であると同時に成果となります。

ワンポイント
 日常的な利用を期待するサービス(ニュースサイトやソーシャルゲームなど)、何らかの目的が発生した際に利用を期待するサービス(飲食店検索サービスやECサイト)では、利用頻度が異なります。成長指数を計算するために使用する指標の集計は、サービスの特性に応じて、日次や週次、月次などから適切な集計期間を選択しましょう。

著者プロフィール

加嵜 長門(著者)
株式会社DMM.comラボ所属。慶應義塾大学大学院 政策・メディア研究科修士課程修了。大学院や学生ベンチャーにて、マルチメディアデータベースを対象とした検索やレコメンドアルゴリズムの研究およびサービス開発に従事し、現在DMM.comラボではビッグデータ活用基盤の構築に携わり、SparkやSQL on Hadoopを用いたレコメンド機能、ビッグデータ活用の研究開発を担当。 共著に『詳解Apache Spark』(技術評論社)。
田宮 直人(著者)
データコンサルタント。エンジニアとして大手新聞社の関連サービス、求人サービス、コミュニティサービスの開発に携わり、株式会社サイバーエージェント在籍時にデータアナリストへ転身、株式会社DMM.comラボではマーケティング開発部マネージャーとしてビッグデータ部を立ち上げる。現在はフリーランスとして、データの解析のみならず、データ解析環境の設計・構築、ログの設計、レコメンドAPIの作成など、データに関連する業務全般を担当している。