Hacking Keyword Research: How to Generate Relevant Keywords 5X Faster

Keyword Research. Whether it’s for SEO or Paid Search…

… really kinda sucks, doesn’t it? 😟

I think it’s incredibly boring and tedious: Definitely not my favourite part of being an SEO freelancer. But also, it’s super important!

So what can we do? 🧐

To make my own life easier, I’ve created a template in Google Sheets which allows me to do keyword research at least 5X faster, with better results.

I’ve used it with dozens of my clients in the last 12 months – and it’s never let me down. Over time, I made a lot of improvements and I decided that my baby is ready to see the world.

So today I’m sharing it with you… 100% free. 🥳🥳🥳

There are just 2 things you need to use it:

  1. 19 mins time to watch my instruction video on YouTube. (Pro tip: watch it on 1.5-2X and you’ll need even less time. 🤓)
  2. A SEMRush Pro, Guru or Business Plan (or trial). If you don’t have one, you can sign up here to get a free 7 day trial with no strings attached: Click Here to Sign Up (Disclaimer: I’ll receive a small commission if you decide to pay for it)

Sounds awesome, where can I start?

First, watch my video for instructions:

Access the sheet here: Click here and make a copy of the sheet

Can you tell me how this works before I waste 20 mins of my life?

Only 19 mins! But hey, I understand! Stay with me…

What is the output of this keyword template?

With this sheet, you will be able to create a list of hundreds to thousands of RELEVANT keywords for your website.

Hundreds to thousands, is that a lot? Yes, and actually QUANTITY isn’t the goal here. This is not a size comparison!

What my template allows you is to actually FILTER your keywords quickly to a manageable number. Some may like more, some less – this is totally up to you. I usually work with around 500 keywords, but this depends on the size of your website.

Here’s a little teaser of the output…

Example output from one of my clients

Is it just keywords, or will I have more data?

Great question amigo! 🤠

There’s A LOT more data that you’ll have available. This is a sheet for the geeks, misfits and data noodlers out there.

Here’s what you’ll have in one neat overview sheet for all of your keywords:

  • The search volume
  • The keyword difficulty
  • Paid Search metrics such as Competition & CPC
  • Number of search results
  • What is the highest rank of any of the domains you’ve put in (a maximum of 6 including your own can be compared)
  • What is the rank for each domain, and how much traffic do these domains receive for each term

But isn’t that just SEMRush data I have anyway?

Yap, there’s no magic new data here. 🤗

What the sheet does is saving you about 16 steps in between, and putting it all into 1 sheet almost fully automatically:

  • No need to de-duplicate keywords
  • No need to manually combine your competitors ranking data
  • Easy filters to narrow down your keyword selection
  • A table you can use directly to create graphs for Share of Voice, Keyword Opportunity etc.

In short, saving you a hell lot of time… 👹

You’ve mentioned filters. What exactly can I filter my data on?

I’m glad you ask, my friend. 🤓

You’ll be able to filter based on all important SEMRush metrics, plus a little magic dust 🧙‍♂️:

  • Monthly Search Volume (sometimes size does count)
  • Min. & Max. Keyword Difficulty (filter for easier opportunities first)
  • Min. & Max. CPC (yes, this also works for paid search!)
  • Min. & Max. Competition

And additional metrics which you can only easily get with my sheet…🧙‍♂️

  • Filter between Brand, Non-Brand or both keywords
  • Filter for Questions only (for English only)
  • Filter keywords based on the number of words they consist of
  • Filter based on the best rank of any competitor (helps to keep relevancy of your selection higher)
  • Filter out NEGATIVE keywords (up to 50!)
All these filters at your finger tips…

Is it really fully automated?

Almost! 😎

The only thing you need to do manually is download a CSV file of ranking data for each of the domains you’re looking at and import it into the sheet.

This only about 5mins. Promise 🤗

But what about Keyword grouping?

You’ve paid attention young padawan, haven’t you? 🤗

Keyword grouping is indeed a feature in my sheet, you might have seen it in my little GIF above 🤩

Is this also automated? NOPE! 😐

Sorry! You’ll still have to group your keywords manually. The sheet makes it a little easier, which I explain at the end of my video.

So far, I have not found a solution to do it (semi-)automatically. Have some good ideas? Leave a comment below!

All this data in Google Sheets, isn’t that going to be super slow?

Yap, it’s not the perfect tool indeed. I’m working on a faster solution as you’re reading this.

But look at the bright side – it’s fully customisable. There are no limits to your creativity and what you can do with this data… 🥳

Right, can I get access now?

🙌Woop woop, I can’t wait for you to try it out!

First, watch my video with instructions:

Access the sheet: Click here & make a copy

Enjoying it? Got some feedback? Leave a comment! 👇👇👇

How to Reference Columns By Name in Google Sheets Query() Function [Quick Bite]

Hi there!

In my previous articles, I’ve mainly focused on in-depth, lengthy content. Hereby I am starting a new series of more digestible articles – delicious bites of marketing & tech delicacies 😋.

Today, this one is about Google Sheet’s Query Function. This function is nothing short but amazing. For me, it’s the nail in the coffin for MS Excel 😃 But decide for yourself!

👉 Want to go straight to the SOLUTION on how to reference by column name? Click here >>>

Here are a few advantages of Google Query over other functions:
  • simplifies complex functions and hence improves adaptability & readability
  • it’s using the same principles as SQL – so no need to re-think!
  • it’s often faster than using complex INDEX/MATCH or VLOOKUP
Cons:
  • You can’t easily ‘sort’ selections which use query(), the sort needs to be part of the query() itself
  • If you have never used SQL before, it might appear more complex first. But don’t give up here!

Alright, I won’t go into a more detail, but if you’re interested on how to use Query() – here’s a great article from CodingIsForLosers – ‘A weapon of mass laziness‘.

Today, I’d like to present a nifty solution to a problem I recently encountered – querying column names!

The problem: You can’t Query the Column Header by Name in Google Spreadsheets

In query() language, it is not possible to reference the header of a column you’d like to include in your query.

What do I mean with that?

Let’s use Sample table with the following information:

If I want to get the average salary of this data set, my query() is as follows as ‘salary’ is in column “E”:
=query(A1:G9,”select avg(E)”)
Pretty simple, right?
However, what if the raw data changes, and the ‘salary’ column is not in column ‘D’ anymore? Or someone just accidentally adds another column in?

In a normal function, Google Sheets would shift the reference accordingly – but not here. It will break the function. Not fun! 

The solution: Query by Column Name

We just need to combine our query() function with the substitute() and match() function, and we’ll be able to reference the actual name of the column. So if the table structure changes, our formula still works! 😁
Let’s use our query from above:
=query(A1:G9,"select avg(E)")
We need to replace the ‘E’ reference with something that’s more versatile, based on the column header name. Let’s dig into it!

1. First, we need a formula that returns the position of the column.

We can use the ADDRESS() in combination with MATCH() for that. ADDRESS() returns the cell position as a string. The structure is as follows:

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

  1. row: this can be “1”, even if your header column is not in row 1 – because it just depends on the range that you provide.
  2. column: this is unknown, so we’ll use the MATCH() function to find the number of the column we want to reference (e.g. in our example”salary”).
  3. [absolute_relative_mode] – optional, “1” be default. 1 is row and column absolute(e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute (e.g. $A1), and 4 is row and column relative (e.g. A1). We are using 4 in this example to keep it fixed.
  4. use_a1_notation – optional, “TRUE” as default.
  5. sheet – optional, absent by default. Needs to be changed if you’re referencing a different sheet (in our case we don’t).

What do we need to set for MATCH()?

Here’s the MATCH() structure: MATCH(search_key, range, [search_type])

  • search_key – The value to search for. We’re using “salary” in our example.
  • range – This is the range of all of the possible header you want to reference. Note, it must be one-dimensional (e.g. A1:F1). In our case, it’s just 1$:$1. ($ to make the reference absolute).
  • search_type – optional,  1 by default. We want to make sure the function is searching for an exact match and the range is not sorted, so we’re using 0

Ok, so 🥁.. here’s our function:

=ADDRESS(1,MATCH("salary",$1:$1,0),4)

Cool beans! However, we don’t want E1, we only want to return the column letter (E).

2. Remove the row number from the returned cell.

We’ll just wrap the function with a simple SUBSTITUTE() function to replace ‘1’ (with nothing): =SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","") 

Note: This only works if you’re header column is in the first row. There are ways to make this more flexible, e.g. by searching for the first number in the string (the row), and remove everything after that (w/ LEFT() function). Add a comment if you’re having problems with that!

3. Combine it back into the Query() function

Adding this into the query function looks a bit complicated, but there’s a standard format for this:

  • The referenced column must be wrapped in single quotes () for strings (if you’re referencing numbers, you don’t need that)
  • Close and re-open the query with double quotes (“”)
  • Use ampersands (&) to add the referenced cell to the query string
Here’s how it looks like: 
=query(A1:G9,"select avg("&SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","")&") ")
Looking good…!

Nice… so what’s so cool about this? Well, I mentioned that it makes the whole spreadsheet more versatile and less error prone. However, there’s more.

Bonus: How to use query() with drop down fields

Wouldn’t it be cool to have drop downs to get different values from our data range in case we need them?

Let’s assume we’re interested not only in the average salary, but also the average age.  How do we get that?

We just need to change the value of our input cell (D10 in my example). And we can use a dropdown by going to Data>Data validation and use the “List of Items” functionality.

Dropdown w Google Sheet

We can even use this to change the aggregate function. maybe we’re interested in the MAX, MIN and SUM as well? Well, here you go. We’re just using another cell to provide these as drop downs, and then reference in the query:

=query(A1:G9,"select "&D11&"("&SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","")&") ")

Here’s my sample spreadsheet if you’d like to play around or make a copy for yourself: Click to see sample spreadsheet

All working? Congrats! Now go and make some awesome spreadsheets 🤠 and if it was useful, please leave a comment, tweet me at @joradig or drop me a line on LinkedIn. It really keeps me going and motivated.

Thanks!
Johannes