This document provides purchase data for 6 customers over 5 months and asks you to analyze customer profitability and rankings using 4 different methods: RFM scoring, PCV scoring, probability of being active (alive), and CLV calculations. The methods produce different rankings because they consider different factors like recency, frequency, monetary value, past profits, future purchase probability, and marketing costs in their calculations.
This document provides purchase data for 6 customers over 5 months and asks you to analyze customer profitability and rankings using 4 different methods: RFM scoring, PCV scoring, probability of being active (alive), and CLV calculations. The methods produce different rankings because they consider different factors like recency, frequency, monetary value, past profits, future purchase probability, and marketing costs in their calculations.
This document provides purchase data for 6 customers over 5 months and asks you to analyze customer profitability and rankings using 4 different methods: RFM scoring, PCV scoring, probability of being active (alive), and CLV calculations. The methods produce different rankings because they consider different factors like recency, frequency, monetary value, past profits, future purchase probability, and marketing costs in their calculations.
This document provides purchase data for 6 customers over 5 months and asks you to analyze customer profitability and rankings using 4 different methods: RFM scoring, PCV scoring, probability of being active (alive), and CLV calculations. The methods produce different rankings because they consider different factors like recency, frequency, monetary value, past profits, future purchase probability, and marketing costs in their calculations.
Assignment 2: RFM, PCV, Palive , NPV and CLV Calculation
Consider XYZ Books, a seller of Professional Textbooks and Reference Books. Below is the purchase data of 6 customers over last 5 months. You are required to do analysis (using any spreadsheet program) for the month of December (assume you are analysing the data in the month of December). The table below lists the rupee amount that each customer has spent during the period:
1. Analyze each customer’s profitability in December using the RFM Method. Use the following table for arriving at the RFM scores:
Recency = 20 points if purchased within the last 1 month
10 points if purchased 2 months ago 3 points if purchased 3 or 4 months ago (count only once i.e. If customer has purchased both 3 and 4 months ago (September and August), the recency point is 3, not 6) 2 points if purchased 5 or more months ago Relative weight 50% Frequency = 3 points for each purchase within the last 6 months Relative weight 20% Monetary Value = 10% of ₹ volume of Purchase within the last 6 months Relative weight 30% Hint: RFM Score = (weightrecency X scorerecency) + (weightfrequency X scorefrquency) + (weightmonetary X scoremonetary)
2. Assume that the gross contribution is 30% of the purchase amount. Compute each customer’s profitability (in the month of December) using the Past Customer Value (PCV) Scoring method, and rank the customers accordingly. Assume an annual discount rate of 15%. The formula for computing the 𝑻
𝑷𝑪𝑽 = ∑ 𝑮𝑪𝒊𝒏 𝑿 [𝟏 + 𝟎. 𝟎𝟏𝟐𝟓]𝒏
𝒕=𝟏 where i = customer n = number of months preceding the current month when purchases were made (for e.g. n=1 for purchase in November and n=2 for purchase in October) GC = Gross Contribution
Hint: First calculate the gross contribution in each month for each customer and apply the appropriate discount factor. For example, the discount factor for gross contribution in October is (1 + 0.0125)2. Summation of discounted gross contribution across all months will give you PCV scores for each customer 3. Calculate for each customer the probability of that customer being active – P(alive) in the months of: a) December b) January c) February
Hint: For calculating P(alive), use the following formula: P(alive)= tn
where, n is the number of months in which the customer has made purchases for the given period; and t is the time of the last purchase (expressed as a fraction) For Example, a customer bought 3 times in between July and November (Customer 4). We are at the end of November and want to assess his probability of being alive in June. t = (4/6) = 0.6667 (4 because last purchase was in October, 6 because the month that we are interested in is December) n=3 (3 because Customer 4 bought 3 times) Thus: P(alive) = (0.6667)3 = 0.296
4. You are also required to analyse profitability of each customer during the period December–February as of the beginning of December using the CLV method. Assume that gross profits are 25% of the purchase value. XYZ Books contacts its customers using: (a) through email campaigns guided to the online store, (b) by directly mailing catalogs to the customers. For the month of December through February, the cost of contacting a customer once via email is ₹ 0.25, and via direct mailers is ₹ 5.00. In each month on average, Customers 1, 2 and 3 are contacted via email 2 times, and direct mail 5 times. Customers 4, 5 and 6 receive email 1 time, and direct mails 3 times per month. Calculate and analyze the CLV of each customer for the months of December, January and February as of the beginning of December.
Hint: The CLV of a customer is their NPV of GC minus their NPV of Marketing Costs.
Step 1: Calculate the probability of the customer being alive, P (alive), for each month and then calculate the Net Present Value of GC of each customer using the formula: 𝑻 𝟏 𝒕 𝑵𝑷𝑽 𝒐𝒇 𝑮𝑪𝒊 = ∑ 𝑷(𝒂𝒍𝒊𝒗𝒆)𝒊𝒕 𝑿 𝑨𝑴𝑪𝑴𝒊 [ ] 𝟏+𝒅 𝒕=𝟏
where, NPV = Net Present Value
GCit = estimated expected gross contribution margin of customer i for a given month t AMCMi = average monthly gross contribution margin based on all prior purchases d = discount rate for month t (15% on a yearly basis, which is 1.25% per month) i = customer t is the month for which NPV is being estimated T is the number of months ahead that are included in the forecast (December, January and February) P (alive) is the probability that customer i is alive in month t (as previously computed)
Step 2: Calculate the Net Present Value of MC of each customer using:
𝒏 𝒏 ∑ 𝒄𝒊𝒎𝒍 𝑿 𝒙𝒊𝒎𝒍 𝑨𝑴𝑴𝑪𝒊 𝑵𝑷𝑽 𝒐𝒇 𝑴𝑪𝒊 = ∑ = ∑ (𝟏 + 𝒅)𝒍 (𝟏 + 𝒅)𝒍 𝒍=𝟏 𝒍=𝟏 where, ci,m,l=unit cost of marketing to customer i through channel m in month l. xi,m,l= number of contacts to customer i through channel m in month l. d= discount rate for money. n is the number of months ahead included in the forecast. AMMC= Average monthly marketing cost for all prior months
Step 3: Calculate CLV of each customer as: CLV= NPV of GC – NPV of MC
5. Compare the rankings of all the customers based on the results obtained from each of the four methods: a. RFM method b. PCV method c. NPV of GC d. CLV method
