Marketing Mix Modeling Assignment
Objective:
You will analyze Red Bull's advertising data to determine which marketing channel is most effective in driving sales. Your goal is to identify the channel that deserves a $100,000 bonus by running regression models and interpreting the results.
Instructions:
1. Download and Explore the Data
- The dataset contains advertising expenditures for different channels (Facebook, Instagram, TV, Twitter, etc.) and their corresponding sales.
- Each file represents a single advertising channel (e.g., "redbull-facebook.xlsx" contains Facebook ad spend and sales).
- Key Variables:
- Sales (Dependent Variable): Total company revenue.
- Ad Spend (Independent Variable): Amount spent on a specific marketing channel.
2. Run Simple Linear Regression for Each Channel
Perform a separate regression for each advertising channel to determine its impact on sales.
- Step-by-Step Guide for Excel:
- Open Excel and load one of the Red Bull data files (e.g., "redbull-facebook.xlsx").
- Go to Data → Data Analysis → Regression.
- Set Input Y Range to the Sales column.
- Set Input X Range to the ad spend column (e.g., Facebook ad spend).
- Click OK to run the regression.
- Key Output to Analyze:
- β (Beta Coefficient): Represents the expected increase in sales for every $1 spent on that channel.
- R² (R-Squared Value): Measures how well ad spend explains sales variation.
- p-value: Determines if the relationship is statistically significant (p < 0.05 is ideal).
- Prediction Task:
- Using the regression equation, predict the expected sales amount for $100 in spending on each channel.
- Repeat this process for all channels (Facebook, Instagram, TV, etc.).
3. Compare Results & Identify the Best Performing Channel
- The best channel will likely have:
- The highest beta coefficient (strongest impact on sales).
- A statistically significant p-value (p < 0.05).
- A high R² value (indicating good model fit).
- Based on your findings, determine which marketing channel should receive the $100K bonus.
4. Run a Multiple Regression (Advanced Analysis)
- Open the "redbull_student.xlsx" file (contains all advertising channels in one dataset).
- Run a multiple regression with sales as the dependent variable and all ad spend variables as independent variables.
- Follow the same steps as before, but select all ad spend columns as Input X Range.
- Evaluate Model Performance:
- Which channel has the highest beta value in the multiple regression?
- Does removing insignificant variables improve Adjusted R²?
5. Present Your Findings
- Create a clear, visually appealing summary of your results.
- Use tables, charts, or a concise report to justify your choice for the $100K bonus winner.
- Consider including:
- A summary of key regression outputs.
- A chart showing predicted sales for $100 of ad spend.
- Insights from the multiple regression model (if applicable).
How is a Good Model Defined?
A good model: ✔ Has a high R² value, indicating strong explanatory power. ✔ Has statistically significant predictors (p < 0.05). ✔ Produces realistic and actionable insights (e.g., does increasing spend logically increase sales?). ✔ Avoids multicollinearity in multiple regression (strong correlations between independent variables can distort results). ✔ Can be used to make accurate predictions about future sales performance.
Final Question: Who Should Win the $100K Bonus?
Using your analysis, justify which marketing team deserves the bonus based on their advertising effectiveness.
This version provides students with clearer guidance on what to do at each step and how to evaluate the quality of their model. Let me know if you’d like any further refinements!