1 Objective
GBA 464: Assignment 3
The general goal of this task is that we try to distinguish potential high-value consumers and sepa- rate them from low-value consumers. Recency, frequency and monetary value (RFM) are 3 factors that we can calculate and use to do the targeting. In this assignment, we will work with a sample dataset from a company called CDNOW, to try and figure out the potential value of a consumer in a given month, using only historical data prior to this month. We will then classify the sample by
the “RFM index” we generated and see how much it is related to actual consumer spending.
2 Loading the data [20%]
You can obtain the dataset via the following link: https://dl.dropboxusercontent.com/s/xxfloksp0968mgu/CDNOW_sample.txt
Note that the data is stored in fixed format, meaning that each variable starts at a fixed column in the text file. I’ve written the code for reading the data. Basically, I use the function ’read.fwf()’ to read the data.
In the raw data, the first two variables are individual consumer identifiers. The second one is a re-coded version of the first one. For simplicity, we drop the first variable and only use the recoded ID as identifiers. I’ve already written this part as well. After dropping the first column in
1
the original data, the remaining columns are individual ID ($id), date of the trip ($date), purchase quantity (i.e. number of CDs purchased, $qty) and total expenditure (in dollar values, $expd).
Our next step is to aggregate the data into individual-month level, so keys should be $id, $year, and $month. During this aggregation process, we should sum up quantity and expenditure for each consumer in each month. You also need how many trips (construct $trips) the individual has been to the shop. Assign the collapsed data (again, on the key of $id, $year and $month) to a new data frame.
Of course, most people will not go to the shop and buy something every month. But we need an RFM prediction for each individual in every month (between January 1997 and June 1998, 18
months in total). When there is no trip in a given month, replace trip, expenditure and quantity to zero. Now, you should be ready to compute recency, frequency and monetary value separately.
3 Computing the RFM measures [80%] 3.1 Recency [20%]
Keep in mind that for any measure in RFM, we can only use historical data, i.e. data in the months before the current month. In this note, we define recency as the number of months since the last
month with purchase. In the example below, if an individual has been to the store in month 1, 2
and 5, her recency is NA in month 1 (because we do not know anything before the data starts), 1 in month 2, 1 in month 3, 2 in month 4, 3 in month 5, and 1 in month 6.
We talked about an example in class that is similar to this recency measure. However, the way we constructed that measure was not optimal. Optionally, try to optimize your algorithm when you construct the recency measure.
3.2 Frequency [20%]
We define frequency as the total number of trips a given individual made in the previous quarter.
A quarter is defined as one of Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec. If the observation is in the very 2
first of this individual, we assign frequency to NA.
3.3 Monetary value [20%]
Monetary value is defined as –still using historical data– the average monthly expenditure for a consumer, in the previous months when she purchased something. For example, in month 1, the consumer came to the store and spent in total 15 dollars. Then, in month 2, her monetary value is 15. In month 2, the consumer came again and spent a total of 30 dollars. Then her monetary value in month 3 is the average, i.e. (15 + 30)/2 = 22.5. She did not come in month 3 and 4, so her monetary value did not change. Finally, she came in month 5 and spent 20, and thus her monetary value is (15 + 30 + 20)/3 = 21.7.
3.4 Example
Let’s create an artificial example with individual “0”. Note that we’ve already organized the data into individual-year-month level, and included the months with no trip. Note that we have computed trips by counting the number of trips in a month with purchase. The last row shows that these statistics should be re-calculated for the next consumer.
id year month trips qty expd quarter recency frequency monval
019971 019972 019973 0 1997 4 0 1997 5 0 1997 6 119971
1115 2230 000 0 0 0 1 3 20 0 0 0 1229
1 NA NA NA
1 1
1 1
2 2
2 3
2 1
1 NA NA NA
NA 15 NA 22.5 3 22.5 3 22.5 3 21.7
Following this example, please calculate recency, defined.
frequency and monetary value measures as
3
4 Targeting [20%] 4.1 RFM index
An RFM index is an weighted sum of the 3 measures, for each individual i in month t:
RFMit =b1Rit+b2Fit+b3Mit
For now, let’s say it is your marketing team’s responsibility to tell you what are the factor loadings.1 For now let’s take b1 = −0.05, b2 = 3.5 and b3 = 0.05. Note that if a consumer is considered “high value” if she has low recency, or high frequency, or high onetary value. I have coded this section already but you need to run it or change the corresponding variable names.
4.2 Validation [20%]
When you have computed this measure, sort your sample according to the RFM index and split it into 10 (roughly) even-sized portions. One way is to use quantile() to generate the cut-offs. The high RFM parts refer to individuals (in particular months) that are more valuable than the low RFM parts of your sample. Examine the average monthly expenditure for each bin of sample, defined by the deciles of the RFM index.2 Plot the average spending (and potentially some other measures if you want to) by group and confirm that the result is more or less monotonic. Which groups of consumers do you want to target?
For example, you might produce something similar to this. You might not get the exact figure because the result depends on how you segment the market.
1In principle, we should estimate these loadings and we should use another sample to validate our results. 2Deciles: 10% quantiles.
4
Figure 1: Average expenditure by deciles in the RFM index
5