Marketing Campaigns

This workbook supports the Marketing Campaigns subject at www.marketingmo.com.

6.9.a Marketing Campaign Quantitative Goals

This worksheet will calculate how many marketing impressions or touches you need to reach your campaign goal, along with your total campaign conversion rate. This is the first metric required to calculate marketing ROI.

Results Calculates the total number of impressions you'll need, the total number of responses and all of your conversion rates throughout the sales process.

6.9.b Campaign Revenue / Profit Projections

This worksheet is linked to 6.9.a and will calculate the revenue and profit projections for your campaign. This is step 2 to calculating your campaign's projected ROI.

Results Calculates the total projected revenue and profit produced from your campaign.

6.9.c Marketing Campaign Budget & ROI Analysis

This worksheet is linked to 6.9.a and 6.9.b (but can also function alone) and will organize your marketing campaign budget, project your initial ROI, and allow you to input actual results to view your actual campaign ROI.

Results Calculates your total budget, projected campaign ROI, and the maximum you can spend and still hit your ROI goal. Also matches your projections to your actual results.

6.9.d Marketing Campaign Budget by Month

This worksheet is linked to 6.9.c and is a report that organizes your marketing campaign budget by month.

Results Calculates your total budget, projected campaign ROI, and the maximum you can spend and still hit your ROI goal. Also matches your projections to your actual results.

6.9.e Marketing Campaign Media Plan

This worksheet outlines your campaign media projections: media to use, cost, impressions, response and converstion rate.

Results Calculates your costs per impression, per response and per conversion.

6.9.f Campaign Results

Matches your campaign results to your original projections.

Results Calculates your revenue, profit or value of customers resulting from your campaign.

6.9.g Ad Response Rate Tracking

Allows you to track your media and response rates for future reference.

Results Averages your response rates for specific measurements.

6.9.h Campaign Variable Test Matrix

Allows you to record results from an a/b test.

Results Calculates your cost per success criteria for each ad.

6.9.i Improve Campaign Results

Review how improvements to specific campaign variables affect your campaign metrics and results.

Results Calculates how improved conversion rates impact your campaign.

www.marketingmo.com

This workbook is designed for use with specific exercises in the MarketingMO planning and management SaaS. If a worksheet has gray and white cells, the white cells inside the gray area are inputs and the results will be displayed in a printable format in the white area of the sheet. If a worksheet is all white, then the inputs and printable report are the same. Some of the worksheets are linked to previous worksheets. Feel free to enter numbers into previous worksheet cell references if you haven't completed the previous sheets. Calculations and formulas aren't protected, so check blank cells on the report sheets before entering values.

Some of the worksheets have tables with summary calculations at the end of the table. If you need to insert more rows, insert them in the middle of the data entry fields so the sum formulas capture the new fields. Some of the worksheets have hidden rows or columns for formula calculations and deleting them will modify some of the features. If you modify worksheets and wish to return to the original format, simply download a fresh copy from your account at www.marketingmo.com.

document.xls - a 2

Marketing Campaign Quantitative Goals Projection

Campaign Name 0

Run Dates 0

Prepared By 0

Date

Based on our conversion rate estimates, here's how many IMPRESSIONS & RESPONSES we'll need to generate in our campaign in order to reach our goal:

Conversion rate estimate 0.00%

Total impressions needed

Total responses needed

Below is the breakdown of projected prospects at each step in our sales process:

Step Step

1 100.00%

Total customers we'll generate at these conversion rates

% of our impressions that will reach our goal

% of our impressions that become customers

Cumulative % of respondees who reach this step (calculated)

# of prospects that should reach this step (rounded up

to next whole number)

document.xls - b 3

Campaign Revenue / Profit Projections

Campaign Name 0

Run Dates 12/30/1899

Prepared By 0

Date 12/30/1899

Products / services

0%

0%

0%

0%

0%

TOTAL 0% 0.0 $ - $ -

% of new customers who

buy this item

Total unit sales (Or just enter the total unit

sales)Total projected

REVENUETotal projected

PROFIT

document.xls - c 4

Marketing Campaign Budget & ROI Analysis

Campaign

Date of budget

Year the campaign starts

ItemBUDGET ACTUAL

NOTESMonth incurred Units Cost per unit Total (or enter total) Month incurred Units Cost per unit

[enter a heading] Choose Choose $ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

[enter a heading] $ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

[enter a heading] $ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

[enter a heading] $ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

[enter a heading] $ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ - *Enter this total into the master budget in this month Total $ - $ - Total

Enter your projected revenue (from b, or enter manually) $ - $ - Variance in expensesEnter your projected profit (from b, or enter manually) $ - % over (under)

ROI for this budget and profit level Enter the revenue you generated

What is your normal goal ROI/hurdle rate? Enter the profit you generated

Maximum you can spend on the campaign and still hit your goal ROI $ - #DIV/0! ROI

Total (or enter total)

document.xls - d 5

Marketing Campaign Budget by Month

This sheet totals your expenses (from the previous sheet) so it's easy to enter into your master budget for the year.

Campaign

Date of budget

Month Budget Actual NOTES

Jan $ - $ -

Feb $ - $ -

Mar $ - $ -

Apr $ - $ -

May $ - $ -

Jun $ - $ -

Jul $ - $ -

Aug $ - $ -

Sep $ - $ -

Oct $ - $ -

Nov $ - $ -

Dec $ - $ -

Jan 1 $ - $ -

Feb 1 $ - $ -

Mar 1 $ - $ -

Apr 1 $ - $ -

May 1 $ - $ -

Jun 1 $ - $ -

Jul 1 $ - $ -

Aug 1 $ - $ -

Sep 1 $ - $ -

Oct 1 $ - $ -

Nov 1 $ - $ -

Dec 1 $ - $ -

Total $ - $ -

document.xls - e 6

Marketing Campaign Media Plan

Plan the number of impressions for each media and project results and costs

Here are the number of impressions to generate, estimated costs and estimated conversion rates

Examples of media to use

Impressions Costs Response & conversion Estimated # of responses Cost projections

Cost per impression # of responses Conversion Results TOTAL COST Cost per response Cost per Conversion

Sales forceIn a, you used 0% #VALUE!

Cold calling - - $ - Sales activities - - $ - Relationship activities - - $ - TelemarketingOutbound calling - - $ - Inbound call handling - - $ - InternetOnline advertising - - $ - Email campaigns - - $ - Organic search - - $ - Paid search - - $ - Webinars - - $ - Viral campaigns - - $ - Ongoing email newsletters - - $ - Social mediaTrade shows & eventsTrade show exhibits or events - - $ - Seminars or events you create - - $ - Events you sponsor - - $ - PublicityProduct- or solution-related stories - - $ - Distribute press releases (and optimize for the web) - - $ - Speaking engagements - - $ - Blogs - - $ - Stories about your company - - $ - Direct mailSimple sales letters or postcards - - $ - Special mailpiece for a particular campaign - - $ - Traditional mediaPrint ads - - $ - Yellow pages - - $ - Radio - - $ - Television $ - Outdoor - - $ - Total - - - * $ - Over (under) goal #VALUE! #DIV/0! #DIV/0!

Overall response *May show slight discrepancy due to rounding

How many impressions do you need/want to generate? (Pulls from a if you completed it.)

# of impressions planned

OR Total cost for this media

Estimated response rate

% of responses that will convert to your goal Cost per

impression

Conversion rate of responses

document.xls - f 7

Campaign Results

Compares campaign results to original projections.

CampaignDate

QUANTITATIVE RESULTS

Step ACTUAL Variance Over (under) goal

123456

TOTAL UNITS, REVENUE AND PROFITIf you prefer to track the number of new customers and their lifetime value, skip this chart and use the next.

Products / services # of units sold TOTAL REVENUE TOTAL PROFIT

$ - $ -

$ - $ -

$ - $ -

$ - $ -

$ - $ - Total - Total $ - $ -

Projected ProjectedVariance - Variance $ - $ -

Over (under) #DIV/0! Over (under) #DIV/0! #DIV/0!

LIFETIME VALUE OF NEW CUSTOMERS

Customer segment

- Total $ - Projected* $ -

- Variance $ - #DIV/0! Over (under) #DIV/0!

Explanation (from sheet a) GOAL (from a if used)

Revenue per unit (price)

Gross profit per product

# of new customers

Lifetime value per customer*

Total profit in today's dollars

document.xls - g 8

Ad Response Rate Tracking

Tracks the response rates for various ads.

Success measurements used:123

#

Ad name Publication Description Run date Total impressions

Number of responses Response rate (of TARGETED impressions)

Measurement 1 Measurement 2 Measurement 3 Measurement 1 Measurement 2 Measurement 3

12345678910111213141516171819202122232425

CURRENT AVERAGE #DIV/0! #DIV/0! #DIV/0!

Total TARGETED impressions

document.xls - h 9

Campaign Variable Test Matrix

Evaluate two different ads to see which generates better results. Test one distinct variable at a time.

Campaign

Test datesPurpose of test

Ad #1 Ad Description

Ad #2 Changes from Ad #1

Criteria to measure success on the campaign For example, number of information requests or number of orders.

Success criteria 1Success criteria 2

Results AD #1 2

Success criteria 1Success criteria 2Total cost of the ad

Cost analysisCost per Cost per

document.xls - i 10

Improve Campaign Results

Displays how improved conversion rates can impact costs and results.

CampaignDateMedia cost of campaign Don't include ad production costs

Results

Improvement goalsWith improved conversion rates at different points, here are the results be with the same # of impressions.

Results GOAL

- - -

Cost comparisonsHere are the resulting costs of the improvements.

Goal

Cost per goal in

Current scenario Improved scenario

Enter the number you achieved

Conversion rate (of previous step)

Conversion rate (of total impressions)

Conversion rate (of previous step)

# of responses you'd generate at goal rate