受注データからF2転換率（リピート購入率）を算出する方法［Excelで顧客分析（初級編）］
CRM（顧客管理）の重要指標の一つに「F2転換率」というものがあります。今回はExcelを使ったF2転換率の算出方法をご紹介します。
F2転換率とは
FはFrequencyの略で、マーケティング上は「購入頻度」と訳されます。2は累計購入回数を表しており、F2転換率＝「2回目購入への転換率」を意味します。新規購入者がどのくらいリピート購入に繋がっているかという指標となり、商品やサービスへの満足度、新規獲得用広告の効果、リピート促進施策の効果などを測るための重要な指標となります。
受注データさえあればこのデータを簡単に取ることができますので、ぜひ実践してみましょう。
必要な知識
本記事の内容は、以下の知識があればスムーズに読み進められます。もし現時点で分からなくても、記事内で解説しているので大丈夫です。
- IF関数
- AND関数
- COUNTIF関数
- セルの並べ替え
- オートフィル
難易度：★☆☆☆☆
所要時間：10分
初歩的な関数と機能しか使わないので、Excel初心者の方でも実践していただけます。また、慣れれば10分以内に出来るような簡単な作業です。
必要なデータ
F2転換率を算出する際に必要なデータは以下の2つです。
- 顧客ID（※１）
- 受注日（※２）
※１、会員登録が不要だったり、ゲスト購入（会員登録なしでの購入）が可能な場合、顧客IDが存在しない受注があるかと思います。その場合はメールアドレスや顧客氏名など、固有の顧客を特定できるデータで代用して下さい。
※２、受注データは「受注データ上の最初の受注」～「F2転換率を取りたい期間の末尾」の期間で用意してください。仮に「サイト公開時～2015年7月」のF2転換率を取りたい場合は、「サイト公開日～2015年7月31日」のデータが必要となります。なお、時間情報が含まれたデータ（受注日時）でも構いません。
また、「受注データから顧客ランク別のアクティブユーザー数を抽出する方法」のデータを持っている場合、手順１～３は読み飛ばして、手順４から進められます（ただし、今回の記事での解説とは列の位置が若干変わりますのでご注意下さい）。
手順１：必要なデータをExcelに貼り付ける
ExcelのA列に顧客ID、B列に受注日を貼り付けます。
以降はこのデータを「基データ」と呼びます。この基データから、F2転換率を算出していきます。
手順２：顧客ID昇順で並べ替え
まず、基データのセルを全て選択します。
（「セルA1をクリック」→「Ctlr＋Shift＋End」で簡単に選択できます。）
次に「並べ替えとフィルター」→「ユーザー設定の並べ替え」を選択します。
「並べ替え」のウインドウが立ち上がるので、「最優先されるキー」を「顧客ID」にします。続いて、左上のあたりにある「レベルの追加」をクリックし、「次に優先されるキー」を「受注日」にします。そのほかの項目は「値」「昇順」のままで構いません。
これで基データのA列が顧客IDの昇順になり、顧客IDが同じ場合はB列が受注日の昇順となるように並べ替えられました。
手順３：顧客別の累計購入回数を抽出する
次に顧客別の累計購入回数を抽出します。セルC2に
「=IF(A1<>A2,1,C1+1)」
と入力して下さい。
数式の意味は、「A列に新しい顧客IDが出てきたら"1"、既に出ている顧客IDなら"一つ上のセルの値＋1"を表示」というものです。A列は顧客ID順、B列は受注日順に並んでいるので、同じ顧客IDであれば累計購入回数が"1"ずつ増えていくという形になります。
数式を入力したら、オートフィル機能でコピーしましょう（「セルC2」をクリック→セル黒枠右下の四角いマークをダブルクリック、または基データの末尾の行までドラッグ）。
これで「各受注について、その受注が同一顧客における累計何回目の購入だったか」というデータが取れました。
あとはこれを集計することでF2転換率が抽出できます。
手順４：F1受注とF2受注を集計する
まず集計のための下準備として、基準となる累計購入回数を入力します。
図の通り、セルE3に1、セルE4に2を入力して下さい。
続いて、F3に
=COUNTIF(C:C,E3)
と入力します。
数式の意味は、「C列（"C:C"）にセルE3の値（=1）がいくつあるか数えて表示する」というもので、ここで表示された数値＝「累計1回目の購入（F1受注）をした顧客数」です。
続いて、この数式をF4にコピーすると、「累計2回目の購入（F2受注）に至った顧客数」が表示されます。
図ではF1受注が5名、F2受注が3名となっています。F2転換率＝「F1受注をした人の内、何人がF2受注に至ったか」なので、「3÷5＝60%」がF2転換率となります。
以上でF2転換率の算出は完了です。
注意点
今回の方法で取れるのは、「サイト公開時から受注データ上の最終日まで」のF2転換率です。例えば「2014年だけ」のF2転換率を取ることは出来ません。
理由としては、仮に2014年だけの受注データを用意したとしても、2014年1月1日の受注の累計購入回数が"1"だった場合に、該当の受注がその顧客にとって本当に1回目の受注なのか、2013年以前にも受注があったのかを判別出来ないためです（2013年以前は無視するという考え方もありですが）。
もちろん手順を増やせば取れるのですが、やや複雑な内容になってきますので、それはまた別の機会にご紹介したいと思います。
まとめ
E列の数値を3にすれば「3回目の購入に至った顧客数」になるので、「F2→F3転換率」や「F1→F3転換率」といった数値も簡単に取れます。
ただ、おそらくこの数値だけ見ても、それが高いのか低いのかをいきなり判断するのはかなり難しいと思います。そこで一つオススメなのが、「比較」をすることです。例えば複数サイトを運営しているならサイト同士での比較などが考えられます。
また生年月日や性別などのデモグラフィックデータ（顧客属性データ）がある場合、それを受注データと結びつけて年代別や男女別での比較を行うことも出来ますし、Googleアナリティクスのeコマースデータと結びつければ「初回購入時の参照元別でのF2転換率」といったデータも取れます。特に広告を運用されている方はぜひ把握しておきたいデータですね。
色々と比較を行ってみることで、その数値が良いのか悪いのかが見えてくると思います。ぜひ様々な比較を行って、現在の施策が顧客にどのような影響を与えているのかを探ってみましょう。
