Reporting on the success of your SEO work by using ranking reports alone doesn’t really paint a full picture. Good SEO should be about way more than just improving rankings; for starters is your client making more money, how’s their organic traffic growing, what does their brand look like online? Using ranking reports in conjunction with other metrics can provide a wider picture of how your website is performing and help to educate the client as to what they are getting from SEO, and what areas might need more attention.
However, despite explaining other important KPI’s and trying our best to explain how to measure true success and value; the one thing clients often want to know about first is where their pages are ranking. Sometimes this question can come before discussing traffic, conversions and other such metrics that actually make a real difference to their business.
A purely fictional (of course!) conversation usually goes something like:
Me: “well, this month has been pretty good. You’ve had the highest number of visits from organic search ever, time on site is up and conversions are looking pretty good too!”
Client: “that’s really interesting, but why have I dropped one position for ‘Andrex comfort rub?’ We really must get that fixed”.
So what’s the problem here? Is it the client not ‘getting it’, or is it us as SEO’s for not communicating the best KPI’s with clients? I think it’s a bit of both. It’s pretty damn cool when a client really gets into SEO and get’s hooked on seeing their site progress in the SERPS, but that can often lead to constantly checking rankings and getting transfixed with ‘vanity’ keywords.
Pulling Together Some Focus
So, how can we start to combine all the important stuff that we know matters, and deliver it in a way that effectively gets across what we’re trying to say? I believe that one of the best ways to do this is to combine ranking reports with other important metrics that give a true impression of where a campaign could be headed. In my opinion, the best SEO projects are agile; they can adapt quickly when needed and focus on converting keywords, or keywords that might be on the edge of greatness. If the goal posts keep moving you’d better keep moving with them!
To try and do this I’ve been looking into combining ranking data with exports from Google Analytics to provide a report that gives information on rankings, revenue and potential future benefits for each ranking. We all know that this is the data we should be looking at and comparing, but by combining it into one easy to view report it should make it easier to communicate your points and spot easy wins.
To make the report I’m using the VLOOKUP function in Excel to combine exported data from Google Analytics, Advanced Web Ranking and search volume. You could use whatever rank checking software you prefer to use, but I’ve found AWR to be particularly good and it’s also very easy to export the data into a CSV file.
The Ingredients
Google Analytics Export – The ideal here is to export the keyword data from your organic visits over the last 30 days. If you’ve got a large amount of referring keywords you might want to do some filtering first to cut down the numbers, as a large set (over a thousand) might take you a long time to process. This will obviously depend on how much detail you want to go into. In the new version of Google Analytics, exporting your keyword data with conversion metrics is dead easy; simply view your organic traffic from the last 30 days, click on the appropriate goal tab above the chart and hit ‘export’.
If you’re looking to automate this, you could of course use the Analytics API to get your data straight into Excel.
AWR – Once you’ve exported your list of keywords from Google Analytics, dump the list of keywords into Advanced Web Ranking and run a report to check for up to date ranking positions. Once that’s completed you can export the data to a CSV file via the ‘quick report’ button.
Search Volume Data – You can get this from your usual source such as the Adwords Tool or any other keyword research tool, but a quick way to get the numbers into Excel would be to use this handy Excel extension. In fact, it would be really interesting to find out what keyword research tools you find most accurate, let me know in the comments!
The clever (ish) bit
Once you’ve got your three exported data sets, you can then start to create a ‘main’ worksheet with a separate tab to dump each data export into. You could keep them in separate worksheets, but I personally prefer to work from tabs in Excel rather than having loads of separate worksheets open. Once you’ve done this you can then start to build up your ‘main data’ tab and build your report.
For this example, I’ve based it on a fictional site http://www.bigrubberduck.co.uk; specialists in rubber ducks and other bathroom accessories. All the numbers are completely made up.
The extent of this report really depends on how much detail you need to communicate. I’ve set this one up to compare visits against conversion rates and search volume to try and spot areas for future growth. However, you could easily extend this out to include revenue per keyword, time on site and so on.
As you can see from the above, I’ve started off by pasting in the data from my Google Analytics Export (keywords, visits and conversion rate per keyword). The next step is to start pulling in the ranking data from AWR or alternative ranking report. To get the job done, I’m using the VLOOKUP function to find the appropriate data and pull it through into the right place:
=VLOOKUP(Table7[[#This Row],[Keyword]],Table1,2,FALSE)
As you can see, all I’ve done is entered the formula to the first cell in the ‘Rank’ column. As I’m working in tables mode, this will automatically be carried down the column for me.
You’ll need to make sure that the numbers in the formula relate to the way your worksheet has been set up, so simply copying the formula above may not work for you. If you enter =VLOOKUP( and then hit ctrl +A you’ll get a handy pop up which will help you through the process:
A quick little guide for you:
Lookup_value: highlight the first keyword in your main data table.
Table_array: highlight the data set from which you want to grab the numbers; for example the AWR export.
Col_index_num: enter the column number within the table that you want to pull through the numbers from. For example, if your rank positions are in column 2 of your AWR export simply enter ‘2’.
Range_lookup: enter ‘FALSE’ to get an exact match.
Once you’ve filled in the VLOOKUP function hit enter and watch all the data come through from your AWR export.
To complete the rest of the table you simply repeat the VLOOKUP function for each column, selecting the appropriate array (original export from Analytics, AWR, search volume, etc) and the column from which you want to collect the data. It might be a little tricky at first, but after a little bit of experimentation you’ll have no worries! Just remember that all you’re doing is pulling through matching data from various different places…you’ll get there in the end!
The end result
Once we’ve got all of this information side by side, it becomes really easy to spot opportunities and areas that you should perhaps be focusing your efforts. For example, in the report above we can see that bigrubberduck.co.uk/bath-toys is currently sitting in position 8 for ‘shark toys for bath’ and it’s already converting traffic into business. Again, despite being on page two ‘bath toys’ is starting to convert; taking a look at the search volume for ‘bath toys’ there could be some good new business to be had by focusing on that keyword. Suddenly you have two very clear focus points for the project.
This is by no means an exhaustive tutorial on VLOOKUP or how to get data from Analytics etc. There’s many ways to go about it and if you had the time you could easily extend this into something that’s pretty much automated. Making more use of API’s would be an obvious step to taking this idea further, but this method should at the very least get you started and perhaps help make more of the data you are no doubt exporting every month anyway ;).
Making Sense of It All
Going back to the original point of this, we were trying to create a ranking report that provides a bit more value and insight than a simple keyword vs position report. Being able to place rankings next to visits, search volume and conversion data makes it really easy to justify what keywords you need to prioritise. If a client has become transfixed on a particular keyword, then presenting data like this clearly shows a business case for improving other less glamorous keywords that could quickly start to yield some revenue.
