A SMARTER APPROACH TO KEYWORD RESEARCH WITH SEARCH CONSOLE AND GOOGLE SHEETS
Keyword research is essential for optimizing your website. If you can effectively target low competition keywords that have decent search volume, you can increase quality traffic to your website.
In this tutorial, we’ll walk through a Google Sheet template developed by SumoLeap, which will speed up your analysis of keyword data from Search Console. What used to take us hours, can now be accomplished in just a few minutes (without any paid subscriptions).
Before we get started, here are some of the possible real-life examples for how you can use this template.
If you are an organization looking for volunteers and are based in the Twin Cities, find out which location-based search terms are most popular in your region (Twin Cities volunteer opportunities, Minneapolis volunteer opportunities, etc.)
If you are a newborn photographer, you can see how many people in your area search for newborn-related phrases versus baby-related phrases.
If you are a large publishing website, learn which content types are driving the most search traffic. Break it down further and see what keywords are performing best based on content types (articles vs podcasts vs resource pages, etc.).
To get started, simply make a copy of the following sheets and follow the instructions below. (Search Console Template, SuperMetrics Version).
Step 1: Gather Your Keyword Data From Search Console and Export to Sheets
Head over to Search Console, and click performance. Then select 12 months in the date range field and make sure all four boxes (impressions, clicks, CTR, average position) are checked. Finally, export the data to Google Sheets.
Step 2: Copy the “Query” Data and Paste it in the “Search Query” Tab
Copy/paste the “Queries” tab data from your Search Console sheet into the “Search Queries” tab of the keyword template. Use the paste special values option to maintain the formatting of the template.
Step 3: Copy the “Dates” Data and Paste it in the “Monthly Trends” Tab
Copy/paste the search trends data and paste it into the corresponding range in the monthly trends tab in the keyword template.
Step 4: Tag Your Data in the Search Queries Tab
The fun begins in step four as you unlock the power of this template with multi-dimensional keyword tagging.
Under the “Keyword Classifications” section, there are two tables. The left side of each table represents search terms that people are actually searching for. The right side of the table is the tag that we want to assign for those search terms. The purpose of the right side is to clean up the data and make it more useful for your analysis.
Notice that there are two tables in this section. One table is labelled “Locations” and the other is labelled “Keywords”. These two tables represent the two dimensions of keyword data that we will be studying. You can change these categories if you’d like, but we’d recommend that you familiarize yourself with the template before doing so.
To get started, enter the search term keywords in lowercase letters in the white column of each table. To include multiple keywords for one category, use the pipe symbol without any spaces (i.e. job|careers|hiring).
In the grey column, enter the label for the corresponding keywords. (i.e. Careers).
The grey column should always have a value. If you don’t have enough search terms/categories, simply enter the word “Other” in the unused cells.
To get the most out of this template, some thought should go into the ordering of your keywords. Watch the video tutorial for more context on this.
Step 5: Dig Into the Data!
Finally, head over to the “Keyword Summary” tab to dig into the data. The top of the page has a pivot table that summarizes the data with both dimensions. You can then dig into the granular data below by utilizing the two dropdown menus.
The “Monthly Trends” tab also shows monthly search trends. If you are using the SuperMetrics template, you can drill down into each month and see the keywords driving the most traffic.