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

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



| フラグ名 | 定義 |SQL上の定義|
|:-----------|:------------|:------------|
|新規ユーザー|当月に初回起動したユーザー |当月にfirst_openイベントがある|
|継続ユーザー|前月起動＆当月起動をしたユーザー|前月にfirst_openもしくはsession_startイベントがあり、当月もsessin_startがある|
|休眠復帰ユーザー|前々月以前起動＆前月起動なし＆当月起動したユーザー|当月のsession_startイベントがあった全ユーザー集合から新規・継続集合を引いた差集合|


## 今回やること
BigQueryのサンプルデータにある[パズルゲーム「Flood-It!」のGA4アクセスログデータセット](https://developers.google.com/analytics/bigquery/app-gaming-demo-dataset)を用いて、フラグ別のMAUとその翌月継続率を算出し、フラグによって継続率に差があるかを見てみる。

※過去に同データセットを用いて書いた記事
[GA4のサンプルデータから成長指数を算出してみる](https://qiita.com/NS007/items/33a7a80aedf16d7fabe3)


## クエリ
サンプルデータの期間は2018/06/12 ~ 2018/10/03であり、継続ユーザーを出すには先月の起動歴が必要かつ、翌月継続率を出すには翌月のデータがいるため、2018/07/01 ~ 2018/09/31のデータを用いて、2018/8に絞った結果を算出する。

```sql
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に占めるフラグ別MAU.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/387b3202-a901-f225-aac4-26fcddfe4db4.png)
![フラグ別翌月継続率.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/4ed96fec-4021-b92f-eb9c-d9acbbce3718.png)


## ここから言えること
このアプリはMAUに占めるユーザーフラグがほぼ1/3ずつ分かれており、若干継続ユーザーが多い。
一方で継続ユーザーの翌月継続率が50%を超えており、他フラグを圧倒している。また、休眠復帰ユーザーの翌月継続率は新規ユーザーよりも9%ほど高い。
このことから、潜在的な休眠ユーザーのパイがあるならば、新規ユーザーを獲得するよりも、休眠ユーザーを復帰させた方が、翌月起動MAUという意味においては効果的であることが言える。


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

[GA4のサンプルデータからタイプ別＆定着度合い別に継続率を出してみる](https://qiita.com/NS007/items/b2c75f4ce27b64dbd89c)