SEO (Search Engine Optimisation), at a very basic level, is the process of optimising your website to manipulate search engine algorithms and rank highly in search results for keywords that are relevant and important to your business. How to measure SEO success is a question that comes up often for both in-house and client led SEOs.
When successful, this process will deliver sustainable, organic traffic to your website on an ongoing basis.
More traffic means more opportunity for your website to convert a higher volume of visitors into customers or leads – i.e. more online sales, contact form fills, eBook downloads, etc.
Each business is different and will therefore have different goals – clearly establish them and how you will measure them at an early stage in the process.
Consider website performance goals (search engine rankings, website traffic, online conversions) vs core business goals (leads generated, revenue generated). Website performance goals alone will only tell half of the story.
In this article, I wanted to share a plugin for Google Sheets that I use (created by the awesome Mihai Aperghis) which allows you to use data from Google to keep track of your keywords performance in search engines.
There are many paid tools that you can use to track keywords – this one is free and will give you more data that is specific to your website / keywords to measure campaign success or identify future opportunities.
Step 1 – Set Up Google Search Console
Make sure you have added your site to Google Search Console (GSC) (click here for instructions) and that is has been running for a few months to collect data.
Adding your website to GSC is an essential task to track the health of your website in the eyes of Google.
It will provide you with details on any errors Google finds, provide information on links to your site and most importantly, in the context of this article, will provide you with details on what search terms users have used to find your site.
Previously Google Analytics would have provided you with keyword data, but Google put an end to that around 2011.
GSC will only collect historical data for 90 days – this will cause problems if you want to track keyword performance over an extended period of time.
You can manually export your data directly from GSC each month and archive it, or automate backups of your data each month using the Search Analytics for Sheets plugin.
Why is it important to track keyword performance?
When you are investing time, effort and money into optimizing your website – content on the pages, fixing technical issues and building links from other websites to yours for example – you will want to be able to track the effectiveness of your work. You will also at some point have to answer the inevitable question from clients or managers – how to measure SEO success?
By monitoring the keywords you are targeting in GSC, you can track how your rankings for keywords improves over time. Keywords that rank higher in Google will typically have a higher click-through-rate and generate more traffic to your website.
In recent years, Google’s algorithm has became much more intelligent in understanding semantic search and user intent when searching.
These semantic connections means Google can better understand what content will be most relevant to serve the user’s query, even at times when users don’t use keywords in their search that are most prominent on the page.
For this reason, many in the SEO industry are now starting to question the need / purpose in tracking individual keyword performance as it may not be a true reflection of their work.
When targeting a specific keyword, there is a chance that other similar keywords will be driving traffic to your target page that you may not have visibility on if you are too focused on your initial keyword.
Nonetheless, I believe it is important to have some form of metric to measure success of your website performance.
You can however help Google to better understand the context and relevancy of your landing pages.
Webmasters and content creators should refrain from focusing too heavily on one keyword in particular on their pages. Using a combination of related keywords and contextually relevant associated terms will help Google understand the purpose of your page and the question it answers for searchers.
Step 2 – Setup Search Analytics For Sheets
The Search Analytics for Sheets add-on was created by Mihai Aperghis and is designed to retrieve data and create automatic backups from Google Search Console (you can find full setup and installation instructions at this link).
You can use this add-on to query your current data (up to 90 days in the past, like GSC) using a range of parameters / groups (Date, Query, Page, Country or Device). The true value in this plug-in comes in its ability to schedule automated backups.
Typically you can set this to backup every month, so when you re-load the Google Sheet you set this up in at the start of each month you should have a fresh batch of data to dig through. This will run indefinitely, removing the problem of losing access to your data that is older than 90 days.
Why is that useful?
SEO should be an on-going task for your website and campaigns targeting specific keywords / pages are likely to last longer than 3 months. Things also change regularly in search – if a competitor starts targeting your keyword, you want to have early visibility on any changes to your ranking position and any negative impact it has on traffic to your website.
At a very basic level, this add-on will give you visibility on ranking improvements on your keywords and how much traffic it is actually sending to your website.
Step 3 – Bonus Tip – Cross Referencing Your Data Each Month To Track Improvements
Data is only useful if you plan to do something with it.
One benefit of having all your data exported into tabs within the one Google Sheet, is that it makes it very easy to analyse and compare keyword performance from month to month.
Using a basic VLOOKUP function you can quickly pull data together and monitor ranking changes and subsequent traffic increases for your top keywords each month.
The example below is based around some keyword targeting improvement for a key term.
This page already ranked quite well for the keyword, normally around 3rd or 4th in Google. It did however have sufficient search volume and attracted the right type of traffic to warrant some SEO focus to try to push the page up a few positions.
I made some slight on-page improvements, consolidated a few similar pages to reduce keyword cannibalization, built a few internal links from other pages on the site and a few external links from other websites.
Rankings started to move after a week or two. Then it was only a matter of waiting for a few months to pass to get a look at how that was reflected in the data.
The changes mentioned above were implemented in early March. It’s clear from the chart when the changes kicked in and we saw a nice boost in ranking for the keyword we were targeting.
Impressions remained pretty consistent across February and March. As we already ranked around 4th for this term before the rankings improvement, this is an expected result.
Pages that rank 1st or 4th will pretty much always have the same number of impressions, it’s clicks we’re interested in though and that’s where we see a real difference.
A pattern is emerging here. You can see the average number of daily clicks are increasing after the rankings improvement kicks in, while also providing much higher spikes in clicks on specific days.
This is a very specific example of a high value keyword. While the volume isn’t huge, it highlights the changes in traffic that can be achieved when you put in some effort to push your page up the search engines.
If you can replicate this for your higher volume keywords, you will see a large spike in traffic to your site around these terms. It’s up to you to make sure your landing pages are in good shape to convert that traffic.
Click Through Rate Increases
As you’ve probably worked out by now, more clicks on a similar number of impressions will result in a higher click through rate (CTR).
The higher you are positioned in the search results, the better your click through rate will tend to be (your title tag and meta description will also play a big part in this).
It goes without saying that the top 3 spots will get the majority of the clicks, with top spot being the most coveted position.
Comparing Data Over Time With A VLOOKUP
A few months after setting up your backup routine, you will have several data exports ready for analysis.
Rather than flicking back and forth between months to compare data, a simple VLOOKUP can make life a lot easier.
Your data dump will look something like this (when exported based upon ‘Query’):
The following month, you will get a new Google Sheet with the data for the month just past.
Within this sheet you need to add 3 extra columns (clicks for the previous month, growth and % growth). This can then be repeated for impressions, CTR and position.
In the clicks for previous month column (‘Clicks Feb’ in the example below), insert your VLOOKUP:
Change the ‘FEBRUARY_SHEET_NAME’ to the sheet you wish to cross reference your data with in your own Google Sheets file.
Your new sheet will looks something like this (for the clicks sections at least):
This example looks at a few specific keywords that I targeted. There is a clear increase in the volume of clicks from one month to the next – an increase of almost 55% in clicks / traffic for a high converting keyword is a good result for a few hours work.
By repeating the above VLOOKUP process above, you can analyse the performance of position, impressions and click-through-rate over an extended period of time. Here is a look at how these metrics were affected for the example above:
Both keywords being targeted jumped a few spaces. On most manual searches, both searches returned the target page in the 1st position every time.
This may not have been the case for everyone and Google has averaged out the position as in around 2nd for both terms.
Impressions virtually unchanged as expected. A page appearing 4th in Google for a keyword will have virtually the same amount of impressions as a page appearing in 1st.
When you start to improve rankings for keywords that were not previously ranking or were on the 2nd page of Google onwards, you will notice improvements in your volume of impressions.
For the first keyword that jumped a few positions in the search results, you can clearly see the huge jump in click-through-rate – an increase of nearly 54%.
SEO doesn’t always have to be about huge wins and big spikes in traffic, although you should aim to shoot the lights out if you believe you can realistically challenge the top rankings for highly competitive terms.
Most websites have some good value keywords that, with a little effort, can be pushed up the search results by a few positions (or pages depending on your niche and SEO efforts) and give you a decent spike in traffic for the keywords that matter most to your business.
Analyse your keyword data in GSC and pay special attention to keywords that are ranking between 4th – 30th that have a good number of impressions. Pushing these up the search results will increase traffic to your site and with it increase opportunities to convert potential customers.
More competitive keywords will naturally take more effort to move up the rankings. Combine all those small wins and they soon add up, giving you a consistent growth in traffic over time.
The data available from GSC will allow you to measure success (to a certain degree) of your campaign. Using the Search Analytics For Sheets add-on will add more value to your data as you will have visibility on your campaigns over a greater period of time.
Traffic, ranking positions and click-through-rates alone won’t be enough to measure the success of your campaigns.
Combining these metrics with core business objectives (online sales, revenue created, number of leads generated, etc) will provide a much more rounded view in terms of website and business performance.
You’ve got nothing to lose really. Setup time will quickly be redeemed after a few automated data exports. Give it a go…