Module 8 Assignment

This Module Assignment is a little different than previous Module Assignments. You will apply skills you learned in your School of Business prerequisites (specifically math, business communication, accounting/finance/Excel, and lower-division business classes) to a "real world" marketing scenario. I will be evaluating two of the Undergraduate Business Program Learning Goals, Problem Solving & Decision Making and Business Communication (LG1 & LG3). Detailed formatting guidelines and grading rubrics for each Learning Goal can be found at the end of this document.

You are a digital marketing intern at a small footwear company located in Portland, OR. You have been asked to evaluate the financial performance of your social media advertising campaigns and make recommendations about how to allocate your digital advertising budget. Your manager gave you the following information:

• Online sales earn a 12% profit margin.
• Your monthly social media advertising budget is $20,000
• You currently allocate 30% to Facebook, 30% to Instagram, 20% to TikTok, and 20% to Twitter.

Part 1: Data Analysis

Download the Campaign Data file attached to the Assignment Dropbox.

Use pivot tables and formulas to evaluate this data. For example, this screenshot is calculating total clicks, grouped by social media channel, and by day of the week. (Pro Tip: Notice how I used conditional formatting to highlight the highest and lowest values in each data range)

Include all of your calculations, pivot tables, and graphs in your excel document (in other words, "show all of your work").

Calculate the following values and enter them in the "Answer Sheet" tab of the Campaign Data file:

1. Which platform had the highest number of clicks? What was it?
2. Which platform had the lowest number of clicks? What was it?
3. Which day of the week had the highest number of clicks? What was it?
4. Which day of the week had the lowest number of clicks? What was it?
5. Which platform had the highest Click-Thru rate? What was it?
6. Which platform had the lowest Click-Thru rate? What was it?
7. Which day of the week had the highest Click-Thru rate? What was it?
8. Which day of the week had the lowest Click-Thru rate? What was it?
9. Which platform had the highest conversion rate? What was it?
10. Which platform had the lowest conversion rate? What was it?
11. Which day of the week had the highest conversion rate? What was it?
12. Which day of the week had the lowest conversion rate? What was it?
13. Which platform had the highest total revenue? What was it?
14. Which platform had the lowest total revenue? What was it?
15. Which day of the week had the highest total revenue? What was it?
16. Which day of the week had the lowest total revenue? What was it?
17. Which Platform had the highest Average Revenue per Transaction? What was it?
18. Which Platform had the lowest Average Revenue per Transaction? What was it?
19. Which day of the week had the highest Average Revenue per Transaction? What was it?
20. Which day of the week had the lowest Average Revenue per Transaction? What was it?
21. Which platform had the highest total profit? What was it?
22. Which platform had the lowest total profit? What was it?
23. Which day of the week had the highest total profit? What was it?
24. Which day of the week had the lowest total profit? What was it?
25. Which Platform had the highest Average Profit per Transaction? What was it?
26. Which Platform had the lowest Average Profit per Transaction? What was it?
27. Which day of the week had the highest Average Profit per Transaction? What was it?
28. Which day of the week had the lowest Average Profit per Transaction? What was it?
29. Which platform had the highest ROAS (Revenue)? What was it?
30. Which platform had the lowest ROAS (Revenue)? What was it?
31. Which platform had the highest ROAS (Profit)? What was it?
32. Which platform had the lowest ROAS (Profit)? What was it?

Check Digits:

To make sure you are calculating your values correctly, you should be able to reproduce these values:

Glossary of Terms:

• Platform: A specific social media/advertising channel, like Facebook.
• Impression: An impression represents one advertising "view". If five people see an ad, there are five impressions.
• Click: When a person sees an ad and clicks on it.
• Transaction: A purchase.
• Click-Thru Rate:The percent of views that resulted in a click.
• Conversion Rate:The percent of clicks that resulted in a transaction (purchase).
• ROAS: This is the percent "return on ad spends". It is identical to ROI. You may be asked to calculate it using revenue and/or profit.
• Pivot Tables:This is a simple but powerful data analysis tool built into Microsoft Excel. If you haven't used pivot tables before (or don't remember how to use them), I've provided a series of short tutorial videos in the content module.

Final Deliverables:

1. Submit your Excel spreadsheet with all tabs showing all of your work and all answers in the Answer Sheet tab to the Module 8 Marketing Analytics dropbox. If I can't see how you got your answers, I will have to assume you did not produce them yourself.

Grading:

Your grade will be based on:

1. The answers to the 32 questions.
2. Proper use of Microsoft Excel analysis tools and techniques (formulas, pivot tables, conditional formatting, and charts/graphs).

*Review the Learning Goal objectives and rubrics below for guidelines and expectations of this assignment.

Learning Goal 1: Problem Solving & Decision Making

Graduates of the Business Administration Baccalaureate program will have the capacity to recognize and understand a wide range of common and novel business problems and to follow a systematic approach to solve them effectively.

Learning Objectives:

1. Problem definition: Students will construct clear and specific problem statements using contextually relevant evidence.
2. Research techniques and data analysis: Students will employ in-depth information and data from relevant sources throughout the problem-solving process.
3. Solution evaluation: Students will thoroughly evaluate solutions from multiple dimensions using appropriate analytical tools.
4. Decision making: Students will make decisions based on a synthesis of research, data analysis, and evaluation of solutions.

	Exceeds Expectations	Meets Expectations	Does Not Meet Expectations
Problem definition	Clearly and thoroughly defines the problem using most relevant contextual factors.	Sufficiently defines the problem using most relevant contextual factors.	Does not construct a clear problem statement.
Research techniques & data analysis	Organizes and synthesizes in-depth data and information from a wide range of research resources. Thorough synthesis of data and information.	Uses a range of sources and a range of analytical tools. Evidence of synthesis of data and information from these sources.	Does not use relevant data or analytical tools appropriately.
Solution evaluation	Thoroughly evaluates solutions in an elegant manner with creative consideration of relevant factors.	Sufficiently evaluates solutions in an adequate manner with appropriate consideration of relevant factors.	Insufficiently evaluates solutions.
Decision making	Cogently presents decisions, using a rich synthesis of research, data analysis, and evaluation of solutions.	Sufficiently presents decisions based on a basic synthesis of research, data analysis, and evaluation of solutions.	Does not present a reasonable or appropriate decision.