はじめに
ECの成長においてLTVは非常に重要なKPIですが、ECにおいては顧客寿命の予測が難しいため、集計・管理上の課題が多いです。コホートLTV分析は、こうした課題を解決する実用的な分析方法です。
この記事はLTV向上に取り組むECマーケティング担当者の方にむけて、Shopifyの注文データ(CSVファイル)から、Google SpreadsheetやExcelを使ってコホート形式でLTVを分析する方法について解説します。
手順が長くて面倒！という方は、ぜひ顧客セグメントツール”ECPower”をご利用ください。Shopify公式アプリの無料インストールはこちらから。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、セグメント単位にワンクリックでコホートLTV分析を行うことができます。
この記事で作成する内容についても、無料の範囲ですぐに表示できます！
この記事の対象と分析環境
- 記事内容は、LTVの基本的な考え方をご存知の方を想定して作成しています。
- Google Spreadsheetを例に作成しています。Excelをご利用の方も同様の手順で分析が可能ですが、一部関数名等が異なる場合があります。
- ECカート「Shopify」でのデータ項目等を前提として作成しています。他のカートシステムをご利用の方でもご参考いただける内容ですが、各カートのデータ項目に読み替えてください。
【前置き】コホートLTV分析とは？
コホートLTV分析について
コホート分析は、顧客を「コホート」というグループに分割し、各コホートの行動を時系列的に測定することにより、過去の注文データから実用的な示唆を得る手法です。
コホートでLTVを計算・分析する際は、顧客を初回購入時期（多くのケースでは「月」）に基づいてセグメント化し、1ヶ月、2ヶ月、….、Nヶ月目時点のLTVを計算します。
たとえばこちらは、あるストアの6か月間のShopifyのデータをもとにExcelで作成された簡単なコホート分析です。
コホート分析の詳細は下記の記事で説明していますので、あわせてご参考ください。
一般的なLTVの計算方法の課題と、コホートLTV分析が優れているポイント
ECにおけるLTV計算の課題は、顧客寿命の予測が難しいという点に尽きます。
ECにおいては、いつ顧客の寿命が終わったかを判断することがとても難しいです。数か月間も音沙汰がなかった顧客が、何かのメールキャンペーンで戻ってきたり、逆に１ヶ月間何度も購入をしてくれていた顧客が翌月以降にはまったく購入しなくなったりなど、ECマーケターであればよく目にするケースなのではないかと思います。
たとえばこちらの記事ではShopifyデータ×ExcelでLTVを計算する最も基本的な方法を紹介していますが、「すべての顧客は現時点で顧客寿命を終えている」という仮定をすることで無理やりLTVの数値を計算しています。
このケースでは、たとえば直近数ヶ月定期的に商品を購入していたリピート顧客も、今後一切購入を行わない、と一律・機械的に仮定していることになるので、実用的な手法ではありますが数字としての信頼度は高くないです。
一方で、過去のデータに基づいて合理的に顧客寿命の予測を行うことは、技術的には可能ですが、大規模ストアレベルの大量のデータが必要ですし、実務的観点からは現実的ではありません。
コホートLTV分析は、予測を含まない過去の正確なデータにのみ基づいており、「最初の購買からXヶ月目のLTV」を最終的なLTVの”先行指標”として扱うことによって、より信頼度高く、実用的にLTVを集計・管理することを可能にする手法です。
【前準備】Shopifyから「顧客」「注文」の２つのCSVをダウンロードして整形する
Shopifyから顧客・注文データをダウンロードし、スプレッドシート上で下記のような形にデータを整形してください。
データのダウンロード方法や、データ整形の方法はこちらの記事で詳しく解説をしています。４章・５章をご参考ください。
参考記事：Shopifyデータ×Excel／スプレッドシートでLTVを計算し、平均注文金額・購入頻度・顧客寿命の観点から分析する方法
顧客ごとに初回購入月を計算する
今回は初回購入月によってコホート（顧客グループ）を分けたいので、各顧客の初回購入月を計算します。
顧客シートのD1, E1セルに「初回購入日」「初回購入月」と名前をつけて、ひとつ下のD2, E2セルに下記の関数を入力してください。
D2セル
=MIN(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = A2))
※サンプルデータでは、注文データのシートを「注文シート」という名前にしています。必要に応じて関数を編集してください。
E2セル
=TEXT(D3, "yyyy-mm")
下記のようになっていれば完成です。
注文ごとに初回購入からの経過月数を計算する
注文ごとに、その顧客の初回購入からの経過月数を計算します。たとえば初回購入が4/1の顧客が、7/1におこなった注文は、経過月数=3となります。
また、今後の表計算で使用するので、初回購入月のデータ列も追加しておきます。
注文シートのD1, E1列にそれぞれ「初回購入月」「初回購入からの経過月数」と名前をつけて、一つ下のD2, E2列に下記の関数を入力してください。
D2セル
=vlookup(A2,'顧客シート'!A:E,5,false)
E2セル
=datedif(vlookup(A2,'顧客シート'!A:D,4,false),C2,"M")
コホート表を作成する
それでは実際にコホート表を作成します。新しくシートを作成して「コホートLTV表」などの名前をつけてください。
まず、分析をおこなう日をB2セルに入力してください。
この日を基準にして表示するデータを決めるのですが、理由の説明が複雑なので、良く分からないという方は読み飛ばして頂いて大丈夫です。
Xヶ月LTVが確定できるのは、現時点(月)を基準としてX-1月初回購入のコホートまでです。たとえば現在が4/1であれば、1ヶ月LTVが確定できるのは2月のコホートまでになります。3月のコホートは、例えば3/15に初回購入した顧客の１ヶ月LTVを確定できるのが4/15なので、4/1時点では1ヶ月LTVを確定することができません。ですので、分析日(今日)を基準に、確定できる箇所のみ表を作成するために、分析日の設定が必要になります。
少し行をあけて、「初回購入月」の列を作成します。この例ではA4から作成しました。
たとえば下記のようにして、初回購入月を重複を除いて一覧化します。
A4セル
=unique('顧客シート'!E:E)
各初回購入月ごとに新規顧客数を集計してみましょう。B4セルに「新規顧客数」と名前をつけ、B5セルに下記の関数を入力します。
B5セル
=countif('顧客シート'!E:E,A5)
下までオートフィルを行ってください。下記のようになっていれば完成です。
次に、上記のように見出し行を作成してください。
表計算の都合上、0, 1, 2, …という数値データの見出し行が必要です。ただし、それだと分かりづらいので、この例ではひとつ上の行にXヶ月LTVというテキストデータを追加しています。
最後に、表の各セルにデータを集計します。まずC5セル（左上）に下記の関数を入力して、表全体にオートフィルを行ってください。
C5セル
=if(C$4+1<datedif($A5,$B$1,"M"),sumifs('注文シート'!$B:$B,'注文シート'!$E:$E,"<="&C$4,'注文シート'!$D:$D,$A5)/$B5,"")
エラーが出てしまう場合、datedif関数が分析日を正しく参照できているか確認してください。上記の例では、B1セルを参照しています。
下記のようになっていれば、コホートLTV表の完成です。
コホートLTV表を分析する
コホートLTV表の基本的な分析のしかたをご紹介します。
左→右にLTVの推移を確認し、どのタイミングでLTVが横ばいになっているかを確認
任意のコホートについて、どのタイミングでLTVが頭打ちになるかどうかが分かります。たとえば下記のサンプルデータの2021-05のコホートを見ると、10ヶ月LTV以降はほぼ頭打ちになっていることが分ります。
基本的な考え方として、頭打ちになるタイミングが遅いほど、平均的な顧客寿命が長いという解釈になります。もし、現時点では３ヶ月付近で頭打ちになっている場合は、頭打ちになるタイミングを４か月目、５ヶ月目と伸ばせるように顧客寿命にフォーカスした施策を実施することで、LTVの向上につながります。
上→下でLTVの数値を比較し、同じXヶ月LTVが時系列的にどう推移しているかを確認
任意のXヶ月LTVについて、コホート間で比較をすることで、LTVが向上しているかどうかを判断することができます。たとえば下記のサンプルデータの3ヶ月LTVを見ると、20,000円前後から22,000円付近まで、若いコホートほど（下にいくほど）数値が向上していることが分ります。
全期間でのLTV向上を目指すにあたって、先行指標としてXヶ月LTVの改善を行うことはとても重要です。
特徴的なコホートを見つけ、どんな施策が効果的だったのかを振り返る
コホート間を比較すると、特別に数字がよかったコホートを見つけることができます。
たとえば、その月に何かのキャンペーンを実施していたり、Xヶ月目のタイミングで特定の新商品を発売したりなど、どんなイベントがそのコホートのLTV向上に寄与していたかを振り返って分析することで、再現性を持ったLTV施策の立案に繋げることができます。
数字のよかったコホートは、「ヒートマップ表示」を行うことでうまく可視化することができます。ヒートマップ表示はスプレッドシートの条件付き書式から設定することができます。
下記の例では、2021-08, 2021-12, 2022-04, 2022-05のコホートが特徴的なので、深く分析してみる価値がありそうです。
最近のコホートの成長をモニタリングし、特に注力すべきコホートを見極める
まだ成長途中のコホートが、過去の平均とくらべて良好なのか注意が必要なのかを判断することができます。
たとえば、下記のような折れ線グラフを作成してみます。灰色の点線が過去１年間の平均値です。最近のコホートはおおむね過去の平均線より上側を推移していることがわかります。これは、大雑把に言うと１年前よりLTVが向上しているということを示しています。
一方で、2022-08のコホートを見ると、平均を大きく下回っていることが分りますね。何らかの問題があったのではないかと推測できます。そして、最も注目すべきはまだ途中までしか伸びていない2023-01のコホートです（赤の矢印）。このコホートは平均を大きく下回って成長する兆しが見えるので、2023年1月の新規顧客に注意を払い、適切な施策を講じる必要があると判断できます。
LTVが時間軸でどのように分布するかを把握して、キャッシュ回収サイクル(Payback Period)を最適化する
新規顧客獲得に広告投資を行っている場合、各月の1人あたり顧客獲得コスト(CAC)とコホートLTV表を見比べることで、どのくらいのサイクルでキャッシュ回収ができているかを把握することができます。
これは「平均LTV」の数字だけでは把握することが難しく、時間軸でどのようにLTVが実現していくのかを可視化することができるコホート表ならではの分析です。
たとえばサンプルデータの例では、2021年05月に新規顧客1人あたり20,000円のCACだったとします。3カ月LTVが21,507円なので、およそ3カ月でキャッシュ回収ができていることがわかります。
最終的にキャッシュ回収ができていたとしても、半年以上かかっている場合などはあまり健全なキャッシュフローとは言えないでしょう。たとえば３カ月以内のキャッシュ回収を目標として3カ月LTVをモニタリングしたり、逆にXヶ月LTVにあわせて広告投資額を見直すといった検討を行うとよいです。
まとめ
この記事では、より信頼度高く、実用的にLTVを集計・管理することを可能にする手法であるコホートLTV分析について、Shopifyデータを使ってスプレッドシートで分析する方法について解説しました。ひとつの表から様々な情報を読み取ることができる便利なツールですので、ぜひご活用ください。
顧客セグメントツールECPowerは、今回紹介したコホートLTV表をワンクリックで作成することができます。さらに、顧客セグメントを作成してセグメント単位ごとにコホート分析を行うことができます。Shopify公式アプリの無料インストールはこちらから。
