【SQL/BigQuery】ユーザー単位のN日後継続率を集計する

こんにちは、ミナピピン(@python_mllover)です。たまに仕事でN日後継続率を集計することがあるので、次からコピペですぐできるようにメモしておきます。

データを用意

install_datetime	user_id	login_datetime
2021/1/1 9:29	1	2021/1/4 16:00
2021/1/1 9:29	1	2021/1/10 12:00
2021/1/1 9:29	1	2021/1/12 13:00
2021/1/1 9:29	1	2021/1/7 23:00
2021/1/1 9:29	1	2021/1/6 4:00
2021/1/2 1:47	2	2021/1/10 22:00
2021/1/2 1:47	2	2021/1/11 7:00
2021/1/2 1:47	2	2021/1/8 16:00
2021/1/3 10:36	3	2021/1/12 12:00
2021/1/3 10:36	3	2021/1/5 2:00
2021/1/3 10:36	3	2021/1/4 2:00
2021/1/3 10:36	3	2021/1/5 8:00
2021/1/3 10:36	3	2021/1/5 17:00

<作成コード>

import random
import datetime
import pandas as pd

now = datetime.datetime.now()

data = []
for i in range(1,6):
    day_ = random.choice([1,2,3])
    install_datetime = now.replace(day=day_, hour=random.randint(1,23), minute=random.randint(1,59), second=0, microsecond=0)
    id_ = i
    for s in range(random.choice([2,3,5])):
        login_datetime = now.replace(day=random.randint(day_+1,10), hour=random.randint(1,23), minute=0, second=0, microsecond=0)
        data.append([install_datetime, id_, login_datetime])
pd.DataFrame(data,columns=['install_datetime','user_id', 'login_datetime']).to_csv('user_access.csv',index=False)

できたCSVからbigqueryの適当なデータセットにテーブルを作成してください。

継続フラグを作る

N日後継続率を集計するためにはまずフラグを作成します。ログイン日をユーザ・インストール日でGROUP BYし、CASE・WHEN文でインストール日から24時間以上48時間以内にログインしているデータがあった場合は1、それ以外は0とするフラグを付け、MAXでその最大値を集計します。(これによってフラグは1か0となります)

SELECT 
  user_id 
  ,DATE(install_datetime) AS install_date 
  ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 24 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 48 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day1_flg 
  ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 48 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 72 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day2_flg 
  ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 72 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 96 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day3_flg 
  ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 120 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 144 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day5_flg 
  ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 172 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 192 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day7_flg 
FROM 
  `dbdapi.blog_sample_data.n_login` 
GROUP BY 
  user_id 
  ,install_date

フラグ数の平均から継続率を算出

そして、そのテーブルをインストール日でGROUP BYしAVG()で平均を取ることで、N日後継続率を集計することができます。

SELECT 
  install_date 
  ,AVG(day1_flg) AS day1_ratio
  ,AVG(day2_flg) AS day2_ratio
  ,AVG(day3_flg) AS day3_ratio
  ,AVG(day5_flg) AS day5_ratio
  ,AVG(day7_flg) AS day7_ratio
FROM
  (
    SELECT 
      user_id
      ,DATE(install_datetime) AS install_date 
      ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 24 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 48 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day1_flg
      ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 48 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 72 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day2_flg
      ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 72 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 96 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day3_flg
      ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 120 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 144 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day5_flg
      ,MAX(CASE WHEN TIMESTAMP_ADD(install_datetime, INTERVAL 172 HOUR) < login_datetime and TIMESTAMP_ADD(install_datetime, INTERVAL 192 HOUR) > login_datetime THEN 1 ELSE 0 END) AS day7_flg
    FROM 
      `dbdapi.blog_sample_data.n_login`
    GROUP BY
      user_id
      ,install_date
  )
GROUP BY 
  install_date
ORDER BY
  install_date