R Notebook

Greg Metcalf works for a national credit card company, and he is performing a customer value analysis on a subset of credit card customers. In order to perform the RFM analysis on the customers, Greg has compiled a data set that contains the dates of the last transaction (Last-TransactionDate), total number of transactions in the past 2 years (Frequency), and total spending during the past 2 years (Spending). A portion of the data set is shown in the accompanying table.

a. Greg wants to calculate the number of days between April 1, 2019 and the last transaction date. Create a new variable “DaysSinceLast” which contains the number of days since the last transaction. What is the average number of days since the last purchase for all the customers? (Round your answer to two decimal places.)

library(readxl)
myData <- read_excel("Ch2_Q45_V03_Data_File.xlsx")
View(myData)

myData$Date = as.Date(myData$LastTransactionDate,format='%m-%d-%Y')
EndDate = as.Date("04/01/2019", format="%m/%d/%Y")
myData$DSL = abs(round(difftime(myData$Date,EndDate),digit=2))
round(mean(myData$DSL), digit=2)

Time difference of 501.82 days

b. Create the RFM scores for each customer. How many customers have an RFM score of 4 4 1? What is their average spending?

# five bins for days Since 1/1/2019, five bins for total spending, five bins for frequency
myData$DateReverse <- as.numeric(myData$DSL * -1)
recencyBins <- quantile(myData$DateReverse, probs =seq(0,1, by=1/5))
myData$Binned_Recency <- cut(myData$DateReverse, breaks = recencyBins, labels = c("1","2","3","4","5"), include.lowest=TRUE, right=FALSE)

FrequencyBins <- quantile(myData$Frequency, probs =seq(0,1, by=1/5))
myData$Binned_Frequency <- cut(myData$Frequency, breaks = FrequencyBins, labels = c("1","2","3","4","5"), include.lowest=TRUE, right=FALSE)

MonetaryBins <- quantile(myData$TotalSpending, probs =seq(0,1, by=1/5))
myData$Binned_Monetary <- cut(myData$TotalSpending, breaks = MonetaryBins, labels = c("1","2","3","4","5"), include.lowest=TRUE, right=FALSE)

myData$RFM <-paste(myData$Binned_Recency, myData$Binned_Frequency, myData$Binned_Monetary)

n = length(which(myData$RFM == "4 4 1"))
myData$AverageOrderSize = myData$TotalSpending/n

myData[which(myData$RFM == "4 4 1"),]

c. Create a new variable called “LogSpending” which contains the natural logarithms for the total spending during the past 2 years. Bin the logarithm values into 5 equal interval groups. Label the groups using numbers 1 (lowest values) to 5 (highest values). How many observations are in group 2?

myData$LogSpending = round(log(myData$TotalSpending),digit=2)
ht = quantile(myData$LogSpending, probs=seq(0,1, by = 1/5))
myData$Binned_Log = cut(myData$LogSpending, breaks=5, labels = c("1","2","3","4","5"), include.lowest=TRUE, right=FALSE)
table(myData$Binned_Log)

  1   2   3   4   5 
  3  10  17  56 114 

d. Create a new variable called “AverageOrderSize” which contains the average spending per order. This is calculated by dividing total spending (Spending) by total number of transactions (Frequency) in the past 2 years. Bin the values of AverageOrderSize into 5 equal interval groups. Label the groups using numbers 1 (lowest values) to 5 (highest values). How many observations are in group 2?

myData$AverageOrderSize <-myData$TotalSpending/myData$Frequency
myData$Binned_AOS <- cut(myData$AverageOrderSize, breaks=5, labels = c("1","2","3","4","5"), include.lowest=TRUE, right=FALSE)
table(myData$Binned_AOS)

  1   2   3   4   5 
169  23   6   1   1