Excel(エクセル)で簡単マスター!RFM分析のやり方と活用法! - リピスト | EC/D2Cサイト構築システム

Excel(エクセル)で簡単マスター!RFM分析のやり方と活用法!

RFM分析は、顧客を、最近の購入日(R:Recency)、購入頻度(F:Frequency)、累計購入金額(M:Monetary)の観点から分析する方法ですが、分析と聞くと難しそうなイメージを持たれるかもしれません。

そこで今回はRFM分析をExcel(エクセル)で簡単に行う方法を紹介します。Excel以外に特別なツールなどは必要ありません。

この機会にExcelを使ったRFM分析の方法を習得して、ワンステップ上のECサイト運営を実現しましょう。

ステップ1.分析に必要なデータを入力する

まずはエクセルを開き、それぞれ、A、B、C…の列に、顧客のIDもしくは名前、直近の購入日(年/月/日)、これまでの来店頻度(累計購入回数)、これまでの使用金額(合計購入金額)を打ち込みます。分析に必要となるデータの入力です。

分析に必要なデータの入力

関数を使ってR値を設定する

この時、直近の購入日(R)を、その日から現在までの経過日数に置き換えます。
例えば、本日までのデータのすべてをRFM分析するのであれば、最新の購入日から本日までの日数を計算します。

また、特定の期間をRFM分析するのであれば、特定の期間内の最終購買日から、その期間の最終日までの日数を計算してください。経過日数を計算する時には、エクセルのDATEDIFという関数を使います。

例えば、経過日数を表示する列をFとするのであれば、F1のセルに分析期間の最終日を打ち込みます。(年/月/日)

関数を使ったR値の設定方法1

そして、F2のセルに『=DATEDIF(R値のあるセル番号,$F$1,”d”)』と打ち込みます。サンプルではB2がR値のあるセル番号となります。

関数を使ったR値の設定方法2

少し計算式がややこしいですが、最初に計算式を打ち込み、後は最後の行まで計算式をコピーすれば、自動的に経過日数が表示されるのでとても簡単です。

関数を入力すると、経過日数が表示されます。このサンプルデータの場合は、「455」という結果になっているので、最終購入日から455日経過していることが分かりますね。

R値の結果

ステップ2.それぞれの数値を区分けする

RFM分析では、それぞれの数値を3段階や5段階で区分けするのが一般的です。これはバラバラの数値を大まかに分類することで、データを見やすくするため。段階ごとにグループ分けするイメージを持つと分かりやすいですね。

商材によって最適な区分け・分類が必要

3~5段階に区分けするということは、分類が大まか過ぎたり細か過ぎたりすると、逆に見にくいデータになってしまいます。これではせっかくRFM分析を取り入れても、有効な分析結果を得ることができません。

具体的には、どのような商材を販売しているかによって、分類の仕方も変わってきます。例えば、安価な消耗品(石鹸や食べ物など)であれば、「数日単位」や「数十円単位」で分類した方がいいでしょう。

逆に、高価な服や家具などであれば、「数か月単位」や「数千円単位」で分類する方が最適です。このように、RFM分析は一様ではありません。扱っている商品に合った区分け、分類を行い、より有効な分析結果を導き出しましょう。

「if関数」を利用して区分けしよう

では、具体的な区分けの方法を見ていきましょう。経過日数(R値)、F値、M値は、「if関数」を用いて区分けします。この区分けには、また新しい列を使用するので、今回はG-I列を対象にします。

それぞれの新しい列の最初にif関数の式を入力していきましょう。G2セルを選び、関数ボタン(fxと表示される場合も)をクリックします。

R,F,Mのランク付け

すると上記のような「関数の挿入」画面が表示されるので、if関数を選んでOKをクリックしましょう。クリックすると「関数の引数」という画面に移動します。ここでは具体的な数値を入力して、値を区分けすることができます。

IF関数を活用してR値の設定をする

例えば経過日数の場合、「何日以上は1に区分けし、何日以下は3や5に区分け」するというように、ショップにとって最も好ましい数値で区分けします。M値の場合は、金額が高ければ高いほど、上の段階に区分けしていきます。F値も同様です。

5つの区分で関数を入力する

今回は、次の条件でR値を5つのランクに区分けしたいと思います。

  • 【ランク5】30日未満
  • 【ランク4】30日以上60日未満
  • 【ランク3】60日以上90日未満
  • 【ランク2】90日以上180日未満
  • 【ランク1】180日以上

ランク付けの具体例

まず論理式の部分に、「F2>=180」と入力します。これは、F2セルの数字が180以上の場合、という意味合いで、関数の条件設定の部分です。次に、真の場合(条件に合う)という部分に「1」と入力しましょう。これで、F2のセルが180以上の場合は1と出力されます。

続いて、偽の場合(条件に合わない)の部分も入力していきます。ここでは、ランクの2~5までの4種類の区分を行うため、やや複雑な関数の入力が必要となります。

IF(F2>=90,2,IF(F2>=60,3,IF(F2>=30,4,5)))

上記が実際に入力する関数ですが、詳しく見てみると、「F2が90以上なら2→F2が60以上なら3…」といったように、先ほどの5つの区分に対応した内容が入力されていることが分かります。

例えるなら、YES・NOチャートのイメージです。YESなら右、NOなら左…といったように、180日以上なら1、90~180日以内なら2…といった具合に、条件を入力してあげることでデータを5つのグループに区分することができます。

同様にして、F値とM値もランク付けしていきます。今回は例として以下のように分類しました。

RFMのランク付けに関する参考例

ステップ3.見方を工夫してデータを意味のあるものにする

こうして区分けしたデータを、さらに意味あるものにするには、RFM分析の見方が重要となってきます。ここでは、2つのデータの見方を紹介します。

合計値で見る

まず1つ目は、それぞれ区分けした数値を合計する方法です。先ほどの例では、R値のみを区分けしていましたが、同様にF値とM値も区分をおこないます。この3つの数値を合計してデータを分析するという訳です。

合計値で見るRFM分析の方法

例えば、3段階の区分けであれば、合計値9が最も優良な顧客です。5段階の区分けであれば、合計値15が最も優良な顧客と言えるでしょう。エクセルで合計値を算出するには、SUM関数を使うのが便利です。

合計値によって、販促の度合いを決めるので、最もシンプルなデータの見方と言えます。

2軸の掛け合わせで見る

2つ目のデータの見方は、2軸のかけ合わせによって分析する方法です。合計値はとても分かりやすいデータの見方ですが、これだけではきめの細かいサービスはできません。なぜなら、R値、F値、M値をすべてひとまとめで評価してしまっているからです。

例えば、R値(1)+F値(5)+M値(2)=8というユーザーと、R値(4)+F値(2)+M値(2)=8というユーザーでは、合計の値は同じ8となります。

しかし、実際には前者はF値の「購入頻度」だけが高く、「経過日数(R)」や「購入金額(M)」は低いことが分かります。対して、後者はすべての値のバランスが良く、購入からの経過日数も近いユーザーと言えます。

両者を区別して分析を行うには合計値の方法では困難です。そこで、よく活用されるのがRFMのうち、2つの軸をかけ合わせて分析する方法があります。たとえば、RとFのかけ合わせた場合の例を見ていきましょう。

RとFの2軸で分析するRFM分析の方法

上の表ではR1-5に対して、それぞれF1-5の人が何人なのかを一目で確認することができます。このようにすると以下のような仮説が立てられます。

    • 仮説1
      R3-5の割合が非常に低い。特にF1の人数が少ないため、30日以内に新規ユーザー獲得に苦戦していると考えられる。

  • 仮説2
    F2,3を合計すると全体の8割を占めることから2回以上のリピートに繋げることができている。ただし、F4,5の割合が低いことから10回以上の購入となるとハードルが高いようだ。

このような仮説まで落とし込めると、サイトの課題や強みが見えてきます。ここから具体的な施策に落とし込んでいき、数値の改善を図りましょう。

データを価値あるものにするRFM分析

ここまで、RFM分析をExcelで行う方法を紹介してきました。

RFM分析から見えてくる様々な数値は、意図的に組み合わせることで、意味をもってきます。エクセルを使った分析は、誰でも手軽に利用することができます。ただ数値を眺めるのではなく、積極的にその数値を活用してしてみましょう。

RFM分析を行うためには、そもそもの顧客データを管理するシステムが不可欠です。

リピストでは分析の元となるデータの出力はもちろん、リピストメールと組み合わせて使用することでシステム上でRFM分析を行うことができます。より効果的なマーケティング活動に繋げるなら、しっかりとしたシステムが整ったリピストのサービスをぜひご利用ください。

リピストの公式サイトはこちら

 

EC運営に関して、お困り事などあれば、お気軽にお問い合わせください!
EC通販に精通したプロがお答えいたします。
新しいタブでページが開きます

03-6284-2812

10:00~18:00( 土日祝日は除く )