Using free tools to create a basic SEO keyword report

Over the last couple of years there has been an explosion in the number of tools available to PR professionals, this lead Ketchum's Stephen Waddington to lead a PR Stack project. Over the next couple of week's we'll be exclusively publishing 4 of the most useful tools.

#1 Investigate existing keywords (30 minutes)
Access your organisation's Google Analytics account. Make sure it's connected to your Google Webmaster account.

• Click Reporting.
• Set the date range.
• Go to the left hand column: ‘Acquisition | Search Engine Optimization (sic) | Queries’.
• Eyes to the bottom and increase the Rows to e.g. 5000.
• Towards the top of the screen Export (to Google Sheets or Excel).

This spreadsheet shows you what Google Search terms are sending people to your website. Hundreds of them. In priority order (look at the ‘Landing Pages’ too to see which work best).

#2 Build your own list of keywords (60 minutes)
Contact your client/colleagues (suppliers/stakeholders too). Ask them what they think people would type into Google to find your organisation.

You'll have your own thoughts. Make a list.

#3 Investigate competitors (60 minutes)
Now for a first look at your competitors:

• Create a new Word or Google Doc.
• Pick five competitors.
• Click their homepage url.
• Right click, then ‘View page source’. 
• Hit CTRL F and search for ‘<title>‘, ‘description’, and ‘keywords’. These are towards the top of the page.
• Cut and paste into your Word doc. 
• Repeat five times.

Gold dust. Particularly the keywords, if available. These are often the phrases your competitor thinks will get people to their site. You're getting inside their minds: mining their intent. 

#4 Explore your competitors' keywords (60 minutes)
Use Ahrefs (Positions Explorer) and SpyFu freemium tools to find the search queries driving the most traffic to your competitors' sites.

• Type the domain name.
• Screengrab, using e.g. Awesome Screenshot or Save to Google Drive.
• Rinse and repeat.

You're looking for the top five organic keywords. Run your own site too.

Repeat the same process for SemRush. It reveals the top 10 organic keywords. Click on ‘Organic Research’ in the left hand column then ‘View full report’ before grabbing.

#5 Use Google Keyword Planner (90 minutes)
Set up a Google Adwords account and get all of your keyword phrases into the Keyword Planner.

I prefer to look for 'closely related' terms too. This way you get all the phrases from your lists but generate some extra nuggets you won't have thought of (often with complete data).

Set a bid, and a budget, and download your plan.

Then, delete the spreadsheet columns you don't need.

Leave yourself with: ‘Keyword’, ‘Average Monthly Searches’, ‘Competition’, and ‘Suggested Bid’.

#6 Keyword volume vs competition (30 minutes)
Everyone likes a magic formula. This one's from Larry Kim at WordStream.

“...You want to go after keywords with some volume, because they’ll have a better return in terms of traffic. But you don’t necessarily want to go after the most competitive keywords, because you’re less likely to be able to rank for them. You’re looking for a sweet spot.

"Take the number of monthly searches for the keyword, multiply that by the suggested bid, then divide it by the competition level on that keyword.”

• Insert a new (fifth) column entitled ‘Magic Formula’ in your Excel doc or Google Sheet.
• Shout over to a techy colleague and ask them to help you insert a formula for column E. 
• Try e.g. B2*D2/C2 where column B is Average Monthly Searches, column D is Suggested Bid, and column C is Competition.
• Then sort the whole kit and caboodle by column E with the highest number being the best. Don’t discard the O’s and unclassified.

#7 Refining your keyword list (90 minutes)
Remember that Google built Keyword Planner to help people buy adverts not affect its organic search results.

While Larry's formula is useful, you must consider the following:

• What is the Domain Authority (DA) - use MozBar - and Alexa Ranking of your competitors. And how does your organisation compare. The higher your DA (on a scale of 1-100) the better.
• If your DA is lower than 30 it's unlikely (even if your competitors are equally poor) that you are going to rank for the most competitive search terms. Try targeting phrases with average monthly search volumes of fewer than 100. Hat tip to Andy Crestodina for this one.
• Try locational search terms, or longer phrases (these are good for blog posts).
• And if you sell something, which phrases are most likely to lead to a sale. Which have the highest user intent to buy.
• Now you’ve refined your terms, use MozBar again to see if you have a chance of ranking on Google’s first page. Check out the DA of the number-one and number-10 ranking site. If your DA is within that range, go for it.

At the end of this step you want:

• Your full list spreadsheet (ranked by magic volume vs competition formula).
• Your top 25 to 50 keyword phrases spreadsheet.
• And I like a one-page Word document with up to ten keyword phrase groups. For example, in the case of my business, Zude PR, I rank the same for PR company Glasgow as Glasgow PR company. It'll be similar for you. These should be the basis of your static web pages. But feel free to have the odd shot at them in a blog post every so often.

#8 Write well
If you spend time doing a keyword report don't leave it lying in a cupboard. Whether you're writing a page for a website or penning a blog post, use your keyword phrase:

• Four times in the body copy (assumes a 1,000-word article).
• Once in the title tag/SERP/the headline you see in Google. At the front. And in the H1 header (usually the same as the title).
• In the meta description (no direct SEO benefit but does affect click through rate).
• Make sure you use loads of synonyms. A neat trick here is to look at the Wikipedia entry for the keyword phrase and see what words are used in the first three paragraphs.
• One keyword phrase (grouping) per page. Bar the homepage where you can try for a few.
• Please measure. Here’s one I wrote earlier (see whatsmyserp).

Above all else write well or no-one's going to read your content. 

About David Sawyer
David owns Glasgow-based Zude PR. He helps businesses and organisations to use SEO, media relations, content marketing and social media marketing. To sell their product/cause to the right people.

Visit PRstack for more information and to learn about the 250+ digital and social media PR tools and 48 how-to guides that the community has written. The education initiative is the brainchild of Ketchum's Stephen Waddington and Prezly's Frederik Vincx.