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?

The Sheet “By Week” has been used to answer this question

A. What was that number?

By seeing the information in the column # of Sales, the highest values are 78 and 63 and this corresponds to the weeks of 11/26/2012 and 12/3/2012 respectively.

B. What was the CPA during that two week period?

The CPA was $399.99 and $533.75 during 11/26/2012 and 12/3/2012 respectively. The values were used from the column named “CPA”.

C. What was the total expense during that two week period?

The column values in “Total spend” are used. For the week of 11/26/2012, the total spend was $31,199 and for the week of 12/3/2012, the total spend was $33,626.

2. What are the best 3 trending DMA’s during this 6 week span?

To do this, I created a new pivot table as shown in Excel by the name ‘By Market’. The best DMAs were the ones with highest number of sales. The highest numbers of sales are 90, 54 and 29 respectively. Thus the best markets are Pittsburgh, Atlanta and Norfork respectively.

3. What is the best performing “buy” (station & program) in the 3 markets you selected?

The Sheet named “By Station” has been used to answer this.
In the top, the area was selected as “Pittsburgh”. Then the station and program with highest sales was selected. For Pittsburgh it was 27 and the show and station are News and ABC – 13 Pittsburgh.
Similarly, for Atlanta, it was News, NBC – 13 Atlanta.
For Norfork, it was News, ABC – 30 Norfork.

Final Project Content: Pick a retail store at which you frequent...