1 Churn 0 132352941 For this assignment, lets assume the following Gross Margin for the firm is 30 We need this to calculate the margin the firm would earn on the revenue generated from their customers Assume annual marketing costs are $300,000 total A customer is considered retained if they have purchased from the firm in the past year The firm spent $500,000 to acquire all of these customers in 2019 2020 The firms discount rate is equal to its weighted average cost of capital, which is 10 Calculate Churn in terms of customer numbers and revenue Calculate the CLV for each customer Use 1 churn to be the retention rate Create CLV categories with cutpoints at the 25th percentile and 75th percentile This should give you three groups (0 24 99, 25 74 99, 75 ) Calculate customer equity for these groups What is average customer equity in each group Assume we have a Markov transition matrix that looks like this High Effort $250000 Low Effort $50000 Future CLV Grouping Future CLV Grouping CLV Low CLV Med CLV High CLV Low CLV Med CLV High Current CLV grouping CLV Low 0 65 0 25 0 1 Current CLV grouping CLV Low 0 8 0 15 0 05 CLV Med 0 25 0 5 0 25 CLV Med 0 2 0 6 0 2 CLV High 0 1 0 15 0 75 CLV High 0 1 0 2 0 7 How many customers would be in the respective groups based on the above Markov matrices Would what would the return on the marketing investment be for low effort and high effort marketing, if we base CLV on the average of the different groupings (calculated above) Should the firm invest in low effort or high effort Explain 5 Create two Pivot Tables to show a) How average CLV changes across customer frequency groups, and b) How average CLV changes across customer recency groups (You will need to create grouping variables on frequency and recency What would be good cut points for these data Recall that frequency is numerical and recency relates to days since purchase, so your categories should make sense from that perspective) 6 If you were working for the firm where this data was generated, how would you advise them to react to this information in terms of marketing activities Provide a one page memo to your fictitious manager outlining your rationale for how the firm should react to your analysis

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 19, 2024

1. Churn = 0.132352941 For this assignment, lets assume the following: Gross Margin for the firm is 30%. We need this to calculate the margin

1. Churn =

0.132352941

For this assignment, lets assume the following:

Gross Margin for the firm is 30%. We need this to calculate the margin the firm would earn on the revenue generated from their customers.
Assume annual marketing costs are $300,000 total.
A customer is considered retained if they have purchased from the firm in the past year.
The firm spent $500,000 to acquire all of these customers in 2019-2020.
The firms discount rate is equal to its weighted average cost of capital, which is 10%.

Calculate Churn in terms of customer numbers and revenue.
Calculate the CLV for each customer. Use 1-churn to be the retention rate.
Create CLV categories with cutpoints at the 25th percentile and 75th percentile. This should give you three groups (0-24.99, 25-74.99, 75 +). Calculate customer equity for these groups. What is average customer equity in each group?
Assume we have a Markov transition matrix that looks like this:

		High Effort		$250000			Low Effort		$50000
		Future CLV Grouping					Future CLV Grouping
		CLV Low	CLV Med	CLV High			CLV Low	CLV Med	CLV High
Current CLV grouping	CLV Low	0.65	0.25	0.1	Current CLV grouping	CLV Low	0.8	0.15	0.05
	CLV Med	0.25	0.5	0.25		CLV Med	0.2	0.6	0.2
	CLV High	0.1	0.15	0.75		CLV High	0.1	0.2	0.7

How many customers would be in the respective groups based on the above Markov matrices? Would what would the return on the marketing investment be for low effort and high effort marketing, if we base CLV on the average of the different groupings (calculated above). Should the firm invest in low effort or high effort? Explain.

5. Create two Pivot Tables to show: a) How average CLV changes across customer frequency groups, and b) How average CLV changes across customer recency groups. (You will need to create grouping variables on frequency and recency. What would be good cut points for these data? Recall that frequency is numerical and recency relates to days since purchase, so your categories should make sense from that perspective).

6. If you were working for the firm where this data was generated, how would you advise them to react to this information in terms of marketing activities? Provide a one-page memo to your fictitious manager outlining your rationale for how the firm should react to your analysis.