As a small business owners, one of the most consternating problems we face today is worrying about whether or not we will show up on Google’s SERP (Search Engine Results Page). If you have done any digging to find out how to show up here, then you undoubtedly know that the two most effective ways to do so are SEO and Paid Search.

While this is not a post about SEO, I recommend you check out the Blog post entitled “Brewing Up Site Traffic through Organic Content and Analytics” for unique insights on how quality content (the kind of content that meets user’s needs) can bring new users to your site and help you show up better on the SERP.

Now let’s talk about Paid Search. If you are new to Paid Search, here is a quick overview:

Businesses/organizations/groups always want to show up on Google’s SERP. Sometimes in addition to their SEO efforts they will also pay for ads to show up on the results page. These ads are placed based on KEYWORDS the business select that they would like to bid on. Businesses select the type of keywords they want to bid on based on how relevant it is to their good or service (best practice is to match the keywords you bid on to the intent that you believe your consumer has when searching for you).

For example: If I owned a Madison cookie shop and wanted to bid on Google AdWords ads, I would bid on the keywords that I believe people would be searching when they intend to either find my business or purchase my product. Some keywords I would consider bidding on would be “madison cookie shop” “cookies downtown madison” and simply “cookies.”

 

Taking a look at the keywords listed in the example above, it is evident that “cookies” will probably get many more searches than a more specific keyword such as “madison cookie shop.” While getting many more searches is a great thing for small business (Bigger reach = good news, right?) there is a caveat: Google AdWords charges users based on a Cost Per Click (CPC) pricing strategy. That means you’re your ad showing up often for popular keywords like “cookies” would be great, but if this keyword ALSO has high competition or a higher suggested bid, every time a user clicks on your ad, your business loses relatively more money than the less expensive keywords such as “madison cookie shop” because you must bid more money on the more competitive and expensive keyword to get that click. ADDITIONALLY, if you are not willing to bid more on the more expensive, competitive keyword such as “cookies,” Google will push your ad down from a good position on the SERP (the first or second position) to further down the page or even on the dreaded second or third pages of the SERP (let’s be real for a second – how often do YOU go to the second page on Google to find what you searched for? Almost never). This means that your ad won’t even be showing up in a relevant spot, making the whole value of bidding on that frequently searched keyword worthless.

This all sounds like bad news for small business owners with limited budget and right about now you may be asking: “What’s the solution?”

 

I give you: The “Moneyball” Approach.

 

The “Moneyball” Approach is a way to select keywords you want to bid on based on analytics from the Google Keyword Planner tool and a little work in Microsoft Excel (see this attached Excel document template). Once you download a large list of suggested keywords from Google’s Keyword Planner tool, simply copy the respective columns of values from the Keyword Planner spreadsheet into the first tab of the Template spreadsheet, titled “PASTE from planner.” (Please note that Google provides more information to advertisers who spend more on Google AdWords, therefore I have provided TWO templates. The first is for advertisers who get RANGES for the “Avg. Monthly Searches” column, and the second is for advertisers who get NUMBERS for the “Avg. Monthly Searches” column).

 

Once the data from the Keyword Planner Tool is copied into the template spreadsheet, the rest of the tabs will auto-populate. Now comes the fun part: filtering based on your objectives.

If you look at the last tab of the Template Spreadsheet titled “Formulas” you will see a list of pre-entered formulas that have been entered for weighting purposes. The weights all add up to 1 for each formula, and it weighs each of the three most valuable metrics the Keyword Planner provides us for each keyword based on business objectives.

 

Here are the three most important metrics from the Keyword Planner Tool explained:

• monthly searches is the mean number of searches that EXACT keyword (or a very close variation) received per month in the time frame and location you set in the Keyword Planner
• Competition is a number between 0.00 and 1.00 that reflects how competitive the keyword is in the time frame and location you selected, 1.00 being the most competitive. Almost certainly you will have to bid more on more competitive keywords to show up well on the SERP, so this metric and Suggested Bid are highly correlated.
• Suggested bid is exactly what it sounds like. The higher the suggested bid for a click, the more you pay per click.

 

Now ideally small business owners want to select keywords to bid on that have a high Avg. Monthly Search metric, while having low Competition and Suggested bids. This is the sweet spot because it is an untapped keyword that users are searching often, but that advertisers are not bidding on often. Therefore, you will get the most bang for your buck if you use it (that is provided that you already have screened the keywords that the Keyword Planner gave you for the searcher intent of your customer – a whole separate topic that is also important for keyword selection).
To find these “Moneyball” keywords, look in the “FINAL” tab of the Template Spreadsheet (once it is auto-populated) and use the “Filtering” tool in Excel to sort the keyword score in ascending order based on which formula you want to use. Conditional formatting will color code the scores as well. The higher the score the better!

 

 

 

In addition to just finding the most cost-effective keywords, other formulas are weighted differently to score keywords based on different objectives such as High Search Volume or Equally Weighing the three metrics.
The “Planner refined” tab is also a great tool to see how each keyword stacks up compared to one another based on the three metrics mentioned above. Use the “Filter” tool in Excel to sort those columns as well.

One final note on using the Template Spreadsheet. Be warned that the “FINAL” tab – once sorted – will not correctly auto-populate a brand new set of keywords. This means that you should save a blank Template file to your computer and create a new file every time you want to analyze a new bunch of keywords from the Keyword Planner.

Thanks for reading and happy keyword hunting!

Nolan Cunningham is a senior Marketing major in the Wisconsin School of Business. In addition to his studies, he also has experience as a Digital Marketing Analyst in the Integrated Marketing Communications Department for the Wisconsin School of Business. You can connect with him on LinkedIn.