概要
チャーンレート(解約率・退会率)とは、全てのユーザーのうち解約したユーザーの割合を示す指標のことです。継続率は、 1 - チャーンレート
で出ます。
今回は前月からのチャーンレートを見ます。2019-06の時は、2019-05時点のユーザ数のチャーンレートを、2019-05の時は、2019-04時点のユーザ数のチャーンレートを、というように変動的にやるSQLです。
SQL
SELECT
this.month
,count(distinct last.user_id) / count(distinct this.user_id) churn_rate
,1 - count(distinct last.user_id) / count(distinct this.user_id) keizoku_rate
FROM (
-- 当月のユーザー数
SELECT
date_trunc(date(u.created), month) month
,u.id user_id
FROM
users u
GROUP BY month
) this LEFT JOIN (
-- 前月のユーザー数
SELECT
date_trunc(date(u.created), month) month
,u.id user_id
FROM
users u
GROUP BY month
) last ON this.user_id = last.user_id
and this.month = date_add(last.month, interval 1 month) -- 前月処理
GROUP BY month
ORDER BY month DESC
ポイントは、当月を前月+1ヶ月と、LEFT JOINすることです。
and this.month = date_add(last.month, interval 1 month)
日別、週別、四半期別、年別に date_trunc(date(u.created), XXXX
)` を使って、分析してみるのも楽しいですね。
また課金しているなどアクティブなユーザーのチャーンレートを見るのも面白いと思います。その場合は、 GROUP BY month, X.user_id
と、ユーザーIDをユニークにすることを忘れずに。