LAPRAS株式会社でカスタマーサクセスを担当している@yuta_maruyamaです。
自分はその中でもLAPRAS SCOUTという企業の採用担当者向けのSaaSプロダクトのカスタマーサクセスを担当しています。
こちらの記事では、サブスクリプションモデルのサービスにおいて、スプレッドシートだけで主要メトリクス（Charn Rate、MRR等）を算出し、簡単なダッシュボードを作るところまでを解説します。
背景
Zuoraのようなサブスクリプションサービスを支援するようなサブスクリプションサービスが存在するように、サブスクリプションの管理は案外厄介なものです。
自分の場合、カスタマーサクセスとしてChurn Rateの抑制を目指すにあたって、顧客の契約状況の把握とそれに応じた主要メトリクスの算出をする必要がありましたがなかなかそのことについて書かれた文献が見つからないという状況でした。
事業のフェーズ的にも、サブスクリプション管理のためだけに新たにSaaSを導入する気にはならなかったので、シンプルにスプレッドシートで管理する方法をとりました。その方法をご紹介します。
対象
領域
- SaaSを作っている人
- サブスクリプションモデルのサービスを作っている人
- B、Cは問わない
スキルや知識
- スプレッドシート（もしくはExcel）でif関数、countifs関数が使える
- サブスクリプションにおけるメトリクスの簡単な理解
- Recurring Revenue
- Churn Rate
- LTV
- 等々
- 簡単なDB言語を扱えるとなお良い（今回は使いません）
前提
- 6ヶ月ごとに自動契約更新をする、月額課金制のBtoBサービスを前提とします
- 月額は15万円です
- サブスクリプション更新ごとに新しく契約情報（contract)を作ります
全体観
契約に関する情報を持つcontractに対して、スプレッドシートで用意されている関数を使ってサブスクリプションに関する情報を付け足し、
それを別シートで集計することによって月次のサブスクリプション契約の動向が分かるようにします。
今回は各月における
- 契約している顧客数
- 新規契約した顧客数
- 離脱した顧客数
- MRR（Monthly Recurring Revenue)
- チャーンレート
を求めるまでを目指します。
サンプルのスプレッドシート
実際にメトリクスを算出したスプレッドシートはこちらになります。こちらを参考にしながら下記の説明を読んでみてください。
https://docs.google.com/spreadsheets/d/1dgVjiU-acRXB_tJ18b0tlQnfEcgqfY939QkVVjtgjr0/edit#gid=1999363116
※入っているデータはダミーデータです
contractデータの整備
contract（契約情報）を整えていきます。必要な情報は以下です。
必要な情報
-
contract_id(A列)
契約情報のIDです -
顧客ID（B列）
サブスクリプション契約をしている顧客のIDです -
契約開始日（C列）
サブスクリプションが始まった日付です。 -
契約終了日（D列）
-
月額（E列）
月あたりの売上です。
データの並び替え
スプレッドシート上である計算をするのにデータの並び順が必要になります。
__顧客IDで昇順に並び替えた上で、同一の顧客IDの中でさらに契約開始日で昇順__に並び替えてください。
データの加工
上記のデータを元に、サブスクリプション管理をする上で必要な情報を付与していきます。
- Subscription Rate
サブスクリプション契約がその顧客にとって何個目のcontractかを表す列です。
if関数を用いて、もし上の行と同じ顧客IDであれば上の行のSubscription Rateに1を足して自身のSubscription Rateとする処理を記述します。2行目で例をとると
=if(B7=B6,F6+1,1)
になります。
-
Cancel Rate
どのcontractでチャーンしてしまったかを表す列です。ここの値が１となっており、かつ契約終了日が今日の日付より前のcontractに関してはチャーンと判定できます。
処理としては、該当の顧客のcontract数をcountif関数で算出し、そこからSubscription Rateを引きます。こうすることで一番古いcontractに1の値が入るSubscription Rateと逆に、最新のcontractに１を振ることができます。
2行目で例をとると
'=countif($B:$B,B7)+1-F7'
になります。 -
新規判定
そのcontractがその顧客にとって最初のcontractかを判定します。trueであれば1、falseであれば0を振ります。
今回の場合、Subscription Rateが1であれば新規とみなせます。
2行目で例をとると
=if(F7=1,1,0)
です。 -
更新判定
そのcontractが契約更新かどうかを判定します。Cancel Rateが1でなければ、次に続くcontractがあるとみなせるので、契約更新と言えます。2行目で例をとると
=if(G7>1,1,0)
となります。 -
離脱判定
そのcontractをもって顧客が離脱してしまったかどうかを判定します。Cancel Rateが1で、かつ契約終了日が今日の日付より前の日付（もう決着がついてしまっている）ようであれば離脱判定となります。
2行目で例をとると
=if(AND(G7=1,D7<today()),1,0)
集計用シート(Subscription Sheet)
整備したcontractの情報をもとに、各月のパフォーマンスを集計していきます。
月を並べる
算出したい月を手作業で並べていくこともできますが、可能であれば自動で月が塗り替わるようにしたいです。
その際はeomonth関数を使うと便利です。eomonth関数は EOMONTH(開始日, 月数) と記述することができますが、開始日を今日ととり、月数を1ヶ月ごとずらしていくことによって常に今月から過去12ヶ月が自動で並ぶといったような書き方が可能です。
その際、月数をいちいち並べると面倒なので、例えば12ヶ月分の月だけ並べるのであれば、A列に0から12の番号を並べ、それぞれを月数として参照するようにしましょう。
2行目を例にとると
=eomonth(today(),-A2)
といった式になります。
また、このままだと日付まで入ってしまうので表示形式の設定で
yyyy-MM
形式に変更しておきましょう。
※A列は見た目上邪魔なので、非表示にしておきましょう
顧客数と売上の集計
月末顧客数
その月に契約をしていた顧客数を集計します。契約開始日がその月の末日より前で、契約終了日がその月の末日より後であればその月に契約状態であったと言えるので、そういった式にします。
後ほどのチャーンレートの計算で、__月初の顧客数と月末の顧客数__の違いが重要になってきますので、それぞれ用意します。
さきほどeomonth関数で算出した日付は月末の日付なので、月末顧客数側をいじっていきます。
subscriptionシートのMonthがcontractシート側の契約開始日より後、契約終了日より前の場合はカウントしたいのでそのような式にします。
2行目を例に取ると
=countifs(contract!$C:$C,"<="&B2,contract!$D:$D,">="&B2)
です。
月初顧客数
月初顧客数は前月末の顧客数と一緒になります。
2行目で例にとると
=D3
です。シンプルです。
MRR
その月で生み出す売上を算出します。
簡単に言うと、その月契約状態となっているcontractの月額の総和となるのでそれを求めます。
2行目で例ととると
=sumifs(contract!$E:$E,contract!$C:$C,"<="&B9,contract!$D:$D,">"&B9)
です。
新規、更新、離脱の集計
sumifs関数を使って、その月に新規、更新、離脱が何件あったかを集計します。
新規顧客数
__契約開始日__が当月の月末より前で前月の月末より後で、新規判定が1の場合、その当月の新規契約とみなせます。
2行目で例を取ると
=sumifs(contract!$H:$H,contract!$C:$C,"<="&B2,contract!$C:$C,">"&B3)
といった式になります。
更新顧客数
__契約終了日__が当月の月末より前で前月の月末より後、そして更新判定が1の場合、その月の更新とみなせます。
2行目で例を取ると
=sumifs(contract!$I:$I,contract!$D:$D,"<="&B2,contract!$D:$D,">"&B3)
になります。
離脱顧客数
__契約終了日__が当月の月末より前で前月の月末より後、そして離脱判定が1の場合、その月の離脱とみなせます。
2行目の式は
=sumifs(contract!$J:$J,contract!$D:$D,"<="&B2,contract!$D:$D,">"&B3)
となります。
チャーンレートの集計
チャーンレートは一番一般的な集計の仕方であれば、__月初の顧客数/離脱した顧客数__となります。
そのレートを求めます。
2行目の式は
=G2/C2
です。
まとめ
今回は手持ちのデータとスプレッドシートで主要なサブスクリプション指標を算出するところまでをやりました。
次回はこれらのデータを用いて、サブスクリプションのコンディションがひと目で分かるダッシュボードをGoogle Data Studioを使って作っていきます。
それではみなさんごきげんよう。