SEMrush is a great tool for SEOs to get access to keyword and competitor data in an easy to use interface. It also provides the ability to export ranking data quickly for potentially thousands of terms. With this data, we can analyse the content on a domain and consider ways to improve it by creating topics around keywords and URLs.
Google’s Hummingbird algorithm brought synonyms and context into the marketing mix, evaluating the whole conversation and trying to decode the user’s intent. In the past, SEOs created many pages optimising for keywords with slight iterations, with the intention of targeting variations of the same keyword. With the introduction of Hummingbird, the need to do this is reduced, as we can now optimise content more effectively for the whole conversation.
To do so, we first need to export keyword data from SEMrush. In the dashboard, after searching for a domain, there is a section for ‘Top Organic Keywords’ as shown in the image below; here you can export the data. You may wish to target specific URLs and combine the data sources to reduce the number of keywords you will need to go through, as it is quite a manual process. However, it may also be useful if you have a section of the domain you want to write more content for.
Analysing the data
Once we have the export of keyword data, we can begin the analysis. Data cleansing is the first step. Clean the results in Excel by removing columns that are unnecessary, in this case Leaving Keyword, Position, Search Volume, and URL. The other data in the export could potentially be used, but for this example these will be the columns analysed.
I like to add an extra column called ‘URL Count’ to give an extra filtering dimension, and to show at a glance how many times in the export a specific URL appears (or how many keywords the URL appears for). To do this, input a COUNTIF formula: the range being the URL Column and the criteria being a specific URL cell.
The export can then be filtered to meet any specific thresholds, perhaps to only show those terms which are currently ranking on pages 2 to 3 to try move them up to page 1. Filtering may be more useful on larger websites where the export is extremely large, but for smaller sites with a digestible amount of data, you may wish to see all terms. In this example I’ve applied a filter for keywords ranking up to position 30.
From here we can filter, sort and segment the data looking for common themes and topics among URL’s and keywords. From this, we can take the learnings to either create new and exciting content for a website to fill gaps in our targeting, or potentially to rework existing content looking for quick wins to improve both user experience and SEO benefit to a page.
I’ve highlighted a couple of different categories our Top 10 Viral Marketing Campaigns page could potentially fall under, or we could look to target marketing campaigns with its own page. How granular you go is up to you. This is a manual process and will take some time to get through, I’ve only looked at a very limited amount of the data I exported from SEMrush but you could map many topics and themes across the entire domain.
At this point, the data can be taken away to brainstorm the topics found, individual pages can be analysed to discover missing areas that could potentially be reworked to improve these rankings, or brand-new content ideas can be brought to the table – all of which can be fed into your content roadmap.
Keyword research may also be done around the topics for reporting purposes and it may show keywords outside of the SEMrush database that you may wish to target.
Next level
To add further insight to the analysis, data from outside of SEMrush such as competitiveness in the SERPs and understanding the potential return for improving content could be added. SEMrush also gives you the ability to look at your competitor’s websites, where they rank and – with some investigative work – why! Analysing competitor data this way is a great way of searching for gaps in your own content especially if competitors are ranking for keywords you may want to but are not targeting already.
Don’t have SEMrush?
While I’ve used SEMrush for this example, the same analysis can be done using different SEO tools out there. Any tool that provides you access to keywords and ranking URL’s will do the trick. Tools like AHREFs provide similar exports while the Google Search Console API can be queried to provide the data needed. Try combining different sources of data to see if additional keywords can be collected.
