GoogleBigQueryで継続率を集計する

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

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


### アウトプットのイメージ
 - 全体の継続率を集計します
![image.png](https://qiita-image-store.s3.amazonaws.com/0/45434/9997d982-f5c0-9b70-f729-2b193ce5c8fe.png)


### データソース

#### 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日後のみを判定対象としており、その間の連続性は特に見ていません

```SQL

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
```


```SQL
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にとって必須スキルである理由](https://qiita.com/yskur/items/db53cd347041935db265)
 - [GoogleBigQueryでFQ別のDAUを集計する](https://qiita.com/yskur/items/d4c48022730097076f02)
 
今後もこういった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
```