はじめに
ECの成長においてLTVは非常に重要なKPIです。この記事は、LTV向上に取り組むECマーケティング担当者の方にむけて、Shopifyの注文データ(CSVファイル)から、Google SpreadsheetやExcelを使って実際にLTVを計算する実用的で最も簡単な方法と、LTVを平均注文金額・購入頻度・顧客寿命の要素に分解して分析する方法を解説します。
手順が長くて面倒！という方は、ぜひ顧客セグメントツール”ECPower”をご利用ください。Shopify公式アプリの無料インストールはこちらから。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、ワンクリックでセグメント単位でLTV、平均注文金額、購入頻度などのKPIを把握することができます。
この記事の対象と分析環境
- 記事内容は、LTVの基本的な考え方をご存知の方を想定して作成しています。ECにおけるLTVについては、下記の記事で詳しく説明しています。
- Google Spreadsheetを例に作成しています。Excelをご利用の方も同様の手順で分析が可能ですが、一部関数名等が異なる場合があります。
- ECカート「Shopify」でのデータ項目等を前提として作成しています。他のカートシステムをご利用の方でもご参考いただける内容ですが、各カートのデータ項目に読み替えてください。
【前置き】この記事で紹介するLTVの計算手順について
この記事におけるLTVの定義
今回採用するLTVの計算方法は、下記のとおりです。
平均注文金額 × 購入頻度[回／月] × 顧客寿命[月]
他にも粗利率を踏まえた定義など、いくつかの考え方がありますが、ECマーケターの実務上はこちらの計算式を使うのが実用的です。詳しくは、下記の記事をご参考ください。
参考記事：ECにおけるLTVとは？重要な理由と実践的な計算方法
「各顧客の合計注文金額を全体で平均する」が最も簡単なLTVの計算方法
先ほど紹介した式をいきなり覆すような形になりますが、少し解説をさせて頂きます。
平均注文金額 × 購入頻度[回／月] × 顧客寿命[月]
これが「各顧客の合計注文金額の全体平均」でよくなる理由は、顧客寿命を計算する難しさにあります。
ECにおいては、いつ顧客の寿命が終わったかを判断することがとても難しいです。数か月間も音沙汰がなかった顧客が、何かのメールキャンペーンで戻ってきたり、逆に１ヶ月間何度も購入をしてくれていた顧客が翌月以降にはまったく購入しなくなったりなど、ECマーケターであればよく目にするケースなのではないかと思います。
顧客寿命を推定する色々な方法が提案されていますが、実務的に最も簡単な方法は「手元にあるデータの初回購入～最後の購入までの期間を顧客寿命と定義する」ことです。
言い換えると、「すべての顧客は現時点で顧客寿命を終えている」という仮定をすることで、不確実な推定や予測を行うことなく実績のみでデータを把握することができます。
こうすると、下記が成り立ちます。
購入頻度[回/月] × 顧客寿命[月] = (現在までの)総注文回数
ですので、LTVは合計注文金額と一致することになります。
LTV ＝ 平均注文金額 × 総注文回数 ＝ (現在までの)合計注文金額
この仮定に違和感を持たれる方、もっともだと思います。この計算方法では、たとえば直近数ヶ月定期的に商品を購入していたリピート顧客も、今後一切購入を行わない、と一律・機械的に仮定をしているからです。
このような単純な方法ではなく、もっと発展的な方法が知りたい！という方向けに、いくつかの記事を執筆しています。併せてご参考ください。
参考記事：
LTVを「平均注文金額」「購入頻度」「顧客寿命」に要素分解して分析を行うことが大切
LTVを計算する目的と用途にもよりますが、”正確”なLTV値を計算することに苦心するよりも、はるかに重要なポイントがあります。それは、LTVを要素に分解して課題を発見し、施策に繋げることです。
ですのでこの記事では、Shopifyデータを使って「平均注文金額」「購入頻度」「顧客寿命」をそれぞれ計算し、分析する方法についても解説します。
それでは、本題に入る前にデータの前準備から説明します。
【前準備】Shopifyから「顧客」「注文」の２つのCSVをダウンロードする
今回のデータ分析では、「顧客データ」「注文データ」の２つのCSVを利用します。
顧客データのダウンロード
まず、Shopifyの「顧客管理」より「エクスポート」を選択してください。
「すべてのお客様」「Excel、Numbersなどのスプレッドシート用のCSV」にチェックを入れて、「顧客をエクスポートする」をクリックします。
※データのダウンロードリンクは、Shopifyアカウントに登録しているメールアドレスに送信されます。件数によっては数分～数時間かかることがあるので、ご注意ください。
下記のようにメールが届くので、ダウンロードしてローカルフォルダに保存します。
注文データのダウンロード
Shopifyの「注文管理」より「エクスポートする」を選択してください。
「すべての注文」「Excel、Numbersなどのスプレッドシート用のCSV」にチェックを入れて、「注文をエクスポートする」をクリックします。
※データのダウンロードリンクは、Shopifyアカウントに登録しているメールアドレスに送信されます。件数によっては数分～数時間かかることがあるので、ご注意ください。
下記のようにメールが届くので、ダウンロードしてローカルフォルダに保存します。
以上で完了です。「customers_export_XXX.csv」「orders_export_XXX.csv」の２つのCSVデータをダウンロードできていれば大丈夫です。
【前準備】新しく分析用のファイルを作成し、データをインポートする
ファイルを新規作成し、５つの空のシートを追加
分析のために、新しくGoogleスプレッドシートを作成します。ファイルの名前は任意ですが、たとえば「LTV計算」などの名前をつけます。
「顧客CSV」「注文データCSV」「顧客シート」「注文シート」「計算シート」の５つの空のシートを作成してください。
なぜこの手順を実施するの？
もちろん、ダウンロードしたcsv上でそのまま計算をしてもよいのですが、次回にシートを再活用することができるので、このような手順を推奨しています。
Customer_export_xxx.csvのインポート
次に、ダウンロードした「Customer_export_xxx.csv」データを「顧客CSV」シートにインポートします。
「顧客CSV」のシートを表示した状態で、「ファイル ＞ インポート」を選択してください。
先ほどダウンロードした「customers_export_XXX.csv」を選択します。
このようなモーダルが表示されるので、「インポート場所」を「現在のシートを置換する」にして、「データをインポート」を選択します。
下記のような形で、「顧客CSV」シートにデータが反映されていれば完了です。（下記画像では一部のデータ項目をマスクしています。）
orders_export_xxx.csvのインポート
次に、同様の手順で「orders_export_xxx.csv」を「注文CSV」シートにインポートします。
下記のような形で、「注文CSV」シートにデータが反映されていれば完了です。（下記画像では一部のデータ項目をマスクしています。）
【前準備】データの抽出
「顧客CSV」、「注文CSV」から、計算に使用したいデータのみを抽出し、それぞれ「顧客シート」「注文シート」にコピーする作業を行います。
顧客CSVから必要なデータ項目を抽出
まず、「顧客CSV」で使用するデータは「C列：Email」「P列：Total Spent」「Q列：TotalOrders」です。
※ShopifyからダウンロードできるCSVデータにおいては、顧客データと注文データを紐づけることができるキーが「Email」しかありません。そのため、途中でEmailが変更された場合、別顧客として分析をするしかなくなります。もし、他カートで顧客IDがデータ項目にある場合は、そちらを利用してください。
それでは「顧客シート」のA1, B1, C1セルに下記の関数を入力し、Enterキーを押してください。
これらの関数は、「顧客CSV」シートの列全体のデータをコピーする関数です。
A1セル
=ARRAYFORMULA('顧客CSV'!C:C)
B1セル
=ARRAYFORMULA('顧客CSV'!P:P)
C1セル
=ARRAYFORMULA('顧客CSV'!Q:Q)
コピー&ペーストではだめなの？
もちろん、コピー＆ペーストしてもよいのですが、データ件数が多いと動作がかなり重くなるのと、次にLTV計算を行う際に、顧客CSVシートのデータを入れ替えるだけでシートを再活用することができるので、このような手順を推奨しています。
各列に下記のようにデータがコピーされていれば完了です。
注文CSVから必要なデータ項目を抽出
次に、「注文CSV」で今回使用するデータは、「B列：Email」「L列：Total」「P列：Created at」「AU列：Cancelled at」です。
「注文シート」のA1, B1, C1, D1の4つのセルに、それぞれ下記の関数を入力してください。
A1セル
=ARRAYFORMULA('注文CSV'!B:B)
B1セル
=ARRAYFORMULA('注文CSV'!L:L)
C1セル
=ARRAYFORMULA('注文CSV'!P:P)
D1セル
=ARRAYFORMULA('注文CSV'!AU:AU)
画像のような結果になっていれば完了です。
以上で、データ分析の【前準備】が完了です。ここから実際の計算に移っていきます。
平均LTVを計算する
この記事で紹介する方法では、LTVの計算はとても簡単です。
「計算シート」を開き、A1セルに平均LTVと名前をつけてください。
A2セルにすべての顧客のTotal Spentを平均する数式を入れて、完成です。
A2セル
=average('顧客シート'!B:B)
それでは、ここから「平均注文金額」「購入頻度」「顧客寿命」それぞれを計算する方法の解説に入ります。説明の都合上、「購入頻度」を最後に計算します。
平均注文金額の計算
各顧客の平均注文金額を算出する
こちらはとても簡単です。「顧客シート」のD1セルに「平均注文金額」と名前をつけます。
D2セルに、下記の数式を入力します。
D2セル
=B2/C2
セルの右下の青丸をダブルクリックして、数式を列全体にオートフィルしてください。
下記のようになっていれば完了です。
全体の平均注文金額を算出する
次に、全体の平均注文金額を算出します。こちらは顧客ごとのデータを、注文件数で重みづけ平均することで計算します。（単純平均ではないことに注意してください）
「計算シート」を開いて、B1セルに「平均注文金額」と名前をつけます。
B2セルに下記の数式を入力して、完了です。
B2セル
=sumproduct('顧客シート'!D:D,'顧客シート'!C:C)/sum('顧客シート'!C:C)
顧客寿命の計算
各顧客の顧客寿命を計算する
この記事でご紹介する最も簡単な顧客寿命の計算方法は、「初回注文日」と「最終注文日」の差分を計算する方法です。
まず、「顧客シート」のE1、F1、G1セルにそれぞれ「初回注文日」「最終注文日」「顧客寿命」と名前を付けてください。
次に、それぞれの一つ下のセルに下記の関数を入力してください。
E2セル
=MIN(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = A2))
F2セル
=MAX(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = A2))
G1セル
=IF(ROUNDDOWN((F2-E2))/30 <1, 1, ROUNDDOWN((F2-E2))/30)
単位は月です。1回しか購入していない顧客など、１以下になってしまうデータについては、このあとの計算の便宜上、機械的に「顧客寿命＝1ヶ月」にする処理をしています。
この処理によって影響があるのは「１回のみの購入者がとても多い」かつ「リピート顧客の購入頻度が長い（たとえば半年以上など）」ECストアです。全体の平均的な購入頻度の数値が、短くなる方向に影響してしまいます。1ヶ月ではなく、たとえば自社の平均的な購入間隔日数に置き換えることで、影響を少なくすることができます。
E2～G2セルを選択して、オートフィルをしてください。このような形になっていれば完成です。
全体の平均顧客寿命を算出する
次に、全体の平均注文金額を算出します。こちらは顧客ごとのデータを平均すればよいです。「計算シート」を開いて、C1セルに「平均顧客寿命」と名前をつけます。
C2セルに下記の数式を入力して、完了です。
C2セル
=average('顧客シート'!G:G)
購入頻度の計算
最後に購入頻度を計算します。購入頻度は、顧客ごとの注文件数を計算し、顧客寿命で除すことで、月単位の平均注文件数を計算することで算出します。
各顧客の購入頻度を計算する
まず、「顧客シート」のH1に「購入頻度」と名前を付けてください。
次に、一つ下のセルに下記の関数を入力してください。
H2セル
=C2/G2
セルの右下の青丸をダブルクリックして、数式を列全体にオートフィルしてください。
下記のようになっていれば完了です。
全体の平均購入頻度を算出する
次に、全体の平均購入頻度を算出します。顧客ごとのデータを、顧客寿命で重みづけ平均します。(単純平均ではないことに注意してください)
「計算シート」を開いて、D1セルに「平均購入頻度」と名前をつけます。
D2セルに下記の数式を入力して、完了です。
D2セル
=sumproduct('顧客シート'!H:H,'顧客シート'!G:G)/sum('顧客シート'!G:G)
下記のようになっていれば完了です。B2、C2、D2の積が、最初に計算した平均LTVと一致していることを確認してください。
LTVを要素に分解して分析する
以上でスプレッドシートを使ったデータの計算が完了しました。
とはいえ、全体のLTVだけ見ていても、打ち手が見えてきません。ここからは、このデータを使ってどのように打ち手を考えるか、いくつかの例を提示してみたいと思います。
データの傾向を把握する
まずは、さきほど計算した各顧客のLTVや、平均注文金額・顧客寿命・購入頻度が、全体でどのように分布しているかをヒストグラムで可視化してみましょう。
ヒストグラムとは、データを区間ごとに区切り、各区間のデータ件数や数値のばらつきを表現したものです。
詳細な手順は割愛しますが、スプレッドシートのグラフツールを使うことで簡単に表示ができます。
この記事で利用しているサンプルデータでは、たとえば下記のようなことが分ります。
- 全体の平均LTVは2.5万円ほどだが、実態として1.2万円付近と4.8万円付近で２つのグループがある。
- 平均注文金額は全体を平均すると9,000円ほどだが、実際はばらつきが大きい。5,000円前後の価格帯の商品を中心に購入している顧客グループと、1.5万円～2万円の高価格帯の商品を中心に購入している顧客グループがあることがわかる。
- 顧客寿命は全体を平均すると3.6ヶ月ほどだが、実際は2ヶ月前後、4か月前後、6ヶ月前後に大きな山がある。これらのタイミングで適切な離脱防止施策を講じる必要がある。※さきほどの計算で、１回しか購入していない顧客・１ヶ月以内の顧客を顧客寿命1ヶ月にまとめたので、その区間のデータが多くなっています。
- 購入頻度は、0.70回/月（およそ1.5ヶ月に１回購入するペース）が最も多く、それより頻繁に購入する顧客が多数。逆に、0.5回/月より少ない購入頻度の顧客はほとんどいないので、このストアにとっては2ヶ月以内にリピートしてもらうことがとても重要。※さきほどの計算で、１回しか購入していない顧客の顧客寿命を1ヶ月にまとめたので、対応する購入頻度（１回/月）の区間のデータが多くなっています。
このように、全体の平均LTVの数値にはあまり意味がないことが分かるかと思います。
実務上は、ここからさらに顧客セグメントを分けて、それぞれに対して適切な打ち手を考えることが大切です。たとえば上記の例では、まずは低価格帯の商品を中心に購入している顧客セグメントと、高価格帯の商品を中心に購入している顧客セグメントに分けてそれぞれ課題を洗い出す必要があるでしょう。
セグメントに分ける方法はケースバイケースで、ここでは解説しきれないため別記事に譲ります。
重要な変数を特定する
LTVを向上させるために、KPIツリーのどこから手をつければよいのか？を判断する方法をご紹介します。LTVの高い顧客と低い顧客に分けて、「平均注文金額」「顧客寿命」「購入頻度」を比較することで、どこに課題があるかを特定することができます。
ここではLTVを基準に顧客を2等分、4等分する例をご紹介します。
Percentile関数を使用することで、顧客を等分した際の閾値を算出することができます。たとえばさきほどのシートで、LTVを基準に上位25%の閾値を計算するには下記の関数を使います。
=percentile('顧客シート'!B:B,0.75)
この記事で利用しているサンプルデータで、LTVを基準に2等分・4等分してセグメントに分けて、「平均注文金額」「顧客寿命」「購入頻度」を集計(平均)した結果がこちらです。
たとえば、下記のことが分ります。
- LTV上位と下位で平均注文金額が大きく異なる。低LTV顧客と高LTV顧客で購入商品が異なる可能性、または、同じ商品でも１度の注文数量が多い可能性がある。LTV向上に、クロスセル施策やアップセル施策が重要そうだと判断できる。
- 上位75%～(＝下位25%)顧客と、それ以外では、顧客寿命に大きな差がある。下位顧客の引き上げにF2のリピート施策や離脱防止策が重要であるが、中位顧客のLTV向上に対しての優先度は低いことが判断できる。
- 平均購入頻度に大きな差はないので、このストアにおいてはLTV向上との相関度が薄いことがわかる。購入頻度を高めるような施策の優先度は低いことが分る。
なお、デシル分析という顧客を10等分する手法がよく取り上げられますが、必ずしも10等分する必要はありません。
大切なのは、全体の傾向を掴むことです。
まとめ
この記事では、Shopifyデータを使ってLTVを計算し、「平均注文金額」「購入頻度」「顧客寿命」の観点から分析を行う方法について解説しました。最もお伝えしたかった内容は、「全体の平均LTV数値だけ見ていても、LTV向上のための打ち手が見えてこない」ということです。まずは、「平均注文金額」「購入頻度」「顧客寿命」の３つの観点に分けて、ヒストグラムなどでデータの傾向を可視化して、どこに課題があるかを特定することが大切です。
さらには、データの傾向を踏まえて複数の顧客セグメントに分解して、セグメント単位でLTVや各種KPIを把握し、打ち手を考えることが必要になります。
これらの分析は、一度実施して終わりになってしまうのはもったいないです。顧客の購買行動は毎日アップデートされていくので、定期的にこのような集計・分析を行い、打ち手を考えて、PDCAをまわすことが重要です。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、ワンクリックでセグメント単位でLTV、平均注文金額、購入頻度などのKPIを把握することができます。
このような集計作業を毎週・毎月のように行うことはとても煩雑です。データの集計や整形にかかる時間を節約して、マーケターにとってもっと大切な、打ち手を考えるという部分に集中してもらいたい、という思いでプロダクトを開発しています。
