はじめに
ECの成長においてLTVは非常に重要なKPIですが、ECにおいては顧客寿命の予測が難しいため、集計・管理上の課題が多いです。この記事では、購買間隔のデータを活用することで、「顧客寿命が尽きた人」を確定して、より実態に近いLTVを計算する方法をご紹介します。
この記事は、下記の記事の内容を前提とした、少し発展的な内容について説明をしています。まずは下記の記事からご確認いただけるとよりよいです。
手順が長くて面倒！という方は、ぜひ顧客セグメントツール”ECPower”をご利用ください。Shopify公式アプリの無料インストールはこちらから。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、セグメント単位にワンクリックでコホートLTV分析を行うことができます。
この記事の対象と分析環境
- 記事内容は、LTVの基本的な考え方をご存知の方を想定して作成しています。ECにおけるLTVについては、下記の記事で詳しく説明しています。
- Google Spreadsheetを例に作成しています。Excelをご利用の方も同様の手順で分析が可能ですが、一部関数名等が異なる場合があります。
- ECカート「Shopify」でのデータ項目等を前提として作成しています。他のカートシステムをご利用の方でもご参考いただける内容ですが、各カートのデータ項目に読み替えてください。
この記事で紹介する方法の概要
今回の記事で採用するLTVの計算方法は、下記のとおりです。
平均注文金額 × 購入頻度[回／月] × 顧客寿命[月]
ここで問題となるのが、顧客寿命を計算する難しさです。
ECにおいては、いつ顧客の寿命が終わったかを判断することがとても難しいです。数か月間も音沙汰がなかった顧客が、何かのメールキャンペーンで戻ってきたり、逆に１ヶ月間何度も購入をしてくれていた顧客が翌月以降にはまったく購入しなくなったりなど、ECマーケターであればよく目にするケースなのではないかと思います。
顧客寿命に関する最も簡単な手法としては「手元にあるデータの初回購入～最後の購入までの期間を顧客寿命と定義する」ことですが、直近数ヶ月定期的に商品を購入していたリピート顧客も、今後一切購入を行わない、ということになるので、実態からかけ離れてしまう可能性があります。
今回の記事では、すべての注文の「前回購入日からの経過日」を分析することによって、顧客寿命が尽きた”可能性が高い”顧客を特定します。その顧客に絞ってLTVを計算することで、より実態に近いLTV数値を計算する方法をご説明します。
たとえば「全体の9割の購入が、前回購入から180日以内に行われている」というような情報があれば、最後の購入から180日経過してしまった顧客は、顧客寿命が尽きた可能性が高いと合理的判断できます。
【前準備】Shopifyから「顧客」「注文」の２つのCSVをダウンロードして整形する
Shopifyから顧客・注文データをダウンロードし、スプレッドシート上で下記のような形にデータを整形してください。
データのダウンロード方法や、データ整形の方法はこちらの記事で詳しく解説をしています。４章・５章をご参考ください。
参考記事：Shopifyデータ×Excel／スプレッドシートでLTVを計算し、平均注文金額・購入頻度・顧客寿命の観点から分析する方法
顧客寿命が尽きた顧客を確定し、抽出する
「前回購入日からの経過日」を計算し、顧客寿命の閾値を決める
まず、すべての顧客の「２回目以降の注文データ」に対して、前回購入日からの経過日を計算してみます。
注文シートのデータを、最初に「CreatedAt」で昇順に並び替え、次に「Email」で昇順に並び替えてください。下記のように、同じメールアドレスごとに注文が古い順に並んでいれば問題ありません。
※Arrayformula関数を使った参照をしている場合、この操作に失敗する場合があります。その場合はすべてのデータを選択してコピーし、同じ場所で「特殊貼り付け」＞「値のみを貼り付け」を行ってください。
注文シートのD1セルに「前回購入からの経過日」と名前をつけ、D2セルに下記の関数を入力してください。
D2セル
=if(A2=A1,if(C2-C1<1,"",C2-C1),"")
こちらの式では、同じ日の注文をカウントしない処理をしています。
これで、前回購入日からの経過日を計算することができました。
ヒストグラムで可視化してみましょう。サンプルデータではこのような形になりました。
90日以降の再購入はほとんどなさそうですので、このストアでは最後の購入から90日経過してしまったら顧客寿命が尽きたと判断できそうです。
ただし、このサンプルデータはリアルなストアデータではないのでとても綺麗な分布になっていますが、実際はもっといびつな分布をしている可能性が高いです。
「顧客寿命が尽きた」と判断する閾値（しきい値）の決め方は、グラフを見ながら感覚的に決めてもよいですが、たとえば下記のような数式で求めることができます。
=PERCENTILE(D:D, 0.9)
この数式は、全体の90%以上の注文は何日以内に行われているか？を計算するもので、サンプルデータでは72日となりました。95%以上にしたい場合は、数式内の0.9を0.95にすればよいです。グラフと見比べながら、閾値を決めてください。
「前回購入日からの経過日」を計算し、顧客寿命の閾値を決める
顧客シートで、各顧客の最後の注文日と今日の日付を比べて、さきほど決めた閾値をもとに顧客寿命が尽きたかどうかを判定しましょう。
D1、E1セルに「最後の注文日」「顧客寿命の判定」などの名前をつけ、一つ下のD2, E2セルに下記の数式を入力してください。
D2セル
=MAX(FILTER('注文シート'!$C$2:$C, '注文シート'!$A$2:$A = A2))
E2セル ※”閾値”の部分を置き換えてください。
=if(DATEDIF(D2,today(),"d")>閾値, 1,0)
このようになっていれば大丈夫です。
より正確なLTVを計算する
冒頭での説明で、今回のLTV計算には下記の式を用いると説明しました。
平均注文金額 × 購入頻度[回／月] × 顧客寿命[月]
ただ、顧客寿命が尽きていると思われる顧客群については、購入頻度や顧客寿命を考える必要なく、ただ現在までの合計購入金額を平均してあげればよいです。
新しく「計算シート」といった名前のシートを作成し、どこでもよいので下記の数式を入力してください。
=averageif('顧客シート'!E:E,1,'顧客シート'!B:B)
こちらが、顧客寿命が尽きた顧客のデータのみに基づいた、より正確なLTVになります。
ぜひ、こちらの記事でご紹介している方法と、比べてみてください。
参考記事：Shopifyデータ×Excel／スプレッドシートでLTVを計算し、平均注文金額・購入頻度・顧客寿命の観点から分析する方法
数年以内のECストアや、アクティブ顧客が多いECストアの場合には、顕著に差が見られるかと思います。
逆に、立ち上げてからの期間が長いストアや、長く業績が安定しているストアは大きな差が見られないかもしれません。
数値を見比べたうえで、どちらのLTV数値を利用するかを判断いただけるとよいかと思います。
まとめ
この記事では、ShopifyデータとExcel／スプレッドシートを利用して、平均購買間隔のデータから「顧客寿命が尽きた人」を確定して、より実態に近いLTVを計算する方法を説明しました。ストアによっては、「簡単な手法で計算した数値」と、今回の「実態に近い数値」がほとんど変わらないこともあるかと思います。
LTVを計算する目的と用途にもよりますが、”正確”なLTV値を計算することに苦心するよりもはるかに重要なのは、LTVを要素に分解して課題を発見し、施策に繋げることです。
顧客セグメントツールECPowerは、ノーコードで顧客セグメントを作成し、LTVや平均注文単価、購入頻度といったKPIをセグメント単位に把握できるので、LTV向上にむけた打ち手を立案することができます。Shopify公式アプリの無料インストールはこちらから。
