Marketing Analytics report with graphs, tables and data analysis

MSc Strategic Marketing Online 2021/22
Marketing Analytics
INDIVIDUAL ASSESSMENT
Assignment Brief
As a brand manager of Pepsi-Cola, you are asked to build an understanding of the sales return of its marketing mix decisions. To that end, information on weekly brand sales of Pepsi and Coca-Cola are collected over a period of 116 weeks.
The data cover real-world sales and marketing mix information on Pepsi-Cola and Coca-Cola aggregated across a selection of grocery stores at 1 European grocery retailer. The Excel file “cola.xls” contains the data on both brands. Brand 1 in the data refers to Coca-Cola, and brand 2 refers to Pepsi-Cola. For example, “sales.brand1” (column B) refers to the sales of CocaCola, whereas “sales.brand2” refers to the sales of Pepsi-Cola (column K). The objective of the analysis is to build an understanding of Pepsi’s sales drivers (not Coca Cola’s drivers). In other words, your response variable will be “sales.brand2” (not “sales.brand1”).
Note that the data are proprietary. No part of the data may be used for other purposes than for completing this assignment, and please do not further distribute any part of the data.
Brand 1 Brand 2
Coca-Cola Pepsi-Cola
The following variables are available for each brand:
Variable name Variable description
Week Ranges from 1 to 116. “1” indicates the first week of the data, “116” the last week of the data. “1” corresponds to the first week of January (in 2015).
Sales Total weekly brand sales (expressed in litres)
Feature Percentage of SKUs (Stock Keeping Units) on feature
Display Percentage of SKUs (Stock Keeping Units) on display
Price Average paid price (i.e., after discounts) per litre
Assortment Average number of SKUs (Stock Keeping Units) by the brand stocked by the retailer
TV Total weekly spending (in euro) on TV advertising
Digital Total weekly spending (in euro) on Digital advertising
OOH Total weekly spending (in euro) on Out Of Home (OOH) advertising
Magazine Total weekly spending (in euro) on Magazine advertising
Questions
1) Calculate descriptive statistics about the sales and marketing mix variables for both brands. Report the statistics and summarize learnings and insights. For instance, who is the market leader? How do the brands compare in terms of pricing, promotion, assortment? How do the brands compare in terms of allocation of spending between the four advertising instruments?
2) Estimate a regression model with a log-log specification that explains Pepsi’s sales
(“sales.brand1”) as a function of Pepsi’s own marketing mix variables. Only include the following 8 predictors in the model: Pepsi’s feature, display, assortment, price, tv, digital, ooh, and magazine (i.e., exclude Coca-Cola’s marketing mix variables from your model). Only add a value of 1 to predictors that contain zero values before estimation. Report the results of the model. Based on this model, what do you conclude about the relation between Pepsi’s marketing mix variables and its sales?
3) The brand would like to understand whether its TV, OOH, magazine, and digital advertising have a longer-term effect on its sales. Estimate the same model as estimated in question 2, but now use an adstock specification for all four advertising instruments, setting lambda to 0.6 for all four advertising instruments. Report the results of the model, interpret the estimates about the impact of advertising on sales and about the impact of the other marketing mix elements, and discuss your findings.
4) Reflect critically on the validity of the estimated coefficients in question 3. For instance, to what extent can the model accurately predict future outcomes? To what extent are predictor variables correlated with one another? Are there any potentially important excluded variables? What are the implications for the validity of your findings? How can the model be improved?
Evaluation
The overall objective is to illustrate that you can use the available data and modelling techniques to build an understanding of sales drivers, interpret results to generate insights, reflect critically on the validity of the model, and provide solutions to overcome limitations of a model.
Marks are assigned per question:
• Question 1: 20% • Question 2: 10% • Question 3: 20%
• Question 4: 40%
• 10% is based on the overall quality of presentation of the answers (e.g., use of tables/figures to present results, clarity and quality of presented answers)
Format
• PDF document: Times New Roman 12, single spaced. Maximum 6 pages (excl. cover page). There is no word count.
o Structure your report along the questions and indicate this clearly using headings in the report (“Question 1”, “Question 2”, “Question 3”, “Question 4”).
o Integrate tables/figures in the main part of your report
• Submit the R code along with your PDF report.