【トレジャーデータ：Presto】ユーザの継続利用状況を可視化するⅠ（アクセス日数遷移）

◇継続利用可視化の重要性

ゲーム、メディア、ECサイト等を運営する上で重要なデータの一つがユーザの継続利用状況。この継続利用状況を可視化することで見えてくる課題は多い。例えば、継続利用の数値が悪ければ、新規獲得コストが高いこと、競合への乗り換えリスクが高いこと、サービスを長期に支えてくれるヘビーユーザ層が少ないこと、長期安定運営が困難なこと等々の課題がある可能性が高い。
 
以前、ゲーム買収の評価分析をしていた際、最も重要としていたのがユーザの継続利用のデータ。実際、この数値が悪ければポジティブな評価をすることはなかった。理由は、1．新規を獲得しても直ぐに離脱するためコストに対するリターンが少ない、2．増え続ける競合に乗り換えるユーザも日に日に増えていくため離脱に対する新規獲得が早い段階で追いつかなくなる、3．1と2よりビジネス的にサービスを長期的に継続することが困難であるからだ。そのため、買収評価の際は、利用状況によってユーザの継続率を予想するモデルを構築し、買収後2年先までの売上を維持するために最低限必要な月間の新規獲得UUをシミュレーションによって厳密に推定していた。
 
ただ、日々の継続利用を把握するために毎日モデルを構築してシミュレーションすることは現実的ではない。そこで、一度のクエリ構築で自動で現状把握できる分析が求められる。この分析の一つが、アクセス日数遷移。これは、各ユーザの当週（集計日から遡って7日間）と前週（当週に対する前週）のアクセス日数の変化を見ることで、ユーザの継続利用状況を可視化できる。また、前週と前々週の結果も合わせて比較することで、施策実施前後の数値から施策の効果を確認することもできる。

◇アクセス日数遷移の集計イメージ（上部が比率、下部がUU）

*数値は仮想のもの

最新		↓当週
前週→	last_week	離脱率	this_1	this_2	this_3	this_4	this_5	this_6	this_7
	1	93%	3%	3%	1%	0%	0%	0%	0%
	2	88%	9%	2%	1%	0%	0%	0%	0%
	3	63%	23%	10%	2%	0%	1%	0%	0%
	4	31%	32%	20%	9%	7%	1%	1%	0%
	5	14%	28%	26%	2%	15%	11%	3%	2%
	6	0%	22%	24%	10%	10%	14%	19%	2%
	7	14%	8%	5%	33%	16%	8%	13%	4%
	当週新規	0%	72%	25%	2%	0%	0%	0%	0%
UU										前週合計
	last_week	this_no_access	this_1	this_2	this_3	this_4	this_5	this_6	this_7	918
	1	700	22	22	7	1	0	0	0	752
	2	130	14	3	1	0	0	0	0	148
	3	6	2	1	0	0	0	0	0	10
	4	2	2	1	1	0	0	0	0	7
	5	0	0	0	0	0	0	0	0	1
	6	0	0	0	0	0	0	0	0	1
	7	0	0	0	0	0	0	0	0	0
	当週新規	0	250	88	8	1	0	0	0	347
	当週合計	838	291	115	17	3	1	0	0	427

◇クエリ例（上の集計イメージ下部のUUを集計する部分。比率は別途計算）

--アクセス日数遷移
--集計日から遡って7日間の内にアクセスした日数を集計
WITH sq_this_week AS(
  SELECT
    user_id,
    COUNT(DISTINCT TD_TIME_FORMAT(TIMESTAMP,
        'yyyy-MM-dd',
        'jst')) AS this_days
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(TIMESTAMP,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-7d'),
      TD_SCHEDULED_TIME(),
      'jst')
  GROUP BY
    user_id
),
sq_last_week AS(
  SELECT
    user_id,
    COUNT(DISTINCT TD_TIME_FORMAT(TIMESTAMP,
        'yyyy-MM-dd',
        'jst')) AS last_days
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(TIMESTAMP,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-14d'),
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-7d'),
      'jst')
  GROUP BY
    user_id
),
sq_days AS(
  SELECT
    sq_last_week.user_id AS last_user_id,
    sq_this_week.user_id AS this_user_id,
    -- nullであれば0とする
    COALESCE(
      sq_last_week.last_days,
      0
    ) AS last_days,
    COALESCE(
      sq_this_week.this_days,
      0
    ) AS this_days
  FROM (sq_last_week) FULL
  JOIN (sq_this_week)
    ON (
      sq_last_week.user_id = sq_this_week.user_id
    )
),
sq_uu AS(
  SELECT
    last_days,
    this_days,
    COUNT(DISTINCT CASE WHEN last_days > 0 THEN last_user_id WHEN this_days > 0 THEN this_user_id END) AS uu
  FROM
    sq_days
  GROUP BY
    last_days,
    this_days
) SELECT
  last_days,
  COALESCE(
    SUM(CASE
        WHEN this_days = 0 THEN uu END),
    0
  ) AS "0",
  COALESCE(
    SUM(CASE
        WHEN this_days = 1 THEN uu END),
    0
  ) AS "1",
  COALESCE(
    SUM(CASE
        WHEN this_days = 2 THEN uu END),
    0
  ) AS "2",
  COALESCE(
    SUM(CASE
        WHEN this_days = 3 THEN uu END),
    0
  ) AS "3",
  COALESCE(
    SUM(CASE
        WHEN this_days = 4 THEN uu END),
    0
  ) AS "4",
  COALESCE(
    SUM(CASE
        WHEN this_days = 5 THEN uu END),
    0
  ) AS "5",
  COALESCE(
    SUM(CASE
        WHEN this_days = 6 THEN uu END),
    0
  ) AS "6",
  COALESCE(
    SUM(CASE
        WHEN this_days = 7 THEN uu END),
    0
  ) AS "7"
FROM
  sq_uu
GROUP BY
  last_days
ORDER BY
  last_days

◇ネクストステップ

• 【トレジャーデータ：Presto】ユーザの継続利用状況を可視化するⅡ（エンゲージメント推移）