The purpose of this assignment is to familiarize you with the basics of calculating RFM and CLV and to demonstrate how they lead to different implications for marketing. Please answer the questions at the end of the RFM and CLV sections. Due February 28.
We are using a very simple model for both RFM and CLV in this exercise. The directions below assume that you are using Excel, but feel free to use SPSS or any other analytical tool you are comfortable with. There is a tip file called "Calculation tips" if you are not sure where to start.
For both the RFM and CLV calculations, you will use the file "bookclub.xls"
This file has data from 2300 customers of an online bookstore, BBBC (BookBinder's Book Club). The variables are:
Customer ID: exactly what it is, the customer's ID number.
Choice: Whether the customer purchased the book The Art History of Florence.
1 corresponds to a purchase and 0 corresponds to a nonpurchase. (We won't use this in this exercise.)
Amount purchased: Total money spent on BBBC books.
Frequency: Total number of purchases in the chosen period (used as a proxy for frequency.)
Last purchase (recency of purchase): Months since last purchase.
First purchase: Months since first purchase.
The goal of the RFM analysis is to assign customers to groups who vary on the amount they spend, their frequency of purchase and how recently they purchased. This assignment will be based on quartiles of the variables Amount, Frequency and Recency.
Our simple model is
RFM score = Amount quartile + Frequency quartile + Recency quartile,
where each of the quartile scores ranges from 1 - 4, where 4 is the highest amount, greatest frequency, and most recent purchase. You will end up with RFM scores ranging from 3 to 12, for a total of 10 groups.
Remember that Recency is reverse scored, so that in the data file the lower numbers are better. You will need to recode the quartile assignments for Recency in order for them to be consistent with the coding for Amount and Frequency.
How many customers are in each group? What is the average amount purchased by each group (use Amount, not the quartile score to figure this out. The easiest way to do this is with a Pivot Table.)
Recently Asked Questions
- What mRNA code would you expect for a polypeptide for the sequence CAAAAATGACTATCA if TGACTA were an intron? a. GUUUUUACUGAUAGU b. CAAAAATGACTATCA c.ACUGAU d.
- A hot air balloon rising straight up from a level field is tracked by a range finder 150m from the lifto↵ point. At the moment the range finder’s elevation
- Are their any Military Veterans that are tutors? I need their opinion on w hat would the military look like if it was more horizontal and decision making was