Keyword research is the foundation of SEO strategy. Normally SEOs use spreadsheets to track and present keyword research data.
The problem with this is this only gives you a snapshot of information about those keywords, and doesn’t include live data about how your site performs for those keywords. If it was live, it would make the data easier to understand and interpret, and give you the opportunity to make decisions about changes to your website and its content to drive better results.
There are tools like Supermetrics or Search Analytics for Sheets that can improve your static sheets but these still don’t solve the problem of not having real-time performance data for your keywords in your research.
So How Can You Turn Static Keyword Research Into a Dynamic, Usable Report?
We’ve created a Data Studio dashboard that can be connected to your keyword research spreadsheet. Here’s why you need it to improve your website performance:
- It’s easy to set up
- It’s completely free
- It gives live information about how your site performs for the keywords in your research.
- The report organises the data into a useful and insightful format to enable you to make decisions to improve performance
The real magic with our process is that we’ve made a spreadsheet template that automatically generates the code that powers the Data Studio reports. All you need to do is copy and paste that code into the right place.
This is a really easy way to transform your static keyword research spreadsheet into a dynamic reporting dashboard, giving you up-to-date information about how much traffic the keywords actually bring to your site.
It’s very simple to follow, every step is explained and the template docs are linked to for easy access.
The Process
To follow this process you’ll need access to the following:
- A Google account that can access Google Drive
- The Search Console property for your website
- Keyword research data from an SEO tool such as Ahrefs or SEMRush
Part 1 - Create a Copy of Keyword Research Document
1 - Go to this link to get a copy of our keyword research template. Then go to ‘File > Make a Copy’.
This will open a popup window where you can save a new version somewhere on your Google Drive. We recommend updating the name to ‘[Your Name] - Keyword Research Template’.
2 - Open the copied version you created. You’ll see the ‘KW Data’ tab is already populated with some dummy keyword research data. Replace this with your own keyword research information. You can get this from your keyword research tool of choice. In the template the columns are:
- Keyword - The individual search query
- Group - The main group the keyword belongs to
- Sub Group - A more granular group that the keyword belongs to
- Difficulty - The ranking difficulty for the keyword. (We get this from Ahrefs but you can get similar metrics from most keyword research tools.)
- Volume - The total monthly searches for the keyword.
Populate the KW Data tab with your own keyword research. Hopefully you already have a keyword research document from which the data can be copied across.
Add Keyword Research as Data Studio Data Source
Now we’ll introduce Data Studio - a data visualization tool that lets you create interactive dashboards for reporting data. We’ll use Data Studio to build dynamic reports that present our keyword research. To do this we need to create a data source for our keyword research that Data Studio can connect to. To do this you’ll need to create a Data Studio account using your Google login details.
1 - Log into Data Studio and press the ‘+ Create’ button, then select ‘Data Source’. If this is your first time using Data Studio you might be shown a window where you need to select your country and email preferences.
2 - You’ll be shown a grid of data sources. Select the ‘Google Sheets’ one (you may need to press an authorisation button to complete this step).
3 - Select ‘All Items’ and find the ‘Keyword Research’ document you created in the last stage within the ‘Spreadsheet’ column. Once selected press ‘KW Data’ from within the ‘Worksheet’ column.
4 - In the top left corner, update the data source name to ‘Keyword Research’. Then press the blue ‘Connect’ button.
5 - Now you’ll be shown a screen that lists the columns from your keyword research spreadsheet. This window can be closed.
Part 2 - Duplicate Data Studio Dashboard
Now we need to create a copy of our template Data Studio dashboard.
1 - Go to this link then press the 3 dots icon in the top right hand corner and select ‘Make a copy’
2 - You’ll be shown a window that asks you to select a data source. We need to set the ‘New Data Source’ to the ‘Keyword Research’ data source you created in the previous step. Once done, press ‘Copy Report’.
Connect Dashboard With Search Console
So far we’ve created a copy of the Data Studio report and connected it to our keyword research. Now we need to connect to Search Console. This is how our dashboard will pull in fresh data about how your site actually performs for the search terms listed in your keyword research.
1 - Open the copied Data Studio report and make sure you’re in edit mode (press the blue Edit’ icon if not).
2 - Go to ‘Resource > Manage Added Data Sources’
3 - Select ‘+Add a Data Source’
4 - Again you’ll be shown a list of data source types, select ‘Search Console’ (again you may need to press a blue authorisation button)
5 - Select your Search Console property from under ‘Sites’ on the left. In the ‘Tables’ column choose ‘Site Impression’ and under ‘Property Parameter’ select ‘Web’. Once done, press the blue ‘Add’ button.
You may be shown a window that says ‘You are about to add data to this report’. If so, press ‘Add to Report’.
Create Extra Fields
Now we’re going to add some extra fields to our Search Console data source.This will allow us to apply extra segmentation to our Search Console data.
1 - Go to ‘Resource > Manage added data sources’ then click ‘Edit’ against your Search console data source
5 - Click ‘Add Field’
6 - You’ll be taken to a screen where you can set up a new field. Set ‘Field Name’ to ‘Keyword Group’
7 - Go back into your keyword research spreadsheet and go into the ‘Data Studio Case Statements’ tab.
8 -Copy everything from cell B2 downwards
9 - Go back into Data Studio and in the ‘Formula’ field enter the following:
CASE [paste what you copied from the keyword research spreadsheet here] ELSE “Other” END
When you’ve entered the correct content into the Data Studio ‘Formula’ field, it should look something like this:
10 - Click ‘Save’, then click ‘Close’.
11 - Now we need to repeat the process for a new field. Click ‘Add a Field’ again. This time call the field ‘Keyword Sub Group’ and copy and paste from E2 downwards from the ‘Data Studio Case Statements’ tab in your keyword research. Remember to enter it into the ‘Formula’ field in this format:
CASE [paste what you copied from the keyword research spreadsheet here] ELSE “Other” END
12 - Click ‘Save’, ‘Done’ and then ‘Close’.
Part 3 - Create Blended Data View
Now we need to use a process called ‘Data blending’. This is where we combine data from multiple data sources. In this case, we will combine our keyword research data with our Search Console performance data.
1 - Go to ‘Resource > Manage Blends’. There is already a blended data view called ‘SC (Site) + KW Research Profile’. Press ‘Edit’ on this.
2 - This will go to a page where you set up the blended data view. On load there will be two columns on the page that lets you select fields from multiple data sources. Make sure the drop-down field in the first column is set to your ‘Keyword Research’ data source.
3 - In the second column update the top ‘Select a data source’ field to your Search Console data source. Once done, it should look like this:
4 - Click ‘Save’ then press the ‘X close’ button
Part 4 - Link Blended Data Source to Charts
If you now view your Data Studio dashboard you’ll see there are two pages.
The first page should already be populated with data. If you go to page 2, you’ll most likely see that all the charts have an error warning within them. This is because they’re not linked to any data source.
This is easily solved. You need to click on each chart individually and link it to the ‘Search Console + Keyword Research Blended’ data source you made in the last step.
To do this just click on any of the charts, then in the right hand menu click ‘Select Data Source’
Then in the popup window choose the ‘Search Console + Keyword Research Blended’ data source underneath ‘Blended Data’
Repeat this for each of the charts on this page.
Part 5 - Using the Report
At this point you should have a working Data Studio dashboard that’s connected to your keyword research data and your Search Console account.
There are two pages in the dashboard:
- Keyword Research Summary
- Keyword Performance
Keyword Research Summary
This sheet summarised information from your keyword research document. Across the top you can see the total number of queries in your keyword research, their total monthly search volume, and the average difficulty score for all your keywords.
Then there are side-by-side tables. The first lists all the keyword groups in your research, and the second list all the keyword sub groups. Each contains columns for the keyword count, the total volume and difficulty scores. This lets you quickly assess the search volume and competitiveness within each keyword group and sub group.
Beneath these is a final table that lists all the queries included in your keyword research spreadsheet.
A useful feature is that you can click on any item in the ‘Keyword Groups’ or ‘Keyword Sub Groups’ tables and it will apply a filter on the queries included in the ‘Keyword’ table below.
This lets you drill down into the queries within your keyword groups and sub groups to gain a clearer understanding of where there is SEO opportunity.
Keyword Performance
The ‘Keyword Performance’ page is where you will find up-to-date information about how many clicks and impressions your site has received for the queries included in your keyword research.
Across the top there is a summary that displays the total number of queries, clicks, impressions, average ranking position and click-through-rate.
Beneath this there is a line chart that plots your clicks and impressions for your keywords over the set date range. This can be adjusted using the date range control in the top right hand corner.
Next there is a table that lists all the ‘Keyword Groups’ from your keyword research. There are columns that display the total clicks, impressions, average position and CTR for each keyword group.
The bottom table lists all the queries in your keyword research along with their individual clicks, impressions, average position and CTR.
You can click on any item in the ‘Keyword Groups’ table to filter the search terms that are displayed in the ‘Keywords’ table. This will also update the line chart to only include data from keywords within that keyword group. By applying keyword group filters you can find really valuable insights and trends about how your site performs for the keywords and keyword groups in your research.
If you're in need of SEO support, we have a team of specialists who excel in improving Shopify sites' SEO performance and would be happy to help.
Shopify SEO Audit TODAY
