BigQuery SQL

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

この記事は約8分で読めます。

 

 

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

 

データを用意

 

<table1>

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

 

 

関連記事:【SQL】分析関数①ROW_NUMBERで集計対象ごとに順番付け

 

関連記事:【SQL】GROUP BYと集計関数での集計、HAVING・WHEREでの条件付き集計

 

 

 

 


プログラミング・スクレイピングツール作成の相談を受け付けています!

クラウドワークス・ココナラ・MENTAなどでPython・SQL・GASなどのプログラミングに関する相談やツール作成などを承っております!

過去の案件事例:

  • Twitter・インスタグラムの自動化ツール作成
  • ウェブサイトのスクレイピングサポート
  • ダッシュボード・サイト作成
  • データエンジニア転職相談

これまでの案件例を見る

キャリア相談もお気軽に!文系学部卒からエンジニア・データサイエンティストへの転職経験をもとに、未経験者がどう進むべきかのアドバイスを提供します。


スポンサーリンク
/* プログラミング速報関連記事一覧表示 */
ミナピピンの研究室

コメント

タイトルとURLをコピーしました