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

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

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

## 準備
公開されているサンプルのデータセットへのアクセスは[こちら](https://support.google.com/analytics/answer/7586738?hl=ja#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9)を参照。

GA4のデータは [**ga4_obfuscated_sample_ecommerce**](https://support.google.com/analytics/answer/10937659?hl=ja#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9) を用いた。（たぶん現状これしかない）

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

## 使用するイベント
GA4の自動収集イベントについては[こちら](https://support.google.com/analytics/answer/9234069)の公式ドキュメントを参照した。

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

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

## コード

```sql
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%以上だったらちょっと薄い青みたいな感じで、色付けすることも可能です。

![コホート_7日間.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/233689/8040e7a4-4cc2-5713-e233-dcc008931bff.png)