Keyword Hero enables you to unlock organic search queries on Google.
This data provides marketers, SEOs, and web experts with a high level of data-driven insight to make better decisions.
This post will focus on how to analyze keyword overlap between top performing organic and paid keywords.
In doing so, this will help you identify where you can optimize your paid keyword efforts.
What is Keyword Overlap?
When you have a dataset of hundreds or even thousands or even thousands of keywords, how can you ensure you are including the best performing organic keywords in your paid campaign?
Well without a solution like Keyword Hero this is simply not possible, as there is no way to see how your keywords perform on your website.
Unlocking these organic keywords allows you to identify new opportunities for your paid campaigns.
By selecting the best organic performing keywords for your paid campaign, you may be able to reduce CPC and increase CTR.
In addition, as Google awards the top 4 above-the-fold positions to paid AdWords, if you ensure you are ranking there for both paid and organic, then you can increase conversion by taking up more page space.
Organic keywords: when someone searches for something on Google, then that search term is an organic keyword.
The higher up your site appears in the rankings, the better this organic keyword is performing for you.
What is the VLOOKUP Function?
V stands for vertical.
Basically, the function in Excel or Google Sheets searches columns and returns more information from that row.
It is incredibly useful for searching large amounts of keyword data.
We are going to use the VLOOKUP function to create a table containing your top performing organic and paid keywords (AdWords).
By analyzing these keywords we will identify new paid keyword opportunities.
Importing the right organic data
First, it is very important to make sure you are importing the correct data.
Please ensure your Keyword Hero property in Google Analytics is configured correctly first.
If you are not sure about how to do this, then please check out these resources.
Note that for the first examples, we are going to keep it fairly simple.
Each user has a specific use case and more advanced keyword segmentation, but the steps in this guide still apply.
Log into your Keyword Hero account and then click on the “View Analytics” button to go to your Keyword Hero Property.
Next, change the secondary dimension from landing_page to search_position_dimension.
You’ll now see all your keywords per search position and the corresponding metrics.
Set your desired time frame and after, click on the Share this report icon.
Next, click on the Download File button.
Click on Download CSV button.
The file will save as “data-export.csv” wherever you browser normally places files that you download, typically this is your “Downloads” folder
Next, please import this file into an Google Sheets or Microsoft Excel depending on what you prefer to use.
Now we have the organic keyword data we need.
Importing the right paid search data
Now it’s time to get your Google Ads data.
To do this, please go to your main, original Google Analytics Property (not the Keyword Hero property) which has your AdWords data.
Next, click on the “Google Ads campaigns” module.
You’ll find this underneath the title “Recently accessed” and it might be to the right of other reports or modules that you’ve look at previously (e.g. Traffic acquisition, Reports snapshot, etc.).
Then, click on “Session Google Ads campaign” to change the primary dimension.
Choose, “Session Google Ads query”.
By default, these paid keywords are listed in order of Users.
Make sure you choose a similar timeframe as you did in the organic keywords for more accurate results.
Now it’s time to export the data.
Please click on the “Share this report” icon.
Then click the Download File button and then the Download CSV button just like you did with organic keyword data.
Once again, your data will save as “data-export.csv” — most likely it will have a different number (e.g. “data-export (1).csv” or “data-export (2).csv”).
Integrating the data sets
Now it’s time to pair your paid search data alongside your organic keyword data so you can compare and contrast.
The goal is to have both sets of data in the same sheet.
In this example, the keyword data has been reduced to just keywords, search_position_dimension, sessions, and engaged_sessions.
We’ve added one additional column, engagement rate (engaged_sessions/sessions), as a gauge of quality of the traffic for this keyword.
You can include whichever metrics are relevant to your organic keyword data to be your gauge.
Finally, we can sort our data by clicking on our desired metric, in this case, engagement rate.
How to Use the VLOOKUP Function
We are now ready to put the VLOOKUP function to use.
Before we do let’s quickly review the syntax of this function (advanced users can skip ahead).
The syntax for the VLOOKUP function is the same in both Google Sheets and Excel:
=VLOOKUP(search term, search range, column to return data from, match type)
- Search term: this is the term we are looking for and can be a number (42), a string “hologram”, or a cell B52.
- Search range: this is the range of cells we are going to search to find our search term. Usually this a column or multiple columns of data.
- Column to return data from: If we search column A for search term “Hologram” we can return the data from Column B associated with this, in this example the definition. This is a number counting from the first column of the range. So column A in a range = 1; B = 2 etc.
- Match type: Setting this to TRUE performs a broad match; setting this to FALSE performs an exact match. For our purposes, we will want to set this to FALSE. By default, this is set to TRUE so don’t forget to change this.
Let’s take a look at a very simple example:
| A | B | C | |
| 1 | Term | Definition | Lookup |
| 2 | Hologram | A three-dimensional image formed by the interference of light beams from a laser or other coherent light source. | =VLOOKUP(“Hologram”, A:B, 2, FALSE) |
Here we query columns A and B for search term “Hologram”.
We then return the corresponding information from column 2 (B)
The output in cell C2 is the definition of the term.
- (Full Google Sheets VLOOKUP reference can be found here)
- (Full Excel VLOOKUP reference can be found here)
Using VLOOKUP to Find Keyword Opportunities
Ok, let’s return to the data.
I have named my two tabs of data paid keywords (AdWords) and organic keywords. The column after your data is where we will enter a VLOOKUP formula.
=VLOOKUP(A2, ‘paid keywords (AdWords)’!A:A, 1, FALSE)
This formula takes the cell A2 as our search term. In our case, this is the organic query “keyword hero”. It then searches the range in our paid keywords (AdWords) data.
To do this we enter the name of that worksheet in single quotes followed by an exclamation mark and the column we want to search.
In our case, we want to search column A in the paid keyword data.
Syntax for searching another worksheet tab: ‘Name_of_your_worksheet’!A:A
We set the value to return to ‘1’.
We simply want to see if the organic keyword is also present in the paid sheet.
If so, then the keyword term will appear in our new column.
If it isn’t, ‘#N/A’ will appear instead.
Copy and paste this formula for your data range.
Make sure there are no ‘$’ signs.
After you hit enter, you can view the results:
We are looking for organic keywords that are both performing well and that don’t appear in our paid campaign.
You will see a “#NA” wherever the organic keyword is not currently covered by your paid keywords campaign with Google.
It’s well worth your time to scan through the keywords that your paid search campaign is missing and add any high-quality engagement rate keywords to your campaign.
Try experimenting with VLOOKUP yourself today.