Customer Lifetime Value

You are a marketing analyst for an online retailer of hand-crafted consumer goods (think an all online version of World Market (Links to an external site.), but more eclectic in style and taste). Your company would like to understand the Customer Lifetime Value associated with each of its current customers. The company is aware that CLV calculation can be an exceptionally complex exercise, but it also knows that “starting (relatively) simple” with CLV estimates can be a good way to begin.

You’ve been tasked with the responsibility to deriving the CLV estimates for current customers. More than mere math, your company also wants you see if you can construct dynamic CLV models for each customer; that is, build models that show how marketing INPUTS shape and influence the forecasted CLV of a customer. Your company uses three primary marketing inputs to manage current customers:

• Direct mailers that focus on UPSELLING. [getting the customer to spend more within product categories they’re already active in] Historically, different customers have been sent a different # of mailers per period.
• Direct mailers that focus on CROSS-SELLING. [getting the customer to spend money in product categories they’re not yet active in but are related to products they have already bought] Historically, different customers have been sent a different # of mailers per period.
• Discount credits applied to future purchases [“Rewards Bucks” that are earned from current purchases, which are then automatically applied as discounts on future purchases]. Each customer has a unique percent discount credit that gets applied to future purchases. Some customers have lower reward % (like 10%) while others have much more generous discount rewards (20% or more).

The company also has a few behavioral attributes about each customer. The dollar amount of each of the customer’s last two purchases is provided. Further, we also know whether or not the customer was active in each of the two previous periods. “Active” in a “period” for this company is defined as whether or not the customer made any purchase in the last 6 months. This company wants to base their CLV calculations based on the next 5 years of predicted customer activity.

PART 1: Static (non-dynamic) CLV calculation & interpretation

For this first part, use the Excel sheet Assignment_7_Part1_CalculateBaseCLV.xlsx (Links to an external site.)

This Excel has a lot of the CLV work already done for you. Notably, see columns “Active (% chance) NEXT Period” and “prediction(REV)“. Some other analyst has already provided you with specific forecasts for each of the customers regarding their forecasted activity (%) and revenue. Whew, that’s fortunate for you, since that can often be challenging work!

Your task in this this sheet is to complete the spreadsheet (you’ll notice there are red cells saying “calculate me ( Step X)” ).

Your submission for Part 1 should be a one-page (max) double-spaced report summarizing your calculation strategy and results ( if you wish to include tables, figures, charts, and screenshots they do not count toward this page length requirement). Here’s some things to consider summarizing:

• If you had to make any judgment calls / make any assumptions as part of your CLV calculations, note them and justify them
• Depict your CLV equation (possibly illustrate it by way of an example). Consider whether explaining it in Excel terms (cell references) or more generally (depicted as a typical math equation) best suits your purpose.
• What is the average CLV? Low? High?
• This CLV model isn’t dynamic (why?) but you could still approximately test a few scenarios to help your reader understand the model a bit. As just a few examples, what would happen to CLV if customer activity suddenly dropped? Say, every customer had 20% lower chance of being active in the next period? What if revenue per customer increased by average of 10%?

PART 2: Dynamic CLV calculation & interpretation

For this part, use Assignment_7_Part2_FOR STUDENTS.xlsx (Links to an external site.) . Note there are two tabs. Note that there are a 1,000 customer records here (rows 10 to 999 are just hidden by default). Note that there are little Comments added to cells (check E4 as an example) to aid you as well. By default, direct mailers cost $0.75 per unit.

The company is aware that Discount, Upsells, and Cross-sells (marketing inputs) actually affect customer behavior in the future (forecasted % Active, forecasted Revenue). In this sheet, you’re going to actually model the dynamic interplay between these inputs and outputs for each customer. In other words, the values calculated in Columns J, K, and L should change if we change the inputs for customers in Columns E, F, and G.

To help you along, some other analyst has already calibrated the response models for % Active and Revenue for you (check sheet “CalibratedParamater”). Whew, that is nice! Still, you’re going to have to utilize that information to complete this spreadsheet.

Your submission for Part 2 should be a two pages (max) double-spaced report summarizing your calculation strategy and results ( if you wish to include tables, figures, charts, and screenshots they do not count toward this page length requirement). The complete content of the report is up to your discretion (see earlier suggestions for Part 1 as a starting point), but management does want clear answers to the following questions:

1. If we implement a new policy that means only our “best” 10% of customers receive a standard 20 percent discount credit and all the rest receive a flat 10 percent discount, how will that affect CLV and Customer Equity? Is it a good or bad plan?
2. We have a new marketing manager who thinks we should standardize our cross-selling and upselling direct mailers. She is proposing we simply send 10 and 10 to each customer for future periods. Based on your analysis, what is your assessment of this plan?
3. Assuming we can’t send more than 40 upselling/cross-selling (combined) to any one customer per period and we can only send a total of 15,000 mailer per period, should we re-allocate who and how we distribute our mailers?
4. You’re welcome / encouraged to consider discussing other alternative scenarios or hypotheticals that are illuminating to management.

5. Criteria
   This criterion is linked to a Learning Outcome Part 1Well-formatted? Are support materials / information provided that allow reader to audit /understand the source of calculations and interpretations?
   This criterion is linked to a Learning Outcome Part 2 Well-formatted? Are support materials / information provided that allow reader to audit /understand the source of calculations and interpretations? Did report include novel CLV optimization solution(s)