Super Quick Content Gap Analysis

We haven’t been using Sistrix for all that long at Screaming Frog, though we’re fast becoming one of their biggest fans. We usually approach ‘all in one tools’ with an air of scepticism, as while they may cover all of the bases, it doesn’t always mean they do it well.

The team at Sistrix however are proving that it is possible to become the Swiss army knife of the SEO world, and while it does still have some (charming!) quirks, every time I log in I find a new way to use the vast amounts of data at my disposal.

One such way is quickly exporting your competitor’s rankings, and comparing them against your own to highlight areas they’re excelling in, that you’re not even competing for. It’s not a new practise, but with the tools Sistrix provides and with some Excel wizardry it’s a super simple task, and can help you spot gaps in your content. It’s possible to achieve this using any ranking software, however this is a way of tapping into Sistrix’s data and viewing all of the terms a site ranks for, as opposed to using a predefined list.

For this example we’re going to be looking at the ‘space’ niche, where sites are competing for the abundance of search volume around ‘facts’ related terms and common queries such as ‘distance to moon’.

Grabbing The Data

Before starting the process you’re going to need an idea of who exactly are your current competitors, which is a feature that Sistrix offers. Simply plug in your own URL and click ‘Competitors’ on the left hand side.

Disregard the likes of Wikipedia, Amazon, YouTube etc., and pick out 3 or 4 sites you feel are direct competitors, which shouldn’t be hard if you’re knowledgeable about the niche. For this example we’re going to use Space-Facts.com as ‘our’ site and nineplanets.org, theplanets.org and planetsforkids.org as our competitors.

Once you have your competitors, start off by putting the first one in the domain field and choosing your desired country, and heading once again to ‘Competitors’ on the left hand site. This time, click ‘Compare Keywords’, and enter your domain into the appropriate field.

The reason for doing it this way round, is because Sistrix will only show you the keywords the site you’re analysing ranks for, and benchmarks competitors against those. Instead of the rankings for our own site, we want to look at the keywords our competitor ranks for and compare those to our own.

Export this table by clicking on the small cog icon on the right hand side, and repeat this for each page or as many as you feel necessary. The more you export, the more data you’ll have to play with. Unfortunately it’s not possible to export the entire table in one go (remember those charming quirks we mentioned?).

Repeat this for all of the competitors, each time comparing your own site against them.

Firing Up The Spreadsheets

Once you have all the exports, it’s time to pull the data together in Excel. Firstly, create a table with columns for your Keywords, Search Volume (more on that later), your site and each of the competitors. You can worry about making it look pretty later. Next open up each of your exports for each page of the first competitor’s rankings, which are .csv’s delimited with semicolons. Combine them into one sheet and do a Ctrl+F for ‘keyword’ to delete the rows with the extra headings (leaving the top heading!), which gives us one big list of terms and rankings.

Tidy the data up by selecting the entire column, using ‘Text to Columns’ (under Data tab), selecting ‘Delimited’, choosing Semicolon as the ‘Delimiter’ and clicking Finish.

Return to the table you made, and paste the data into the appropriate columns, being sure to keep it all in the same order.

Repeat the process for each of the competitors, by pasting the keywords into one big list in Column A, and the rankings once again in the appropriate columns. Again, execute caution when matching up the keywords and rankings.

Finally, we need to grab the search volume for these terms using Google Keyword Planner. Add a filter to your table and sort the keywords alphabetically, in order to ensure everything matches up. Dump all the keywords into the Keyword Planner and export them, and paste the keywords along with search volume into a separate sheet in columns A and B respectively. The reason we do this is because Keyword Planner removes duplicate terms, which we don’t want to do, so some simple VLOOKUP is required to pull the volume next to the corresponding term.

Assuming your spreadsheet is set up the same as this example, and assuming the search volume is in Sheet2 (Keywords in colum A, volume in column B) the formula is as follows:

=VLOOKUP(A:A,Sheet2!$A$1:$B$419,2,FALSE)

Hopefully the end result looks something like this:

Mind The (Content) Gap

You’re now able to sift though the data as you see fit, sorting by the highest volume terms and filtering your own rankings to display just the blanks, using this to inform your content strategy. For example, you can add a column to indicate areas of content you wish to pursue:

There are some things to consider before trying this out for yourself. It’s important to pick direct competitors where possible, as if you choose a competitor that covers many niches there won’t be much opportunity revealed. Along a similar vein, if your competitors are large brands they will rank for lots of branded terms which you of course can’t pursue. The workaround for this is to simply Ctrl+F and delete any branded terms of theirs.

This technique is great for highlighting where your competitors are getting their organic visibility from. Your domain may be more authoritative than the competition, but you may simply lack the content needed to rank. Use this to draft up a content short list and knock your competition off their perch.