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.
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 #SEO 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 @EstevanRafa1
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.
- Select Data Sources from the main datastudio.google.com main screen.
- Locate and select a Search Console-Site type data source to edit, or create a new one. See the video for details if needed.
- From the fields page select “Add A Field” the blue plus button on the upper right side.
- 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!
- From the fields page select “Add A Field” the blue plus button on the upper right side.
- 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.
- Create a simple banner – set order to bottom.
- Put in some text for the title over the header (and a logo!).
- Create a Date Range Selector. ( I leave these set to 28 days as the default )
- 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
Thanks for this. I love this. One thing. How do I stop the column custom colours from over-riding the red-up , green-down table colours that we have implemented for the entire table? Can’t figure this out!! Thanks, Lisa
Hi Lisa , I think you already have the answer for this , but it was buried in the table style setup instructions . Table Colors: Up Arrow to Red, Down Arrow Green
Unfortunately you can only change the colors for up and down for the whole table for now. In some cases (like bounce rate and ranking) where better is lower, I have created my own metrics so better always goes up! in the case of bounce rate, I just create a new field called “stick rate”. It is simply 1-bounce rate. This inverse of the bounce rate works like all the other metrics in that going up is better. It is good for people that are not used to looking at bounce rates also. In the case of rankings , I do 101 – average rank and call this “Rank Score” where 100 is the best score. This works pretty well if you filter for average ranking < 100. Its nice because it is better for calculations and charts when plotted with other metrics.
Thanks Lee. I have a great result from this 🙂 One further question on this: If I am using this dashboard to SEO optimize answer opportunities, how can I add custom field so that I can mark out the URLs that have been optimized?
And secondly, is it possible to add “page value” from Analytics to this dashboard? If so , what would use as Join Key? Thank you
Unfortunately, this is still tricky, even with blends. You can blend SC URL data with your analytics if you join on date and landing page, but your results may vary if you start throwing in queries. The way blend works causes a lot of mistakes to be made – like trying to blend SC Site and SC URL data to get CTR and Rank for a query/page combination. The problem is you are really joining on one thing and the aggregated metrics are compiled differently. If you do this you end up with issues when a query has impressions for more than one page – you will see the rank is equal for both pages which is definitely not correct. I think you may be able to pull page value from GA, but take it with a very large grain of salt!
Great question and the subject of another post! I have done this in the past and it has been pretty useful. I make an update dimension using a case statement. Basically, this is tied to the URLs you update. something like this
March Updates
case
when Landing Page in “/url1”, “/url2” then “Question Update”
when Landing Page in “/url3”, “/url4” then “Some other Update”
Else “None” End
This lets you filter on the update type and compare. If you are doing multiple updates to individual pages you may want more than one update field. This is good if you only need to track several changes at a time , but may not scale for hundreds of pages. You can also create annotations this way – See the post from Data Runs Deep on creating annotations https://datarunsdeep.com.au/blog/simple-annotations-google-data-studio-time-series-charts
Hey Lee, thanks for the video and post, this is good stuff! We definitely get a lot of questions in our long tail, this will help us pull out opportunities. Going to get this set up.
Question (ha) in the meantime:
I’m trying to do something [I thought was] pretty easy: filter GSC data by a specific page. I can do this with the URL connection but the data is way off compared to native views in GSC. The site connection matches the top-level metrics exactly, and if I’m in GSC I can filter by page – why can’t I do this in Data Studio?
Thanks Lee – James
Hi James, I see this question fairly often now. The URL hits are pretty accurate, but if you add them all up they dont equal the site totals. I believe this is due to Google not reporting URL detail for some very long tail queries and some secure instances where they feel the user identity is at risk of being exposed. Basically you can be pretty confident in the site totals as a whole, and the URL details as being pretty correct for any individual page, but they will seldom add up the same. As they say , “It’s not a bug, it’s a feature!” .
Hi, Lee
Thanks a lot for the article and video, it was very insightful and inspiring!
I’ve got one not-so-related question, but as I haven’t found an answer after quite a bit of research (even reading through GSC API documentation), I’d really appreciate your take on this:
Can I get data for “Pages in Index” and “Crawl Errors” from GSC into Data Studio?
Hi Bojan,
Sorry I’ve been tied up the past few months writing the data studio book. I haven’t seen anything yet, at least not in the standard connectors. I will keep an eye out through – quite possible people may build a community connector for this if they can find an API to access.
I love this strategy of segmenting out questions in data analysis…I’d like to replicate this, but formula for Query Category seems to be causing syntax error.
Hiya, Sorry I didnt get to this earlier – missed the notifications. Thanks for the feedback and I will check the formula again – you never know , might have had a typo in there.