Data Studio for SEOs: Reporting Automation Tips

I’m Nefise, one of the “SEO Painkillers” team members. Here to share with you the recap of our latest Weekly SEO from on Feb 18, 2022

Lazarina Stoy was our guest in our last Weekly SEO stream. In this episode where we talk about Data Studio, Lazarina gave us some great templates and tips about Data Studio that you should never miss. You can access the templates Lazarina mentioned on the slides below.

If you’re ready, let’s start!

• Host: Adem Yıldız, SEO Team Leader at BoostROAS
• Guest Speaker: Lazarina Stoy, Technical SEO & Innovation Manager

“We will talk about data studio, some benefits and limitations, some reporting, automation, tips and tricks. Although I say that we’re only going to be scratching the surface today. There are so many things that you can do with data studio, and we cannot possibly cover that in 30 minutes. So, just some things to get your start at, essentially. Then how to turn spreadsheets into reports? I will be sharing a very holistic overview of my process when I encounter a spreadsheet that I think will be more useful as a dashboard and some templates that will go into your arsenal to make your life easier as an SEO.”

So let’s get started. But before we do, I just wanted to ask people in the audience; Are you guilty of any of these statements?

The first one is “I’m not that good with data.”

The second one is “Not an analytics person myself.”

The third one is “I wish I could have more time for fun automation and advanced tech projects, but I’m too busy, and at the same time, I manually report on data in spreadsheets or slides every week and month.”

If any of these apply to you or even just the latter to meet your new best friends, honestly, like data studio, you will become your new best friend because you have access to data for more than 800 data sets, almost 500 connectors.

There is probably not a single thing that you report on that you will not be able to report on in the data studio. And not only that, but you also have a huge arsenal of templates and dashboards that are already created that you need to plug your data in and 90% of the time, they’re going to look better in data studio. Then things look in the slides, in the spreadsheets or any other form of reporting that you currently do for your clients. And the beauty about it is that they can become fully automated as well.

Benefits of Data Studio

So Let’s talk about some benefits of data studio. I even bother with this at all. And it’s a long list, but I’ll try to get over through it as quick as possible. 

1. First of all, you can visualise any data you want and easily, you plug in a dataset based on the template that you have, you will have that visualised.
2. You can pull data from multiple data sources,
3.  And we’re going to talk about how you can also blend these data sources as well in a bit, but this will essentially increase your understanding of data and how particular data points connect.
4.  You can also easily share and export data studio reports, which is always a plus.
5. They’re very easy-to-read and pretty to look at if you put in the time to, you know, make them pretty, but most of them already are pretty advanced anyways, and there are some speedy ways to, you know, make reports beautiful.
6.  Another cool thing is that it is dynamic and that there’s also fresh data with most of these datasets. Of course, we’re going to talk about some limitations, but only even just these two things are a massive plus over. Slides or reports that are just pulled and, you know, they’re not interactive.
7. So interactive experience, that’s something that’s every single stakeholder is going to be able to appreciate and also the fact that you can go into a data studio report, maybe whilst you’re talking with a client. And have an interactive experience together where you’re both interacting with this report, pulling the most critical insights for every stakeholder that is going to be interacting with it.
8. And also, as I mentioned, there’s just an abundance of data studio templates that are free and including for things you know, the generic ones that are for Google Analytics, maybe for AdSense, Google Search Console, a YouTube Analytics. Still, then you can go very deep into the rabbit hole of templates and find things that are very edge cases that you might think that nobody has taught to make templates like this. But in most cases, there are already templates out very edge cases. So, it’s a huge benefit to get started with a template and build it. So it suits your clients.
9.  Finally, it’s very easy to learn with free Google data studio tutorials. There is a tutorial in Google Analytics as far as I remember, but there’s also a ton of resources out there. Not only video resources but articles and different tutorials to teach you essentially everything that you need to know about Google data studio. 

Limitations of Data Studio

1. So, first of all, you cannot combine more than five days of source blending, and all of the data sources should have a joining key. So the joining key part a is no longer the case, but you still have a limit of no more than five data sources for blending. An alternative is to blend data offline or use automation tools to ease that process.
2. You can incorporate many data sources. However, what I’ve found is that if you go maybe above 30 or maybe even 50 data sources for a single dashboard, for instance, if you have a multipage dashboard, like ten plus pages, all of which Every single page of this dashboard, having maybe ten different data sources, not only is that not going to tell a good story, but also, your report is likely going to break. So people that use data studio a lot know that it has a tendency to show as a blank error of death, you know. So how does it make reports should be? Should it be something on the front lines? 
3. So another thing to beware of sampling. Data studio samples, a lot of the data sources and also the more filters that you apply to a particular chart or table, the more likely it’s going to be that you’re going to see some sampling and one example that we have at the moment is that the bar charts are sampled at the bigger rates than scorecards. So you might have a scorecard on a particular page of the template, and you see, for instance, a scorecard showing 400 pages with clicks or something like that, whatever and then the bar charts are showing like 300, and if you see some discrepancies between the charts that you have on the table, just scroll down to the bottom left-hand corner. And if you know a show sampling button, click on that. And it will showcase to you the different rates at which different charts on the page are sampled. So just so the very important thing to know, for instance, is why you’re seeing discrepancies between the same type of fields on the same page and the same data source, most likely at sampling.
4. And the other thing is that my coping filters from dashboard template, you’ll copy the template and you plug in your data, the filters most likely are not going to be copied. So you need to manually go in and select the dimension that matches the filter from the original tutorial. So that’s something for people that are creating templates. And if you are publishing them with a post, it’s always good to say exactly how your tables have been filtered in order to achieve the particular views in your template because it is very useful for users to know.
5. The other thing is that the ability of connectors highly depends on your internal database schema. And I don’t want to name a shame, but there are some platforms that we use as SEOs that have a very segmented database schema. So what this results in is that the, of particular platform. We’ll have maybe three or four different connectors, depending on, for instance, whether they offer position tracking with one connector, and then they offer a site audit, but another connector and things like that. Suppose you are trying to visualise the 360 data using that type of connector. In that case, that will very quickly multiply the number of connectors you have for a particular report which is, of course, kind of a limitation considering what we said about having multiple data sources for a single template. So it means that your dashboard will be a lot more error-prone that way. So the alternative for this will be to use an API instead of the connectors when you see that these connectors have a very fragmented database. And in that way, you’ll be able to kind of control the data source there.
6.  Another final and most exciting thing is that data blending uses the life outer to join. So this was the case until yesterday. So as of yesterday, this is no longer the case; exciting news data studio updated their data blending options. So we have four new types of blending available to us. We now have an inner join, right outer join, left outer join, full outer join and crossed outer join. You can pause there, and later on, you can review these in the slides, but what this means is that we no longer have to have a common field between the two data sources that we are joining. We are no longer limited to the fact that we have to have the first data source containing all of the data that we want to display, which was the case of what’s left outer join. So this is something that if you’re familiar with, SQL, whichever way you pronounce it, it might be a little bit more complex to kind of grasp. However, Data Studio has very good guidelines on how to blend data. So it is all I can say within these short webinars today is that this is a topic on its own, but it opens up a lot of opportunities for blending data and visualising different data sources and really getting to the nitty-gritty of the data that you are displaying.

Quick Wins, Tips and Tricks 

When applying, starts and dates to do so. I will share a few formulas most of the time, and these are just custom dimensions that are, regardless of what data source you’re using as an SEO, you might want to segment your data using these custom dimensions.

Extract Brand Name from URL

The first one is to extract the brand name from a URL. And the use case here is when you’re doing SERP analysis or competitor analysis, and if you have the URL as a dimension, you might want to see a custom dimension where it just summarises the different brands and how returns account on how many URLs they have in your particular dataset. So, for instance, if we have a competitor analysis that includes five brands amongst, which are Google, Moz, Semrush or whatever you will with this custom dimension, you will have an account of the URLs in this particular field. And you will have Google.com or moz.com as the label of this count. So a very cool useful thing to have. 

Bucket Position Result into Groups

Another thing is that formula here shows you two different ways to create buckets for positions. And also a way that you can visualise this using kind of like a distribution chart, use cases here are when you’re doing SERP analysis when you’re doing Google search console analysis for your queries that you’re ranking for, or, again, when you’re doing competitor analysis and that particular chart that I have shown here is, for a competitor analysis of eight different competitors and the positions within which they are ranking URLs

So that is very useful to know and really have a bird’s-eye view of your dataset. So in this regex here, You can see different ways to achieve this. Again, I’m not going to go into too much depth about this, there are a lot of project guides out there, and you’ll be able to very quickly navigate how to build these rejects functions.

Extract URL Top Level Directory

Another thing is to extract the local tilts directory very useful when you are doing again, competitor analysis to see how competitors are structuring their URLs. And it’s also very useful when doing a SERP analysis.

Page Section Segmentation

Another thing that this formula is going to be very useful for is if you want to segment your data based on the page section if you’re analysing on your own site. This is one way here. If you already know the sections of the site, but if you don’t know them, the first step would be to extract the top-level directories and then visualise them with this type of drop-down filter. Again, very, very useful, very useful to be able to you know how this type of filter in your report because you will be able, for instance, if you’re doing things like core web vital reports or any form of website analytic report, it will be very useful to know which sections are performing well. And especially useful if you are benchmarked on the performance of a particular section on the website, for instance, the blog. Let’s say because most of the non-brand pages are typically in the book.

Click/ No-click Pages 

Another thing is to create a very, very simple field to show the pages with clicks and pages without clicks. Again, this is very beginner-friendly, but they’re useful in order to see and prioritise the pages that you’re giving recommendations for, and maybe pages that you want to do some on-page optimisation for.

URL Language Filter

Regarding the language filter, this is very useful when there are some international pages or versions of the website. If you have within the URL and you are able to create a filter in the report that will allow you to segment the data that you are looking at based on the language of the pages. So a very quick but very useful, especially for Search Console analytics website analytics. And even when you’re doing competitor research and SERP analysis.

Search Intent Classification

Another thing is some dashboard that I shared a while ago, um, that search intent classification in data studio has become quite popular recently. But if you, for some reason, don’t have access to SEMrush or any of the other tools that do provide these labels for the search intent of the keywords, then you can create a very, perhaps a little more basic version of this type of filters and data studio. I’ve written a long tutorial about this, and I also have a data studio dashboard template about this. But just to kind of give a quick overview, how this is done is essentially you include keywords that signal the type of intent. So, for instance, if we look at the four main categories of informational, navigational, commercial, and transactional intent, you will have particular keywords indicating the intent for long-form queries.

And for instance, for transactional, as we can see in the example on the slides is that This is indicated by keywords, like “buy, price, cheap, expensive” If we look at, for instance, informational intent, it will be a kind of queries, which are, maybe question oriented, like “who, why, what” or maybe indicate the type of content that the person is looking for to be a guide or an infographic or things like that. So again, this is a very long topic, but you can find cool resources about this in the resources mentioned.

Branded Vs Non-branded Keywords Split

And the other thing, and this is the final one, is to do a branded vs non-branded keyword split. And that is very, very common, and I’m sure we’re not groundbreaking with this recommendation.

But at the same time, I know a lot of reports that I’ve personally seen. I don’t have this, but it is a very, very useful filter to have in many reports. Especially because, in most cases, you might find that branded queries are skewing the performance, like report performance being higher when actually when you remove the branded queries, it turns out to be a little bit lower.

Turning Spreadsheets into Dashboard

How to turn spreadsheets into dashboards or otherwise how to turn these boring spreadsheets into these cool dashboards and why we should actually think about this more frequently? So I have tried to summarise the process into four steps.

Access & Explore Data

The first one is to access and explore your data. So when you have a spreadsheet or any type of data, you would first need to know the structures. So, for instance, if you have a data set with 20 different columns, you would need to know which columns contain numeric data and which columns contain textual data. Are there any that contain bullying data such as yes or no, or like any sort of maybe labels for this? And just to provide an example of what we discussed in all of the different tips and tricks. These are essential labels that we will provide to the data. So if you know that your data does not have labels by exploring it, you will be able to know where you need to provide labels to enhance the user’s understanding later on for the particular dashboard that you’re building.

Analyse the Data & Build Support

You need to analyse data and build the support dimensions mentioned earlier. The most important thing here is to start by exploring the data via a table or anything like that. And then try to see where you can provide additional support for the people who will be using the dashboard via custom dimensions.

And specifically, here, I want to highlight that it’s important that the dimensions that you build tell a story. So you might have a data set. That does not have any additional support, and via the dimensions that you build, you might be able to tell particular stories about “How your international pages are performing” “How the pages are performing in the context of branded versus non-branded or search intent” “How particular pages are performing” Maybe that tackles a commercial search intent and that are non-branded. So if you get my drift here, the more custom dimensions you build, the more support you built for this particular data set, the more useful there will be for very niche audiences of this data.

Split by Sections, Visualise

So the step after that would be to split by sections and to visualise. I do want to highlight that it’s important to kind of balance things out, and here, the knowledge that you have for a particular data set from step number one is going to be very, very useful because if you know the particular dimensions of your data and the types of data that you have, you will know what will be important for your stakeholders to see. For instance, if you are building a report for C-suite managers and executives, you might not need to include any tables that allow them to, you know, dig deep into the data because they might not be interested in doing that. They just want to see the bird’s eye view and maybe some comparative metrics of how the site is progressing in overtime and things like that. But if your dashboards are going to be something that is going to be used daily, maybe by an analyst or a manager, then you would need to include additional pages that allow for them to dig very deep into the data and essentially use this dashboard as a tool that is a very good indicator for them to know exactly where to look at later on when they access the issues that they are that have been highlighted by the dashboard.

So, again here, it’s very, very good to know whether any exploration is needed to allow for the users to explore the data set themselves within the dashboards. We use a pivot chart or a table to allow for a very nuanced understanding of the data. But if an overview is enough, then stick to only official organizations in scorecards and also at the start, if you use this process, you should come up with different questions that you want this dashboard to answer, kind of like the KPIs of this data set. And of course, in step number three, when you visualize, you want all of these sections to kind of answer the essential questions that the users for this specific dataset.

Customise Marcos, Colour & Pretty-fy

Finally, customise the Marcos implement some colour and provide a dashboard. And honestly, this is my favourite step because when you have done all the hard work and especially when you’re building a dashboard, most of the time, it doesn’t really look that pretty until you influence a consistent colour scheme. You put some cool filters, and then you start playing with the dashboard yourself. You kind of see that much, much cooler and just the simple as a spreadsheet. So that’s my process, and that’s what I usually do.

Useful Data Studio Templates & Shoutout to Creators

Now let’s get to the fun part. So in the next section, I just want to give shout outs to a few different people and the dashboards that they built and kind of show you some different use cases of tasks that you are already doing and reporting on, but you might not be using a dashboard. So essentially, hopefully, these will save you some time in your day-to-day job.

Website & Search Analytics

So, first of all, website and search analytics, you can absolutely visualise that I’ve included an example of one of the dashboards that I use. However, there are so many dashboards for Google Analytics and Search Analytics that you are able to copy and include your dataset in. so absolutely do that and save yourself.

URL Inspection API Data

And the other thing is the URL inspection API Data. So you can visualise this. I recently released a dashboard for this API, and you can get the template and the tutorial on my website.

Search Intent Keyword Research

You can also do a search intent keyword research and classification. This is another dashboard that I released a while ago. Very quick visualisation on a data set you have for different keywords that you’ve pulled perhaps from different sources, and you can split them based on the intent that they signal in the query.

Google Ads Data 

Then you can also do Google ads data. You can visualise it. You can see if you are reporting and learning on that type of thing. You can see the different metrics for visibility, acquisition, and conversion costs on one page, which is very, very convenient when you have to do very quick summaries of the performance.

Site Crawl 

Then, I just want to give a shout out here to Alice Roussel. She recently released the dashboards for a crawl. So if you are doing a site crawl with a screaming frog, you can just look in the data source there. And she has a very cool template with many different sections, as you can see here, that shows you the performance page by page for the important sections of the website. Screaming frog has also released a dashboard for automated crawls for the different sections that you can report on when you’re doing the site crawl. You can get a copy of this dashboard on their website.

Core Web Vitals

I’ve also previously talked about how you can visualise in data studio a little bit more technical crawls and audits, such as the core web vitals audit. Again my dashboard here uses a screaming frog, and you can get it on my website, but I also want to give a shout out here to Tony McCreath, that has created a dashboard that is so comprehensive and really, really cool. It has as far as I remember, the connection has been made via a big query for the dataset. And again, He utilises the page speed insights API. 

So I just want to give all of these examples because I want to show you that there are not one, not two, but maybe like 50 or a hundred different templates for most of the tasks you are already doing. And you can just pick and choose which one suits your needs the best and the stakeholders that you’re reporting.

Why is Data Studio Important?

That’s a really useful dashboard for SEOs and also for software developers. Because someone is tricking the performance here and operating here and changing something on the website technically software developers, and that’s an excellent, perfect and helpful dashboard.

So, just here, the page level drill-down of my dashboard is exactly what they use when I communicate with the developers in my freelance clients because it allows you to see certain issues present across entire sections. So if, for instance, the website uses a particular theme for certain sections of the website, and that theme is causing some trouble in terms of page experience, loading and core web vitals. If you’re able to see this page section split, then it’s very easy to communicate to developers. Hey, you have this theme causing issues, or maybe the phones you use on this section out a little bit, too much, or things like that. Essentially it could even be just a plugin that is implemented on this section, and having this data allows you to essentially prioritise your recommendations because when you look at the reports that are given by Google or even the lighthouse reports, most of the time, they do signal that the issue is there. Still, they don’t allow you to dig deep into the data, especially for multiple, thousands of pages and things like that.

Before we finish, I just want to say that we are really just scratching the surface at the end. I can talk about data studio for hours. I know that might bore some people, So I absolutely encourage you to go ahead, copy a template, get started with it. Just try, try to build some cool templates. There are so many opportunities and so many unexplored areas, and I guarantee you that your stakeholders are going to be very happy that you are enabling them with a tool that they can actually play with themselves instead of giving them a static report of performance.

You can check out slides here

Here is the our latest recap of our latest Weekly SEO about Search Intent!