GoogleBigQueryで継続率を集計する #MySQL

目的

プロダクト分析で用いられる継続率を集計するQueryサンプルを書きます
ユーザーの定着状況を図る指標の1つです
GAでも見れるのになぜわざわざQueryで書くのかは後述します

継続率をQueryで集計するメリット

継続率は通常 登録日からn日後にユーザーがプロダクトを利用してくれているかを計測する指標です
- チャーンレート（解約率）の逆ですね
全体の継続率を計測して指標として追うことも大事です（アプリDL時のウォークスルーとかを改善すると、1日後の継続率は改善したりします）
ただ、継続率をQueryで集計する最大のメリットは特定行動の有無（回数でも可）での継続率差分を計測可能 なことです
- 会員登録情報をどこまで入れた人が継続してるのか？登録初日に3つのコンテンツをお気に入り登録してくれた人のほうが継続してくれているのか？みたいな使い方ができます

アウトプットのイメージ

全体の継続率を集計します

データソース

daily_active_users

ログイン情報が入ってるテーブル

dt	user_id
2019-01-01	100
2019-01-01	101
2019-01-02	101
2019-01-02	103
2019-01-03	100

users

会員登録日がわかるテーブル

created_at	user_id	nick_name
2019-01-01	100	hoge
2019-01-01	101	huga
2019-01-02	101	hoge
2019-01-02	103	huge
2019-01-03	100	hoge

Query

登録日と会員登録uu、それにそれらのユーザーの1日後、3日後、7日後、14日後、30日後の継続率を集計しています
期間はサービスの特性に合わせて任意に変えてください

ポイント

ログイン日 = 会員登録 + n日で会員登録から n日後に該当ユーザーがログインしているかを判定しています
ログインから3日後の場合は3日後のみを判定対象としており、その間の連続性は特に見ていません


cast(COUNT(distinct(CASE WHEN t1.date = t2.first_login_date + integer '1' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day1RR

SELECT
    t2.register_date
    ,COUNT(distinct t2.user_id) as register_uu
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '1' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day1RR
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '3' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day3RR
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '5' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day5RR
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '7' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day7RR
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '14' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day14RR
    ,cast(COUNT(distinct(CASE WHEN t1.date = t2.register_date + integer '30' then t1.user_id else null END))as real)/cast(COUNT(distinct t2.user_id)as real)) as day30RR
FROM
    (
    /*ログイン*/  
    SELECT
        login_date
        ,user_id
    FROM daily_active_users
    ) t1
    LEFT OUTER JOIN
    (
    /*会員登録*/      
    SELECT
      created_at as register_date
      ,user_id
    FROM users
    )t2
    ON t1.user_id = t2.user_id
GROUP BY
    t2.register_date

さいごに

他にも書いてるので合わせてどうぞ！

今後もこういったPM向けの分析の記事をちょこちょこ書くので、よかったらQiitaとかTwitterとかフォローしてもらえると嬉しいです！

ツイッターアカウント：https://twitter.com/ysk_ur

追記

date関数ハマったので改めてメモ

SELECT
    t2.register_dt
    ,COUNT(distinct t2.user_id) as reg_uu
    ,SAFE_DIVIDE(COUNT(distinct((CASE WHEN t1.dt = date_add(t2.register_dt ,INTERVAL 1 DAY) then t1.user_id else null END))),COUNT(distinct t2.user_id)) as day1RR
    ,SAFE_DIVIDE(COUNT(distinct((CASE WHEN t1.dt = date_add(t2.register_dt ,INTERVAL 3 DAY) then t1.user_id else null END))),COUNT(distinct t2.user_id)) as day3RR
    ,SAFE_DIVIDE(COUNT(distinct((CASE WHEN t1.dt = date_add(t2.register_dt ,INTERVAL 7 DAY) then t1.user_id else null END))),COUNT(distinct t2.user_id)) as day7RR
    ,SAFE_DIVIDE(COUNT(distinct((CASE WHEN t1.dt = date_add(t2.register_dt ,INTERVAL 14 DAY) then t1.user_id else null END))),COUNT(distinct t2.user_id)) as day14RR
FROM
    (
    SELECT
        dt
        ,user_id
    FROM daily_active_users
    WHERE dt BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE() /*直近90日*/
    )t1
    LEFT OUTER JOIN
    (
    SELECT 
        register_dt
        ,user_id
    FROM users
    WHERE register_dt BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE /*直近90日*/
    ) t2
    ON t1.user_id = t2.user_id
GROUP BY
    t2.register_dt