Window関数を使ってスマートに継続率を算出する

継続率はサービスのKPIなどで良く使われる指標なので検索すれば様々な算出方法が出てくる思いますが、Window関数を使うことでこれをよりシンプルに求めることが可能です。

この例では翌週継続率を求めます。

Window関数とは？

分析関数とも呼ばれる比較的新しい機能で分析系SQLを書く上でとても強力な武器ですが、一方で理解するのが難しい機能でもあります。

そろそろSQLのウィンドウ関数を理解したい - 連載1/3話 - Qiita

データの用意

例えば以下のようにユーザーセッションの日付を並べます。

※ 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() -- 全てのレコード数を数えます

SUM(1) OVER(PARTITION BY user_id) -- 同じユーザー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日先の期間に入っているもののみを数えることができます。

翌週継続率の算出

あとは上記のテーブルをユーザーごとにグループ化し、平均化するなどして翌週継続率が算出可能です。