SQLで月次チャーンレート(解約率・退会率)・継続率を出す

4481 ワード
MySQL SQL BigQuery redash MySQL テキストリンク

概要

チャーンレート(解約率・退会率)とは、全てのユーザーのうち解約したユーザーの割合を示す指標のことです。継続率は、 1 - チャーンレート で出ます。

今回は前月からのチャーンレートを見ます。2019-06の時は、2019-05時点のユーザ数のチャーンレートを、2019-05の時は、2019-04時点のユーザ数のチャーンレートを、というように変動的にやるSQLです。

※値は適当です。最初のデータは前月がないので0になります。

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をユニークにすることを忘れずに。