Customer Relationship Management

Customer Relationship Management
Prof. V. Kumar
Prof. V. Kumar
Regents’ Professor,
Richard and Susan Lenny Distinguished Chair & Professor of Marketing,
Executive Director, Center for Excellence in Brand & Customer
Management, Director of the Ph.D. Program in Marketing
J. Mack Robinson College of Business, Georgia State University, Atlanta GA
and
Chang Jiang Scholar, HUST, Wuhan China.
Fellow, Hagler Institute for Advanced Study, TAMU, College Station,
TX Senior Fellow, Indian School of Business, India Email: [email protected]
Assignment 1
Consider ABC Sportswear, a catalog seller of Sportswear. You have data on the purchase behavior
of six customers over the last five months (assume you are analyzing the data in the month of
June). The following table gives the dollar amount that each customer spent during January
through May:
Customer Jan Feb March April May
1 80 0 200 30 70
2 30 60 0 150 0
3 50 0 50 0 40
4 0 20 0 50 0
5 30 200 0 0 40
6 100 40 130 100 90
The following table gives the frequencies of purchase in each month for the six customers:

1 3 0 2 1 1
2 1 2 0 3 0
3 1 0 1 0 1
4 2 1 1 5 0
5 1 0 0 0 5
6 4 1 3 1 0
1. Analyze each customer’s profitability in June using the RFM Method and rank the
customers based on their scores. Use the following table for the RFM scores:
1
Prof. V. Kumar
Recency = 20 points if purchased within the last 1month

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 in 3 and 4 months
ago (Feb and March), 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%
2. Let the Gross contribution be 30% of the purchase amount. Compute each customer’s
profitability (in the month of June) using the Past Customer Value Scoring method, and
rank the customers accordingly. Assume an annual discount rate of 15%. The formula for
computing the Past Customer Value is given below:
5
 GC *(1 0.0125) n
n1 in
where, i = customer
n = number of months preceding the current month when purchases were made
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 April
is (1+0.0125)2. Summation of discounted gross contribution across all months will give you
PCV scores for each customer.
2
Prof. V. Kumar

Prof. V. Kumar
and
Assignment 2
Consider ABC Sportswear, a catalog seller of Sportswear. You have data on the purchase behavior
of six customers over the last five months (assume you are analyzing the data in the month of
June). The following table1 gives the dollar amount that each customer spent during January
through May:

1 80 0 200 30 70
2 30 60 0 150 0
3 50 0 50 0 40
4 0 20 0 50 0
5 30 200 0 0 40
6 100 40 130 100 90
The following table gives the frequencies of purchase in each month for the six customers:

1 3 0 2 1 1
2 1 2 0 3 0
3 1 0 1 0 1
4 2 1 1 5 0
5 1 0 0 0 5
6 4 1 3 1 0
3. Calculate for each customer the probability of that customer being active in the months of
a) June
1
Note: This is the same data set as in Assignment 1
3
Prof. V. Kumar
b) July
c) August
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 &
t is the time of the last purchase (expressed as a fraction)
Illustration: Say, a customer was active for 3 months between Jan and May (Customer 4).
We are at the end of May and want to assess his probability of being alive in June.
t = (4/6) = 0.6667 (4 because last purchase was in April, 6 because month that we are
interested in is June)
n=3 (3 because Customer 4 bought 3 times)
3
Thus: Prob. (Alive) = (0.6667) = 0.296
4. We want to know customers’ profitability for the months of June through August as of the
beginning of June using the CLV. Let the gross profits be 30% of the purchase amount. ABC
Sportswear has three ways of contacting customers with marketing messages- (a) through email
campaigns guided to the online store, (b) by directly mailing catalogs to customers and
(c) selling through telephone call-ins. For the month of January through May, the cost of
contacting a customer once via email is $0.25, via direct mailers is $1 and via telephone
calls is $3. On the average per month, Customers 1, 2 and 3 are contacted via email 2 times,
and direct mail 5 times while Customers 4, 5 and 6 receive email 1 time, and telephone calls
3 times per month.
Now calculate and analyze the CLV of each customer for the months of June, July and
August as of the beginning of June.
Hint:
The CLV of a customer is their NPV of GC minus their NPV of Marketing
Cost. (Refer notes for formula)
Step 1: Calculate the probability of the customer being alive, P (alive) for each month and
then calculate the Net Present Value using the formula:
where,
NPV = Net Present Value
GCit = estimated expected gross contribution margin for a given month t
AMCMi = average monthly gross contribution margin based on all prior purchases
4
Prof. V. Kumar
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 (June, July and August)
P (Alive) is the probability that customer i is alive in month t (previously computed)
Step 2: Calculate NPV of MC using:
Where
ci,m,t=unit cost of marketing to customer i through channel m in month t.
xi,m,t= number of contacts to customer i through channel m in month t.
d= discount rate for money (15% on a yearly basis which is 1.25% per month).
T 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) Past Customer Value Scoring Method
c) NPV of GC
d) CLV Method
Why do you think the rankings differ?
5
Prof. V. Kumar

Prof. V. Kumar
and
Assignment 3
Objective: to compute the Lifetime Value (LTV) and develop a differential strategy based
on the LTV
Is the dollar value of the first purchase indicative of lifetime value?
The marketing manager of a mail order catalog firm is revisiting past customer data with a
view to lay down some guidelines for the future. Currently, the firm does not differentiate between
its customers. All catalogs are sent to all customers in the database.
She has a hunch that the first purchase made by a customer may be an indicator of the future
profitability of that customer. If this were to be true, she would be able to tailor all marketing
activities to a customer based on the very first purchase that a customer makes. The marketing
manager decided to look at a set of 7,953 customers who first purchased six years ago.
To make the analysis comparable, she extracted data pertaining to the first purchase and all the
subsequent purchases for a period of 5 years from the date of the first purchase for each individual
customer in this group. Thus if a customer had made the first purchase six years ago in March 1,
2009 the five-year period for that customer started the day after the first purchase i.e. on March 2,
2009. She noted that some customers did not make a repeat purchase in a given year, but there
were some customers who made more than one repeat purchase.
The marketing manager found out that for this group of 7,953 customers, the average initial
purchase amount was $58. She decided to split the customers into two groups using $50 dollars as
the dividing value. There were 4,657 customers whose initial purchase value was less than $50.
The remaining 3,296 customers had spent at least $50 on their first purchase. For each of the two
groups, she obtained the average initial order value and the average repeat order value for
each of the following five years.
6
Prof. V. Kumar
In order to compare the two groups, the marketing manager would need to compute the average lifetime
value of the customers in each group. She is armed with the following formula:
n
Pt (Qt  t ) n
( Dt  Rt )
LTV  
t 1 dt
 
t 1 dt
A
where,
Pt = the probability of purchase in period t
Qt = the quantity purchased in period t
t = the margin on purchases in period t
dt = the discount rate , where d = (1+(interest rate * risk factor))
Dt = costs to develop the relationship in period t
Rt = cost to retain the customer in period t
A = initial acquisition cost
n = the number of periods
The marketing manager summarized the data that was at her disposal:
1. New Customer Acquisition costs: (to compute A)
a) Average cost to obtain prospect name = $0.10
b) Average cost to send initial catalog = $0.75
c) Average response rate = 2.3%
2. Customer Purchase Analysis: (to compute Qt in dollars)
a) New customers who made their first purchase 6 years ago = 7953
b) Average initial purchase = $58, hence customers are split into two
groups: Group1: # of customers with initial purchase < $50 is 4657
Group2: # of customers with initial purchase >=$50 is 3296
c) Table 1 and Table 2 indicate the number of repeat purchases made by these customers
d) Table 3 gives the average initial order size (in dollars) and the average repeat
order size for the 5 years that followed
3. Development and Retention costs: (to compute Dt + Rt)
b) Number of catalogs mailed annually to each acquired customer = 5
c) Cost of mailing a catalog to a customer = $0.75
4. Average Margin on orders ( t ) = 42%

5. Annual interest rate = 20% (to compute d)
6. Risk Factor = 1 (to compute d)
7. Since the analysis is being done on past data the value of Pt is taken as 1
7
Prof. V. Kumar
Additional hints on solving the problem

Points to remember:
 All calculations should be per customer basis because you will be computing average
 lifetime value for a customer.
 Always multiply order amount by the average margin (=42%) while calculating the LTV.
 Remember that you are bringing back the contributions to the year of first purchase. So for
orders placed in Year 1, t is taken as 1 and for orders placed in Year 2, t = 2 …… and for
 Year 5, t = 5. These values of t can be applied in the equation for LTV.
 Remember to incorporate the value of the initial order in your calculations for Q2.
Computation of LTV:
Since the analysis is being done on past data, Pt = 1. Hence the equation for LTV gets reduced to,
(Qt  t ) n ( Dt  Rt )
n
LTV   t
 t
A
t 1 d t 1 d
Calculating the quantity term (Qt):
This is illustrated by applying to last year orders for <$50 group.
<$50 group
# orders last year
Frequency of Total
Year 5 # of orders Total # of orders = (0*3837)+(1*626)+(2*141)
0 3837 0 +(3*38)+(4*10)+(5*3)+(8*2)=1,093
1 626 626
2 141 282 Qt * πt (1,093*53.63*42%) = $24,619.40
3 38 114 dt = (1+20%)5 = 2.49
4 10 40
5 3 15
(Qt * πt)/( dt) = $24,619.40/2.49 = $9,893.98
8 2 16
Total 4,657 1,093
8
Prof. V. Kumar
Questions:

1. What is the average lifetime value of a customer in each of the two groups with the initial
order value?
 Group1- # of Customers with initial purchase < $50
 Group2- # of Customers with initial purchase >=$50

2. Is the decision to mail all catalogs to all customers justified in the light of the above
analysis?
3. What other methods of grouping these customers can be considered that will help us
differentiate customers based on their value?
4. What can we predict in terms of behavior in the coming year? What additional analysis
would we need?
9
Prof. V. Kumar

Prof. V. Kumar
and
Assignment 4.1 (Reading Based Questions)
For the article, “Knowing What to Sell, When, and to Whom”, please answer the following
questions:
1. What are the two problems in the traditional model for predicting customer behavior that
are addressed by the proposed model?
2. What are the benefits of a sales/contact strategy based on the proposed model? Based on
your experience, do you foresee any organizational challenges in implementing such a
strategy?
For the article “What Drives Customer Equity”, please answer the following questions,
3. What are the drivers of customer equity? Among these drivers, which one do you think
is the most important driver for customer equity? Why?
4. Are these drivers related to each other? If they are, what are their relationships
between each other? What can be done to improve brand equity?
10
Prof. V. Kumar

Prof. V. Kumar
and
Assignment 4.2 (Reading Based Questions)
Please read the article “Getting the Most out of All Your Customers”, and answer the following
questions.
1. What are the problems of focusing on customer acquisition and retention as measures of
performance? Explain how ARPRO approach works? Is there any drawback of this approach?
2. Can this framework be used for other resource allocation besides acquisition and retention?
Please provide example if necessary.
Please read the article “Building and Sustaining Profitable Customer Loyalty for the 21st
Century”, and answer the following questions.
3. Explain how a two-tiered reward system is used (in terms of the three fundamental objectives) as
an optimal way to build and sustain customer loyalty. Does this system apply in all cases or are
there situations where other approaches might be better?
4. What do you see as the potential obstacles that managers might face when implementing a
loyalty program with this framework?
11
Prof. V. Kumar
Prof. V. Kumar
and
Assignment 5
Consider a telecommunication service provider. You have the following quarterly data:
STATISTICS TYPICAL CONSUMER
Number of referrals per period (n=n1+n2) 6

of which, customers that joined due to the referral (n1) 2
of which, customers that would have joined anyway (n2) 4
Marketing cost per period (Mty) $20
Average gross margin (Aty) $70
Cost of referral (aty) $15
Acquisition cost savings (ACQ1ty and ACQ2ty) $10
Yearly discount rate (r) 15%
Calculate CRV of a typical customer for one year (over 4 quarters).
12
Prof. V. Kumar
Prof. V. Kumar
and
Assignment 6
1. With respect to its long-term pleasant relationship with IBM, Welingkar refers six other
universities to purchase IBM’s cloud computing service. The degree to which
Welingkar’s reference impacted university n’s purchase decision (Ref), the degree of
influence of Welingkar’s reference on university n (DOI), the customer lifetime value of
university n, and the time university n became a customer of IBM that are given here:
University Name Ref DOI CLV ($) Time
NIMS 50% 80% 1725 Year 1
GSU 60% 50% 1204 Year 1
IIM-B 30% 40% 4000 Year 2
Alliance 60% 30% 1205 Year 2
ICFAI 40% 30% 2415 Year 3
Bombay University 50% 50% 2055 Year 3
Calculate the Business Reference Value (BRV) of Welingkar for IBM. Assume the
discount rate is 12% per year.
13
Prof. V. Kumar
2. Acquisition Equity: Three companies want to assess their acquisition equity to

understand if there is room for improvement. They provide a consultant with the number
of prospects they have, the acquisition cost per prospect, their response rate, the average
sales revenue from the first sale, and the gross margin.
Acquisition Avg. Sales

Number of cost per Response Rev. from Gross
Prospects prospect Rate 1st Sale Margin
Company 1 10000 $0.60 4% $45.00 40%
Company 2 10500 $0.55 3% $35.00 40%
Company 3 5200 $0.70 5% $75.00 40%
3. Calculate the average acquisition equity per customer for each of the companies.
Also, which company needs to reexamine their strategy the most? Give at least one
way they could easily change their average acquisition equity per customer.
14
Prof. V. Kumar
Prof. V. Kumar
and
Assignment 7
What is the CIV of each customer (7 customers)?
Amber
Jung
Joe
Ashley
Lauren
Maria Jose
Customer Amber Ashley Joe Lauren Jung Maria Jose

CLV 10 20 10 25 10 15 20
CIV
Hint: CIVAshley = [CLVMaria + 0.5CLVJose] +[CIVMaria + 0.5CIVJose]
15
Prof. V. Kumar

Prof. V. Kumar
J. Mack Robinson College of Business, Georgia State University, Atlanta
GA and
Assignment 8
Objective: To find the optimal level of Customer Brand Value (CBV) components to
maximize Customer Lifetime Value (CLV).
The marketing manager of a mail order catalog firm is revisiting customer data with a view to lay
down some guidelines for the future. Currently, the firm does not differentiate between its
customers. By looking at the customer transaction and marketing data, the marketing team has
computed CLV for its 100 customers. Also, the marketing team has surveyed these customers to
obtain the measurements of customer brand value (shown in A8-CBV to CLV Linking – Data.xlsx).
Responses to eight components of Customer Brand Value (CBV) have been obtained which ranges
from 1 to 10 (with scale value 10 highest, 1 lowest). With the information, the marketing team now
wants to know how to maximize the customers’ profitability by optimizing each brand value
components.
6. Find the relative importance of CBV components by performing a regression analysis.
Hint:
CLVi β0 β11Awarenessi β12Awarenessi2 β21Imagei β22Imagei2 β31Trusti
+ β32Trusti2 β41Affecti β42Affecti2 β51Purchase Intentioni
+ β52Purchase Intentioni2 + β61Premium Pricei β62Premium Pricei2
+ β71Loyaltyi β72Loyaltyi2 β81Advocacyi β82Advocacyi2 εi
7. Find the optimal level of brand value components (8 components) to maximize the
average CLV of all customers.
Hint: The optimal level of brand value components should not go below the current average level of
all customers, or above the highest possible level. E.g. Brand Awareness should not go below 5.25 or
above 10.
16

Customer Relationship Management

Uploaded by

Copyright:

Available Formats

Customer Relationship Management

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Customer Relationship Management

Uploaded by

Copyright:

Available Formats

Customer Relationship Management