マーケティングや事業経営上の重要指標である「LTV」。
算出には様々な計算式がありますが、今回はこのLTVをスプレッドシートで、月ごとに時系列で算出する方法を解説していきます。
月ごとに時系列でLTVを算出しておくことで、
- 特定期間経過時点でLTVが落ちている
- 特定の月の初回購買顧客のLTVが高い
など、LTVの動きを動的に管理・検知することができます。
時系列LTVの算出には、以下の画像のように「顧客ID（id）」「購入日（purchase_date）」「購入月（purchase_month）」「購入金額（purchase_amount）」が格納されているデータを元データとして想定します。
このダミーデータからスプレッドシートで時系列のLTVを算出していきます。
もし「購入日」しかデータがなく「購入月」を手動で算出する必要がある場合は、
=EOMONTH(購入日セル,-1)+1
として、「購入月」（”YYYY/MM/01″ のように1日で切り詰められた日付）を算出しておいてください！
時系列LTVをスプレッドシートで算出する手順
顧客IDごとに初回購入月を算出する
まずは、上記の元データに「初回購入月（first_purchase_month）」のカラムを付け足して、顧客IDごとの初回購入月を算出していきます。
E1セルに
=MINIFS($C:$C,$A:$A,$A2)
と関数を入力します。
E2セルをデータの最終行までコピペします。
MINIFS関数を使うことで顧客IDごとに初回購入月を算出するという流れです。
この初回購入月がLTV算出の起点月になります！
初回購入月ごとの顧客数を算出する
次に、初回購入月ごとに顧客数を算出します。
上記の画像のように横軸（1行目）に購入月、縦軸（B列）に経過月数を置いた表を作成します。
なお、「購入月」の値は”YYYY/MM/01″で、表示形式が”yyyy/mm”となっています。
購入月はD1セルに
=EOMONTH(C1,)+1
として、表の最終列までコピペしておくと月が増えていった場合でも楽に入力ができます。
C2セルに
=COUNTUNIQUEIFS(‘元データ’!$A:$A,’元データ’!$E:$E,C$1)
と関数を入力し、表の最終列までコピペします。
COUNTUNIQUEIFS関数で初回購入月ごとにユニークな顧客IDの数をカウントできます。
1年以内の時系列LTVを算出する
月ごとの顧客数がカウントできたら、1年以内の時系列LTVを算出していきます。
まず、C3セルに
=SUM(C4:C16)
と関数を入力し、当月～12ヶ月以内の部分を足し上げます。
続いて、C4セルに
=IFERROR(SUMIFS(‘元データ’!$D:$D,’元データ’!$E:$E,C$1,’元データ’!$C:$C,C$1)/C$2)
と関数を入力し、表の最終列までコピペします。
これにより、初回購入月を起点とした当月中の購入金額を顧客数で割ることで、1顧客あたりの当月中の平均購入金額がわかります。
ここからさらに、初回購入月を起点として1ヶ月以内～12ヶ月以内までの購入金額を算出していきます。
C5セル（1ヶ月以内の行）に
=IFERROR(SUMIFS(‘元データ’!$D:$D,’元データ’!$E:$E,C$1,’元データ’!$C:$C,EOMONTH(C$1,)+1)/C$2)
と関数を入力し、表の最終列までコピペします。
ポイントは、SUMIFS関数の「’元データ’!$C:$C」の条件を
EOMONTH(C$1,)+1
のようにEOMONTH関数とし、引数を空欄にすることです。
これにより、初回購入月の「翌月」が条件となるため、翌月の購入金額を合計することができます。
C6セル（2ヶ月以内の行）には、
=IFERROR(SUMIFS(‘元データ’!$D:$D,’元データ’!$E:$E,C$1,’元データ’!$C:$C,EOMONTH(C$1,1)+1)/C$2)
と関数を入力し、表の最終列までコピペします。
今度は、SUMIFS関数の「’元データ’!$C:$C」の条件を
EOMONTH(C$1,1)+1
のように引数に「1」を入力することで、初回購入月の翌々月の購入金額を合計できます。
このようにして、EOMONTH関数の引数を
- 3ヶ月以内：2
- 4ヶ月以内：3
- 5ヶ月以内：4
- 6ヶ月以内：5
・・・
のように1ずつ増やしていきます。
これで月ごとに時系列LTVを算出できました！
今回は1年以内（12ヶ月以内まで）での算出でしたが、経過月を増やしていくことで1年半以内・2年以内など長期のLTVも算出ができます。
まとめ
平均継続期間などからLTVを算出するケースもありますが、今回の例のように初回購買月と経過月数を元にして時系列でのLTVを算出することもできます。
月ごとに時系列でLTVを算出しておくことで、
- 特定期間経過時点でLTVが落ちている
- 特定の月の初回購買顧客のLTVが高い
など、LTVの動きを動的に管理し、異常値や離反の兆しなどを早めに検知することが可能です。
以下でご紹介している「F2転換率」の算出もあわせて活用することで、顧客のリピートや収益性をトラッキングでき、多角的に課題を抽出できます。
あわせてご覧ください！
