目的
- プロダクト分析で用いられる継続率を集計する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