SQL Server'da T-SQL ile RFM analizi

SQL Server'da T-SQL ile RFM analizi

Veri biliminde sıklıkla gündeme gelen RFM analizinin SQL Server üzerinde T-SQL kodları ile nasıl yapılacağını uygulamalı şekilde ele alıyoruz.
Ömer Çolakoğlu07 Haz 2021

Recency, frequency ve monetary kelimelerinin baş harflerinden oluşan RFM'de ilgili üç metriğin hesaplanması ve birleştirilmesiyle bir skora ulaşılıyor ve müşterilerin mevcut durumu analiz edilerek bu skorlara göre müşteriler segmentlere ayrılıyor. Bu üç metriği kısaca şu şekilde açıklayabiliriz:

  • Recency: Bu metrik müşterinin ne kadar zamandır internet sitesinden/mağazadan hizmet aldığı veya siteye üye olduğu gibi bilgileri içerir. Bugünden üyelik tarihinin veya son sipariş tarihinin çıkartılmasıyla hesaplanabilir.
  • Frequency: Müşterinin ne sıklıkla hizmet aldığını veya ne sıklıkla siteye giriş yaptığını gösteren bir metriktir. Genellikle sipariş numarası veya kodunun saydırılmasıyla hesaplanır.
  • Monetary: Müşterinin yaptığı harcamaların toplamıdır. Müşterinin e-ticaret sitesine getirdiği ciro veya aldığı hizmetler sonrası edindiğimiz getiri olarak da düşünülebilir. Müşteri bazında yapılan tüm harcamalar toplanarak hesaplanır.

Metriklerin hesaplanmasının ardından her bir metrik bazında müşteri verisi beş eşit parçaya ayrılır. Ulaşılan rakamlar bir araya getirilerek bir RFM skoru atanır. RFM analizi bir satış veri seti üzerinde çalışarak elde edilir ve yapılan çalışma sonucunda bir müşteri sınıflandırma işlemi gerçekleştirilir. Nihayetinde elde etmek istediğimiz tablo aşağıdaki gibidir:

Buradaki alanları ise şu şekilde açıklayabiliriz:

  • CustomerID: Sınıflandırılamak istenen müşterinin ID’sidir. Burada müşteri kodu, müşteri adı gibi bilgiler de olabilir.
  • LastInvoiceDate: Müşterinin son alışveriş yaptığı tarih ve zaman bilgisini tutar. Bu bilgi bizim recency değerimizi hesaplamak için kullanacağımız alandır.,
  • Recency: Müşterinin en son kaç gün önce alışveriş yaptığı bilgisini içerir. Bugünden son alışveriş tarihi çıkarılarak hesaplanır.
  • Frequency: Müşterinin ne sıklıkta alışveriş yaptığı bilgisidir. Burada fatura numarası ya da sipariş numarası gibi alanlar distinct olarak sayılarak tespit edilir.
  • Monetary: Müşterinin yaptığı harcamaların toplamıdır. Bir müşterinin firmaya kazandırdığı toplam miktardır.
  • Recency_Scale: Elde edilen recency değerinin 1 ile 5 arasına sıkıştırılmış halidir. Diyelim 100 satır kaydımız var. Tüm veriyi recency değerine göre büyükten küçüğe sıraladığımızda bu sıralamada 1 ve 20. satırlar arası 1, 21-40 arası 2, 41-60 arası 3, 61-80 arası 4 ve 81-100 arası 5 olacak şekilde bir yeniden boyutlandırma (scaling) işlemi gerçekleştirilir. Burada önemli olan nokta recency değeri en düşük olanın 5, en yüksek olanın 1 olacağıdır. Yani daha az süre önce alışveriş yapmış müşterinin skoru yüksektir.
  • Frequency _Scale: Elde edilen frequency değerinin 1-5 arasına sıkıştırılmış halidir. Burada frequency değeri en yüksek olanın skoru 5, en düşük olanın 1 olacaktır. Yani fazla sayıda alışveriş yapmaya bağlı olarak artan bir skordan söz ediyoruz.
  • Monetary _Scale: Elde edilen monetary değerinin 1-5 arasına sıkıştırılmış halidir. Burada da monetary değeri en yüksek olanın skoru 5, en düşük olanın 1 olacaktır. Yani firmaya fazla getirilen paranın fazlalığına bağlı olarak artan bir skor söz konusu.
  • Segment: Elde edilen Recency_Scale, Frequency_Scale, Monetary _Scale değerlerine göre belli bir formül ile müşterinin sınıflandırılmasıdır. Bu sınıflandırmada her bir müşteri Need_Attention, Cant_Loose, At_Risk, Potential_Loyalists, Loyal_Customers, About_to_Sleep, Hibernating, New_Customers, Promising, Champions gruplarından biri içerisinde yer alır.

RFM analizi için önce veri setimizi indirelim. Dosyamız şöyle bir görünüme sahip:

Dosyada 2009-2010 ve 2010-2011 yıllarına ait satış verileri bulunmakta. Uygulamamızda bu iki veriden birini seçip çalışacağız. İstenirse bu iki veri birleştirilebilir. Biz şimdilik Year 2010-2011 verilerini dikkate alalım.

Baktığımızda 540 457 satırlık bir verinin olduğunu görüyoruz. Burada bir müşteriye ait birden fazla fatura bulunuyor ve bir faturanın altında da birden fazla ürün var.

Sütunlardan bahsedelim:

  • Invoice: Fatura numarası
  • StockCode: Satılan ürünün kodu
  • Description: Satılan ürünün adı
  • Quantity: Ürün adedi
  • InvoiceDate: Fatura tarihi
  • Price: Ürün birim fiyatı
  • Customer Id: Müşteri numarası
  • Country: Müşterinin ülkesi

Excel dosyasını gördüğümüze göre artık SQL Server'a geçme vakti. Malum yazımızın RFM analizini MSSQL üzerinde gerçekleştirmeyi konu alıyor.

Öncelikle Excel datasını SQL Server’a aktarmak gerekiyor. Bunun için SQL Server üzerinde RFM isimli bir veri tabanı oluşturalım. Bunun için aşağıdaki gibi New Database diyerek yeni bir database oluşturabiliriz:


RFM isimli database hazır:

Şimdi Excel dosyasındaki veriyi buraya import edeceğiz. Bunun için database üzerinde sağ tıklayarak Task ->Import Data diyoruz:


Next butonuna bastığınızda aşağıdaki hatayı alıyorsanız merak etmeyin çözümü var. Hata almıyorsanız bu kısmı okumasanız da olur. 

Burada Microsoft.Ace.Oledb.12.0 provider hatasını görüyoruz. Bu hatayı gidermek için Microsoft Access Database Engine’i bilgisayarınıza yüklemeniz gerekiyor. Bağlantıdan indirebilirsiniz.

Kurulumu Next diyerek default ayarlar ile yapabilirsiniz.


Kurulum tamamlandı.

Şimdi tekrar Excel dosyamızı import etmeye çalışıyoruz. Import and Export Wizard üzerinde kaynak olarak Excel dosyamızı göstermiştik. Hedef olarak ise SQL Server’ı göstereceğiz.

Bağlanacağımız SQL Server’ı seçiyor ve kullanıcı bilgilerini giriyoruz. Benim kullandığım SQL Server kendi makinem olduğu için server name kısmında localhost yazıyor ve kullanıcı bilgileri için Use Windows Authentication seçeneğini işaretliyorum. Siz kendi bağlandığınız SQL Server bilgilerini girebilirsiniz.

Copy data from one or more tables or views seçeneğini seçiyoruz.

Next dediğimizde karşımıza aşağıdaki ekran geliyor. Source kısmında Excel dosyasındaki sheet adı, Destination kısmında ise SQL Server’da oluşturacağımız tablonun adı geliyor. Burayı elle değiştirebiliriz. 2010-2011 yılları arasındaki veriyi kullanmayı tercih ediyoruz. 

SQL Server’a aktaracağımız tablonun adını ONLINERETAIL_2010 olarak değiştiriyoruz.

Burada Next deyip devam edebiliriz ancak Edit Mappings butonuna basıp yeni oluşan tablonun alanlarını ve veri tiplerini de görebiliriz. Edit Mappings butonuna basınca bir süre beklemeniz gerekebilirz. Zira 540 bin satır Excel dosyasını okuyor. Bilgisayar donabilir, panik yapmayın. Bir süre beklediğinizde aşağıdaki ekranı göreceksiniz. Tablomuzun alanları ve veri tipleri. OK deyip ilerleyebiliriz.

Next dediğimizde Run Immediately seçeneğini işaretliyoruz ve tekrar Next diyoruz.

Finish diyor ve satırlarımızın aktarılmasını bekliyoruz.

Import işlemi tamamlandı.

Şimdi kontrol edelim:

Artık Excel dosyamız veri tabanımızda. Buraya kadarki işlemlerde hata yaşadıysanız üzerinde çalışacağımız veri tabanını bağlantıdan indirebilirsiniz.

Verilerimizi aktardığımıza göre RFM analizi işlemlerine başlayabiliriz. Yazının başında RFM analizi sonucunda aşağıdaki gibi bir tablo elde etmek istediğimizden bahsetmiştik.

Tabloyu elde etmek için yapılan en büyük hatalardan biri karmaşık SQL cümleleri yazarak tek seferde bu tabloyu elde etmeye çalışmak. Basit düşünelim. Sonuçta bir Excel tablomuz var. Burada tekrar etmeyen CustomerIDler var ve bu CustomerIDlere göre hesaplanan bir takım sütunlar var. Öncelikle bu mantıkta bir tablo oluşturup içine önce CustomerIDleri tekrar etmeyecek şekilde dolduralım. Sonra sırayla diğer alanları hesaplayarak ilerleyelim.

İlk iş ulaşmak istediğimiz formatta bir SQL tablosu oluşturmak. 

Görüldüğü gibi tablomuzun içi şimdilik boş.
Biz yine de tablonun içini boşaltacak kodu yazalım ve tabloyu içerisinde tekrar etmeyecek şekilde CustomerIDler ile dolduralım

TRUNCATE TABLE RFM

INSERT INTO RFM (CUSTOMERID)
SELECT DISTINCT [Customer ID] FROM ONLINERETAIL_2010

Burada Excel'den aktarırken Customer ID kolonunda arada boşluk olduğu için Customer ID yazarken köşeli parantezler içinde yazdık. Sorgu neticesinde 4373 kayıt eklendi.

Şu anda içinde sadece CustomerID olan, diğer alanları null olan 4373 satır kaydımız var.

Diğer alanları da hesaplayalım. Hesaplayacağımız ilk alan LastInvoiceDate, yani müşterinin yaptığı son satın alma zamanının bulunması. Bu değeri bulmalıyız ki recency değeri bu tarih ile şimdiki zamanın farkı üzerinden hesaplanabilsin.

Bu işlem için basit bir UPDATE sorgusu kullanabiliriz. Aşağıdaki sorgu her bir müşterinin ONLINERETAIL_2010 tablosunda son alışveriş yaptığı zamanı bulup RFM tablosunu güncelleyecektir.

UPDATE RFM SET LastInvoiceDate=(SELECT MAX(InvoiceDate) 
FROM ONLINERETAIL_2010 where [Customer ID]=RFM.CustomerID)

Artık LastInvoiceDate alanımız da hazır.

Bir sonraki adım recency değerinin bulunması. Bunun için bugünden son alışveriş tarihini çıkarmamız ve tabloyu güncellememiz gerekiyor. Tıpkı bir Excel dosyasında satır satır formül çalıştırır gibi bir sorgu ile satır satır güncelleme yapacağız.

SQL Server’da iki tarih arasındaki farkı alan komut DateDiff. Bu komut içerisine üç parametre alır:

  1. Farkın türü (Gün, ay, yıl gibi)
  2. Başlangıç tarihi (LastInvoiceDate)
  3. Bitiş tarihi (Bugünün tarihi. Fakat veri setimiz 2011 yılında geçtiği için bu örnekte bitiş tarihini 31.12.2011 olarak alabiliriz.)

Buna göre UPDATE sorgumuzu çalıştıralım ve sonuca bakalım:

UPDATE RFM SET Recency = DATEDIFF (DAY, LastInvoiceDate, '20111231')

Görüldüğü gibi recency değerini hesaplatmış durumdayız. Sırada frequency var. Frequency bir kişinin ne sıklıkta alışveriş yaptığı bilgisini içerecekti. Yani fatura numaralarını tekil olarak saydırırsak bu değeri bulabiliriz.

UPDATE RFM SET Frequency=(SELECT COUNT(Distinct Invoice) FROM ONLINERETAIL_2010 where CustomerID=RFM.CustomerID)

Sonuca tekrar bakalım. Görüldüğü gibi frequency değerini de hesapladık.

Sırada monatery değeri var. Bir müşterinin yapmış olduğu toplam alışverişlerin parasal değeri. Bunu da aşağıdaki SQL sorgusu ile bulabiliriz. Burada her bir müşteri için birim fiyat ile miktarı çarptırıyoruz.

UPDATE RFM SET Monatery=(SELECT sum(Price*Quantity)  FROM ONLINERETAIL_2010 where CustomerID=RFM.CustomerID)

Sonuçlara bakalım. Görüldüğü gibi monetary değeri de hesaplanmış oldu. 

Bu aşamadan sonra R, F ve M için scale değerlerini hesaplamaya sıra geldi. Bunun için değerleri istenen kolona göre sıralayıp sıra numarasına göre 1-5 arası değerlendirmeye tabi tutmamız gerekiyor. Bunun için kullanacağımız komut ise Rank. Recency için uygulamayı gerçekleştirelim. Hatırlarsanız burada önemli olan nokta recency değeri en düşük olanın 5, en yüksek olanın 1 olacağıydı. Yani daha az süre önce alışveriş yapmış müşterinin skoru yüksek olacağını belirtmiştik. O nedenle sıralamayı desc diyerek büyükten küçüğe yapıyor ve büyük olanla 1 atayarak başlıyoruz.

UPDATE RFM SET Recency_Scale= 
(
 select RANK from
(
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Recency desc) Rank
FROM RFM
) t where  CUSTOMERID=RFM. CUSTOMERID)

Recency_Scale değerini de hesapladık.

Sırada Frequency_Scale var.

update RFM SET Frequency_Scale= 
(
 select RANK from
(
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Frequency) Rank
FROM RFM
) T where  CUSTOMERID=RFM. CUSTOMERID)

Görüldüğü gibi Frequency_Scale de hesaplanmış durumda.

Monetary_Scale değerini de şu şekilde hesaplıyoruz:

update RFM SET Monetary_Scale= 
(
 select RANK from
 (
SELECT  *,
       NTILE(5) OVER(
       ORDER BY Monetary) Rank
FROM RFM 
) t where  CustomerID=RFM.CustomerID)

Monetary_Scale de tamam.

Son olarak artık tüm değişkenlerimiz hesaplandığına göre geriye bir tek sınıflandırma etiketi kaldı. Aşağıdaki sorguya göre sınıflandırmalar yapılabilir.

UPDATE RFM SET Segment ='Hibernating' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[1-2]%'  
UPDATE RFM SET Segment ='At_Risk' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[3-4]%'  
UPDATE RFM SET Segment ='Cant_Loose' 
WHERE Recency_Scale LIKE  '[1-2]%' AND Frequency_Scale LIKE '[5]%'  
UPDATE RFM SET Segment ='About_to_Sleep' 
WHERE Recency_Scale LIKE  '[3]%' AND Frequency_Scale LIKE '[1-2]%'  
UPDATE RFM SET Segment ='Need_Attention' 
WHERE Recency_Scale LIKE  '[3]%' AND Frequency_Scale LIKE '[3]%' 
UPDATE RFM SET Segment ='Loyal_Customers' 
WHERE Recency_Scale LIKE  '[3-4]%' AND Frequency_Scale LIKE '[4-5]%' 
UPDATE RFM SET Segment ='Promising' 
WHERE Recency_Scale LIKE  '[4]%' AND Frequency_Scale LIKE '[1]%' 
UPDATE RFM SET Segment ='New_Customers' 
WHERE Recency_Scale LIKE  '[5]%' AND Frequency_Scale LIKE '[1]%' 
UPDATE RFM SET Segment ='Potential_Loyalists' 
WHERE Recency_Scale LIKE  '[4-5]%' AND Frequency_Scale LIKE '[2-3]%' 
UPDATE RFM SET Segment ='Champions' 
WHERE Recency_Scale LIKE  '[5]%' AND Frequency_Scale LIKE '[4-5]%'

Sonuçlara bakalım:

Tüm müşterilerimizi sınıflandırmış durumdayız. Hangi sınıftan kaç müşteri olduğuna bir bakalım:

İşlem tamam!

 

T-SQL hakkında daha geniş kapsamlı bilgiye erişmek, kariyerinizde bilginizle fark yaratmak isterseniz Miuul'un sunduğu T-SQL ile Veritabanı Programlama eğitimine göz atabilirsiniz.

 

Kaynaklar

Etiketler

T-SQL / RFM
Miuul topluluğunun bir parçası ol!

Abone ol butonuna tıklayarak Miuul'dan pazarlama ve haber içerikleri almayı onaylıyorum.