GA4のサンプルデータからMAUに占めるユーザータイプ別に指標を作ってみる

ユーザーのタイプについて

ユーザーの属性にはデモグラフィック（年齢・性別・居住地域・職業等）やアプリ内行動から見える興味関心等幅広いものがあるが、今回はユーザーの訪問歴から新規・継続・休眠復帰とし、定義は下記とする。

フラグ名	定義	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という意味においては効果的であることが言える。

さらにちょっと深堀してみた記事

GA4のサンプルデータからタイプ別＆定着度合い別に継続率を出してみる