Calculation tips for RFM and CLV exercises: RFM We are going to use quartiles to divide the customers in groups. Each quartile contains 25% of the customers. In other words, the first quartile has people from 0-25%, the second has 26- 50%, the third has 51-75% and the fourth has 76-100%. For each variable of interest, we will find the numbers that separate the different quartiles. In Excel, use the QUARTILE function to find the 25th, 50th, and 75th percentiles for Amount, Frequency and Recency. When you know the quartiles, you can create the new variables Amtcode, Freqcode and Reccode and recode the data from the original variables into the new variables. Each variable should take the value 1-4, where 4 is the best (highest amount, highest frequency and most recent purchase). For Amtcode and Freqcode, numbers above the 75th percentile should be coded 4, above the 50th to the 75th should be 3, above the 25th to below the 50th 2, and 25th and below should be 1. Recency is based on the month of the last purchase, so smaller numbers are better. Because of this, for Reccode you need to assign the value 4 to the 25th percentile, 3 to the 50th, 2 to the 75th, and 1 to the maximum. The reason for this is that we want the variables to be consistent so that when they are added, the highest numbers are the best prospects and the lowest numbers are the worst. You can assign the values to the new variables by using nested IF statements or by sorting and relabeling. In SPSS you can do this with Visual Binning and automatic cutpoints (under the Transform tab). (Tip on this below) Once you have your new variables, create another variable called RFM by adding together Amtcode, Freqcode and Reccode. RFM scores will range from 3 to 12, giving you ten groups of customers. These ten groups will be the input for the CLV calculation