GA4のサンプルデータからBigQueryでコホート表を作ってみる

やること

ユーザーが初回訪問からどの程度定着しているのかをリテンションレートで表現する。
本当はアプリのアクセスログからやりたいが、BigQueryの公開されているサンプルデータはおそらくEコマースのWEBデータなので継続率は低め。（アプリの特性によるが、翌日継続率であれば40%ぐらいを目指したいところ）

仕事では0〜30日継続率を算出しているが、コードも長くなるので、今回は7日間までで書いた。

準備

公開されているサンプルのデータセットへのアクセスはこちらを参照。

GA4のデータは ga4_obfuscated_sample_ecommerce を用いた。（たぶん現状これしかない）

当然BigQueryの環境が必要で、可視化したいのであれば Googleデータポータル と連携させて表現することをオススメします。

使用するイベント

GA4の自動収集イベントについてはこちらの公式ドキュメントを参照した。

イベント名	自動的にトリガーされるタイミング
first_visit	アナリティクスが有効になっているウェブサイトや Android Instant Apps に、ユーザーが初めてアクセスしたとき、またはこれらを起動したとき
session_start	ユーザーがアプリやウェブサイトを利用したとき

※アプリのアクセスログであれば、first_visitではなく、first_open を用いるが、WEBだとそのイベントがないため、こちらで代用した。

コード

WITH mst_first_visit_date as (
    SELECT
        user_pseudo_id
        , DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') as day_0
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 1 DAY) as day_1
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 2 DAY) as day_2
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 3 DAY) as day_3
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 4 DAY) as day_4
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 5 DAY) as day_5
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 6 DAY) as day_6
        , DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL 7 DAY) as day_7
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
       _TABLE_SUFFIX BETWEEN '20201101' AND '20210131' 
    AND
        event_name = 'first_visit'
)
, mst_action_date as (
    SELECT
        user_pseudo_id
        , DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') as action_date
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
       _TABLE_SUFFIX BETWEEN '20201101' AND '20210131' 
    AND
        event_name = 'session_start'
)
, flag as (
    SELECT
        a.user_pseudo_id
        , a.day_0
        , MAX(IF(a.day_0 = b.action_date, 1, 0)) as flag_0
        , MAX(IF(a.day_1 = b.action_date, 1, 0)) as flag_1
        , MAX(IF(a.day_2 = b.action_date, 1, 0)) as flag_2
        , MAX(IF(a.day_3 = b.action_date, 1, 0)) as flag_3
        , MAX(IF(a.day_4 = b.action_date, 1, 0)) as flag_4
        , MAX(IF(a.day_5 = b.action_date, 1, 0)) as flag_5
        , MAX(IF(a.day_6 = b.action_date, 1, 0)) as flag_6
        , MAX(IF(a.day_7 = b.action_date, 1, 0)) as flag_7
    FROM
        mst_first_visit_date as a
    LEFT JOIN
        mst_action_date as b
    USING(user_pseudo_id)
    GROUP BY 
        1, 2
)
, mst_sum as (
    SELECT
        day_0
        , SUM(flag_0) as sum_0
        , SUM(flag_1) as sum_1
        , SUM(flag_2) as sum_2
        , SUM(flag_3) as sum_3
        , SUM(flag_4) as sum_4
        , SUM(flag_5) as sum_5
        , SUM(flag_6) as sum_6
        , SUM(flag_7) as sum_7
    FROM
        flag
    GROUP BY
        1
)
SELECT
    day_0 as first_visit_date
    , sum_0 as users
    , IF(sum_0 / sum_0 > 0, ROUND(sum_0 / sum_0, 3), NULL) as rr_0
    , IF(sum_1 / sum_0 > 0, ROUND(sum_1 / sum_0, 3), NULL) as rr_1
    , IF(sum_2 / sum_0 > 0, ROUND(sum_2 / sum_0, 3), NULL) as rr_2
    , IF(sum_3 / sum_0 > 0, ROUND(sum_3 / sum_0, 3), NULL) as rr_3
    , IF(sum_4 / sum_0 > 0, ROUND(sum_4 / sum_0, 3), NULL) as rr_4
    , IF(sum_5 / sum_0 > 0, ROUND(sum_5 / sum_0, 3), NULL) as rr_5
    , IF(sum_6 / sum_0 > 0, ROUND(sum_6 / sum_0, 3), NULL) as rr_6
    , IF(sum_7 / sum_0 > 0, ROUND(sum_7 / sum_0, 3), NULL) as rr_7
FROM
    mst_sum
ORDER BY
    1

出力結果（データポータルで簡単に可視化）

アプリでこんな継続率だったら泣いちゃうぐらいの惨劇ですが、あくまでサンプルなので。。。
データポータルで出力していますが、40%以上だったら濃い青、30%以上だったらちょっと薄い青みたいな感じで、色付けすることも可能です。