2024年3月21日

BigQuery活用！GA4のコホートデータ探索レポートをLooker Studioで再現してみた

GA4の探索レポートを用いることで、管理画面上での深掘り分析の幅はUAの時よりも広がりました。

一方で、せっかく作成した探索レポートを共有したくても、権限の問題などで共有できないといった問題も発生しているのではないでしょうか。

そこで今回は、共有が簡単なLooker Studioを使用してコホートデータ探索レポートを再現してみました。GA4から無償版でもエクスポート可能になったBigQueryを使って再現しているので、SQL面でも参考にしていただけたら嬉しいです。

GA4探索レポートについておさらい

GA4で用意されている探索レポートのテンプレートは、自由形式を含めて7種類あります。

自由形式：もっとも基本的で、もっともカスタマイズ性の高いレポート。UAのカスタムレポートに類似。
ファネルデータ探索：任意の開始ステップ、次のステップ、到達ステップでファネルを描けるレポート。
経路データ探索：任意のページやイベントから、次のページやイベントへの遷移について深堀りできるレポート。
セグメントの重複：ユーザー側で作成した任意のセグメント同士において、重複の度合いを確認できるレポート。
ユーザーエクスプローラ：個別のユーザーの、個別の行動を表示できるレポート。UAのユーザーエクスプローラと同様。
コホートデータ探索：「ユーザーの初回訪問獲得日」などを基準にユーザーをグループ化し、特定のコホートに含まれるユーザーの後続の訪問やトランザクション、特定のイベント発生動向を把握できるレポート。
ユーザーのライフタイム：LTVに関する特定のディメンションと指標を可視化できるレポート。

コホートデータ探索レポートとは

この記事でLooker Studio上で再現するコホートデータ探索レポートは、公式ヘルプでは以下のように記されています。

コホートは、Google アナリティクスのディメンションを使用してこのレポートで表示できる、共通の特性を持つユーザーのグループです。たとえば、獲得日が同じユーザーはすべて同じコホートに属します。コホートデータ探索を行うと、アプリまたはサイト内での、時間の経過に伴うこれらのグループの行動を確認できます。

たとえばECサイトの場合だと、自社サイトにはじめて接触してから購入するまでの時間経過を見たり、購入した翌月・翌々月・・・に再購入したユーザー数をトラッキングしたりできます（単位は日・週・月から選択可能です）。

上記はGoogle Merch ShopのGA4ですが、こちらを見ると購入から2-3ヶ月後に再び購入する傾向がありそうです。

このように、グルーピングしたユーザーがその後どのタイミングで行動を起こすのか？を可視化したものがコホートデータ探索レポートです。

コホートデータ探索レポートをLooker Studioで再現する方法

まずはBigQuery上でテーブル準備

今回は、アプリの月別維持率をLooker Studioで可視化してみたいと思います。グルーピングユーザーは「first_openイベントを発生させたユーザー」、その後の特定イベントは「app_open」とします。

こうすることで、アプリをダウンロードしたユーザーが、Nヶ月後にもアプリを利用してくれているのか？を見ることができます。
※first_openイベントはアプリの初回起動なので厳密にはダウンロードではありませんが、GA4ではダウンロードに値するイベントがないため、first_openイベントをダウンロードとみなします。

GA4の探索レポートでは「数」しか見ることができませんが、BigQueryを通すことで「率」として見ることができるため施策を立案しやすいです。たとえば、もし「ダウンロード後2ヶ月で維持率が10％まで落ちる」ということが分かれば、2ヶ月の継続利用でインセンティブを付与するなどの施策を打ち出すことができます。

では、実際のSQLクエリを見てみましょう。まずは必要イベント（今回はfirst_openとapp_open）のみを抽出します。
※本記事で紹介するSQLクエリは一例です。自社の環境に合わせて編集してください。

WITH
  base AS (
    SELECT
      event_timestamp
      ,event_name
      ,user_pseudo_id
    FROM
      `XXXX` --データ抽出元を入れる
    WHERE
      event_name IN ('first_open','app_open') --初回起動・起動イベントのみ抽出
    AND _TABLE_SUFFIX 
      BETWEEN FORMAT_DATE('%Y%m%d', DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 25 MONTH), MONTH)) 
      AND FORMAT_DATE('%Y%m%d',CURRENT_DATE('Asia/Tokyo'))
  )

ここで、「user_pseudo_id」(＝デバイスID)を出していることがポイントです。

アプリに会員登録機能などがあれば「user_id」を使った方が正確に維持率を追えますが、ダウンロード（first_open）時点ではuser_idが付与されていないため、ダウンロードからの維持率を出すにはuser_pseudo_idでユーザーを判別する必要があります。GA4コネクタではuser_pseudo_idを出すことができないので、この再現はBigQuery経由だからこそできることです。

たとえば、1月に購入した会員がNヶ月後にも購入しているか？の維持率を出すのであれば、user_idを使ってユーザー判別を行った方がより正確になります。

続いて、ダウンロード（first_open）とアプリ起動（app_open）の各イベントについて、イベント発生月とuser_pseudo_idを持った一時テーブルを作成します。

--初回起動情報
  first_open_info AS (
    SELECT
      DISTINCT
      DATE_TRUNC(DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo'), MONTH) AS first_open_month --初回起動月
      ,user_pseudo_id
    FROM
      base
    WHERE
      event_name IN ('first_open')
  )

同様にアプリ起動も作成します（SQLクエリ略）。

次に、ダウンロード月と起動月の差分を出します。この際、先述の「user_pseudo_id」がキーとなります。

--各起動月と初回起動月の差分算出
  calc_month_diff AS (
    SELECT
      foi.first_open_month AS first_open_month
      ,oi.open_month AS open_month
      ,DATE_DIFF(oi.open_month, foi.first_open_month, MONTH) AS month_diff
      ,foi.user_pseudo_id AS user_pseudo_id
    FROM
      first_open_info AS foi
    LEFT JOIN open_info AS oi USING(user_pseudo_id)
  )

これで、ユーザーごとのダウンロード月、起動月、その差分がcalc_month_diffという一時テーブルとして作成されました。
※LEFT JOINが分からない方はこちらを参考にしてみてください。

さらに、Nヶ月目の起動ユーザー数を算出するため、起動有無を判別するための処理を行います。

--月の差分情報から継続有無のフラグ処理
  check_ret AS (
    SELECT
      first_open_month
      ,user_pseudo_id
      ,CASE WHEN month_diff = 0 THEN 1 ELSE NULL END AS is_ret_open_0
      ,CASE WHEN month_diff = 1 THEN 1 ELSE NULL END AS is_ret_open_1
      ,CASE WHEN month_diff = 2 THEN 1 ELSE NULL END AS is_ret_open_2
  --必要月数、上記を繰り返す
    FROM
      calc_month_diff
  )

コホートの計算方式は複数ありますが、今回は「標準」として再現したかったので、Nヶ月目に起動しているかどうかだけを判断軸としています（たとえば3ヶ月目の維持率を算出する際に、1ヶ月目・2ヶ月目にも起動しているかどうかは考慮しない）。

あとは、ダウンロード月、ダウンロード数（UU数）、Nヶ月目のUU数、Nヶ月目の維持率（Nヶ月目のUU数/ダウンロード数）を算出すればSQLクエリは完成です。

このクエリを実行すると、以下のような結果が出てきます。