Search Engine Marketing

This workbook supports the SEO and SEM subject at www.marketingmo.com.

20.12.a Keyword Brainstorming & Traffic Projection

Brainstorm potential keywords and then estimate the traffic for each.

20.12.b Keyword Priorities

Prioritize the keywords based on relevancy and volume.

20.12.c Keyword Review

Record other sites ranking for your selected keywords.

20.12.d Keyword Short List

List the keywords you've decided to use in your campaign, ranked by priority and then volume.

20.12.e Current Keyword Rankings

Document your position for various keywords on different search engines.

20.12.f Negative Keywords (for paid search)

List the keywords that you want to exclude from a paid search 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

Keyword Brainstorming & Traffic Projection

Use this tool to brainstorm potential keywords and then estimate the traffic for each.

Today’s date

Campaign you're working on

Percent of total search volume that Google represents [A]

Date that you obtained the % used in A above

Month for which the tool is reporting volume

KEYWORD SELECTOR TOOL.

Keyword or phrase

total: 0

Available at Search Engine Watch

STEP 1: Enter all of the keywords and phrases that you think your potential customers will use while searching for information that's related to your product/service

STEP 2: Go to Google AdWords search marketing

Rank (calculated)# of Google searches in

the month above [B]

Estimate: Total # searches in all engines

(B/A)

Level of targeting (helps you prioritize)

document.xls - a 3

Keyword or phrase

total: 0

Rank (calculated)# of Google searches in

the month above [B]

Estimate: Total # searches in all engines

(B/A)

Level of targeting (helps you prioritize)

document.xls - a 4

Keyword or phrase

total: 0

Rank (calculated)# of Google searches in

the month above [B]

Estimate: Total # searches in all engines

(B/A)

Level of targeting (helps you prioritize)

document.xls - b 5

Keyword Priorities

Today’s dateCampaign you're working on

Priorities

High 0

Med 0

Low 0 Keywords you're using

Total 0 0

Keyword or phrase Notes

1 #N/A #N/A #N/A

# you've assigned to this level

Use this sheet to prioritize your keywords (from A). They're ranked by VOLUME; enter a priority level and any notes. Then go to sheet C to see your keywords in order of priority, then volume.

NOTE: If a line contains "#N/A", there was a tie between two keywords for the rank on the line above. To fix, go back to A and increase the traffic estimate by one visit; then the keywords will all display on this sheet.

Rank by VOLUME

(calculated)

Estimate: Total # of searches in all

engines Level of targeting (from A)

Priority: How important is this

keyword?

Here is the keyword's rank by priority, then volume (calculated)

Use this keyword in the campaign?

Priority/ volume rank among the keywords you're

using

document.xls - b 6

Keyword or phrase NotesRank by VOLUME

(calculated)

Estimate: Total # of searches in all

engines Level of targeting (from A)

Priority: How important is this

keyword?

Here is the keyword's rank by priority, then volume (calculated)

Use this keyword in the campaign?

Priority/ volume rank among the keywords you're

using

document.xls - c 7

Keyword Review

If you need to see who is advertising or ranking for different terms, use this sheet to record the sites that appear. (To shorten your list, highlight all of the records in the KEYWORD and PRIORITY columns, then SORT by priority.)

Search engine

Date

Enter the revelant results from your search

1 2 3 4 5 6 7 8 9 10

#N/A 1

Keywords you've decided to use in your campaign

Priority / volume rank

document.xls - d 8

Keyword Short List

This sheet displays the keywords you've decided to use in your campaign, ranked by priority and then volume.

TIP: When you set up your campaign, use the tools provided by the advertising network; those tools can help you add more relevant keywords to your list

Campaign

Keyword Targeting Notes

1 #N/A #N/A #N/A #N/A

Priority / volume rank

Estimated total traffic (month)

Priority (1=high,

2=med, 3=low)

document.xls - e 9

Current Keyword Rankings

Use this sheet to document your position for various keywords on different search engines.

Date of analysis:

Suggestion: If you really want to measure your progress over time, you can dig deeper into search results. However, you may want to only focus on Google's top 2 or 3 pages.

If you enter your last report results, the sheet will highlight the rankings that are rising or falling. Black bold = RISING; Gray = NO CHANGE; Red bold = FALLING

Ratings on last report date:

Keywords you're using in your campaign

Your current rank Current p 1 impressions Current p 2 impressions Current p 3 impressions

Google Yahoo Bing Google Yahoo Bing Google Yahoo Bing Google Yahoo Bing Google Yahoo Bing

#N/A #N/A #N/A #N/A #N/A - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

- - - - - - - - - -

TOTAL #N/A #N/A #N/A #N/A - - - - - -

Google share

Yahoo share TOTAL CURRENT PROJECTED IMPRESSIONS:

Bing share Google #N/A

Yahoo #N/A

Estimated percentage of total searchers who go beyond page 1 results Bing #N/A

To page 2 (11-20)

To page 3 (21-30)

Total monthly searches (from

short list)

document.xls - f 10

Negative Keywords (for paid search)

Use this sheet to store the words that you want to exclude in your paid search campaign.

Today’s date

Campaign

Ad name (if relevant)

Comments

Keyword or phrase Notes / comments

#REF!

document.xls - f 11