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

成長指数とは？

成長指数はサービスの成長度合いを数値化して、サービスが成長しているかを表す指標です。この成長指数が1以上の時はサービスが成長していることを示し、0以下の時は停滞または衰退をしていることを示します。
（SQL書籍 ビッグデータ分析・活用のためのSQLレシピ にて紹介されています）

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

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

※上記紹介書籍にて記載

今回やること

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

クエリ

デモデータセットにあった114日間の全ての日付テーブルを用いた。

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

アウトプット

可視化結果

ここから言えること

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