This assignment to be completed individually.
Assignment #6: ROAS Evaluation
The goal of this assignment is to practice reviewing dollars spent on advertising and estimate the return on the investment. Review the following scenarios and determine if the project meets the needs of the business.
STEP 1:
Use the following excel sheet which can also be found in the Week 6 files folder:
Data for Homework for Week 6 .xlsx Download Data for Homework for Week 6 .xlsx
STEP 2:
Use the Powerpoint template below to answer the following questions/ complete the following exercises:
ROAS Assignment Template.pptx Download ROAS Assignment Template.pptx
Scenario 1:
Calculate the ROAS and answer the following questions:
Based on Facebook, which year had the best ROAS, Jan-2020 or Jan-19?
If the benchmark for a well-performing ROAS is 3.50x which year is exceeding?
If your spend was $150,000 more in Jan 2020, but revenue stayed the same, what would your new ROAS be?
What could have accounted for the difference between the GA revenue figure & the FB revenue figure? How would you determine which one is correct?
Exercise:
Exercise:
Include a bar graph that shows the ROAS for Jan-19 vs. Jan-20 (GA vs. FB)
1-2 sentences describing your chart/ graph
2 sentences – What does this graph tell us about GA vs. FB ? -include data points from your analysis file
Scenario 2:
Fill in the yellow boxes, so you can calculate the ROAS for your Google Search campaigns and answer the following questions:
Did Branded ROAS improve from 2019 to 2020 or get worse?
Did branded or non-branded search have a higher ROAS in 2020?
If the benchmark for Google Search ROAS is a 6x or better, which year met the ROAS goal?
Why do you think the CTR for branded search higher than non-branded search?
Exercise:
Screenshot of non-branded vs. branded search – include observations 4 sentences or more – use data points from your Excel analysis
Include a bar graph that shows the ROAS for Jan-19 vs. Jan-20 ( Branded vs. Non-Branded )
1-2 sentences describing your chart/ graph
2 sentences – What does this graph tell us about Branded vs. Non-Branded? -include data points from your analysis file
Scenario 1 vs. Scenario 2:
Include 4-5 sentences on where you think this company should invest their marketing budget in 2021, FB or Google based on the ROAS achieved in each.
Include data points from your Excel file to reinforce your recommendation.
Here’s a refresher on some of the formulas we’ve used in this class so far:
ROI (Return on investment) = ($ Gains – $ Investment) / $ investment X 100 (can format as % instead of multiplying by 100 in Excel)
ROAS (return on ad spend) = Amount gained from ads / Amount spent on ads
CVR ( Conversion rate ) = total conversions or transactions / total clicks
CTR (Click-Through Rate) = Clicks / Impressions
CPC ( Cost Per Click) = total ad spend /Clicks
AOV ( Average Order Value) = Total Revenue / transactions OR AUR (average units retail) * UPT (units per transaction)
% Change = (new – old) / old
Final Submission:
Your final submission should include 2 Files:
Your Excel File:
The completed tables in Excel
Your bar graphs
Your Powerpoint File:
Your Answers to the questions – in the template
Scenario 2 example non-branded vs. branded
Bar graphs for each scenario showing the ROAS for Jan-19 vs. Jan-20 (GA vs. FB, Branded vs. Non-Branded). It should also include a few sentences on where you think this company should invest their marketing budget in 2021, FB or Google based on the ROAS achieved in each.
