View the step-by-step solution to:

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

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

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.


Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question