[RESOURCE] A free spreadsheet tool for topical analysis in keyword research

Tomislav Lukinić 18.06.2020

Let’s say you want to do a topical analysis on a keyword dataset. You could of course do this analysis with R packages, python libraries or by querying 3rd party services for a fee.

However, it’s also possible to do a similar analysis and create most of the same insights by simply plugging in the data to a Google spreadsheet. Also, it is often much more convenient to upload or copy-paste an exported list to a sheets file and have the result data already prepared in a format that can be automatically pulled for further analysis within the same document (i.e. through another worksheet).

This spreadsheet’s purpose is to do just that, which is why it’s a crucial first step in our keyword research process. Considering it might be helpful to others, we’ve decided to share it: You can open and make a copy of the spreadsheet here or by clicking on the button below:

What does the spreadsheet do exactly?

The spreadsheet tool accepts an exported or copy-pasted list of queries from i.e. the search console (or a clickstream analysis tool of choice) with an associated quantity dimension, like volume or traffic. Then it churns out a list of separate unique words found within the list, along with their aggregated contribution data. This data refers not only to the amount of times the word appears in different keyword queries, but also what total volume or traffic (or whatever metric you paired the initial queries with) corresponds to the queries containing those specific words.

Who is it for?

The analysis performed through this spreadsheet can be of use to specialists and analysts in these areas of business and digital marketing:

  • SEO,
  • Performance marketing,
  • Content marketing,
  • Customer research
  • And probably other areas, too.

How does it help?

Topical analysis is and has been a thing in SEO for some time already, but it’s also very helpful in analyzing the digital context of a business or industry in general. As hinted above, this type of analysis can help anyone dealing with keyword research, like performance advertising and many other activities. But to give you a sense of how this tool/analysis is relevant, here’s a list of some benefits we see in using it.

  1. It enables you to do a quick topical analysis with your own dataset. This allows for all of the following points, and very probably some other ones we haven’t yet thought of.
  2. Related to the previous point, it also speeds up the initial phase of the keyword research process. Simply by copy-pasting the resulting table to another sheet and sorting it by the chosen metric or amount columns, you can already get a glimpse of the business & industry context relating to search engine data.
  3. It serves as a basis for keyword grouping. Knowing all the topics will get you so far, but pairing that with all the possible industry-relevant modifiers will enable a more scalable research process where things like type of intent and competition will be much easier to categorize.
  4. Depending on the amount of query data you feed into it, it broadens the keyword analysis by showing quantitative data across the whole distribution, not just the top segment. This means you can see what the actual “popularity” of a topic or modifier is, not just how similarly people search for it. To put it bluntly, it basically expands your reach far deeper into the infamous “long-tail”, making the analysis much less biased.
  5. Specifically in PPC, it can support automation and/or efficiency of the negative keyword exclusion process. Additionally, by plugging the data into a list of targeted topics (which could also be expanded by data from this tool), the initial broad-keyword campaigns can be narrowed down and optimized more quickly, which can result in less spend.

How it’s used at Granular Group

Other than what I mentioned above, we usually plug some GSC or clickstream data into the tool and then take the results for further analysis. To understand the whole keyword spectrum, we need to analyse both of those 2 sources, because GSC will generate ideas for the current website and the clickstream tool for expanding those ideas as well as all other possibly relevant queries (i.e. competition’s and general industry-related queries).

We separate topics & themes from modifiers and map them to certain intent buckets (i.e. informational, commercial, product-related, competition-related, questions, etc.), some of which differ from client to client and from industry to industry. This is then fed into a visualization tool like Data Studio where we create an encompassing and easy-to-understand view of the keyword data.

Again, it’s up to you to how the output of a topical analysis tool will be used, especially considering all the different approaches to the process of keyword research, but in its core the tool helps us better categorize the keyword data and prioritize for the next steps of further research and analysis, regardless of the specific tactical channel the data will be used for (i.e. SEO, PPC or Content).

Some caveats to be aware of

The tool is obviously made in Google Sheets so it might not work when downloaded in Excel or other desktop spreadsheet tools’ formats. Currently it supports a query list of up to 25 000 rows, which means you might need to “cut the tail short” a bit, in terms of clearing up or discarding queries by some criteria (i.e. queries with monthly impressions/volume/traffic less than a certain number). Although in our experience it isn’t a too often case where there are tens of thousands of different queries in this category that could significantly add to the quality of research (at least not in that phase). However if you do encounter such a case – consider more advanced analysis tools like python or R, as stated before.

We are also planning some improvements to make the spreadsheet more accessible and useful, some of which are:

  • the ability to analyze word clusters (as in not per word, but per bigrams or even n-grams)
  • adding sensitivity to specifics in local regional languages like noun forms (i.e. in Croatian or Serbian)
  • checking the possibility of adding modes of analysis, for example a “topic-only” mode which would only look at word roots (without plurals, etc.) to enable a focus on topics rather than variations in queries
  • looking into removing or at least tagging misspellings and similar issues (from Search Console type of sources)

Final thoughts

As stated in the beginning, this is a basic topical analysis tool that can help you get started if you’re not yet fully swimming in more specialized analysis tools. Of course, if you’re ready for heavy-duty lifting with Python, R and the likes, there is certainly a lot of great material on the topic (no pun intended) out there, like this one from Conductor (not affiliated). But in case you just want to get going as quickly as possible, this spreadsheet tool will make for an easy way to generate insights.

Of course, if you plan to use the tool or have used it and you have some questions or suggestions, feel free to reach out to us, or contact me directly on my Twitter account @tlukinic.