GA4のサンプルデータからマジックナンバー分析をしてみる

マジックナンバー分析とは？

マジックナンバー分析とは、サービスの成長に寄与する重要なユーザー行動を特定するための分析手法。
例えば、、、

サービス名	参考事例
Facebook	最初の10日間で7人以上友達になったユーザーは継続率が高い
Twitter	初日に5人以上フォローしたユーザーは継続率が高い
oVice	5人以上が1時間以上話すと定着率が高くなる

※詳細は参照した事例の元記事をご覧ください。
・ Reproさんのブログ
・ アプリマーケティング研究所さんのブログ

今回やること

BigQueryのサンプルデータでGA4のEコマースのアクセスログデータセット「ga4_obfuscated_sample_ecommerce」を用いて、簡易的に 初回訪問日にどのページを訪問すると、ユーザーの継続率が高まる傾向にあるのかを特定する。
そのために、SQLを書き、BigQueryとGoogleデータポータルを連携させ、可視化する。

本当はもっと詳細に色々なイベントを用いてやりたいのですが、サンプルデータがこれしかないみたいなので、、、（残念）

準備

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

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

使用するイベント

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

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

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

コード

2020-11-01 ~ 2021-01-31に初回訪問をしたユーザーの1~7日後継続率を算出している。
※クエリでは、1~7日後継続率を算出しているが、今回可視化するのは翌日継続率に限定する。
※また、該当期間に1,000以上のpage_view UU数があるものに絞っている（数が多くなって分かりづらくなるし、母数が少なければ偶然高かっただけかもしれないので）

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 '20211231' 
    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_page_view as (
    SELECT
       DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') as event_date
       , user_pseudo_id
       , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE
       _TABLE_SUFFIX BETWEEN '20201101' AND '20211231'
    AND
        event_name = 'page_view'
)
, mst_events_flag as (
    SELECT
        a.event_date
        , a.user_pseudo_id
        , a.page_title
        , b.flag_0
        , b.flag_1
        , b.flag_2
        , b.flag_3
        , b.flag_4
        , b.flag_5
        , b.flag_6
        , b.flag_7
    FROM
        mst_page_view as a
    INNER JOIN
        flag as b
    ON 
        a.user_pseudo_id = b.user_pseudo_id
    AND 
        a.event_date = b.day_0
    WHERE
        a.page_title IS NOT NULL
    GROUP BY
        1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
)
, mst_sum as (
    SELECT
        page_title
        , 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
        mst_events_flag
    GROUP BY
        1
)
SELECT
    page_title
    , sum_0 as first_users
    , sum_1 as day1_users
    , ROUND(sum_0 / sum_0, 3) as rr_0
    , ROUND(sum_1 / sum_0, 3) as rr_1
    , ROUND(sum_2 / sum_0, 3) as rr_2
    , ROUND(sum_3 / sum_0, 3) as rr_3
    , ROUND(sum_4 / sum_0, 3) as rr_4
    , ROUND(sum_5 / sum_0, 3) as rr_5
    , ROUND(sum_6 / sum_0, 3) as rr_6
    , ROUND(sum_7 / sum_0, 3) as rr_7
FROM
    mst_sum
WHERE
    sum_0 >= 1000
ORDER BY 
    5 DESC 

可視化結果

ディメンション：ページタイトル
X軸：初回利用時利用ユーザー数
Y軸：翌日継続率
バブルサイズ：翌日のユーザー数
※ダッシュボードでは、バブルにカーソルを当てると、それに関する情報が表示されるように設定できます。（でないと、分からない）

ここから得られる示唆

前提として、このサービスの翌日継続率はだいたい3~5%ぐらいであった。
※前投稿参照

左上の項目群を右側に移行させる（もっと多くのユーザーに初日に使ってもらう）ことができれば、サービス全体の継続率が向上するわけですが、それなりに母数があるものを優先的に選ぶと、、、
「Shopping Cart」「payment method」「特定の条件で絞った詳細ページ？（Men's / Unisex | Apparel | Google Merchandise Store等）」 の閲覧したユーザーが翌日継続率が10%強と、平均より高くなっているというデータが得られました。
つまり、初回でこれらのページに遷移させることを目指す改善を行っていこうということになります。