Home » Posts tagged 'Database marketing'
Tag Archives: Database marketing
RFM stands for
- Recency – How recently did the customer purchase?
- Frequency – How often do they purchase?
- Monetary Value – How much do they spend?
To create an RFM analysis, one creates categories for each attribute. For instance, the Recency attribute might be broken into three categories: customers with purchases within the last 90 days; between 91 and 365 days; and longer than 365 days. Such categories may be arrived at by applying business rules, or using a data mining technique, such as CHAID, to find meaningful breaks.
If you are new to RFM or need more step by step help, please read here
and here is R code- note for direct marketing you need to compute Monetization based on response rates (based on offer date) as well
##Creating Random Sales Data of the format CustomerId (unique to each customer), Sales.Date,Purchase.Value sales=data.frame(sample(1000:1999,replace=T,size=10000),abs(round(rnorm(10000,28,13)))) names(sales)=c("CustomerId","Sales Value") sales.dates <- as.Date("2010/1/1") + 700*sort(stats::runif(10000)) #generating random dates sales=cbind(sales,sales.dates) str(sales) sales$recency=round(as.numeric(difftime(Sys.Date(),sales[,3],units="days")) ) library(gregmisc) ##if you have existing sales data you need to just shape it in this format rename.vars(sales, from="Sales Value", to="Purchase.Value")#Renaming Variable Names ## Creating Total Sales(Monetization),Frequency, Last Purchase date for each customer salesM=aggregate(sales[,2],list(sales$CustomerId),sum) names(salesM)=c("CustomerId","Monetization") salesF=aggregate(sales[,2],list(sales$CustomerId),length) names(salesF)=c("CustomerId","Frequency") salesR=aggregate(sales[,4],list(sales$CustomerId),min) names(salesR)=c("CustomerId","Recency") ##Merging R,F,M test1=merge(salesF,salesR,"CustomerId") salesRFM=merge(salesM,test1,"CustomerId") ##Creating R,F,M levels salesRFM$rankR=cut(salesRFM$Recency, 5,labels=F) #rankR 1 is very recent while rankR 5 is least recent salesRFM$rankF=cut(salesRFM$Frequency, 5,labels=F)#rankF 1 is least frequent while rankF 5 is most frequent salesRFM$rankM=cut(salesRFM$Monetization, 5,labels=F)#rankM 1 is lowest sales while rankM 5 is highest sales ##Looking at RFM tables
Note-you can also use quantile function instead of cut function. This changes cut to equal length instead of equal interval. or see other methods for finding breaks for categories.
The Ohri Framework tries to create an economic alternative to proprietary data mining softwares by giving more value to the customer and utilizing open source statistical package R , with the GUI Rattle , hosted on a cloud computing environment.
It is based on the following assumptions-
1) R is relatively inefficient in processing bigger file sizes on same desktop configuration as other softwares like SAS.
2) R has a steep learning curve , hence the need for the GUI Rattle .
3) The enhanced need for computing resources for R is best solved using a cloud computing on demand processing environment. This enables R to scale up to whatever processing power it needs. Mainstream data mining softwares charge by CPU count for servers and are much more expensive due to software costs alone.
Closed Customers aren’t really closed; they stay on in your database.
Database marketing can help you win revenue even after a customer relationship is discontinued. This is illustrated by the following example – A prominent global financial services giant, with nearly 100 years of history, faced a unique problem while operating in India. While it had been one of the earliest entrants in the credit card industry in India, it had rapidly been losing market share to newer and nimbler more aggressive local competitors.
Indian customers have one of the lowest levels of debt worldwide due to cultural aversion to debt and lack of competition in the pre 1990 era. The credit cards receivables business in India is also a loss making operation as of 2006, because of rampant competition and discounting on annual fees and charges. Lending in India is complicated because the credit bureau CIBIL was only in nascent stages, and declared income and actual income of people varied due to the tax laws and ‘black money’ economy.
However the average receivables per card had been steadily increasing in India and it had potential to make huge profits once Indian customers became comfortable with rotating balance and paying finance charges. The credit card division also had a culture of conservative lending only to prime customers, with a good track record. On the other hand, the company’s personal loan business was making great strides in both revenue and profitability growth due to aggressive selling to both prime and sub prime customers. As a result of this the company had built up a database of 3 million customers, out of which nearly 2 million had paid off their loans.
To improve the profitability of the credit card division, and offer its customers a more value added portfolio of financial services, the company embarked on a data mining project of cross selling to its closed personal customers. After extensive tests and research based on selective tele-calling to its customer database, the company found out the following analytical findings-
1) Customers who had paid back their loans on time were the customers who were good credit customers. These customers had also increased their income since the time they had closed their personal loan.
2) People who had closed their personal loans were targeted for re churning by the person loans business. However after 6 months of closing their loans, if the customer did not take another personal loan, they were unlikely to ever take a personal loan. Thus if these customers were called again for personal loan, it would be unprofitable since the incremental expenses were not justified by incremental revenues.
3) People who bounced cheques but paid off their entire loan were bad credit risks, especially for a revolving line of credit as in for credit cards.
4) People who were called by the credit card division had better brand recall if they had an earlier relationship with personal loans division. Since they paid off their loans on time, their experiences with the company as a whole were very positive. This goodwill of the company’s brand helped to trigger higher response ratios (almost 20 % of such people took the credit card compared to only 5 % for the general population)
5) De to regulatory reasons both the credit card division and the personal loan division had to maintain an arm’s length distance. In order to do so, the credit card division decided on a transfer price of 600 rupees plus 1% of average receivables to the personal loan division. This helped track the profitability of the exercise better.
As a result of the exercise the company managed to sell an extra fifty thousand credit cards. The program was such a success that it was adopted world wide. The personal loan division earned tens of millions of rupees from its closed customer database, and the credit card division managed to increase its market share slightly.
Thus mining its own database of customers helped the company achieve the following-
a) Increase profitability
b) Improve brand recall and enhance the existing relationship
c) Cut down on marketing costs by targeting more responsive customers
d) Improve the life time value of revenues earned from each customer
This article builds up an argument for using internal data at a customer level for decreasing marketing costs and enhancing brand recall.