CSVから顧客生涯価値（LTV）を計算する方法（ステップ・バイ・ステップ）
取引 CSV から顧客生涯価値を計算するには、平均注文額、購入頻度、および顧客寿命を算出します。この3つを掛け合わせ、粗利益率を適用します。計算自体は単純です。しかし、どの顧客をチャーン（離脱）とみなすか、過去と未来のどちらに目を向けるかといった判断によって、その結果が実用的なものになるかどうかが決まります。
2つのチームが同じファイルに対して同じ数式を実行しても、3倍も異なる2つの数値が導き出されることがあります。どちらも間違いではありません。彼らは気づかないうちに、異なる質問に答えていたのです。
このガイドでは、必要なインプット、3つの段階に分けた手動での計算方法、そしてスプレッドシートによる方法が限界を迎えるポイントについて解説します。
始める前に必要なもの
必要なのは、取引レベルのエクスポートデータです。注文ごとに1行で、顧客識別子、注文日、注文額が含まれている必要があります。月ごとのサマリーでは機能しません。なぜなら、生涯価値とは本質的に、個々の顧客が時間の経過とともにどのように行動するかに関するものだからです。
数式を適用する前に2つの決定を下す必要があり、これらはどのような計算よりも結果を大きく左右します。
実績値（Historical）か予測値（Predictive）か。 実績値ベースの顧客生涯価値は、顧客がこれまでに実際に費やした金額を合計したものです。予測値ベースは、顧客が将来費やすであろう金額を推定します。実績値は根拠が明確ですが常に過小評価され、予測値は予算編成に役立ちますがリテンション（維持率）の仮定に完全に依存します。
何をもってチャーン（離脱）とするか。 サブスクリプションの場合、これは解約日となるため簡単です。都度課金型のビジネスには解約イベントがないため、ルールを決める必要があります。たとえば、12ヶ月間購入がない顧客を休眠顧客として扱う、といった具合です。期間（ウィンドウ）を決定し、明記しておきましょう。
優れた成果物は次のようになります。顧客ごとの総支出額、注文数、初回および最終注文日、そしてセグメントラベルが含まれるテーブルが得られます。その横には、利益率の前提条件が明記された主要な数値が配置されます。
手動で行う方法
オプション 1：平均注文額と購入頻度を算出する
取引ファイルを顧客識別子ごとにグループ化します。顧客ごとに注文額を合計し、注文数をカウントします。平均注文額は、全体の総売上を総注文数で割ったものです。購入頻度は、対象期間中の総注文数をユニーク顧客数で割ったものです。
ここでの罠は「期間」です。12ヶ月間で測定した頻度と、全履歴で測定した頻度は異なる数値であり、これらを1つの数式に混在させると無意味なものになってしまいます。まず期間を固定し、それを一貫して使用してください。
オプション 2：リテンションカーブを追加する
顧客寿命は、多くの人が推測で入力しがちな項目です。根拠のある数値を出すには、自社のデータを使用します。顧客を獲得月ごとにグループ化し、その後の各月に何人がアクティブなままであったかをカウントします。
これによりリテンションカーブが得られます。平均寿命はおおよそその曲線下の面積（スプレッドシートでは月次リテンション率の合計）になります。たとえば、100人の顧客が3ヶ月間でそれぞれ80%、65%、55%維持された場合、大雑把な数値を仮定するのではなく、月ごとに寿命を累積していくことになります。コホートリテンションチャートの作成方法に関するガイド（building a cohort retention chart）ではチャート作成の側面をカバーしており、コホート分析とは何かに関する記事（what cohort analysis is）ではその概念を説明しています。
オプション 3：LTVとLTV:CAC比率に変換する
平均注文額、購入頻度、顧客寿命を掛け合わせ、さらに粗利益率を掛けます。売上ベースの生涯価値は、低利益率の製品を高利益率の製品と同じように扱ってしまうため、数値を過大評価（時には大幅に）してしまいます。
次に、顧客獲得コスト（CAC）で割ります。この比率こそが、この数値を算出する目的です。単体の生涯価値は単なる豆知識にすぎませんが、獲得のために支払ったコストに対する生涯価値は、意思決定の材料になります。
手動ルートが限界を迎える理由
セグメンテーションを行うと、この方法は破綻します。単一のブレンドされた数値は、最良のコホートと最悪のコホートを平均化してしまい、情報価値のない数値になってしまいます。本当に有用なのは、チャネルごと、プランごと、または獲得月ごとの数値です。セグメントを分けるたびに、フィルタリングされたサブセットに対して一連の数式をすべて再構築する必要があります。通常、チェックする価値のあるセグメントは根気が続く限界を超えて存在するため、ほとんどがチェックされないまま放置されます。
再実行が2つ目の問題です。来四半期のファイルには行が増え、多くの場合、列名も変更されています。構築に半日かかったリテンショングリッドは、範囲を拡張し、数式を再チェックする必要があります。範囲の指定漏れは出力結果には見えないため、これが危険な原因となります。
そして、前提条件が見えなくなります。半年後、ファイルにはある数値が表示されていますが、それが売上ベースだったのか利益率ベースだったのか、あるいはどのチャーンウィンドウが使用されたのか、誰も覚えていません。こうして、古くなった数値が取締役会の資料に紛れ込むことになるのです。
さらに、より気づきにくい失敗もあります。モデルの再構築にはコストがかかるため、一度実行しただけで1年間引用され続ける傾向があります。生涯価値は一定ではありません。価格設定が変更されたとき、獲得チャネルの構成がより安価なチャネルにシフトしたとき、および製品の変更によってリテンションが変化したときに変動します。去年の春に計算された数値は、もはや存在しないビジネスの状態を示している可能性があります。
Powerdrill Bloom で顧客生涯価値を計算する方法
ステップ 1：データをアップロードする
取引 CSV を直接アップロードします。Powerdrill Bloom が列のプロファイリングを行うため、グループ化が行われる前に、使用可能な顧客識別子とクリーンな日付列があることを確認できます。
ステップ 2：自然言語で計算内容を説明する
同僚に説明するようにルールを伝えます。「12ヶ月間購入がない顧客を休眠顧客として扱い、この粗利益率を適用し、獲得チャネルごとに生涯価値を計算してください」といった具合です。その後、「上位25%と下位25%で数値がどのように異なるか」や「獲得コホート間でどのように推移したか」といった、重要な追加質問を投げかけます。
ステップ 3：チャート、レポート、またはスライド資料をエクスポートする
セグメントごとのテーブル、その背景にあるリテンションカーブ、または数値とともに前提条件が記録されたテキストのサマリーを取り出します。
毎四半期モデルを再構築するよりもこれが優れている理由
| スプレッドシートによる方法 | Powerdrill Bloom | |
|---|---|---|
| チャネルによるセグメンテーション | セグメントごとに数式チェーンを再構築する | リクエストに追加するだけ |
| チャーンウィンドウの変更 | リテンショングリッドを作り直す | 新しいウィンドウを指定して再度質問するだけ |
| 前提条件の記録 | 手動のメモ（紛失しがち） | リクエスト内に明記される |
| 来四半期のファイル | 範囲を拡張し、数式をチェックする | アップロードして再度質問するだけ |
2行目こそが、得られる知見を大きく変える部分です。12ヶ月のチャーンウィンドウと6ヶ月のものでは、非常に異なる生涯価値が算出される可能性があります。これに対処する誠実な方法は、両方を実行して結論が揺るがないかを確認することです。スプレッドシートでは、誰もそれを2回も行おうとはしません。
よくある間違い
利益率ではなく売上を使用する。 売上ベースの生涯価値は、あらゆるビジネスを過大に見せてしまい、獲得コストとの比較を著しく歪めます。
すべての顧客を1つの数値にブレンドする。 異なるセグメントが混在するベース全体の平均値は、実際の顧客の姿を反映していません。結論を出す前にセグメンテーションを行いましょう。
寿命を推測する。 「約2年」は最もよく使われる入力値ですが、最も根拠の薄いものです。たとえ期間が短くても、自社のリテンションカーブから導き出してください。
生存者効果を無視する。 実績値ベースの生涯価値は、すでに存在していた顧客のみをカウントするため、在籍期間の長い顧客に偏ってしまいます。コホートベースの計算を行うことで、これを回避できます。
CACなしで報告する。 生涯価値が$900であっても、獲得コストが$90なのか$800なのかが分からなければ、何の意味も持ちません。
数値を不変のものとして扱う。 価格設定、製品、または獲得チャネルの構成が大幅に変更された場合は、必ず再計算してください。昨年から引き継がれた数値は、小数点がついた単なる推測にすぎません。
結論
CSV から顧客生涯価値を計算するには、3つの段階を経ます。平均注文額と頻度、自社コホートからのリテンションカーブ、そして利益率と獲得コストに対する比率です。スプレッドシートで行う方法でも機能はします。セグメンテーションが必要になったり、異なるチャーンウィンドウをテストしたくなったりするまでは。
会議で誰かに求められたときにしかモデルを再構築しないのであれば、取引エクスポートデータを使って Powerdrill Bloom をお試しください。当社の CSV AI assistant ページではより幅広いワークフローをカバーしており、turning a CSV into a chart ではビジュアル出力について説明しています。
よくある質問
顧客生涯価値の計算式は何ですか？
平均注文額、購入頻度、平均顧客寿命を掛け合わせ、さらに粗利益率を掛けます。利益率ではなく売上を使用すると、結果が膨れ上がり、獲得コストとの比較の信頼性が失われます。
Excel で顧客生涯価値を計算するにはどうすればよいですか？
取引ファイルを顧客識別子ごとにグループ化し、注文数と合計額を取得します。コホートリテンショングリッドを構築して寿命を導き出し、3つのインプットを組み合わせます。リテンショングリッドの作成は手間のかかる作業であり、ウィンドウが変更されるたびに再構築する必要があります。
実績 LTV と予測 LTV の違いは何ですか？
実績値は顧客がすでに費やした金額を合計するため、常に過小評価されますが、根拠を説明しやすいです。予測値はリテンションの仮定から将来の支出を推定するため、計画策定にはより有用ですが、誤りが発生しやすくなります。
適切な LTV と CAC の比率はどれくらいですか？
一般的に言及される目標は3対1ですが、これは利益率の構造や回収期間によって異なります。より有用な問いは、顧客が獲得コストを回収するのに何ヶ月かかるか、ということです。
最近購入していない顧客はどのように扱えばよいですか？
チャーンウィンドウを定義し、それを一貫して適用します。都度課金型のビジネスでは、12ヶ月間購入がないことをルールとするのが一般的です。ウィンドウを変更すると生涯価値が大幅に変動する可能性があるため、数値の横にウィンドウを記録しておきましょう。