Reliable Acorn

Call David Zimmerman at (704) 931-8801

Excel Formula: Avg Monthly Searches from Groupings in AdWords Keyword Planner

The other day Google stopped providing an estimate of search volume for each keyword. Now, for most customers, they are providing a range of search volumes. I don’t know about you, but this is throwing off my entire keyword research process. I need the number of estimated searches, to pick the best keyword.

That’s why I wrote a couple Excel formulas.

The first one gives you the minimum average monthly searches for exact matches on a given keyword. This is probably for the “glass-half-empty” crowd.

=VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(D2,FIND(CHAR(208),D2)-1),”K”,”000″),”M”,”000000″))

The second excel formula tells you the maximum average monthly searches for exact matches on a keyword. Use this if you’re a glass-half-full kind of person.

=VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(D2,LEN(D2)-FIND(CHAR(208),D2)),”K”,”000″),”M”,”000000″))

 

In both these formulas, we’re referencing the ASCII value of 208, which is the dash the Keyword Planner is giving us.

 

Is this the end of keyword research?

Nah. Remember:

  • Google was always giving us an estimate. Now these estimates are more rounded but still estimates.
  • The number given didn’t mean much by themselves. They were were useful when comparing phrases with each other (or seeing which phrases had no search volume). That’ hasn’t changed.
  • Google might be grouping similar keywords into one set of search volume but aren’t we past specific keyword yet? The organic algorithm considers these phrases the same, anyway, so we don’t need to distinguish them in our keyword research any longer.
  • Search volume alone was never enough to determine which keywords are fruitful. I always multiplied Search Volume with the Competition ratio and the Estimated Cost Per Click to compare phrases.
    • Why use the other metrics? Because just because someone searches for a phrase, doesn’t mean they will be a good customer. If people are competing over these phrases and bidding high for those visits, they must pay off!
    • Why multiply these metrics?
      • I like to think of the Competition score (a digit between 0 and 1) as a percent of relevant visits. To find how many searches are “relevant”, multiply it with the number of searches.
      • If you multiply that percent by the Est CPC you’ll see more expensive phrases rise above cheaper ones. Those are the money phrases for SEO, too.

So, keyword research might be a little less specific, but it’s still achievable with Grouped Data in the AdWords Keyword Planner.

Leave a Reply

Your email address will not be published. Required fields are marked *

Reliable Acorn, LLC

Charlotte internet marketing consulting, specializing in search engine optimization, for B2B companies.

(704) 931-8801

Mon-Fri: 8:00 AM to 5:00 PM Eastern Time

TwitterLinkedInFacebook

Privacy Policy

How We Can Help

Recent Blog Posts