MMK365- Tutorial Solution- Week 9

pdf

School

Deakin University *

*We aren’t endorsed by this school

Course

MMK365

Subject

Marketing

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by DrBoar2427

Ali Tamaddoni, PhD MMK365 D EAKIN B USINESS S CHOOL D EPARTMENT OF Marketing MMK365 Marketing Insights Tutorial: Week 9 Solution 1- Carnegie mart is a privately owned grocery store in the Melbourne suburb of Carnegie. It earns $300 contribution margin per customer every year on an average. The store currently has about 1000 regular customers, and it loses 10% of customers every year to competition. To improve its market position, Carnegie mart is considering two strategies A. An advertisement campaign targeted at 4000 residents at a cost of $100,000. The campaign is expected to bring additional 400 customers to the store. B. A loyalty program to reward its existing customers which is expected to cost $10 per customer per year ($10,000 in first year) and is expected to help retain half of the defecting customers. Analyze whether acquisition or retention would be the best strategy for Carnegie mart. You can assume a discount rate of 10%. Strategy A Margin per customer = $300 Attrition rate = 100/1000 = 10% Retention rate = 1- attrition rate = 90% CLV = Margin * ((retention rate / (1+discount rate – retention rate)) = 300 * [(.9)/ (1+0.1-0.9)] = $1350 In strategy A, $100,000 is spent to acquire 400 new customers whose lifetime value is $1350. So, the value generated by this strategy is 400 * $1350 - $100,000 = $440,000. The total value of all customers of the firm now is 1000 * $1350 + $440,000 = $1.79M. Strategy B $10 per customer is spent to cut the attrition rate to half. The new retention rate now is 0.95 and the new margin from customers is $290 (if we subtract the retention investment of $10 from the margin from customer). The CLV of each customer now is, CLV = 290 * (.95) / (1+0.1-0.95) = $1837. The total value from strategy B now will be = 1000 * $1837 = $1.837M.

Ali Tamaddoni, PhD MMK365 So, strategy B yields higher total value than strategy A and is therefore the recommended strategy. 2- Virgin Mobile Phones charges $9.99 per month for mobile connection. Variable costs are about $2 per account per month. Virgin also has a marketing spend of $10 per year, with an attrition rate of 0.25% per month. They also have a monthly discount rate of 2%. What is their customer lifetime value? CLV = Margin × Retention rate / (1 + Discount rate - Retention rate) Contribution margin per month = $9.99 - $2 - (10/12) = $7.16. Retention rate = 1 – 0.25% = 0.9975. Discount rate = 2% or 0.02. CLV = $7.16 * 0.9975/(1 + 0.02 - 0.9975) = $317.28. 3- Complete the following lift analysis table obtained from running an RFM analysis on a dataset of 1,250,000 customers, with 162,753 churners. Decile Number of customers Number of churners Response rate (%) Lift Concentration (%) 1 125,000 45,221 2 125,000 32,456 3 125,000 25,654 4 125,000 18,227 5 125,000 12,769 6 125,000 9,433 7 125,000 7,463 8 125,000 6,553 9 125,000 3,475 10 125,000 1,502 Total 1,250,000 162,753

Ali Tamaddoni, PhD MMK365 Decile Number of customers Number of churners Response rate (%) Lift Concentration (%) 1 125,000 45,221 36.2% 2.78 27.79% 2 125,000 32,456 26.0% 1.99 47.73% 3 125,000 25,654 20.5% 1.58 63.49% 4 125,000 18,227 14.6% 1.12 74.69% 5 125,000 12,769 10.2% 0.78 82.53% 6 125,000 9,433 7.5% 0.58 88.33% 7 125,000 7,463 6.0% 0.46 92.92% 8 125,000 6,553 5.2% 0.40 96.94% 9 125,000 3,475 2.8% 0.21 99.08% 10 125,000 1,502 1.2% 0.09 100.00% Total 1,250,000 162,753 13.0% Example calculations for decile 2 Response rate = (No. churners in decile 2) / (No. customers in decile 2) = 32,456 / 125,000 = 26.0%. Lift = (Response rate in decile 2) / (Overall response rate) = 26.0%/13.0% = 1.99. Concentration (%) = (Cumulative no. churners up to decile 2) / (Total no. churners) = (45,221 + 32,456) / 162,753 = 47.73%. 4- The Time magazine charges $12 per month for its subscription. Variable cost is $2 per customer per month, with marketing spending of $6 per year, and an attrition rate of 0.5% per month. At a monthly discount rate of 1% what is the CLV a customer over a period of 6 months? Since we have a defined time horizon the finite formula should be used. 𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝑚𝑚𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 = ($12 − $2 − $6/12) = $9.5 𝑅𝑅𝑅𝑅𝐶𝐶𝑅𝑅𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝐶𝐶𝑚𝑚𝐶𝐶𝑅𝑅 = 0.995 𝐷𝐷𝐶𝐶𝐷𝐷𝐷𝐷𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝐶𝐶𝑚𝑚𝐶𝐶𝑅𝑅 = 0.01 CLV over 6 months = 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 + 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 × 𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 1+𝑑𝑑𝑅𝑅𝑑𝑑𝑑𝑑𝑅𝑅𝑑𝑑𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 + 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 × � 𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 1+𝑑𝑑𝑅𝑅𝑑𝑑𝑑𝑑𝑅𝑅𝑑𝑑𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 � 2 + 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 × � 𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 1+𝑑𝑑𝑅𝑅𝑑𝑑𝑑𝑑𝑅𝑅𝑑𝑑𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 � 3 + 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 × � 𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 1+𝑑𝑑𝑅𝑅𝑑𝑑𝑑𝑑𝑅𝑅𝑑𝑑𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 � 4 + 𝑀𝑀𝑚𝑚𝐶𝐶𝑚𝑚𝐶𝐶𝐶𝐶 × � 𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 1+𝑑𝑑𝑅𝑅𝑑𝑑𝑑𝑑𝑅𝑅𝑑𝑑𝑅𝑅𝑅𝑅 𝑟𝑟𝑟𝑟𝑅𝑅𝑅𝑅 � 5

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Ali Tamaddoni, PhD MMK365 CLV over 6 months = $9.5 + $9.5 × 0 . 995 1+0 . 01 + $9.5 × � 0 . 995 1+0 . 01 � 2 + $9.5 × � 0 . 995 1+0 . 01 � 3 + $9.5 × � 0 . 995 1+0 . 01 � 4 + $9.5 × � 0 . 995 1+0 . 01 � 5 = CLV over 6 months = 9.5 + 9.36 + 9.22+ 9.08 + 8.95+ 8.81 = $54.92 5- Tom, runs a local grocery store in Burwood, and is planning to open his second store in a nearby neighbourhood. To this end, Tom would like to have a better understanding of his customers’ spending behaviour and factors affecting that. Tom provided you with the dataset (“data_question5.xlsx”) collected from a sample of his customers. You are asked to help Tom understand the data and obtain the insights. (1) What is the best central tendency statistic for ‘average_spending’ of customers in this dataset? (2) What is the effect of one’s order_count on her/his average_spending? What insights can you draw? What will you recommend Tom to do based on these insights? (3) Jack, who runs a local convenient store in Richmond, is a friend of Tom. Jack is also considering to get some insights about his customers buying behaviour. Jack asks you the following question during his consultation with you: “Do new customers spend less per transaction?” Can you use the available dataset (i.e., data_question5.xlsx) to answer Jack’s question? If yes, please conduct the data analysis and answer Jack’s question; if no, please explain why you cannot use this data to advise Jack. (1) Answer: The descriptive statistics of average_spending is average_spending Mean 70.25962 Standard Error 1.506334 Median 68.5 Mode 71 Standard Deviation 15.36166 Sample Variance 235.9805 Kurtosis -0.91217

Ali Tamaddoni, PhD MMK365 Skewness 0.295889 Range 62 Minimum 41 Maximum 103 Sum 7307 Count 104 Since the skewness is 0.29, which is between -1 and 1, the ‘mean’ is the best central tendency statistics. (2) Analysis : average_spending=a+b*order_count+c*new+d*loction+error SUMMARY OUTPUT Regression Statistics Multiple R 0.96996283 R Square 0.9408279 Adjusted R Square 0.93905273 Standard Error 3.79240895 Observations 104 ANOVA df SS MS F Regression 3 22867.75382 7622.58461 529.995191 Residual 100 1438.236562 14.3823656 Total 103 24305.99038 Coefficients Standard Error t Stat P-value Intercept 27.9807396 1.367703118 20.4581969 1.6119E-37 Order_count 1.49036163 0.037976355 39.2444625 1.4803E-62 new -4.6064337 0.747949946 -6.158746 1.5404E-08 location -1.0315973 0.754754509 -1.3667985 0.17475349 (a) Since the estimated coefficient of order_count is 1.4903 and its P-value of order_count is 1.4803E-62 < 0.05 , one’s order_count has a positive and significant effect on her/his average_spending. (b) Holding other things constant, if one’s order_count increases by one, then her/his average_spending will increase by $1.4903 .

Ali Tamaddoni, PhD MMK365 Insights: More frequent buyers are likely to have higher spending per purchase. Recommendation: Tom should work to encourage customers to visit the store more frequently. Alternative model: Answer: The regression model is 𝑚𝑚𝑎𝑎𝑅𝑅𝐶𝐶𝑚𝑚𝑚𝑚𝑅𝑅 _ 𝐷𝐷𝑠𝑠𝑅𝑅𝐶𝐶𝑠𝑠𝐶𝐶𝐶𝐶𝑚𝑚 = 𝑚𝑚 + 𝐶𝐶 ∗ 𝐶𝐶𝐶𝐶𝑠𝑠𝑅𝑅𝐶𝐶 _ 𝐷𝐷𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 + 𝑅𝑅𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 Perform the regression analysis in Excel gives Coefficients P-value Intercept 24.80542 2.53E-32 order_count 1.492183 6.97E-57 (3) The available dataset cannot be used to answer the question for Jack’s use, because: (1) Jack is in Richmond, but the data only applies to Burwood or Surrey Hills, as indicated by the variable “location” in the data (2) It is not in the feasible domain of the prediction 6- A survey of a company’s customers reports that there were 20% promoter, 70% passives and 10% detractors. Based on the survey results calculate the Net Promoter Score for this company. NPS = promoter (%) – detractors (%) = 20-10 = 10 7- Calculate the Net Promoter Score for company X based on the sample data in the following table: ID On a scale from 0 to 10 how likely are you to recommend company X to a friend or colleague? 1 10 2 10 3 0 4 5 5 9 6 7 7 4 8 3 9 7 10 9 11 8

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Ali Tamaddoni, PhD MMK365 12 4 13 8 14 8 15 9 16 7 17 1 18 2 19 2 20 10 Promoters count = 6  6/20 = 30% Passives count= 6  6/20 = 30% Detractors count = 8  8/20 = 40% NPS = 30% - 40% = -10 8- Julie, a micro-entrepreneur, operates an online store that sells gaming devices. She provides you with the following information about two potential customers. Calculate the net CLV of these potential customers to help Julie choose the one with a higher long-term profit. Name: Patty Annual Margin: $600 Number of purchases per year: 1 Retention rate: 10% Acquisition cost: $600 Discount rate: 10% Net CLV = CLV – acquisition cost CLV = $Margin (M) × � 𝐶𝐶𝑅𝑅𝐶𝐶𝑅𝑅𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝐶𝐶𝑚𝑚𝐶𝐶𝑅𝑅 ( 𝐶𝐶 ) 1 + discount rate(d) − retention rate (r) � CLV = 600 × � 0.1 1 + 0.1 − 0.1 � = $60 Net CLV = 60 – 600 = - 540 Name: John Annual Margin: $300 Number of purchases per year: 1 Retention rate: 80% Acquisition cost: $600 Discount rate: 10%

Ali Tamaddoni, PhD MMK365 Net CLV = CLV – acquisition cost CLV = $Margin (M) × � 𝐶𝐶𝑅𝑅𝐶𝐶𝑅𝑅𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶𝐶 𝐶𝐶𝑚𝑚𝐶𝐶𝑅𝑅 ( 𝐶𝐶 ) 1 + discount rate(d) − retention rate (r) � CLV = 300 × � 0.8 1 + 0.1 − 0.8 � = $800 Net CLV = 800 – 600 = $200 Looking at the Net CLV of the two potential customers, John is the preferred customer to be acquired.

MMK365- Tutorial Solution- Week 9

Related Documents