わかりやすく汎用的なリテンション率の求め方

リテンション率とは

リテンション率とは、Web サービスやアプリなどの新規顧客が一定期間内に再訪した割合を示す数値です。「定着率」「継続率」「顧客維持率」とも呼ばれています。

課題感

リテンション率を SQL で求める参考記事はインターネット上にいくつかありますが、以下のような課題感があったので改めて自分なりにまとめてみました。

• 初回訪問から３０日間でのリテンション率を求める場合、愚直に 1 日ずつ ３０ 列カラムを用意している
• 初回訪問日を基準として集計している記事が少なく、モニタリングの際にどの時点でのどのユーザー群のリテンション率を表しているか理解するまでに時間がかかる（できれば直感的に素早く理解したい）

やること

1. リテンション率を求める
2. Looker Studio で可視化

※今回は GA 4 のサンプルデータを利用します。

1. リテンション率を求める

retention_rate.sql

DECLARE from_date STRING DEFAULT '20201201';
DECLARE to_date STRING DEFAULT '202112０１';
DECLARE rr_days INT64 DEFAULT 7;

WITH first_visit_log AS (
    SELECT DISTINCT
        user_pseudo_id,
        DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS first_visit_date,
        i AS diff_days,
        DATE_ADD(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo'), INTERVAL i DAY) AS target_date
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
        UNNEST(GENERATE_ARRAY(0, rr_days, 1)) AS i
    WHERE 1=1
        AND _TABLE_SUFFIX BETWEEN from_date AND to_date
        AND event_name = 'first_visit'
)
, 
action_log AS (
    SELECT DISTINCT
        user_pseudo_id,
        DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS action_date
    FROM
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE 1=1
        AND _TABLE_SUFFIX BETWEEN from_date AND to_date
        AND event_name = 'session_start'
)
, 
flag AS (
    SELECT
        a.user_pseudo_id,
        a.first_visit_date,
        a.target_date,
        a.diff_days,
        case when b.action_date is not null then 1 else 0 end AS action_flag
    FROM
        first_visit_log AS a
    LEFT JOIN
        action_log AS b
    ON a.user_pseudo_id = b.user_pseudo_id
        AND a.target_date = b.action_date
)
SELECT 
    first_visit_date,
    target_date,
    diff_days,
    count(user_pseudo_id) as user_cnt,
    SAFE_DIVIDE(sum(action_flag), count(user_pseudo_id)) as rr
FROM flag
WHERE target_date <= PARSE_DATE('%Y%m%d', to_date)
GROUP BY 1,2,3
ORDER by 1,2,3

ポイントは UNNEST(GENERATE_ARRAY(0, rr_days, 1)) AS i です。愚直にカラムを生成していないため、rr_days を変更すればリテンション期間を簡単に変更できます。
あとは、flag 句でリテンションのターゲット日にアクションがあったかどうかを action_flag で判定し、最終的にカウントすれば終わりです。簡単！！

2. Looker Studio で可視化

上記のクエリを実行 & BigQuery にテーブルを作成し、Looker Studio で以下のように可視化しました。

diff_days = 1 は 常に 100 % であるためフィルタ機能で除外しています。
また、カラースケールを付けておくと、パッと見てリテンション率の変化が分かります。例えば、上記の表では 2020/12/02 に新規登録したユーザーの 1 日後・ 2 日後のリテンション率は高くなっていますが、3 日後にはガクッと下がっていることがわかります。（短期型の施策を打ったからでしょうか？）

また、時系列で確認してみるのもおすすめです。どうしても表だと長期的な比較は厳しいので。

このグラフから、年末年始で全体的にリテンション率が下がり、それ以降は回復していることがわかります。

まとめ

リテンション率を求める際に毎度インターネットで求め方を検索していましたが、自分が求めたいリテンション率に整形するためにかなり苦労していましたが、これからは上記のクエリ一発で求められるようになりました。