This is the first post in a series that explores ways you can use Search Console (SC) and Google Data Studio (DS) together.   The goals here are:

  • Get better and faster insights.
  • Reuse and build on previous work.
  • Where possible, eliminate exports to worksheets for analysis.
  • Streamline Search Engine Optimization (SEO) data analysis for action. 

In this example, we will be adding some simple custom dimensions that will help you find “Question Queries” and do some basic analysis.   

Here are the links to the Data Studio workbenches we will be building. 

Questions Workbench Ex 1 V1.0     and    Questions Workbench Ex 1V1.1  


Update Feb 16, 2019:  After working with Estavan Rafa (Twitter @EstevanRafa1) we finally worked out a Spanish version of this Questions Workbench!  So for all my Spanish SEO friends out there …. What other languages would you like to see?

Para mis nuevos amigos en España.  Aquí está la versión en español del banco de trabajo de preguntas de la Consola de búsqueda. Puede utilizar de inmediato para analizar sus propias propiedades. Gracias a

Preguntas Workbench Ex 1 V1.0   


I call them workbenches because they are designed for exploration and quick modification.  They are not designed to be end reports in themselves, but to give you the ability to play with and analyze data.  This is going to be one of the few examples where you can actually use the workbench immediately without customizing for your own purposes.

I have included ‘Data Selectors’ in the design so you can use them right away – you don’t even need to copy and connect your own sources because, as long as you are logged into Google, you can select any search console profile from the selector and the workbench will just work.   You can also copy them as templates and modify them too!

The main reason for this series is to get you building your own custom reports and workbenches.  There are a lot of great templates out there, but they really can only go so far. Templates have to be fairly generic: They really can’t provide the custom fields that are needed to provide more actionable insights.  I won’t bore you with the details here, but I will write a separate post on why this is so, and why I use my own data in these examples.

Onward!

They ask. Are you answering? What, Who, Why, Where, When and How

These are high-value queries that indicate exactly what your audience wants to know.  If you show up in the results then at least Google thinks your site may provide the answers. Understanding how you rank for these, and other metrics can guide you to better content creation and better SEO results. They are pretty universal,  easy to analyze,  and the actions the data suggests are pretty clear. This first example will help lay the groundwork for more customized examples to come.  

You can play with the data from Helpfullee.com in the version embedded on this page. Dynamic selectors and embedding don’t mix, so if you want to see some of your own site data go directly to one of the links above.

The primary way to group just about anything in Data Studio is to create a custom dimension. The most popular way to do this is to use simple case statements.  ‘Case When’ statements in data studio are like a group of simple ‘if-then’ statements.  There are some subtleties you may want to consider when you use them, however.  See this for the gory details.  Data Studio does not have regular ‘if-then’ option, so get used to using case statements!

If you’re not thrilled about reading further, check out this video. It goes through the full process of building this workbench and also looks at a more exciting live example and goes through analyzing some of the results.

So, here’s the plan. We will create two new dimensions. First, we will create a ‘Query Category’ field that will capture the queries with questions and break them out into types by keyword.   Anything left over from this gets tagged as ‘Other’.  Next, we create a dimension called ‘Query Type’ that simply groups these values as ‘Questions’ or ‘Other’.  You need to create the detail field first because ‘Query Type’ depends on the output of the ‘Query Category’ field!  Thoughtful field design allows you to chain fields and build much more useful and complex capabilities.

If you are a beginner with editing in Data Studio, don’t be concerned about the instructions below.  There are a lot of guides and tutorials to get you familiar with the basics.  I won’t cover the basics in detail here. The video above will walk you through this example. Need more help? Please see the Google Data Studio Resources finder if you want a good post or video to review.

New dimension field setup for ‘Question Queries’ using a Case Statement.

  1. Select Data Sources from the main datastudio.google.com main screen.
  2. Locate and select a Search Console-Site type data source to edit, or create a new one. See the video for details if needed.
  3. From the fields page select “Add A Field”  the blue plus button on the upper right side.
  4. Add the values below and hit “Save”.

Field Name:  Query Category
Formula:
Case
When REGEXP_MATCH( Query, “.*why.*”) then “Why”
When REGEXP_MATCH(Query, “.*who.*”) then “Who”
When REGEXP_MATCH(Query, “.*where.*”) then “Where”
When REGEXP_MATCH(Query, “.*how.*”) then “How”
When REGEXP_MATCH(Query, “.*what.*”) OR REGEXP_MATCH(Query, “which”) then “What”
When REGEXP_MATCH(Query, “.*when.*”) then “When”
Else “Other”
END

Now that we have our detail dimension, we will make another field so we can easily switch between the question queries and all the other queries!  

  1. From the fields page select “Add A Field”  the blue plus button on the upper right side.
  2. Add the values below and hit “Save”.

Field Name:  Query Type
Formula:
Case
When Query Category = “Other” Then “Other”
Else “Questions”
END

Building the Questions Workbench Ex 1 V1.0 

Now that we have our fields, let’s put them to use!   Let’s start with a fresh data studio report. I like naming these “Workbenches” to distinguish them from final reports.  I like starting with a simple table to make sure everything is functioning correctly, then modify from there. Pretty standard settings here, but please note the color change for up and down arrows:  We want drops in rank to be flagged with a green arrow, not a red one!  

Create a new table with these settings (really, see the video if you need help here! ) 

Main results table setup

DATA Tab Settings
Dimensions:
  Query
Metrics: Impressions, Clicks, Site CTR, Average Position
Rows per Page: 2500
Show Summary Row: Checked
Sort By: Impressions
Comparison Date Range: Previous Period

STYLE Tab Settings
Table Header:
Wrap Text
Table Colors: Up Arrow to Red, Down Arrow Green
Table Body: Uncheck Row Numbers, Check Wrap Text
Table Footer: Uncheck Show Pagination
Column #1 – Uncheck Show Compare, Select Bar and Show number
Column #2 – Uncheck Show Compare, Select Bar and Show number
Column #3 – Uncheck Show Compare, Select HeatMap, and Show number
Column #4 – Select HeatMap and Show Absolute Change.

So now that we have the basic results table, we need to add some filters so we can slice and dice the data with our new dimensions. Instead of using standard drop-down filter selectors, I like using the new interactive chart filters.  While you can get some more functionality from the normal selectors, like searching through values, in this case, we have a very limited number of selections we can make.  We can save some space and create some extra quick insight by using graphs. For the query selector, we will make a simple table.

Create a new table with these settings for the Query Category selector

DATA Tab Settings
Dimensions:
  Query Category
Sort By: Impressions
Interaction: Apply filter

STYLE Tab Settings
Table Header:
Wrap Text
Table Body: Uncheck Row Numbers, Check Wrap Text
Table Footer: Uncheck Show Pagination

Create the Query Type table/selector

We can use the Impressions bar chart to show some of the relative amounts of Questions and Other types of queries.  Here are the settings

DATA Tab Settings
Dimensions:
  Query Type
Metrics: Impressions
Sort By: Impressions
Interaction: Apply filter

STYLE Tab Settings
Table Header:
Wrap Text
Table Body: Uncheck Row Numbers, Check Wrap Text
Table Footer: Uncheck Show Pagination
Column #1 –  Select Bar

All that is left now are a couple of details that will make the workbench complete.

  1. Create a simple banner – set order to bottom.
  2. Put in some text for the title over the header (and a logo!).
  3. Create a Date Range Selector.  ( I leave these set to 28 days as the default )
  4. Add a Data Control Selector! 

Unless you only own a single property, adding a Data Control Selector can be a major time saver for workbenches!  As long as the work you have done setting up your new dimensions can be applied to other properties you will simply be able to change data sources from the selector instead of making a new report!   Normally, you would remove these for actual reports you would share.  In this case, just about any site can use the Questions fields as we have them set up here.  When we get into things like brand terms and content groupings the data selector won’t make sense for multiple properties.

Please note that this is why I am using my own site data here as the default for the selector instead of the sample store connectors provided by Google. You cannot edit the sample connectors, so as great as they are for demos, they do no good here!

And yes, there are plenty of SEO opportunities on my site so feel free to tell me about them!

Using the Questions Workbench for Insight and Action

So, now that we are all set up, can we actually get some insight and can it indicate actions we should take regarding SEO?  I think so!  If you watch the video I briefly go through some of the opportunities on the volunteer site we looked at.  I look forward to your comments.
Here are some basic guidelines.

  • Look for questions with high impressions and good ranking (less than 10) and low CTR.  Actions I suggest here to find the pages that the queries are pointing to and optimize the title and meta description tags. (Identifying the pages in the workbench is another post!) Better descriptions can increase CTR and indirectly increase rankings.
  • Find similar questions that are poorly ranked. If they relate to your site, this is a good target for new content.
  • Take some of these queries and explicitly answer the questions, and answer them well, either in existing content, a FAQ or in new focused content.
  • Using question and answer markup may help you score top featured snippets on the search results page.
  • They ask. You ANSWER!     (tip of the hat to Marcus Sheridan for his now classic book)

These question queries are about the simplest, most direct to action data you have available.  Use it and share with your clients.

This example was mostly inspired by Andy Simpson’s post on the SterlingSky Local Search Forum ‘Data Studio Report for “What Where Why When and How”…with a little local SEO on the side‘ from February 4, 2019.   After creating a couple of my own workbenches I decided this was a good topic to start this Search Console / Data Studio exploration series. In the post, he provides an example of using filters in DS to find question queries. I’m choosing to focus on using dimensions here because the method will be used often in other examples.

In order to get this post out in my copious free time, I went easy on the graphics and layout.  When I have time I will tweak it to make it prettier. After all, updates are good for SEO, right? I hope the format works as I am planning several posts in this series.

As always, I invite your comments here, and you can contact me on Twitter – @Helpfullee