If you came to this chapter thinking this guide is going to use a third-party tool that costs money, you’d be mistaken.
Third-party tools and even Google’s own Keyword Planner often return back little to no search volumes on local keywords. These estimated search volumes can also be misleading. It’s not the third-party tools’ fault, they rely on Clickstream data (except Google).
You craft out what you believe to be a great keyword list, only to find “n/a” on a majority of keywords you added.
This is a bummer, especially for long-tail keywords.
There are 4 different ways to bid on a single keyword with Google Ads. The default is broad match, meaning, Google will show you ad for anything they deem relevant. If you want to try and find accurate search volumes in Google’s Keyword Planner, stick to “phrase” and [exact] match. Even then, take the results with a grain of salt.
Let’s move on…
In this chapter, I’m going to show you how to combine Google Search Console with Google Data Studio to show REAL search volume data.
The best part of this report? Besides the useful information it shows? It’s completely free.
If your business is new or you don’t have Google Search Console set up, I’ll be adding to this very chapter soon. Yes, I’ll be adding how to use third-party tools to perform local keyword research.
The report I’m sharing in this chapter has 3 different pages, each with its own purpose.
Each page has 3 different charts/tables.
Before we start…
I would like to thank and acknowledge Jordan Choo & Noah Learner for their help in building out this data studio report.
Check out some of the nifty stuff they’ve done on AgencyAutomators.
WHY IS THIS REPORT IMPORTANT?
Creating a local SEO strategy without knowing what local people search for is like setting sail to a destination without a GPS or compass.
This report can help you:
- Group keywords by category
- Identify the top 10 keywords in Google My Business (by impressions)
- Identify the top 10 keywords in Google Search Console (by impressions)
- Look at historic averages (individual & categories)
- Find related keywords and their search volumes
- Create local SEO strategies
- Other ways I can’t think of right now
If you want to skip to setting up your own, respect, go there now.
Onward.
PAGE 1 (UTM)
Spoiler alert, if you don’t have UTM codes on your website’s Google My Business listing this page isn’t going to work well for you. If you want to learn more about tracking and how to set up UTM codes in Google My Business, I covered it in the tracking the chapter.
The UTM code that you place on your website link, Appointment URL, GMB posts, etc. in Google My Business show up in the Performance tab in Google Search Console.
We’re going to use the UTM code to organize keywords in this sheet.
The end result?
Something like this:
CHART #1
This chart will show you the top 10 keywords and their daily search volumes from Google My Business.
CHART #2
This chart categorizes keywords using regex to show you which category is getting the most search volume in Google My Business.
If you’ve had your UTM code set up for a long period of time, you can look at seasonality trends based on historic data.
CHART #3
This table shows you the keywords that didn’t get matched up in the regex. This table can be useful in showing you potential keywords you never thought of. You can also create a new Filter to show individual categories to find your semantically related keywords.
CAVEAT
If someone searches and your listing is not shown, there is a possibility that no impression will trigger.
PAGE 2 (EXCL. UTM)
We’re going to duplicate the 1st page and change up what we’re going to look at.
Instead of only looking at Google My Business, we’re going to look at Google Search Console data and EXCLUDE Google My Business.
The charts are the same but they’re showing data from Google Search Console (traditional organic search) instead of Google My Business.
Here’s how that page looks:
PAGE 3 (LOCATION RESEARCH)
Page 3 is a duplicate of Pages 1 & 2 but it has a different regex.
If you work with a business that wants to rank in different cities, this page is going to be your best friend.
The regex pulls in city-specific keywords from Google Search Console (traditional organic search) and excludes Google Maps.
Chart #2 contains the names of the locations you want to look at search volume data.
Here it is in all its glory:
SETTING UP PAGE 1 OF THE REPORT
Step 1: Grab a copy of the report.
THE TEMPLATE IS GOING TO HAVE CONFIGURATION ISSUES, FOLLOW THE REST OF THE STEPS.
Step 2: After clicking on make a copy, a box will open and allow you to add a New Data Source. If you have used Google Search Console in Data Studio before, select it and head to step 7. If you have not set up Google Search Console as a data source before, continue to step 3.
Step 3: Click on CREATE NEW DATA SOURCE
Step 4: First column, 6th option down is Search Console, click on it.
Step 5: You’ll then see a list of sites that your Gmail account has access to in Google Search Console. Select the website and in Tables, select URL impression.
Step 6: In the upper-left-hand navigation, you can change the name of Connection if you wish. When finished, click on the button ADD TO REPORT.
Step 7: Click into the 1st chart so you can see the Data Source, Dimension, etc. on the right-hand side of your screen. Make sure everything matches with the image.
Step 8: Click into the 2nd chart, you’re going to run into an issue with the Breakdown Dimension. We’re going to fix that. Go ahead and remove it for now.
Step 9: In the top navigation, select Resource, then click on Manage added data sources.
Step 10: Click on the EDIT icon for the site you want this to work on (you might have many sources to choose from).
Step 11: Click on the ADD A FIELD button in the top-right.
Step 12: Under field name, name it something you can remember. I named ours WHATEVER YOU WANT MAN.
Step 13: We need to set up regex to group keywords to the category you want. Below is a sample of raw code for you to copy:
CASE
WHEN REGEXP_MATCH(Query, “((?i).*keyword1.*|.*keyword2.*|.*kw3.*|.*kw4.*|.*kw5.*)”) THEN “Category 1”
WHEN REGEXP_MATCH(Query, “((?i).*keyword1.*|.*keyword2.*|.*kw3.*|.*kw4.*|.*kw5.*|.*kw6.*)”) THEN “Category 2”
WHEN REGEXP_MATCH(Query, “((?i).*keyword1.*|.*keyword2.*)”) THEN “Category 3”
WHEN REGEXP_MATCH(Query, “((?i).*keyword1.*|.*keyword2.*|.*kw3.*|.*kw4.*)”) THEN “Category 4”
WHEN REGEXP_MATCH(Query, “((?i).*keyword1.*|.*kw2.*|.*kw3.*)”) THEN “Category 5”
END
The formula outside of “(“”)” the keywords is case-sensitive, use all caps. You don’t have to use all-caps when naming.
Simple definitions from a guy who knows very little about regex:
(?i) = to allow all case types of a keyword
.* before and after keywords = It allows words to happen before and after the keyword.
| = a separator between 2 different keywords.
Grab the raw code and paste it into your formula.
From here, start removing the placeholder keywords but make sure to keep the .* before and after each keyword. If you want to add more keywords, make sure to add a | separator.
Make sure you name your Categories, too.
When finished, click on Save in the bottom-right, then click Done in the top-right, then click close (top-right).
Step 14: Click back into the 2nd chart and in the Breakdown Dimension, start typing the name of the field you created. Viola.
Step 15: Click into the 3rd table, and under Dimension. remove the Invalid dimension and replace it with the one you made.
Step 16: Scroll to the bottom to show the single filter called maps utm. Click on the pencil icon to edit the filter.
Step 17: Change ‘brand name’ to your brand name, click Save. The purpose of this exclusion is to remove brand queries from showing in the report.
Step 18: We have 1 more Filter to create before this page is complete. Don’t worry, the rest of the pages are a breeze to set up after the 1st page is set up. Click on ADD A FILTER.
Step 19: I named ours null filter, it’s purpose is to show the keywords that didn’t get put into a category from the regex formula.
The Filter should be: Exclude > ::The field you created in Step 13:: > Is Null.
Click Save.
Step 20: Page 1 is complete but you are not completely done yet. When I first set this up there were more than 300 keywords that were in the 3rd table. Now there are 26 in my current view. I went through and made notes of keywords that belong to specific categories, then edited the regex formula. If you don’t want to set this up right now, skip Step 21.
Step 21: You’ve gone through your list of null and want to edit the formula, bully for you. In the top navigation, click on Resource, Manage added data sources. Click edit, then click on the fx button of the Field you created in Step 12.
Edit the formula away, click Update in the lower-right, then Done in the upper-right, then click close.
The last step to see your results is to Refresh the report, click on the Refresh data button (top nav).
You might need to go through this a few times before your happy, repeat this step until your satisfied.
SETTING UP PAGE 2
Step 22: Click on the 1st graph and make sure your Dimensions and Metrics look like this image (your Data source will be the data source you set up first):
Step 23: Scroll down to the Filter section, click on ADD A FILTER.
Step 24: Name your field (I named mine excl. utm).
Exclude > Landing Page > Contains > the utm code you use for Google My Business or put ?utm_source=.
Again, we wish to exclude brand searches.
Exclude > Query > Contains > ‘your brand name’.
Click Save.
Step 25: Click into the 2nd chart. You’ll need to update several fields.
Breakdown Dimension: The Field you created in Step 12.
Metric: Impressions
Breakdown dimension sort: Impressions
Filter: You’ll see red under the WHATEVER YOU … filter, hover over the symbol to edit it.
You can rename the filter (whatever man) if you wish.
In the second field that’s labeled Missing, click into it and do a search for the Field you created in Step 12. Save.
Now add the new excl. utm filter you created in the step above.
Step 26: Click into the 3rd table, there will be some Dimensions, Metrics, and Filters to update.
Dimension: The Field you created in Step 12.
Metric: Impressions:
Sort: The Field you created in Step 12.
Filter: Add the null filter you created in Step 19.
Add the new filter you created in Step 24.
You have completed Page 2.
SETTING UP PAGE 3
To get locations to show up only, we need to create a new Field as we did in Step 13. Yes, we’re going to be using regex again. After that, it’s as simple as creating a few filters and you’re done.
Onward.
Step 27: Click on Resource, then Manage added data sources.
Step 28: Click on edit to edit the connection you’ve been working with.
Step 29: Click on ADD A FIELD in the top-right.
Step 30: Name your Field (I named mine Locations).
Step 31: Copy and paste the regex code as your formula:
CASE
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location A”
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location B”
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location C”
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location D”
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location E”
WHEN REGEXP_MATCH(Query, “((?i).*location.*)”) THEN “Location F”
END
Step 32: Start changing the location placeholders with the locations you want to see impression data. Don’t forget to rename Locations after each THEN, too. You can add more lines to track more locations, remove lines to track fewer locations, etc.
Step 33: Update the Field (bottom-right), then click Done (top-right), then click close (top-right).
Step 34: Refresh the report.
Step 35: Click into the 1st chart, we are only going to need to add a few filters.
Scroll down and click on ADD A FILTER. Select the excl. utm filter you created.
Click ADD A FILTER again, then click on CREATE A FILTER.
Name your filter (I named mine Locations)
Exclude > ::the new Locations field you created:: > Is Null. Save.
Step 36: Click into the 2nd graph, we need to update a few things:
Breakdown Dimension: The new Locations field you created.
Metric: Impressions.
Breakdown dimension sort: Impressions.
Filters: Add both the excl. utm filter & the new Locations filter.
Step 37: Click into the 3rd table, we need to update a few things and then your report is complete:
Dimension: Locations field.
Metric: Impressions.
Sort: Impressions.
Filters: Add both the excl. utm filter & the new Locations filter.
Congratulations, you have a new report to dive deep on.
