マナティ

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

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

 ユーザーのサービス利用開始から日時が経過すると、ロイヤリティが高いユーザーに成長する、あるいは利用を中止して退会、登録は残っているものの再び利用することがない状態(休眠)になるなど、ユーザーの状態に変化が生じます。

・ユーザーが継続して利用する(リピート)
・ユーザーが利用を中止する(退会・休眠)

 サービスを運営する側としては、継続して利用してもらうことを願っています。そのためには、まずどの程度のユーザーが継続して利用しているのかを把握して、目標に対する乖離をどのように埋めるか検討する必要があります。また、しばらくサービスの利用がなかったり、休眠の傾向があるユーザーに対しては、継続してもらうための工夫を常に考えたいと思っているはずです。
 退会ユーザーに対する施策は実施が困難ですが、休眠ユーザーに対しては、メルマガやCM、広告などの施策で、利用を再開してもらいたいところです。

 ユーザーのサービス利用に対する時系列の変化を数値化し可視化することで、現状の把握、施策の効果、今後の計画に役立つ糸口となるレポートと集計するSQLを解説するChapter5-2「ユーザー全体の時系列による状態変化を見付ける」から、5-2-4「アクション回数に応じた定着率を集計する」を紹介します。

 本節では、下記に示す2つのテーブル、ユーザーマスタとアクションログを元に解説します。SNSに代表されるコミュニティサービスを想定してください。

●データ:ユーザーマスタ(mst_users)
6-0a.png

●データ:アクションログ(action_log)
6-0b.png

 FacebookやTwitterなどに代表されるSNSの事例で、「登録後1週間以内に10人をフォローしたらユーザーはサービスを継続的に利用する」ことを耳にしたことはないでしょうか。このような調査結果から、大きな方針を示すことができれば、「○○さんは友達ではないですか?」、「人気のユーザーをフォローしましょう」などを表示するモジュールを開発したり、チュートリアルに人気ユーザーをフォローする導線を設けるなど、サービスの活性化に繋がる具体的な施策を検討できます。
 本項では例として、下図に示す通り、登録日と登録日以降の7 日以内(7 日定着判定期間)に実行したアクション数に応じて、14 日定着率に変化があるかを集計します。

●図:今回のアクション集計日と定着率の判定期間

5-1.png

 本項では、下図に示す通り、各アクション別に度数分布表を作成し、達成者における14日定着率を 集計するクエリを紹介します。

●図:アクション回数に応じた定着率

5-2.png

 まず、上図に示したアクションの階級マスタを一時テーブルとして定義し、定着率を求めるためのユーザーアクションフラグとの組み合わせを、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
;

sankaku.png

 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句での絞り込みが必要となりますが割愛します。

●コード:登録後7日間のアクション度数を集計するクエリ
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
;

sankaku.png

-[ 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日定着率を集計します。

●コード:登録後7日間のアクション度数別に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;
    

sankaku.png

 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 |

 本項ではユーザーが行ったアクションに着目して集計していますが、受けたアクション別(いいね、コメント、フォローなど)で集計すると、より活性化させるべきアクションが見えてくるはずです。

著者プロフィール

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