Window関数を使って継続率をスマートに算出する
date
Aug 8, 2021
type
Post
status
Published
slug
get-retention-rate-by-window-function
summary
分析系SQLの要であるWindow関数を使いこなす
tags
DataAnalysis
Tech
備考
継続率はサービスのKPIなどで良く使われる指標ですが、算出するのが少し難しい指標でもあります。Window関数を使うことでこれをコンパクトな処理で求めることが可能です。
コンパクトに継続率を出すことができるようになれば「どのような行動がユーザーの継続率に影響しやすいか？」といった分析もやりやすくなります。
この例では翌週継続率を求めます。
Window関数とは
分析関数とも呼ばれる比較的新しい機能で分析系SQLを書く上でとても強力な武器になります。
データの用意
例えば以下のようにユーザーセッションの日付を並べます。
※ BigQueryではwindow関数内での日付の絞り込みにはunixdateで行う必要があるので
UNIX_DATE(session_date) でunix_dateを追加しています。Window関数で翌週継続した日数を計算する
SELECT
user_id,
session_date,
SUM(1) OVER(
PARTITION BY user_id
ORDER BY unix_session_date
RANGE BETWEEN 7 FOLLOWING AND 13 FOLLOWING
) AS repeat_dates_in_next_week
FROM sessionn_dates_with_unixdate
上記を実行することで下記のように翌週継続の期間のセッション日数を集計することができます。
クエリの内容の解説
SUM(1) OVER(xxx) のようにWindow関数を使うことで、 OVER(xxx) の中に書いた条件式にあてはまるレコード数を算出しています。OVERとは
並んでいる他のレコードを全て捜査します。
例えばこの例でOVER()の中身に何も指定しない場合は他のレコード数をすべて数えます。
-- 全てのレコード数を数える
SUM(1) OVER()-- 同じユーザーIDのレコードだけを数える
SUM(1) OVER(PARTITION BY user_id)この例でOVERの中に書いた条件式
PARTITION BY user_id- 同じ user_id をを持つレコードをのみを捜査します。
ORDER BY unix_session_date RANGE BETWEEN 7 FOLLOWING AND 13 FOLLOWING- 捜査するレコードをunix_session_dateがそのレコードから7日先~13日先の間に入っているレコードに限定します
結果として、同じuser_idをもつ他のセッション日のなかで7~13日先の期間に入っているもののみを数えることができます。
翌週継続率の算出
上記のようなテーブルが作れたら、あとはユーザーごとにグループ化や平均化するなどして翌週継続率が算出可能です。(最後の2週間をWHERE句などで除外するのを忘れないようにしましょう。)