ユーザーのタイプについて
ユーザーの属性にはデモグラフィック（年齢・性別・居住地域・職業等）やアプリ内行動から見える興味関心等幅広いものがあるが、今回はユーザーの訪問歴から新規・継続・休眠復帰とし、定義は下記とする。
| フラグ名 | 定義 | SQL上の定義 |
|---|---|---|
| 新規ユーザー | 当月に初回起動したユーザー | 当月にfirst_openイベントがある |
| 継続ユーザー | 前月起動＆当月起動をしたユーザー | 前月にfirst_openもしくはsession_startイベントがあり、当月もsessin_startがある |
| 休眠復帰ユーザー | 前々月以前起動＆前月起動なし＆当月起動したユーザー | 当月のsession_startイベントがあった全ユーザー集合から新規・継続集合を引いた差集合 |
今回やること
BigQueryのサンプルデータにあるパズルゲーム「Flood-It!」のGA4アクセスログデータセットを用いて、フラグ別のMAUとその翌月継続率を算出し、フラグによって継続率に差があるかを見てみる。
※過去に同データセットを用いて書いた記事
GA4のサンプルデータから成長指数を算出してみる
クエリ
サンプルデータの期間は2018/06/12 ~ 2018/10/03であり、継続ユーザーを出すには先月の起動歴が必要かつ、翌月継続率を出すには翌月のデータがいるため、2018/07/01 ~ 2018/09/31のデータを用いて、2018/8に絞った結果を算出する。
WITH tmp AS (
SELECT DISTINCT
DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS event_month
, event_name
, user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20180701' AND '20180931'
AND event_name IN ('session_start', 'first_open')
)
#全ユーザーログ
, mst_all_users AS (
SELECT DISTINCT
event_month
, user_pseudo_id
FROM tmp
)
#新規ユーザーログ
, mst_fitst_users AS (
SELECT DISTINCT
event_month
, user_pseudo_id
, 'new' AS flag
FROM tmp
WHERE event_name = 'first_open'
)
#継続ユーザーログ
, mst_continue_users AS (
SELECT DISTINCT
A.event_month
, A.user_pseudo_id
, 'continue' AS flag
FROM mst_all_users AS A #今月
INNER JOIN mst_all_users AS B #先月
ON A.user_pseudo_id = B.user_pseudo_id
AND DATE_DIFF(A.event_month, B.event_month, MONTH) = 1
)
#復帰ユーザーログ
, mst_return_users AS (
SELECT
event_month
, user_pseudo_id
, 'return' AS flag
FROM (
SELECT event_month, user_pseudo_id FROM mst_all_users
EXCEPT DISTINCT
SELECT event_month, user_pseudo_id FROM mst_fitst_users
EXCEPT DISTINCT
SELECT event_month, user_pseudo_id FROM mst_continue_users
)
)
#フラグ付き全ユーザー
, mst_union_users AS (
SELECT event_month, user_pseudo_id, flag FROM mst_fitst_users
UNION ALL
SELECT event_month, user_pseudo_id, flag FROM mst_continue_users
UNION ALL
SELECT event_month, user_pseudo_id, flag FROM mst_return_users
)
#翌月継続率ユーザーをJOIN
, mst_join_next_month AS (
SELECT
A.event_month
, A.flag
, A.user_pseudo_id
, 1 AS current_month_flag
, MAX(IF(B.user_pseudo_id IS NOT NULL, 1, 0)) AS next_month_flag
FROM mst_union_users AS A #当月
LEFT JOIN mst_all_users AS B #翌月
ON A.user_pseudo_id = B.user_pseudo_id
AND DATE_DIFF(B.event_month, A.event_month, MONTH) = 1
GROUP BY 1, 2, 3, 4
)
, mst_sum_uu AS (
SELECT
event_month
, flag
, SUM(current_month_flag) AS current_uu
, SUM(next_month_flag) AS next_uu
, SUM(next_month_flag) / SUM(current_month_flag) AS rr_1
FROM mst_join_next_month
GROUP BY 1, 2
)
SELECT
event_month
, MAX(IF(flag = 'new', current_uu, NULL)) AS new_uu
, MAX(IF(flag = 'continue', current_uu, NULL)) AS continue_uu
, MAX(IF(flag = 'return', current_uu, NULL)) AS return_uu
, MAX(IF(flag = 'new', next_uu, NULL)) AS new_next_uu
, MAX(IF(flag = 'continue', next_uu, NULL)) AS continue_next_uu
, MAX(IF(flag = 'return', next_uu, NULL)) AS return_next_uu
, MAX(IF(flag = 'new', rr_1, NULL)) AS new_rr_1
, MAX(IF(flag = 'continue', rr_1, NULL)) AS continue_rr_1
, MAX(IF(flag = 'return', rr_1, NULL)) AS return_rr_1
FROM mst_sum_uu
WHERE event_month = '2018-08-01'
GROUP BY 1
ORDER BY 1
アウトプット
| 新規MAU | 継続当月MAU | 休眠復帰当月MAU | 新規翌月MAU | 継続翌月MAU | 休眠復帰翌月MAU | 新規翌月継続率 | 継続翌月継続率 | 休眠復帰翌月継続率 |
|---|---|---|---|---|---|---|---|---|
| 1,298 | 1,523 | 1,237 | 191 | 791 | 293 | 14.71% | 52.23% | 23.69% |
ここから言えること
このアプリはMAUに占めるユーザーフラグがほぼ1/3ずつ分かれており、若干継続ユーザーが多い。
一方で継続ユーザーの翌月継続率が50%を超えており、他フラグを圧倒している。また、休眠復帰ユーザーの翌月継続率は新規ユーザーよりも9%ほど高い。
このことから、潜在的な休眠ユーザーのパイがあるならば、新規ユーザーを獲得するよりも、休眠ユーザーを復帰させた方が、翌月起動MAUという意味においては効果的であることが言える。