Skip to content

How to do a comprehensive keyword audit on your site

by Martin Harrison on

In this post, I’m going to show you how to carry out a comprehensive keyword audit on your site. The objectives of this are as follows:

  • Identify areas for improvement in site content.
  • Improve landing page relevancy, to boost both SEO traffic and improve Adwords quality score.
  • Ensure that you are covering all of the long-tail keywords that are driving traffic to your site.

What you’ll need

  •     Google analytics tracking and ideally conversion tracking.
  •     A spreadsheet.
  •     A couple of free hours and some strong coffee.

Step 1 – Get the data

The first step in your audit is to grab all of your keyword data from analytics. Log in and navigate to Search > Overview in the left hand column. Ensure that the date range is set to capture as much data as possible.

Next, filter the referring keywords from both organic and paid traffic by navigating to Filter by Secondary dimension > Traffic Sources > Keyword:

Next, export this data to a CSV file so that you can use the data in a spreadsheet.

Then toggle the number of the rows at the bottom of the screen to 500. Unless you are dealing with an ecommerce site with thousands of SKUs, this should give you enough data to play with.

toggle the number of the rows at the bottom of the screen to 500.
toggle the number of the rows at the bottom of the screen to 500.

Next, export this data to a CSV file so that you can use the data in a spreadsheet.

export this data to a CSV file so that you can use the data in a spreadsheet.
export this data to a CSV file so that you can use the data in a spreadsheet.

Open the file in the spreadsheet package of your choice, make sure that you select the comma separated option in the popup before opening the sheet.

The data black hole – ‘not provided’

Google tell us that their decision to block access to the keyword data you’ll see as ‘not provided’ is a privacy measure. Really it’s in a bid to push their premium Analytics package.

The bad news is that unless you’ve forked out the $150,000 Google is asking for this, a lot of your referring traffic will be anonymous. The good news is that if you are running an Adwords campaign, you will be able to see what terms are converting.

Step 2 –  Sort the data

Once you have your data, you can sort it by a number of different metrics. Which ones you choose will depend on what your objectives are:

Want to increase traffic? – The second column, visits is the most relevant, sort the data to see the terms that are driving the most traffic.

Want to increase profit? – If you have conversion tracking set up on your site, there are several columns that can help you to view your money terms:

Revenue, Transactions, Average Value, Ecommerce Conversion Rate and Per Visit Value.

Step 3 – Categorise the data

Once you have sorted your data, the next step is to categorise it. Add a new column and give each keyword a category based on the words it includes e.g. ‘content’ or ‘articles’. This may seem like a laborious process, but it does get quicker with time, particularly when the cells start to auto-populate as you type common terms:

Give each keyword a category
Give each keyword a category

Once complete, you can order sort these terms alphabetically to see them in groups:

Sort your sheet by category
Sort your sheet by category

Step 4 – Analysis

Analyse your data to identify areas of improvement and action points:

Add extra columns for analysis
Add extra columns for analysis

I have done this by adding 6 new columns to the sheet, which are as follows:

URL – Is there a page for this keyword that already ranks, or do we need a new one?
Keyword in URL – Is the keyword included in the URL?
Keyword in title – Is the keyword included in the meta title?
Keywords in meta description – Is the keyword included in the meta title?
Keyword in H1 tag – Is the keyword included in the page’s H1 title?
Keywords in copy – Is the keyword present in the body copy on the page?

Step 5 – Actions

Once you have your completed sheet, you are ready to action any changes that need to be made. This might involve adding new landing pages or editing the meta data and page copy of your existing ones.

Martin Harrison

Works at Copify