私たちREADYFORでは、GA4 × BigQuery × Redashという組み合わせでユーザーの行動を分析して、日々のプロダクト改善に活用しています。
Google Analyticsの従来版（ユニバーサルアナリティクス）のサポート終了が4ヶ月後に迫り、GA4を使った分析に切り替えている最中です。
さて、この組み合わせで分析をやっていると、誰かしらこういう願望を抱くことでしょう。
「BigQueryにエクスポートしているGA4のイベントログをもとに、Redashでコホート分析したい...」
（画像はRedashの公式ドキュメントより引用）
Redashは可視化の機能に強みを持っており、コホートもその一つとして提供されています。
コホート分析という手法自体の説明は省きますが、こちらの記事などが参考になります。
この記事では、過去6週間を集計期間として、会員登録から週ごとのリテンションの推移を可視化してみます。
前提条件
今回の集計は以下を前提条件としています。
- GA4のイベントがBigQueryにエクスポートされている（参考）
- ページ閲覧時にpage_viewイベントを送信している
- 会員登録時にsign_upイベントを送信している
- page_viewイベント・sign_upイベント両方にuser_id（ユーザーID）を付与している
page_viewイベントはGA4の拡張計測機能によって自動で送ることができます。
sign_upについては自前でカスタムイベントとして実装する必要があります。sign_upという名前はGA4の推奨イベントに基づいていますが、何でも構いません。
user_idについては、ログイン時に以下のようにして送信する必要があります。
gtag('config', 'G-XXXXXXX', {
'user_id': 'USER_ID'
});
詳しくは公式ドキュメントを参照してください。
集計
まずは以下のようなテーブルが出てくるように集計を行います（グレーのところは数字が入ります）。
コホートに必要な項目についてはこちらの公式ドキュメント（Redash）が参考になります。
このドキュメントの項目と、今回のアウトプットとなるテーブルのカラム名の対応を以下に示しておきます。
- Cohort Date:
cohort_week（どの週にグルーピングするか＝会員登録した週） - Period:
weeks_elapsed（経過週数） - Count Satisfying Target:
user_count（cohort_weekに会員登録したユーザーのうち何人がweeks_elapsedに該当する週にもサイトを訪問したか） - Total Cohort Size:
population（cohort_weekに会員登録したユーザーの全体数）
activity_week はサイト訪問のあった週です。可視化には不要ですが、weeks_elapsedだけだと具体的にどの週か分かりづらいので入れています。
以下、WITH句を使って、クエリを分解しています。
これらを全部繋げると、一つのクエリになります。
固まりごとにみていきましょう。
会員登録イベントの集計（母集団）
会員登録のイベントログをもとに、ユーザーIDごとにどの週に会員登録したのかを出します。
週は月曜始まりとしています。
xxxxxx-yyyyyy.analytics_zzzzzzzzz.events_* の部分は適宜変更してください。
WITH
population AS (
SELECT DISTINCT
DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK(MONDAY)) AS cohort_week
, user_id
FROM `xxxxxx-yyyyyy.analytics_zzzzzzzzz.events_*`
WHERE true
AND _TABLE_SUFFIX BETWEEN
FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) - INTERVAL 48 DAY)
AND FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) - INTERVAL 1 DAY)
AND event_name = 'sign_up'
AND user_id is not null
),
これをコホート分析における母集団とします。
ページビューイベントの集計
次に、ページビューイベントを集計して、どの週に誰がサイトを訪れたかを出します。
events AS (
SELECT DISTINCT
DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK(MONDAY)) AS event_week
, user_id
FROM `xxxxxx-yyyyyy.analytics_zzzzzzzzz.events_*`
WHERE true
AND _TABLE_SUFFIX BETWEEN
FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) - INTERVAL 48 DAY)
AND FORMAT_DATE('%Y%m%d', DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY)) - INTERVAL 1 DAY)
AND event_name = 'page_view'
AND user_id IS NOT NULL
),
母集団とイベントをJOIN
上記2つをJOINして、どのコホートに属するどのユーザーがどの週にサイトを訪れたか分かるようになります。
activity AS (
SELECT
population.user_id
, event_week AS activity_week
, cohort_week
FROM population JOIN events ON population.user_id = events.user_id
),
コホートごとの母集団のサイズを集計
Redashの機能で可視化するためには「会員登録したユーザーのcohort_weekごとの全体数」も出す必要があるので集計しておきます。
population_agg AS (
SELECT
cohort_week
, COUNT(distinct user_id) AS population
FROM population
GROUP BY cohort_week
)
完成
上記2つをJOINして、Redashのコホート分析を利用できる形式に整えます。
SELECT
activity.cohort_week
, activity_week
, population
, DATE_DIFF(activity_week, activity.cohort_week, WEEK(MONDAY)) AS weeks_elapsed
, COUNT(distinct activity.user_id) AS user_count
FROM activity JOIN population_agg ON activity.cohort_week = population_agg.cohort_week
GROUP BY activity.cohort_week, activity_week, population, weeks_elapsed
HAVING weeks_elapsed >= 0
今回は会員登録をコホートの基準にしていてweeks_elapsedは必ず0以上になるので
HAVING weeks_elapsed >= 0
については本来不要ですが、cohort_weekよりも前にページビューイベントが発生する可能性がある場合はあった方がいいでしょう。
可視化
Visualizationを追加して、 [Visualizaion Type] に Cohort を選びます。
下記のようにカラムを指定します。
今回は週間のリテンションを集計しているので [Option] > [Time Interval] を Weekly にします。
これで保存したら完成です。
GA4とRedashの使い分け
GA４にもコホート分析の機能は備わっていて、「会員登録後のリテンション」であればGA４でもっとサクッとできます。
ただ、RedashならサービスのDBと組み合わせてさらにカスタマイズして分析することもできるのが強みです。既存のレポートをRedashで出している場合は、その一部としてコホート図を入れられるように今回の手法を使うのも手です。
また、GA４でコホート分析をするには「会員登録」のようにリテンションを見る起点になるイベントがちゃんとGA4に送信されていることが前提になります。そうでない場合はデータソースを組み合わせて分析できるRedashに頼る必要が出てくるでしょう。
注：駆け足での説明となったため、クエリの細かい部分など解説できていません。もし気になるところがあればコメントで教えていただけたら幸いです！
参考
「みんなの想いを集め、社会を良くするお金の流れをつくる」READYFORのエンジニアブログです。技術情報を中心に様々なテーマで発信していきます。 ( Zenn: zenn.dev/p/readyfor_blog / Hatena: tech.readyfor.jp/ )
Discussion