Please answer the following questions after reviewing the data on the Excel sheet.

Situation: A Ford car dealership is preparing the release of a new Ford Focus model. In anticipation of the release date, they decide to launch a digital marketing program with a budget of $100,000. Historical evidence suggests that getting prospect customers to test drive the car is the most efficient way to generate sales - so they set the program's goal to achieve 250 test drives.

For simplicity:

- the company will be using email marketing as well as placing banners on Autotrader.ca. They will invest the $100 k equally on the two campaigns.

- they will focus their effort on generating as many CTR to drive traffic to the company website, and their visitors are offered the opportunity to fill out a form and sign-up for the test drive...specifically targeting 30-45 year olds

Email marketing database:
Database distribution by age bracket:
15-24	10,000
25-30	10,000
30-34	12,500
35-45	15,500
>45	2,000	The company's experience with their ESP suggests the following conversions: - email bounce rate 20% - CTR 50% - cost per email: $2.5 - share on social media 25% - % emails forwarded 20%
Total	50,000

Company website:

Historically the website showed the following numbers:

- visitors only came through marketing campaigns (ex email and banner)

- 40% bounce rate

- 25% of visitors filled in personal details from email CTRs

- 15% of visitors filled in personal details from Banner advertising

- 20% of those who provided personal details through email requested a test drive

- 15% of those who provided personal details through banner requested a test drive

Banners on Autotrader.ca:
		Audience	% unique visits	CTR (similar campaigns)
15-24		2,51,000	32%	0.5%
25-30		4,53,500	42%	2.5%		Uniques	Clicks?
30-34		4,30,750	40%	1.8%			0
35-45		4,63,000	41%	3.7%			0
45-54		1,88,750	34%	12.0%
55-64		1,03,500	32%	13.3%
>65		1,57,750	28%	14.5%
Total		20,48,250
Cost per 1000 impressions:		$50

For actions to be performed, each member of the audience needs to see the ad three times.

What is the campaign objective?

What are the campaign goals?

What are the relevant KPIs?

All emails	0		Paid banners	0	$ -
Net emails	0		Unique	0
CLICK	0			0
CPC	#DIV/0!			#DIV/0!
Net website	0			0
Fill form	0			0
Test drive	0			0

Email funnel

Calculate the number of emails that can be sent. Consider the email budget and divide it by cost per email.

Calculate the number of net emails delivered by considering the email bounce rate.

Calculate the number of clicks by multiplying net emails by email CTR.

Calculate the CPC by dividing the budget by number of clicks.

Calculate the net website visitors by multiplying clicks by bounce rate.

Calculate the number of visitors who fill out the form by the percentage of who fills out the form.

Calculate the number who take the test drive by multiplying the percentage.

Banner funnel

Calculate the number of unique visits for each target t age cohort i.e. 30-45 year olds.

Multiply by three to calculate the total impressions as each visitor needs to see the ad three times.

Calculate clicks by multiplying each ad. Sum the two groups to collect the total clicks.

Calculate the cost of the banner campaign. Consider the CPI - Cost per Impressions.

Calculate the CPC. Consider dividing the budget by the number of clicks.

Calculate the net website visitors by multiplying the clicks by the bounce rate.

Calculate the number of visitors who fill out the form by the percentage who fill out the form.

Evaluate and Optimize the Two Campaign Funnels

Which campaign delivered more test drives?

Which campaign costs more?

If you worked for Ford, what would you suggest or do next time?