önce M. AKÇA tarafından yazılmıştır. Excel ile RFM Analizi

Excel ile RFM Analizi

<strong>RFM analizi pazarlama’da çok kullanılan bir analiz yöntemidir. Genellikle müşteri segmentasyonu için kullanılmaktadır. RFM; Recency, Frequency, Monetary kelimelerinin baş harflerinden oluşur.

Recency: Müşterinin son işlem güncelliğini belirtmektedir.

Frequency: İşlem sıklığının ölçüsüdür.

Monetary ise müşterinin harcadığı toplam paradır.

Müşteri satın alma davranışını özetlemek için RFM Analizlerine ihtiyaç duyarız.  Özet olarak bu davranışları ERP programları gibi veri kaynaklarından çekebiliriz. Daha büyük yapılarda Veri Ambarları bu iş için kullanılacaktır.

RFM konusuna kısa bir giriş yaptıktan sonra, bu işlemi kolaylıkla nasıl uygulayacağımıza birlikte gözatalım: Öncelikle müşteri bilgilerinin bulunduğu veri tabanından satın alma bilgilerini çekmemiz gerekiyor. En temel satış bilgileri Müşteri Kimlik Numarası, Tarih ve Tutar’dır. Açılan siparişler sonrasında bir transaction oluşacaktır. Sipariş bilgilerinde siparişin zamanı, sipariş miktarı ve müşteri bilgileri bulunmaktadır.

Matematiksel gösterim olarak (x,tx,T) gösterimini kullanabiliriz. Burada x; T periyodunda gözlemlenen transaction(sipariş) sayısı tx ise son gözlemlenen siparişin ne zaman gerçekleştiği bilgisini bize verecektir. (0 <= tx <= T)

 rfm1

ID,Tarih,Tutar detaylı Müşteri Satış Verileri

Not:Veri tabanından sorgulama yaparken ya da excel üzerinde kolaylıkla bu bilgiler Müşteri Kimliği, ve Tarih sıralı çekilebilir.

 

Burada dikkat edilmesi gereken bilgilerin ID’ye göre sıralanmasıdır.  Bir müşteri bir gün içerisinde birden fazla harcama yapabilir.  Örnek olarak 25 numaralı müşteri 25.06.2011 tarihinde iki harcama yapmış olabilir. Bu aynı günde birden fazla siparişleri tespit etmek için D2 hücresine

=EĞER(VE(A2=A1,B2=B1),1,0)

Formülü yazılarak bu mükerrerlikler tespit edilebilir.

rfm2

 

Bu Transaction akış modeli aslında satın almalar arasındaki zaman hakkında bize bir şeyler anlatır. Bu nedenle satın almalar arasındaki zaman farkının 0 olmaması istenir. İşte bu hesaplama ile kontrol sağlanabilir.

D sütununu temizleyerek devam edelim:

=EĞER(VE(A2=A1;B2=B1);C2+D1;C2)

Yukardaki formülü kullandığımızda bu kayıtları temizlemiş oluruz. Bu formül bize transaction-tarih şeklinde, tutar toplamlı tekil bir veri seti sunar.

Fakat şimdi de bir gün içerisinde birden fazla işlem olduğunda bunları tekrarlı olarak alacağı için tekrarlıları süzmek için E sütununa alttaki formülü yazıyorum.

=EĞER(VE(A2=A3;B2=B3);1;0)

 rfm3

 

Formülü uygulamak için veri setinin sonuna kadar çekiyorum. Şimdi yapmam gereken şey E sütununu başlıkla birlikte küçükten büyüğe sıralamaktır. Sonrasında altta kalan “1” içeren sütunları silmek gerekecektir.

Son olarak ID ve tarihe göre tablomu küçükten büyüğe olacak şekilde yeniden sıralıyorum.  Artık C ve D sütunlarına ihtiyacım kalmadığı için bu sütunları ortadan kaldırabilirim. Artık hiçbir müşteri aynı tarihte birden fazla satış verisine sahip değil.

 

“Frequency” Değerini Elde Etmek

Artık verimizi temizlediğimize göre Frekans hesabı parasal değer hesabı yapabiliriz. Elimizde bulunan x haftalık bir veri setini ikiye bölerek x/2 lik kısmını ayar için, x/2 lik kısmını da değerlendirmek için kullanabiliriz. Verilerin başlangıç tarihi 01/01/2003, bitiş tarihi ise 30/12/2012 olduğu için bu iki tarihin ortası olan     31/12/2007 tarihini işlemlerin merkezindeki tarih olarak belirleyerek veri setini bu tarihten itibaren “AYAR” ve “DOĞRULAMA” olmak üzere ikiye ayırabiliriz.

Müşterinin şimdiye kadarki ilk alışverişi olup olmadığını ve ayar periyodunda mı yoksa doğrulama periyodunda mı olacağını belirlemek için F2 hücresine alttaki formül yazılabilir.

=EĞER(A2<>A1;”İLK ALIŞVERİŞ”;EĞER(B2<=20071231;”AYAR”;”DOĞRULAMA”))

 rfm4

Şimdi istenen frekans özetini alabiliriz. Bunun için Excel’de yer alan Özet Tablo’lardan yararlanmamız gerekiyor. A ve D sütunlarını içerecek şekilde özet tablo oluşturmamız gerekmektedir.

Özet tablo ile ortalama harcama ve tekrarlı işlemleri ve ayarlama ve doğrulama periyotlarını hesaplayabiliriz. Özet tablo hesaplamasında kimlik bilgileri satıra, periyot bilgileri sütunlara ve Kimlik bilgileri de Say şeklinde ayarlanarak toplam alanına konduğunda,  Kimlik numarası bazında Frekansları elde etmiş olacağız.

rfm5

 

 “Monetary Değerini Elde Etmek

Tıpkı bir önceki özet tabloda olduğu gibi kimlik değerlerini satır başlıklarına, periyot bilgilerini ise sütun başlıklarına gelecek şekilde ayarladığımızda, toplam alanına Harcamaların ortalamasını attığımızda, periyotlar bazında kişilerin ortalama harcamalarını elde etmiş olacağız.

rfm6

 

 

Recency Değerini Elde Etmek

Bir sonraki adım recency değerini tesbit etmek olacaktır. Müşterilerin ilk alışveriş zamanlarını bulmak için alttaki formülü E1 hücresine yazabilirsiniz.

=EĞER(A2<>A1,B2,0)

rfm7

Sonrasında bir pivot table daha oluşturuyoruz, ID kolonunu başa alarak ilk satın almaları topluyoruz. Müşterinin doğrulama periyodu öncesinde yaptığı son satın almayı belirlemek için F2 hücresine alttaki formülü yazıyoruz.

=EĞER(B2<=20071231;EĞER(YADA(A2<>A3;B3>20071231);B2;0);0)

rfm8

 

Daha sonra tablo üzerinde yapılacak bir özet tablo ile tüm müşterilerin doğrulama periyodu öncesindeki son satın alma zamanlarına bu yöntemle ulaşabiliriz.

Özet tabloda ilk alışverişi bulmak için satırların bulunduğu alana Kimlik bilgileri,  toplam alanına ise ilk alışveriş bilgisi (toplam olarak) yerleştirilir.

 

rfm9

 

Modelde aslında recency gözlem periyoduna göre son satın alma zamanıdır. Bu nedenle tx, ilk satınalma ile gözlemlenen son satınalma arasında geçen zamandır.

 

Ayar Zaman diliminde Son Satınalma-İlk Satınalma olarak hesaplanacaktır.?

Tarihleri bulduktan sonra tarihler arasında farkı bulmak için, tarihleri Excel’in anlayacağı bir formata dönüştürmek gerekecektir. Bunun için pivot table 3 e

= TARİH(SOLDAN(B5,4),PARÇAAL(B5,5,2),SAĞDAN(B5,2))

Tanımlaması ile excelin istediği formata çevirebiliriz.  Sonrasında diğer hücrelere de bu formülü genişleterek istediğimiz değişikliği yapmış oluruz.