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

◇エンゲージメントの定義

サービスの永続にはユーザの愛着心、すなわち、エンゲージメントを高めることが必要とされる。そのため、KPIの一つとして、エンゲージメントの推移を日々確認し、PDCAを回していくことが求められる。しかし、サービスの多様性からエンゲージメントの定義も様々あり、統一的な指標は存在しない。そこで、今回は、メディアの場合のエンゲージメントの指標について考える。結論から言うと、前週アクセス有ユーザの翌週アクセス率（実際のアクセス日数/7）、すなわち、週次ログイン密度の全体（前週アクセス有ユーザ数を母数とする）の平均値をエンゲージメントと定義する。理由は、1. 翌週も引き続き利用している日数が多いほど気に入っている可能性が高い、2. 前週アクセス無ユーザを含めないことで新規流入に伴う影響が排除される、3. ユーザ全体の傾向として週間単位で統一したアクセス行動が見られる（例えば土日にアクセスして平日はしない）ため曜日要因を排除することができるからだ。

◇エンゲージメントの計算方法

計算のイメージ

	翌週アクセス日数ｄ				週次の場合：m=7
前週アクセス有ユーザi	0	1	2	...	m
1		d=1
2	d=0
3					d=m
.
.
.
n			d=2

$Engagement = \displaystyle\frac{1}{n}\sum_{i=1}^{n}\frac{d_i}{m}$

◇エンゲージメントの推移（イメージ）

以前、【トレジャーデータ：Presto】ユーザの継続利用状況を可視化する（アクセス日数遷移） という記事で各ユーザの前週アクセス日数と翌週アクセス日数を集計することで簡易的に継続利用状況を可視化できることを示した。しかし、この前回の方法では、ユーザ全体の継続状況のトレンドを把握し辛い。一方、今回の方法では、エンゲージメントの時系列変化が分かるため、ユーザ全体のトレンドを容易に把握することができる。

◇エンゲージメントのクエリ分析例

--集計日から遡って7日間の内にアクセスした日数を集計
WITH sq_this_week AS(
  SELECT
    user_id,
    COUNT(DISTINCT TD_TIME_FORMAT(time,
        'yyyy-MM-dd',
        'jst')) AS this_days
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(time,
      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(time,
        'yyyy-MM-dd',
        'jst')) AS last_days
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(time,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-14d'),
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-7d'),
      'jst')
  GROUP BY
    user_id
),
sq_user_engagement 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,
    COALESCE(
      sq_this_week.this_days,
      0
    )/ 7.0 AS engagement_rate
  FROM (sq_last_week) LEFT
  JOIN (sq_this_week)
    ON (
      sq_last_week.user_id = sq_this_week.user_id
    )
  ORDER BY
    engagement_rate DESC
) SELECT
  SUM(engagement_rate)/ COUNT(1) AS total_engagement
FROM
  sq_user_engagement