GA4のサンプルデータから成長指数を算出してみる

## 成長指数とは？
成長指数はサービスの成長度合いを数値化して、サービスが成長しているかを表す指標です。この成長指数が1以上の時はサービスが成長していることを示し、0以下の時は停滞または衰退をしていることを示します。
（SQL書籍 [ビッグデータ分析・活用のためのSQLレシピ](https://book.mynavi.jp/ec/products/detail/id=65863) にて紹介されています）


この成長指数は以下の変数を用いて算出します。

| 変数 | 状態 |ポイント（フラグ）|
|:-----------|:------------|:------------:|
|Signup| 新規登録をした |1|
|Deactivation|アクティブユーザーが非アクティブユーザーになった|-1|
|Reactivation|非アクティブユーザーがアクティブユーザーになった|1|
|Exit|退会し利用を停止した|-1|

※上記紹介書籍にて記載

## 今回やること
BigQueryのサンプルデータにある[パズルゲーム「Flood-It!」のGA4アクセスログデータセット](https://developers.google.com/analytics/bigquery/app-gaming-demo-dataset)を用いて、成長指標（Growth Index）を可視化する。
なお、Signupは「first_open」イベント、Exitは「app_remove」イベントを使用した。また、日次で成長指数を計算する。（サービスの特徴によって、週次・月次での算出も良いかと思う）
※「app_remove」はAndroid限定で使用できるイベントであるが、今回はそれを無視する。

## クエリ
デモデータセットにあった114日間の全ての日付テーブルを用いた。
```sql
WITH tmp AS (
    SELECT DISTINCT
       PARSE_DATE('%Y%m%d', event_date) AS event_date
       , event_name
       , user_pseudo_id AS user_id
       , DENSE_RANK() OVER (
            ORDER BY event_date
       ) AS day_number
    FROM `firebase-public-project.analytics_153293282.events_*`
    WHERE event_name IN ('first_open', 'app_remove', 'session_start')
)
, mst_base AS (
    SELECT 
        a.event_date
        , b.user_id
    FROM tmp AS a
    CROSS JOIN tmp AS b
)
, mst_first_open AS (
    SELECT
        event_date
        , user_id
    FROM tmp
    WHERE event_name = 'first_open'
)
, mst_app_remove AS (
    SELECT
        event_date
        , user_id
    FROM tmp
    WHERE event_name = 'app_remove'
)
, mst_session AS (
    SELECT
        event_date
        , user_id
        , day_number
    FROM tmp
    WHERE event_name = 'session_start'
)
, mst_status AS (
    SELECT
        a.event_date
        , a.user_id
        , MAX(IF(b.user_id IS NOT NULL, 1, 0)) AS is_new
        , MAX(IF(c.user_id IS NOT NULL, 1, 0)) AS is_exit
        , MAX(IF(d.user_id IS NOT NULL, 1, 0)) AS is_access
        , MAX(IF(e.user_id IS NOT NULL, 1, 0)) AS was_access
    FROM mst_base AS a
    LEFT JOIN mst_first_open AS b
        ON a.event_date = b.event_date
        AND a.user_id = b.user_id
    LEFT JOIN mst_app_remove AS c
        ON a.event_date = c.event_date
        AND a.user_id = c.user_id
    LEFT JOIN mst_session AS d
        ON a.event_date = d.event_date
        AND a.user_id = d.user_id
    LEFT JOIN mst_session AS e
        ON a.event_date = DATE_ADD(e.event_date, INTERVAL 1 DAY)
        AND a.user_id = e.user_id
    GROUP BY 1, 2
)
, mst_growth_index AS (
    SELECT DISTINCT
        event_date
        , user_id
        , CASE WHEN is_new = 1 THEN 1 ELSE 0 END AS signup_flag
        , CASE WHEN is_new = 0 AND is_access = 1 AND was_access = 0 THEN 1 ELSE 0 END AS reactivation_flag
        , CASE WHEN is_access = 0 AND was_access = 1 THEN 1 ELSE 0 END AS deactivation_flag
        , CASE WHEN is_exit = 1 THEN 1 ELSE 0 END AS exit_flag
    FROM mst_status
)
SELECT
    event_date
    , SUM(signup_flag) AS signup
    , SUM(reactivation_flag) AS reactivation
    , SUM(deactivation_flag) AS deactivation
    , SUM(exit_flag) AS exit
    , SUM(signup_flag + reactivation_flag - deactivation_flag - exit_flag) AS growth_index
FROM mst_growth_index
GROUP BY 1
ORDER BY 1
```

## アウトプット
![スクリーンショット 2022-09-24 18.16.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/7dd0b7bd-4183-864d-146a-6b6cc3ca9eb0.png)


## 可視化結果
![成長指数の推移.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/d538ea07-4d04-ed87-602b-e6ec87ca8f98.png)


## ここから言えること
正直言って、思ったより言えることはなかった。。。
2018年6月後半あたりは、アクティブになったり、非アクティブになったりと激しい乱高下をしたが、その後はずっと成長度合いは停滞を続けている。週次ベースで出せれば良い示唆が生まれるかもしれないが、期間は114日分しかないため悪しからず。