Assignment:
It is very important for analytics to have the ability to review campaign performance and give direction on what needs to be kept and what needs to be cut from the budget. Using the attached example of one of our TV campaign stat sheets, please answer each of the following questions, including detailed explanation if necessary. The goal is to stay under a $550 CPA, with the target of hitting a $400 CPA, while sustaining a given budget.
1. Which two week span produced the highest sales total?
A. What was that number?
B. What was the CPA during that two week period?
C. What was the total expense during that two week period?
2. What are the best 3 trending DMA's during this 6 week span?
3. What is the best performing "buy" (station & program) in the 3 markets you selected?
4. Our target is a $15k net budget per week for the remaining 3 weeks of December 2012. You can only use up to two spots per week per timeslot on each station. You can select as many timeslots as you prefer to use per station based on the data you see. Produce a full order and include each timeslot on each station you would purchase and the corresponding net cost for each timeslot.
A. What would be your forecast for total sales per week?
B. What is the CPA average per week for the last three weeks in December?
5. Considering all information within the Excel sheet, what else would you analyze to improve ROI and grow this campaign?
6. What negative trends have you noticed that need to be adjusted?
7. What positive trends have you noticed that should be capitalized on?
8. In the number series below, nothing has changed with volume or rates. Please complete the cells with your CPA projections for November and December.
C. How did you determine your answer to Question 4B?
|
Month
|
Orlando CPA
|
Tampa CPA
|
Jacksonville CPA
|
|
Jan
|
$250
|
$800
|
$450
|
|
Feb
|
$225
|
$750
|
$350
|
|
March
|
$310
|
$775
|
$350
|
|
April
|
$390
|
$780
|
$425
|
|
May
|
$405
|
$650
|
$450
|
|
June
|
$430
|
$500
|
$600
|
|
July
|
$500
|
$400
|
$350
|
|
Aug
|
$460
|
$425
|
$425
|
|
Sept
|
$600
|
$450
|
$450
|
|
Oct
|
$550
|
$425
|
$500
|
|
Nov
|
|
|
|
|
Dec
|
|
|
|
A. How did you determine your projections?
9. Considering all information within the Excel sheet, what other questions do you have that you believe affect the bottom line of these campaigns?