このチュートリアルのゴール
はじめに：なぜD2Cではコホート表が要るのか
SaaS企業のLTV/CAC分析は、月額課金の解約率（チャーン）を基準に組むのが定石です。基本的な考え方はLTV・CACやLTV/CACの基本解説で扱われているので、本チュートリアルでは再説明しません。D2Cの定期購入ブランドが同じ発想を使う場合に厄介なのは、月額固定ではなく「注文のたびに原価・配送費・決済手数料がかかる物販」だという点です。売上が増えても、原価と配送費を引いた後の貢献利益がいくら残るかは商品ごとに違います。だからLTVを売上だけで計算すると、実態より大きく見えてしまう危険があります。
この違いを数字で確認する土台になるのが「獲得月別コホート表」です。何月に獲得した顧客が、何か月後にどれだけ残っていて、1件あたりいくらの貢献利益を生んでいるか。縦に獲得月、横に経過月を並べた表を組めば、広告費（CPA）をどこまでかけていいかの答えである「許容CPA」まで一本の数式でつながります。売上予測とドライバー設計で触れている「単一の成長率に頼らない」考え方の、D2C版の実践だと捉えてください。
| 用語 | ひとことで言うと |
|---|---|
| CPA | 新規顧客1人を獲得するのにかけた広告費 |
| 残存率 | 獲得時点を100%として、経過月ごとに何%の顧客が注文を続けているか |
| 貢献利益 | 売上から原価・配送費・決済手数料・販促費を引いた、1件あたりの取り分 |
| LTV | 1人の顧客が生涯にわたって生む貢献利益の合計 |
| CAC回収月数 | 累積の貢献利益がCPAに達するまでにかかる月数 |
| 許容CPA | LTVから逆算した、広告費としてかけていい上限額 |
完成形の全体像
今回組むシートは7つのブロックでできています。上から順に組めば、最後は自動的に許容CPAまでつながります。
| B | C | D〜H | |
|---|---|---|---|
| 4-11 | ① 前提ブロック | 単価・原価率・CPA・目標倍率など8個の入力 | |
| 13-14 | ② 単価あたり貢献利益 | 初回・継続の2値 | |
| 17-18 | ③ 経過月・残存率カーブ | 経過月0 | 経過月1〜5 |
| 21-23 | ④ コホート別 継続顧客数 | 獲得月ごとに1行（4月・5月・6月） | 経過月0〜5へ横展開 |
| 26-28 | ⑤ コホート別 貢献利益 | ④×単価あたり貢献利益 | |
| 32-39 | ⑥ 顧客1人あたりの指標 | LTV・CAC回収月数・許容CPA | |
| 44-48 | ⑦ 月次事業計画への集計 | 特定月の売上合計・貢献利益合計 |
設例のブランド：単品の消耗品を定期購入で販売する、架空のD2Cブランドです。初回は新規客向けの割引クーポンを使い、2回目以降は定価で購入が続く想定にしています。4月に定期便の販売を開始したばかりで、3月以前の獲得顧客はいない前提です。また、定期便は毎月1回お届けし、継続中の顧客は毎月1件ずつ注文する（スキップや複数個注文はない）前提にしています。数字はすべて説明用の設例で、実在のブランドの実績ではありません。
1前提ブロックを作る（5分）
目的：単価・原価率・CPAなど、あとで何度も参照する数字を1か所にまとめます。触るのはここだけ、という状態を作るのが最初の作業です。
| B | C | |
|---|---|---|
| 4 | 初回受注単価 | 6,000 |
| 5 | 継続受注単価 | 5,000 |
| 6 | 原価率 | 30% |
| 7 | 配送費（1件あたり） | 700 |
| 8 | 決済手数料率 | 4% |
| 9 | 初回販促費（クーポン等） | 500 |
| 10 | CPA（新規顧客獲得単価） | 4,000 |
| 11 | 目標LTV/CAC倍率 | 3 |
つまずき：「4%」はパーセント記号ごと入力してください。「4」と打ってからパーセント表示にすると400%になる事故が定番です。
2経過月と残存率カーブを作る（5分）
目的：チャーンレート・NRRはSaaSの月次解約率や、解約と既存顧客の増額を差し引きしたNRRで語られますが、D2Cの物販では「獲得月を100%として、何か月後に何%の顧客がまだ注文しているか」という残存率で置くのが実務的です。1回でも離脱した顧客が戻ってくる前提は置かず、単調に下がる曲線にします。
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 17 | 経過月 | 0 | 1 | 2 | 3 | 4 | 5 |
| 18 | 残存率 | 100% | 44% | 34% | 26% | 22% | 20% |
3コホート別 継続顧客数を組む（10分）
目的：獲得月ごとの新規獲得数に残存率を掛けて、経過月ごとに何人が注文を続けているかを表にします。4月・5月・6月の3コホートを例にします。
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 21 | 4月獲得 | 500 | =$C21*D$18 | 170 | 130 | 110 | 100 |
| 22 | 5月獲得 | 550 | =$C22*D$18 | 187 | 143 | 121 | 110 |
| 23 | 6月獲得 | 600 | =$C23*D$18 | 204 | 156 | 132 | 120 |
期待値：4月獲得コホートの経過月0〜5は 500・220・170・130・110・100人。5月獲得コホートは 550・242・187・143・121・110人。6月獲得コホートは 600・264・204・156・132・120人になります。
4単価あたりの貢献利益を計算する（5分）
目的：限界利益・貢献利益とは、売上から原価だけでなく配送費・決済手数料・販促費まで引いた、1件あたりの正味の取り分です。物販のD2Cでは原価率だけを見て粗利を語ると、配送費と決済手数料の分だけ過大評価になります。この2つは固定費・変動費の分解で言えば、注文件数に比例する変動費です。
| B | C | |
|---|---|---|
| 13 | 初回貢献利益 | =C4-C4*C6-C7-C4*C8-C9 |
| 14 | 継続貢献利益 | =C5-C5*C6-C7-C5*C8 |
5コホート別 貢献利益表を組む（10分）
目的：STEP3の継続顧客数に、経過月0は初回貢献利益、経過月1以降は継続貢献利益を掛けます。人数と単価が別の表になっているので、掛け合わせるだけです。
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 26 | 4月獲得 | =C21*$C$13 | =D21*$C$14 | 442,000 | 338,000 | 286,000 | 260,000 |
| 27 | 5月獲得 | 1,518,000 | 629,200 | 486,200 | 371,800 | 314,600 | 286,000 |
| 28 | 6月獲得 | 1,656,000 | 686,400 | 530,400 | 405,600 | 343,200 | 312,000 |
=C22*$C$13で1,518,000円、C28は=C23*$C$13で1,656,000円になります（行が相対参照なので個別入力は不要です）。4月獲得コホートの6か月累計は3,278,000円。これを新規獲得数500人で割ると6,556円になり、STEP6で計算する顧客1人あたりのLTVと一致します（コホート表と1人あたり指標がつながっている検算）。6顧客1人あたりのLTVとCAC回収月数を出す（10分）
目的：1人の平均的な顧客を主語にして、残存率×貢献利益を経過月ごとに積み上げ、累積がCPAを超える月を探します。それがCAC回収期間、本記事での「CAC回収月数」です。CAC Payback Periodの基本解説もSaaSの月額課金を前提にした説明なので、ここでは物販の貢献利益ベースで組み直します。
| B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|
| 32 | 1人あたり貢献利益 | =C18*$C$13 | =D18*$C$14 | 884 | 676 | 572 | 520 |
| 33 | 累積貢献利益 | =C32 | =C33+D32 | 4,788 | 5,464 | 6,036 | 6,556 |
| 34 | CPA未達フラグ | =IF(C33<$C$10,1,0) | 1 | 0 | 0 | 0 | 0 |
| B | C | |
|---|---|---|
| 35 | CAC回収月数 | =COUNTIF(C34:H34,1) |
| 36 | LTV（貢献利益ベース） | =H33 |
つまずき：COUNTIF(C34:H34,1)は「1の個数」を数えているだけです。累積貢献利益が単調に増える設計なので、この個数がそのまま回収月数になります。0になった場合は34行目の不等号の向き（>と<の取り違え）を疑ってください。6（＝列数）になった場合は必ずしも式の誤りではなく、「経過月5までに回収できていない」という正しい結果のこともあります（例えばCPAをもっと高い金額に変えた場合など）。
7許容CPAを逆算し、売上ベースLTVの罠を確認する（10分）
目的：LTVが分かれば、広告費としてかけていい上限「許容CPA」が決まります。あわせて、貢献利益ではなく売上でLTVを計算するとどれだけ甘い数字になるかを、同じ表で確認します。
| B | C | メモ | |
|---|---|---|---|
| 37 | 許容CPA（貢献利益ベース） | =ROUNDDOWN(C36/C11,0) | 正しい上限 |
| 38 | LTV（売上ベース・誤り例） | =C4+SUM(D18:H18)*C5 | 原価・配送費・決済手数料を引いていない |
| 39 | 許容CPA（売上ベースの誤り例） | =ROUNDDOWN(C38/C11,0) | 過大評価 |
期待値：許容CPA 2,185円／売上ベースLTV 13,300円／売上ベースの許容CPA 4,433円。売上ベースのLTVは貢献利益ベースの約2.03倍です。
8月次の事業計画へ集計する（5分）
目的：コホート表は「1人あたり」「獲得月ごと」の視点ですが、経営会議で見るのは「その月の売上合計」です。ある月の売上は、その月に獲得した新規顧客の初回注文と、過去の複数コホートからのリピート注文の合計になります。本設例は4月に販売を開始しているため3月以前のコホートは存在せず、6月度の売上は4月・5月・6月の3コホートを合計すればすべてです。
| B | C | |
|---|---|---|
| 44 | 6月度 新規売上（6月コホート・経過月0） | =C23*$C$4 |
| 45 | 6月度 リピート売上（5月コホート・経過月1） | =D22*$C$5 |
| 46 | 6月度 リピート売上（4月コホート・経過月2） | =E21*$C$5 |
| 47 | 6月度 売上合計（月次事業計画へ） | =SUM(C44:C46) |
| 48 | 6月度 貢献利益合計（STEP5表との検算） | =C28+D27+E26 |
どのコホートの何列目を足すかは「対象月−獲得月＝経過月」で決まるので、この3コホートの例では手作業で列を選べます。コホート数が増えて自動化したい場合は、B列とは別にA21:A23へ獲得月の番号（4・5・6）を追加し、「獲得月＋経過月＝対象月」を満たすセルだけを合計するSUMPRODUCT式に置き換えます。6月度なら次の式になります：=SUMPRODUCT(($A$21:$A$23=6)*$C$21:$C$23)*$C$4+SUMPRODUCT(($A$21:$A$23+$D$17:$H$17=6)*$D$21:$H$23)*$C$5（結果はC47と同じ5,660,000円）。経過月ではなく暦月で列を並べ替えた表に作り直せば、SUMIFS関数でも集計できます。
なお、STEP1の図1で触れたとおり、本記事の新規売上（C44）はクーポン適用前の定価6,000円で計上しています。会計上はクーポン値引きを売上から控除するのが一般的なので、決算数値と突き合わせる場合は新規売上を(6,000−500)×600＝3,300,000円、6月度売上合計を5,360,000円として読み替えてください。貢献利益（C48）とLTVの金額は、値引きを販促費として引いているため、どちらの計上方法でも変わりません。
遊んでみる：前提を動かす実験3つ
完成したモデルは、前提（青字のセル）を変えて初めて道具として使えます。3つとも実際に手を動かして、LTV・CAC回収月数・許容CPAがどう動くか確認してください。
- 実験①：初期解約が改善したら？ 経過月1の残存率（D18）を44%→54%に。LTVは6,556円→6,816円に上がるだけでなく、CAC回収月数も経過月2→経過月1に短縮します。許容CPAも2,185円→2,272円に上がります。「最初の1か月の定着」が数字全体に効くことが分かります。
- 実験②：CPAが上がったら？ C10を4,000円→5,000円に。LTVは6,556円のまま変わりませんが、CAC回収月数は経過月2→経過月3に後ろ倒しになります。LTVと許容CPA（2,185円）は変わらないので、広告費だけが先に上がった分、投資効率は悪化しています。
- 実験③：配送費が上がったら？ C7を700円→1,200円に（配送会社の値上げを想定）。初回貢献利益は2,760円→2,260円、継続貢献利益は2,600円→2,100円に下がり、LTVは6,556円→5,326円に縮みます。CAC回収月数も経過月2→経過月3に後ろ倒しになり、許容CPAは2,185円→1,775円まで下がります。原価率だけを見ていると、配送費の変動を見落とします。
よくあるミス
- コホート表の列を暦月で揃えてしまい、行ごとに参照列がずれて数式が崩れる。経過月で揃える設計に戻すのが最短の直し方です。
- 貢献利益に配送費や決済手数料を含めず、粗利率だけでLTVを計算してしまい、STEP7のように許容CPAを過大に見積もる。
- CAC回収月数が短いことだけを見て投資判断をし、LTV/CAC倍率（本記事の設例では1.64倍）を確認しない。回収できることと、割に合う投資であることは別の指標です。
- $の付け忘れ。STEP3の$C21やSTEP5の$C$13のように、コピー時に固定したい列・行を意識してF4キーで$を付けてください。
よくある質問（FAQ）
Q. なぜOFFSETやINDEXを使わない設計にしているのですか？
A. コホート表の列を経過月で揃えれば、どの行でも同じ列位置が同じ経過月を指すため、単純な相対・絶対参照のコピーだけで表が完成します。カレンダー月で列を揃える（行ごとに開始列がずれる階段状の）設計にすると行ごとに参照列がずれるため、OFFSETやINDEXのような動的な参照が必要になり、数式の意味を追いにくくなります。本チュートリアルでは可読性を優先しました。
Q. 残存率はどうやって決めればいいですか？
A. 実務では、過去に獲得した顧客の実際の注文履歴から、経過月ごとに何%が注文を継続したかを集計して作ります。集計の考え方はSTEP2で触れたコホート分析の一般論と同じです。本記事の残存率は理解のための設例値であり、特定のブランドの実績ではありません。
Q. 目標LTV/CAC倍率の「3倍」は絶対的な基準ですか？
A. いいえ。SaaSでよく語られる目安であり、本記事ではD2Cの設例に同じ倍率を当てはめて計算方法を示しているだけです。実際の目安は資金繰りの余裕やCAC回収月数の許容範囲によって会社ごとに異なります。
Q. 経過月6以降のリピートは無視していいのですか？
A. 本チュートリアルは説明を単純にするため経過月5までで打ち切っていますが、実務ではより長い期間や、残存率が一定の比率で下がり続けると仮定した式に拡張して計算します。今回の骨格（残存率×貢献利益の積み上げ）はそのまま延長できます。
まとめ
- コホート表は「縦＝獲得月、横＝経過月」で列を揃えると、OFFSETやINDEXを使わず単純なコピーだけで組めます。
- LTVは貢献利益（売上−原価−配送費−決済手数料−販促費）ベースで計算しないと、許容CPAを過大に見積もります。
- CAC回収月数が短くても、LTV/CAC倍率が低ければ広告投資は過大です。両方の指標をセットで見る癖をつけてください。
出典・参考（2026年9月確認）
本記事の設例（受注単価・原価率・配送費・決済手数料率・残存率・CPAなどの数値）はすべて説明用の仮設例であり、実在のブランドの実績や統計に基づくものではないため、出典はありません。LTV/CAC・CAC回収期間・コホート分析の一般的な考え方については、本文中でリンクした既存記事（LTV/CACの基本解説・CAC Payback Periodの解説・コホート分析の実務）を参照してください。
※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための仮設例です。