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

A while ago Google stopped providing an estimate of search volume for each keyword. Now, for free or smaller 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. Whether you’re doing SEO or PPC, this is still useful:

• 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 Google Ads Keyword Planner.