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

**※[前回の記事](https://qiita.com/NS007/items/a16f9cb254b0d9a48ace)からの続きです**

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

| フラグ名 | 定義 |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)
[GA4のサンプルデータからMAUに占めるユーザータイプ別に指標を作ってみる](https://qiita.com/NS007/items/a16f9cb254b0d9a48ace)


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

```sql
WITH tmp AS (
  SELECT DISTINCT
    PARSE_DATE('%Y%m%d', event_date) AS event_date
    , 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_action_days AS (
  SELECT
    event_month
    , user_pseudo_id
    , COUNT(DISTINCT event_date) AS action_days
  FROM tmp
  GROUP BY 1, 2
)
#全ユーザーログ
, 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
    , B.action_days
    , 1 AS current_month_flag
    , MAX(IF(C.user_pseudo_id IS NOT NULL, 1, 0)) AS next_month_flag
  FROM mst_union_users AS A #フラグ付き当月ログ
  INNER JOIN mst_action_days AS B #起動日数
    ON A.user_pseudo_id = B.user_pseudo_id
    AND A.event_month = B.event_month
  LEFT JOIN mst_all_users AS C #翌月ログ
    ON A.user_pseudo_id = C.user_pseudo_id
    AND DATE_DIFF(C.event_month, A.event_month, MONTH) = 1 #翌月-当月=1
  GROUP BY 1, 2, 3, 4, 5
)
, mst_sum_uu AS (
  SELECT
    event_month
    , flag
    , action_days
    , 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, 3
)
SELECT
  event_month
  , MAX(IF(flag = 'new' AND action_days = 1, current_uu, 0)) AS new_1_mau
  , MAX(IF(flag = 'new' AND action_days = 2, current_uu, 0)) AS new_2_mau
  , MAX(IF(flag = 'new' AND action_days = 3, current_uu, 0)) AS new_3_mau
  , MAX(IF(flag = 'new' AND action_days = 4, current_uu, 0)) AS new_4_mau
  , MAX(IF(flag = 'new' AND action_days = 5, current_uu, 0)) AS new_5_mau
  , MAX(IF(flag = 'new' AND action_days = 6, current_uu, 0)) AS new_6_mau
  , MAX(IF(flag = 'new' AND action_days = 7, current_uu, 0)) AS new_7_mau
  , MAX(IF(flag = 'new' AND action_days = 8, current_uu, 0)) AS new_8_mau
  , MAX(IF(flag = 'new' AND action_days = 9, current_uu, 0)) AS new_9_mau
  , MAX(IF(flag = 'new' AND action_days = 10, current_uu, 0)) AS new_10_mau
  , MAX(IF(flag = 'new' AND action_days = 11, current_uu, 0)) AS new_11_mau
  , MAX(IF(flag = 'new' AND action_days = 12, current_uu, 0)) AS new_12_mau
  , MAX(IF(flag = 'new' AND action_days = 13, current_uu, 0)) AS new_13_mau
  , MAX(IF(flag = 'new' AND action_days = 14, current_uu, 0)) AS new_14_mau
  , MAX(IF(flag = 'new' AND action_days = 15, current_uu, 0)) AS new_15_mau
  , MAX(IF(flag = 'new' AND action_days = 16, current_uu, 0)) AS new_16_mau
  , MAX(IF(flag = 'new' AND action_days = 17, current_uu, 0)) AS new_17_mau
  , MAX(IF(flag = 'new' AND action_days = 18, current_uu, 0)) AS new_18_mau
  , MAX(IF(flag = 'new' AND action_days = 19, current_uu, 0)) AS new_19_mau
  , MAX(IF(flag = 'new' AND action_days = 20, current_uu, 0)) AS new_20_mau
  , MAX(IF(flag = 'new' AND action_days = 21, current_uu, 0)) AS new_21_mau
  , MAX(IF(flag = 'new' AND action_days = 22, current_uu, 0)) AS new_22_mau
  , MAX(IF(flag = 'new' AND action_days = 23, current_uu, 0)) AS new_23_mau
  , MAX(IF(flag = 'new' AND action_days = 24, current_uu, 0)) AS new_24_mau
  , MAX(IF(flag = 'new' AND action_days = 25, current_uu, 0)) AS new_25_mau
  , MAX(IF(flag = 'new' AND action_days = 26, current_uu, 0)) AS new_26_mau
  , MAX(IF(flag = 'new' AND action_days = 27, current_uu, 0)) AS new_27_mau
  , MAX(IF(flag = 'new' AND action_days = 28, current_uu, 0)) AS new_28_mau
  , MAX(IF(flag = 'new' AND action_days = 29, current_uu, 0)) AS new_29_mau
  , MAX(IF(flag = 'new' AND action_days = 30, current_uu, 0)) AS new_30_mau
  , MAX(IF(flag = 'continue' AND action_days = 1, current_uu, 0)) AS continue_1_mau
  , MAX(IF(flag = 'continue' AND action_days = 2, current_uu, 0)) AS continue_2_mau
  , MAX(IF(flag = 'continue' AND action_days = 3, current_uu, 0)) AS continue_3_mau
  , MAX(IF(flag = 'continue' AND action_days = 4, current_uu, 0)) AS continue_4_mau
  , MAX(IF(flag = 'continue' AND action_days = 5, current_uu, 0)) AS continue_5_mau
  , MAX(IF(flag = 'continue' AND action_days = 6, current_uu, 0)) AS continue_6_mau
  , MAX(IF(flag = 'continue' AND action_days = 7, current_uu, 0)) AS continue_7_mau
  , MAX(IF(flag = 'continue' AND action_days = 8, current_uu, 0)) AS continue_8_mau
  , MAX(IF(flag = 'continue' AND action_days = 9, current_uu, 0)) AS continue_9_mau
  , MAX(IF(flag = 'continue' AND action_days = 10, current_uu, 0)) AS continue_10_mau
  , MAX(IF(flag = 'continue' AND action_days = 11, current_uu, 0)) AS continue_11_mau
  , MAX(IF(flag = 'continue' AND action_days = 12, current_uu, 0)) AS continue_12_mau
  , MAX(IF(flag = 'continue' AND action_days = 13, current_uu, 0)) AS continue_13_mau
  , MAX(IF(flag = 'continue' AND action_days = 14, current_uu, 0)) AS continue_14_mau
  , MAX(IF(flag = 'continue' AND action_days = 15, current_uu, 0)) AS continue_15_mau
  , MAX(IF(flag = 'continue' AND action_days = 16, current_uu, 0)) AS continue_16_mau
  , MAX(IF(flag = 'continue' AND action_days = 17, current_uu, 0)) AS continue_17_mau
  , MAX(IF(flag = 'continue' AND action_days = 18, current_uu, 0)) AS continue_18_mau
  , MAX(IF(flag = 'continue' AND action_days = 19, current_uu, 0)) AS continue_19_mau
  , MAX(IF(flag = 'continue' AND action_days = 20, current_uu, 0)) AS continue_20_mau
  , MAX(IF(flag = 'continue' AND action_days = 21, current_uu, 0)) AS continue_21_mau
  , MAX(IF(flag = 'continue' AND action_days = 22, current_uu, 0)) AS continue_22_mau
  , MAX(IF(flag = 'continue' AND action_days = 23, current_uu, 0)) AS continue_23_mau
  , MAX(IF(flag = 'continue' AND action_days = 24, current_uu, 0)) AS continue_24_mau
  , MAX(IF(flag = 'continue' AND action_days = 25, current_uu, 0)) AS continue_25_mau
  , MAX(IF(flag = 'continue' AND action_days = 26, current_uu, 0)) AS continue_26_mau
  , MAX(IF(flag = 'continue' AND action_days = 27, current_uu, 0)) AS continue_27_mau
  , MAX(IF(flag = 'continue' AND action_days = 28, current_uu, 0)) AS continue_28_mau
  , MAX(IF(flag = 'continue' AND action_days = 29, current_uu, 0)) AS continue_29_mau
  , MAX(IF(flag = 'continue' AND action_days = 30, current_uu, 0)) AS continue_30_mau
  , MAX(IF(flag = 'return' AND action_days = 1, current_uu, 0)) AS return_1_mau
  , MAX(IF(flag = 'return' AND action_days = 2, current_uu, 0)) AS return_2_mau
  , MAX(IF(flag = 'return' AND action_days = 3, current_uu, 0)) AS return_3_mau
  , MAX(IF(flag = 'return' AND action_days = 4, current_uu, 0)) AS return_4_mau
  , MAX(IF(flag = 'return' AND action_days = 5, current_uu, 0)) AS return_5_mau
  , MAX(IF(flag = 'return' AND action_days = 6, current_uu, 0)) AS return_6_mau
  , MAX(IF(flag = 'return' AND action_days = 7, current_uu, 0)) AS return_7_mau
  , MAX(IF(flag = 'return' AND action_days = 8, current_uu, 0)) AS return_8_mau
  , MAX(IF(flag = 'return' AND action_days = 9, current_uu, 0)) AS return_9_mau
  , MAX(IF(flag = 'return' AND action_days = 10, current_uu, 0)) AS return_10_mau
  , MAX(IF(flag = 'return' AND action_days = 11, current_uu, 0)) AS return_11_mau
  , MAX(IF(flag = 'return' AND action_days = 12, current_uu, 0)) AS return_12_mau
  , MAX(IF(flag = 'return' AND action_days = 13, current_uu, 0)) AS return_13_mau
  , MAX(IF(flag = 'return' AND action_days = 14, current_uu, 0)) AS return_14_mau
  , MAX(IF(flag = 'return' AND action_days = 15, current_uu, 0)) AS return_15_mau
  , MAX(IF(flag = 'return' AND action_days = 16, current_uu, 0)) AS return_16_mau
  , MAX(IF(flag = 'return' AND action_days = 17, current_uu, 0)) AS return_17_mau
  , MAX(IF(flag = 'return' AND action_days = 18, current_uu, 0)) AS return_18_mau
  , MAX(IF(flag = 'return' AND action_days = 19, current_uu, 0)) AS return_19_mau
  , MAX(IF(flag = 'return' AND action_days = 20, current_uu, 0)) AS return_20_mau
  , MAX(IF(flag = 'return' AND action_days = 21, current_uu, 0)) AS return_21_mau
  , MAX(IF(flag = 'return' AND action_days = 22, current_uu, 0)) AS return_22_mau
  , MAX(IF(flag = 'return' AND action_days = 23, current_uu, 0)) AS return_23_mau
  , MAX(IF(flag = 'return' AND action_days = 24, current_uu, 0)) AS return_24_mau
  , MAX(IF(flag = 'return' AND action_days = 25, current_uu, 0)) AS return_25_mau
  , MAX(IF(flag = 'return' AND action_days = 26, current_uu, 0)) AS return_26_mau
  , MAX(IF(flag = 'return' AND action_days = 27, current_uu, 0)) AS return_27_mau
  , MAX(IF(flag = 'return' AND action_days = 28, current_uu, 0)) AS return_28_mau
  , MAX(IF(flag = 'return' AND action_days = 29, current_uu, 0)) AS return_29_mau
  , MAX(IF(flag = 'return' AND action_days = 30, current_uu, 0)) AS return_30_mau
  , MAX(IF(flag = 'new' AND action_days = 1, rr_1, 0)) AS new_1_rr
  , MAX(IF(flag = 'new' AND action_days = 2, rr_1, 0)) AS new_2_rr
  , MAX(IF(flag = 'new' AND action_days = 3, rr_1, 0)) AS new_3_rr
  , MAX(IF(flag = 'new' AND action_days = 4, rr_1, 0)) AS new_4_rr
  , MAX(IF(flag = 'new' AND action_days = 5, rr_1, 0)) AS new_5_rr
  , MAX(IF(flag = 'new' AND action_days = 6, rr_1, 0)) AS new_6_rr
  , MAX(IF(flag = 'new' AND action_days = 7, rr_1, 0)) AS new_7_rr
  , MAX(IF(flag = 'new' AND action_days = 8, rr_1, 0)) AS new_8_rr
  , MAX(IF(flag = 'new' AND action_days = 9, rr_1, 0)) AS new_9_rr
  , MAX(IF(flag = 'new' AND action_days = 10, rr_1, 0)) AS new_10_rr
  , MAX(IF(flag = 'new' AND action_days = 11, rr_1, 0)) AS new_11_rr
  , MAX(IF(flag = 'new' AND action_days = 12, rr_1, 0)) AS new_12_rr
  , MAX(IF(flag = 'new' AND action_days = 13, rr_1, 0)) AS new_13_rr
  , MAX(IF(flag = 'new' AND action_days = 14, rr_1, 0)) AS new_14_rr
  , MAX(IF(flag = 'new' AND action_days = 15, rr_1, 0)) AS new_15_rr
  , MAX(IF(flag = 'new' AND action_days = 16, rr_1, 0)) AS new_16_rr
  , MAX(IF(flag = 'new' AND action_days = 17, rr_1, 0)) AS new_17_rr
  , MAX(IF(flag = 'new' AND action_days = 18, rr_1, 0)) AS new_18_rr
  , MAX(IF(flag = 'new' AND action_days = 19, rr_1, 0)) AS new_19_rr
  , MAX(IF(flag = 'new' AND action_days = 20, rr_1, 0)) AS new_20_rr
  , MAX(IF(flag = 'new' AND action_days = 21, rr_1, 0)) AS new_21_rr
  , MAX(IF(flag = 'new' AND action_days = 22, rr_1, 0)) AS new_22_rr
  , MAX(IF(flag = 'new' AND action_days = 23, rr_1, 0)) AS new_23_rr
  , MAX(IF(flag = 'new' AND action_days = 24, rr_1, 0)) AS new_24_rr
  , MAX(IF(flag = 'new' AND action_days = 25, rr_1, 0)) AS new_25_rr
  , MAX(IF(flag = 'new' AND action_days = 26, rr_1, 0)) AS new_26_rr
  , MAX(IF(flag = 'new' AND action_days = 27, rr_1, 0)) AS new_27_rr
  , MAX(IF(flag = 'new' AND action_days = 28, rr_1, 0)) AS new_28_rr
  , MAX(IF(flag = 'new' AND action_days = 29, rr_1, 0)) AS new_29_rr
  , MAX(IF(flag = 'new' AND action_days = 30, rr_1, 0)) AS new_30_rr
  , MAX(IF(flag = 'continue' AND action_days = 1, rr_1, 0)) AS continue_1_rr
  , MAX(IF(flag = 'continue' AND action_days = 2, rr_1, 0)) AS continue_2_rr
  , MAX(IF(flag = 'continue' AND action_days = 3, rr_1, 0)) AS continue_3_rr
  , MAX(IF(flag = 'continue' AND action_days = 4, rr_1, 0)) AS continue_4_rr
  , MAX(IF(flag = 'continue' AND action_days = 5, rr_1, 0)) AS continue_5_rr
  , MAX(IF(flag = 'continue' AND action_days = 6, rr_1, 0)) AS continue_6_rr
  , MAX(IF(flag = 'continue' AND action_days = 7, rr_1, 0)) AS continue_7_rr
  , MAX(IF(flag = 'continue' AND action_days = 8, rr_1, 0)) AS continue_8_rr
  , MAX(IF(flag = 'continue' AND action_days = 9, rr_1, 0)) AS continue_9_rr
  , MAX(IF(flag = 'continue' AND action_days = 10, rr_1, 0)) AS continue_10_rr
  , MAX(IF(flag = 'continue' AND action_days = 11, rr_1, 0)) AS continue_11_rr
  , MAX(IF(flag = 'continue' AND action_days = 12, rr_1, 0)) AS continue_12_rr
  , MAX(IF(flag = 'continue' AND action_days = 13, rr_1, 0)) AS continue_13_rr
  , MAX(IF(flag = 'continue' AND action_days = 14, rr_1, 0)) AS continue_14_rr
  , MAX(IF(flag = 'continue' AND action_days = 15, rr_1, 0)) AS continue_15_rr
  , MAX(IF(flag = 'continue' AND action_days = 16, rr_1, 0)) AS continue_16_rr
  , MAX(IF(flag = 'continue' AND action_days = 17, rr_1, 0)) AS continue_17_rr
  , MAX(IF(flag = 'continue' AND action_days = 18, rr_1, 0)) AS continue_18_rr
  , MAX(IF(flag = 'continue' AND action_days = 19, rr_1, 0)) AS continue_19_rr
  , MAX(IF(flag = 'continue' AND action_days = 20, rr_1, 0)) AS continue_20_rr
  , MAX(IF(flag = 'continue' AND action_days = 21, rr_1, 0)) AS continue_21_rr
  , MAX(IF(flag = 'continue' AND action_days = 22, rr_1, 0)) AS continue_22_rr
  , MAX(IF(flag = 'continue' AND action_days = 23, rr_1, 0)) AS continue_23_rr
  , MAX(IF(flag = 'continue' AND action_days = 24, rr_1, 0)) AS continue_24_rr
  , MAX(IF(flag = 'continue' AND action_days = 25, rr_1, 0)) AS continue_25_rr
  , MAX(IF(flag = 'continue' AND action_days = 26, rr_1, 0)) AS continue_26_rr
  , MAX(IF(flag = 'continue' AND action_days = 27, rr_1, 0)) AS continue_27_rr
  , MAX(IF(flag = 'continue' AND action_days = 28, rr_1, 0)) AS continue_28_rr
  , MAX(IF(flag = 'continue' AND action_days = 29, rr_1, 0)) AS continue_29_rr
  , MAX(IF(flag = 'continue' AND action_days = 30, rr_1, 0)) AS continue_30_rr
  , MAX(IF(flag = 'return' AND action_days = 1, rr_1, 0)) AS return_1_rr
  , MAX(IF(flag = 'return' AND action_days = 2, rr_1, 0)) AS return_2_rr
  , MAX(IF(flag = 'return' AND action_days = 3, rr_1, 0)) AS return_3_rr
  , MAX(IF(flag = 'return' AND action_days = 4, rr_1, 0)) AS return_4_rr
  , MAX(IF(flag = 'return' AND action_days = 5, rr_1, 0)) AS return_5_rr
  , MAX(IF(flag = 'return' AND action_days = 6, rr_1, 0)) AS return_6_rr
  , MAX(IF(flag = 'return' AND action_days = 7, rr_1, 0)) AS return_7_rr
  , MAX(IF(flag = 'return' AND action_days = 8, rr_1, 0)) AS return_8_rr
  , MAX(IF(flag = 'return' AND action_days = 9, rr_1, 0)) AS return_9_rr
  , MAX(IF(flag = 'return' AND action_days = 10, rr_1, 0)) AS return_10_rr
  , MAX(IF(flag = 'return' AND action_days = 11, rr_1, 0)) AS return_11_rr
  , MAX(IF(flag = 'return' AND action_days = 12, rr_1, 0)) AS return_12_rr
  , MAX(IF(flag = 'return' AND action_days = 13, rr_1, 0)) AS return_13_rr
  , MAX(IF(flag = 'return' AND action_days = 14, rr_1, 0)) AS return_14_rr
  , MAX(IF(flag = 'return' AND action_days = 15, rr_1, 0)) AS return_15_rr
  , MAX(IF(flag = 'return' AND action_days = 16, rr_1, 0)) AS return_16_rr
  , MAX(IF(flag = 'return' AND action_days = 17, rr_1, 0)) AS return_17_rr
  , MAX(IF(flag = 'return' AND action_days = 18, rr_1, 0)) AS return_18_rr
  , MAX(IF(flag = 'return' AND action_days = 19, rr_1, 0)) AS return_19_rr
  , MAX(IF(flag = 'return' AND action_days = 20, rr_1, 0)) AS return_20_rr
  , MAX(IF(flag = 'return' AND action_days = 21, rr_1, 0)) AS return_21_rr
  , MAX(IF(flag = 'return' AND action_days = 22, rr_1, 0)) AS return_22_rr
  , MAX(IF(flag = 'return' AND action_days = 23, rr_1, 0)) AS return_23_rr
  , MAX(IF(flag = 'return' AND action_days = 24, rr_1, 0)) AS return_24_rr
  , MAX(IF(flag = 'return' AND action_days = 25, rr_1, 0)) AS return_25_rr
  , MAX(IF(flag = 'return' AND action_days = 26, rr_1, 0)) AS return_26_rr
  , MAX(IF(flag = 'return' AND action_days = 27, rr_1, 0)) AS return_27_rr
  , MAX(IF(flag = 'return' AND action_days = 28, rr_1, 0)) AS return_28_rr
  , MAX(IF(flag = 'return' AND action_days = 29, rr_1, 0)) AS return_29_rr
  , MAX(IF(flag = 'return' AND action_days = 30, rr_1, 0)) AS return_30_rr
FROM mst_sum_uu
WHERE event_month = '2018-08-01'
GROUP BY 1
ORDER BY 1
```
</div></details>

## クエリを書く際のちょっとした工夫
SQLをみてもらえれば分かると思いますが、とにかく最後は書きたくないです。
たぶんpivot関数とか使えばうまくかけるのかと思いますが、正直にそのまま記載しています。
ここでは覚えたてほやほやのPythonを使ってSQLを記述し簡単なDXをしてみました。
```python
def sql_text(t, u, i):
    for i in range(1, 31):
        print(f', MAX(IF(flag = \'{t}\' AND action_days = {i}, {u}, 0)) AS {t}_{i}_mau')

sql_text("new", "current_uu", 1)
sql_text("continue", "current_uu", 1)
sql_text("return", "current_uu", 1)
```


## アウトプット
![起動日数別フラグ別当月MAU.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/a7fd2642-bc4a-e65c-df6f-fe3e219c1bd6.png)
![起動日数別フラグ別翌月継続率.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/91214f1e-6d4c-ec35-c9a6-fe7d5e62361a.png)
![1日分起動日数を上げた時の翌月継続率の変化量.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/936531ab-e1e2-a260-c465-92ced787e6a3.png)


## ここから言えること
「起動日数別フラグ別当月MAU」から起動日数が増えるたびにユーザー数が逓減していくのが見てとれます。1回の起動で離脱していっていると考えられます。

「起動日数別フラグ別翌月継続率」は、とにかくこのデータではユーザー数が少なく、起動日数も少ないため、10日以降ぐらいからは無視してもらえればよいですが、起動日数が増える＝定着するほど翌月も継続することが分かります。（ユーザー数が増えると綺麗な弧を描いてくれるのですが、、、）

また、3つ目のグラフは起動日数が1日増えた時に翌月継続率がどれ程上昇するかになります。1日起動→2日起動に増えると、継続ユーザーで16%ほど翌月継続します。また、2日起動→3日起動をすると、5%ほど上昇します。一般的にこの上昇幅は起動日数が増えるほど、徐々に減少してきます。

つまり、どのフラグにおいても、最もユーザーがいる月1起動ユーザーを何とか2日起動に持ち込むことで、定着度合いが上がり、翌月継続率が大きく上がることが期待されると考えられます。