RFM-Kundensegmentierung in EXCEL

Heute zeige ich Ihnen, wie Sie eine RFM-Analyse (Recency, Frequency, Monetary) in Excel erstellen, um so interessante Kundengruppen für Kampagnen zu erschließen. Die RFM-Analyse ist eine einfache und schnelle Methode, um eine Kundensegmentierung zu erstellen.

Einführung in die RFM-Analyse

Die RFM-Analyse ist ein Scoring-Modell, um eine einfache Kundensegmentierung anhand von drei Kennzahlen, die auf dem Kaufverhalten basieren, zu erstellen. RFM steht für: Recency (Aktualität), Frequency (Häufigkeit) und Monetary Value (Umsatz):

  • Recency (Aktualität): Gibt an wie lange die letzte Bestellung zurückliegt.
  • Frequency (Häufigkeit): Wie oft hat der Kunde in dem Analysezeitraum eine Bestellung aufgeben.
  • Monetary Value (Umsatz): Umsatz des Kunden im Analysezeitraum.

Analysiert man den Umsatzverlauf eines Kunden und bewertet ausgehend davon die Aktualität, die Häufigkeit und den finanziellen Wert auf einer Punkteskala, so lassen sich die einzelnen Ergebnisse zu einer Gesamtpunktzahl für den Kundenwert zusammenfassen.

Mit Hilfe der ermittelten Informationen können die Kunden in Segmente (Klassen) eingeordnet werden und entsprechend zielgerichtete Marketingmaßnahmen durchgeführt werden.

Mit einer typischen RFM-Analyse wird der Kundenbestand durch eine einfache Codierung aufgeteilt. Dazu wird der Datensatz zunächst nach Datum sortiert und in fünf gleich große Segmente geteilt (Aktualität). Die oberen 20% (also die Kunden die zuletzt gekauft haben) werden mit einer 5 codiert, die nächsten 20% mit einer 4 usw. bis 1. Die Vorgehensweise gilt ebenso für die Kriterien: Häufigkeit und Umsatz.

Als Ergebnis wird jedem Kunden ein Code zugeordnet bspw. Kunde A mit RFM 555 (bester Kunde) oder Kunde B mit RFM 111 (schlechtester Kunde). Aus dieser Einteilung ergeben sich 5³ = 125 Klassen.

RFM in Excel

Um eine RFM-Analyse in Excel zu erstellen brauchen wir Kundendaten, um genau zu sein: Transaktionsdaten von Einkäufen in der Vergangenheit. Also müssen wir uns zunächst ein paar fiktive Daten erstellen.

Damit Sie nachvollziehen können, wie die Analyse funktioniert, können Sie sich folgende Tabelle kopieren oder Sie laden sich direkt das Excel herunter:

KundennummerTage seit LetztkaufAnzahl TransaktionenUmsatz €
987-83410
976-301456
899-232290
876-158876
764-453712
657-812999
566-344200
456-562600
300-33123
234-59789
133-105340
123-127456
111-188150
102-28678
100-1151245

Diese Daten sind die Grundlage für unsere RFM-Analyse. Mit den Kennzahlen können wir die Analyse erstellen:

  • Tage seit Letztkauf (Recency)
  • Anzahl Transaktionen (Frequency)
  • Umsatz (Monetary)

Schritt 1: Recency berechnen

Folgend müssen wir den Recency-Score berechnen. Dafür müssen wir für jeden einzelnen Datensatz ermitteln in welcher der fünf Klassen jeder steckt. Dafür tragen wir diese Formel in die Excel-Tabelle ein:

RFM Recency-Score in Excel mit dieser Formel berechnen.
Recency-Score in Excel mit dieser Formel berechnen.

Als nächstes berechnen wir den F-Score anhand der Einkaufshäufigkeit.

Schritt 2: Frequency berechnen

Den Frequency-Score berechnen wir mit der gleichen Formel, allerdings auf Basis der Kennzahl „Anzahl Transaktionen“. Wir nutzen hier fiktive Zahlen, in der Praxis sind alle 3 Kennzahlen oft für einen bestimmten Zeitraum (bspw. letzte 12 Monate) berechnet.

Die RFM-Kennzahl Frequency wird mit der identischen Formel berechnet.
Die RFM-Kennzahl Frequency wird mit der identischen Formel berechnet.

Jetzt fehlt nur noch die monetäre Größe.

Schritt 3: Monetary berechnen

Um den Umsatz in unsere 5 RFM Klassen einzuteilen nutzen wir erneut unsere Excelformel:

M-Score Berechnung in Excel
M-Score Berechnung

RFM-Score in Excel berechnen

Abschließend müssen wir den RFM-Score aus den drei berechneten Scores zusammensetzen. Dazu verketten wir in Excel die Score-Werte:

Mit der Formel "Verketten" in Excel können wir den RFM Score zusammensetzen.
Mit der Formel „Verketten“ in Excel können wir den RFM Score zusammensetzen.

Um die Kunden in einer Reihenfolge zu bringen, können wir diese nach den RFM-Klassen sortieren.

RFM-Score in Excel sortiert.
RFM-Score in Excel sortiert.

Fazit RFM Score in Excel

Der RFM-Score ist eine sehr einfach Marketinganalyse, die jeder als DIY-Analyse (Do It Youtself Analyse) erstellen und nutzen kann. Die Analyse hilft Ihnen dabie Kundensegmente zu bilden und gezielte Kampgnen für diese zu entwickeln. Natürlich gibt es auch einige Nachteile dieser Analyse. Wenn Sie mehr zum Thema RFM-Analyse erfahren wollen, dann schauen Sie sich diesen Artikel zu RFM von mir an.

Ihr Ansprechparnter: Laurenz Wuttke

Wir entwickeln Künstliche Intelligenz, mit der Sie mehr aus Ihren Kundendaten machen – für wachsende Umsätze und eine nachhaltig hohe Kundenzufriedenheit.
Laurenz Wuttke

Auch interessant für Sie