はじめに
ECの成長においてLTVは非常に重要なKPIですが、ECにおいては顧客寿命の予測が難しいため、集計・管理上の課題が多いです。この記事では、サブスクリプションサービスの考え方である「離脱率」を用いて顧客寿命を合理的に推測し、将来の購入までを考慮したLTVを計算する方法を説明します。
この記事は、下記の記事の内容を前提とした、少し発展的な内容について説明をしています。まずは下記の記事からご確認いただけるとよりよいです。
手順が長くて面倒！という方は、ぜひ顧客セグメントツール”ECPower”をご利用ください。Shopify公式アプリの無料インストールはこちらから。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、セグメント単位にワンクリックでコホートLTV分析を行うことができます。
この記事の対象と分析環境
- 記事内容は、LTVの基本的な考え方をご存知の方を想定して作成しています。ECにおけるLTVについては、下記の記事で詳しく説明しています。
- Google Spreadsheetを例に作成しています。Excelをご利用の方も同様の手順で分析が可能ですが、一部関数名等が異なる場合があります。
- ECカート「Shopify」でのデータ項目等を前提として作成しています。他のカートシステムをご利用の方でもご参考いただける内容ですが、各カートのデータ項目に読み替えてください。
この記事で紹介する方法の概要と、説明のステップ
一般的なLTVの計算方法は、下記のとおりです。
平均注文金額 × 購入頻度[回／月] × 顧客寿命[月]
ここで問題となるのが、顧客寿命を計算する難しさです。
ECにおいては、いつ顧客の寿命が終わったかを判断することがとても難しいです。数か月間も音沙汰がなかった顧客が、何かのメールキャンペーンで戻ってきたり、逆に１ヶ月間何度も購入をしてくれていた顧客が翌月以降にはまったく購入しなくなったりなど、ECマーケターであればよく目にするケースなのではないかと思います。
顧客寿命に関する最も簡単な手法としては「手元にあるデータの初回購入～最後の購入までの期間を顧客寿命と定義する」ことですが、直近数ヶ月定期的に商品を購入していたリピート顧客も、今後一切購入を行わない、ということになるので、実態からかけ離れてしまう可能性があります。
一方で、契約開始～契約終了のタイミングを確定することができるサブスクリプションサービスにおいては、「解約率」から合理的に顧客寿命を予測する手法が用いられています。それは、下記のような計算方法です。
LTV ＝ 平均顧客単価/月 ÷ 月次解約率
ここでは、顧客寿命 ＝ 1 ÷ 月次解約率 という関係性になります。
なお、月次解約率は下記のような式で計算します。
月次解約率 ＝ 月間解約数 ÷ 月初の顧客数
今回の記事では、サブスクリプションサービスの考え方に倣って、ECの解約率を疑似的に計算して、一定の合理性をもった顧客寿命を推定してLTVを計算する方法をご紹介します。
説明のステップとしては、まずはじめに各顧客の契約開始～解約のタイミングを決めたうえで、さきほどの数式に従って計算を行う形とします。
【前準備】Shopifyから「顧客」「注文」の２つのCSVをダウンロードして整形する
Shopifyから顧客・注文データをダウンロードし、スプレッドシート上で下記のような形にデータを整形してください。
データのダウンロード方法や、データ整形の方法はこちらの記事で詳しく解説をしています。４章・５章をご参考ください。
参考記事：Shopifyデータ×Excel／スプレッドシートでLTVを計算し、平均注文金額・購入頻度・顧客寿命の観点から分析する方法
データの集計
まず、各顧客の注文データに基づいて、疑似的に「契約開始」「契約終了」のタイミングを集計します。何をもって終了とするかは難しい論点ですが、今回は各顧客の「最初の注文日」と「最後の注文日」を利用することにします。
（もしかするとこの時点で、勘のいい方であれば記事のオチが想像できるかと思いますが、お付き合いください。）
顧客ごとの契約開始日・契約終了日を集計する
顧客シートのD1、E1セルに、それぞれ「契約開始日」「契約終了日」と名前をつけます。
さらに、一つ下のD2、E2セルにそれぞれ下記の数式を入力し、全体にオートフィルしてください。
D2セル
=MIN(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = C2))
E2セル
=MAX(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = C2))
下記のようになっていれば大丈夫です。
集計のために、注文データに購入月の列を追加する
このあとの計算のために、注文データに購入月の列を追加しておきます。
注文シートのD1セルに「購入月」と名前をつけて、一つ下のD2セルに下記の数式を入力してオートフィルしてください。
D2セル
=text(C2,"yyyy-mm")
このようになっていれば完了です。
解約率とLTVの計算
月次解約率表の作成
それでは、解約率を計算するための表を作成する新しいシートを作成してください。
下図のような空の表を作成してください。横のラベルが「購入月」「月初顧客数」「月間解約顧客数」「解約率」で、縦のラベルが月です。
月に関しては、下記の数式をA1セルに入れると簡単に作成することができます。
A1セル
=unique('注文シート'!D:D)
さらに、B2, C2, D2, E2セルにそれぞれ下記のような数式を入力し、オートフィルしてください。
B2セル
=COUNTIFS('顧客シート'!$D:$D,"<"&A2) - COUNTIFS('顧客シート'!$E:$E,"<"&A2,'顧客シート'!$E:$E,"<>")
C2セル
=COUNTIFS('顧客シート'!$E:$E,">="&A2,'顧客シート'!$E:$E,"<"&EOMONTH(A2,0)+1)
D2セル
=sumif('注文シート'!D:D,A2,'注文シート'!B:B)/(B3+C2)
E2セル
=C2/B2
下図のようになっていれば完成です。これで、各月の疑似的な解約率を算出することができました。
LTVの計算
各月の顧客単価と解約率を平均することで、期間全体での平均値を算出しましょう。
このときに注意点があります。この計算をする際に「契約終了日」を「最後の注文日」にしていることから、現在アクティブな顧客についても解約済みという処理をしてしまっています。ですので、現在日時（分析を行っている日時）から近いほど解約率が高くなっているはずです。
こちらはあまり正確な数字ではないので、全体を見て安定している範囲の解約率を平均しましょう。
サンプルデータのケースでは、下図のように2021/8～2022/12の解約率が安定しているように見えるので、その範囲を平均することにしました。
この数値をもとに、下記の式に従ってLTVを計算してみます。
LTV = 顧客単価/月 ÷ 月次解約率
※顧客寿命 ＝ １ ÷ 月次解約率 です。
このように、解約率の考え方を用いてLTVを計算することができました。
こちらの記事で紹介している”最も簡単な方法”と比べて、いかがだったでしょうか。
手元のサンプルデータでは、正直なところ誤差レベルかな？という差しかありませんでした。
この方法の問題点
この方法で議論の余地があるポイントは、実は「契約終了日」を「最後の注文日」にしていることです。ここの決め方次第で、大きく数値が異なってしまうのです。
たとえば「最後に注文をしたときは、まだ”商品に魅力を感じている顧客”のはず。そこから数週間、数か月経ったタイミングで解約とすべきだ」といった議論はすぐに思いつきます。
仮に、一律に1ヶ月、”契約終了日”のタイミングを遅らせたとしましょう。この記事のサンプルデータのケースで計算をしなおすと、月次解約率の数字は17%ほどに下がり、LTVは26,059円から30,355円に上がりました。
しかし、このようにタイミングをずらすとして、どのくらいの期間が適切と言えるのでしょうか？
たとえば平均的なリピート日数が2週間のストアでは14日が適切かもしれないですし、半年のストアでは半年が適切かもしれません。任意に決めた数字でLTV数値がこうも大きく変わってしまうのであれば、もはや信頼性のある指標とは言えなくなります。
ですので、結局「ECは顧客が解約(離反)するタイミングが分からないからこそ、顧客寿命を予測することが難しい」という結論に堂々巡りしてしまうのです。
「契約終了日」を「最後の注文日」に見立てて解約率を算出し、顧客寿命を合理的に予測してLTVを計算する、という手法自体は一定の正確性を持ちますが、完璧な方法ではないということを認識しておくことが大切です。
まとめ
この記事では、サブスクリプションサービスの考え方である「離脱率」を用いて顧客寿命を合理的に推測し、将来の購入までを考慮したLTVを計算する方法を説明し、ECビジネスに適用するうえでの問題点を指摘しました。ストアによっては、「簡単な手法で計算した数値」と、今回の「顧客寿命の予測をふまえた数値」がほとんど変わらないこともあるかと思います。
LTVを計算する目的と用途にもよりますが、”正確”なLTV値を計算することに苦心するよりもはるかに重要なのは、LTVを要素に分解して課題を発見し、施策に繋げることです。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、LTVや平均注文単価、購入頻度といったKPIをセグメント単位に把握できるので、LTV向上にむけた打ち手を立案することができます。Shopify公式アプリの無料インストールはこちらから。
