マーケティングにおけるリピートの鍵になる「F2転換率」。
F1＝1回目購入、F2＝2回目購入・・・というように購入の頻度・回数（F＝Frequency）を指し、2回目購入される確率を示す指標です。
特に、EC事業やD2C、物販ビジネスで重要視されています。
このF2転換率をスプレッドシートで、QUERY関数＋COUNTIF関数を使って算出する方法を解説していきます。
以下の画像のように「顧客ID」と「購入日」が格納されているデータを想定します。
このダミーデータからスプレッドシートでF2転換率を算出していきます。
F2転換率をスプレッドシートで算出する手順
QUERY関数でID順にソートする
まずは、QUERY関数を使って、データを「顧客ID」順に並べます。
（顧客IDがないようなデータであれば、「顧客名」順でもOK）
D1セルに
=QUERY(A:B, “select * where A is not null order by A,B”,1)
と関数を入力します。
すると、以下の画像のようにデータが顧客ID・購入日で昇順に並んで返ってきます。
ポイントは
- 「where ID列 is not null」として、IDが空白の行を除外すること
- 「order by ID列」として、顧客IDを昇順で並べること
です！
COUNTIF関数でリピート回数をカウントする
次に、COUNTIF関数を使って顧客IDごとのリピート回数をカウントしていきます。
F列にリピート回数をカウントする列（例では、「repeat_cnt」）を作ります。
F2セルに
=IF($D2=””,,COUNTIF($D$2:$D2,$D2))
と関数を入力します。
F2セルをコピーして、シートの一番下まで関数を貼り付けます。
ポイントは
- F2セルのCOUNTIF関数を「COUNTIF($D$2:$D2,$D2)」として、参照範囲の上限だけを2行目で固定すること
- 「IF($D2=””,,~)」のようにIF関数でネストして、顧客IDが空白だった場合に空白で返すこと
になります。
COUNTIF関数の参照上限だけを固定することで、顧客IDごとの出現回数（＝顧客IDごとのリピート購入回数）をカウントすることができます。
また、IF関数で「顧客IDが空白の場合に空白で返す」こと＆数式をシートの下限までコピペすることにより、購買データが更新されてデータが追記された場合でも自動で集計することが可能です！
リピート回数ごとの顧客数と転換率を算出する
リピート回数をカウントする列を作成できたら、購入頻度ごとに出現数をカウントしていきます。
M列にF1～算出したい購入頻度までを入力します（例では、F10まで）。
最後の行は、購入頻度＋1～（例では、F11～）としておきます。
I2セルに
=COUNTIFS($F:$F,MID(H2,2,2))
と関数を入力します。
（COUNTIF関数でもOK）
MID関数で文字列「F1」から「1」を取得するようにしています。
MID関数を使わず、COUNTIFS関数の引数にそのまま「1」を指定してもOKです。
ただし、MID関数で取得しておけば、そのまま下のセルにコピーして算出できるので、関数の編集の手間がなくなります！
最後のセル（例では、F11～に該当する「I12セル」）では、MID関数の前に「“>”&」を追加します。
F11以降の購入頻度をすべて合算するためです。
これで、各購入頻度（F1、F2・・・）の出現回数を集計できました！
ここから各購入頻度ごとに比を算出することで、転換率を算出することができます。
J3セルに
=IFERROR(I3/I2)
と関数を入力します。
これを最後の購入頻度のセルまでコピペします。
これでF2転換率を含む、各購入頻度ごとの転換率を取得できました！
例示したダミーデータでは、
- F2転換率は39.7%、F3転換率は28.8%、F4転換率は50.9%
- F3転換率が最も低く、F4転換以降は相対的に高めの転換率
- F3転換率に課題が見え、ここを改善することでLTVが伸びる可能性が高い
といったことがわかりますね！
今回活用したダミーデータは、「顧客ID」と「購入日」だけの非常にシンプルなデータでしたが、ここにカテゴリーや購買チャネル、居住地などを加えれば、カテゴリーごとのF2転換率・購買チャネルごとのF2転換率・居住地ごとのF2転換率なども算出できます。
まとめ
QUERY関数とCOUNTIF関数をうまく活用することで、F2転換率や各購入頻度ごとの転換率を算出できました！
マーケティングや事業経営において、リピートは決定的に重要です。
そのキーとなるF2転換率を算出する場面は実務上も多いため、ご参考になれば幸いです。
F2転換率に関連して、以下の記事ではLTVを時系列で算出する方法を解説しています。
あわせてご覧ください！
