How to create Google Data Studio Dashboards for SEO

How to create Google Looker Studio Dashboards for SEO

“Data is the new oil” is a slogan that we hear almost every day in the modern digital world.

The data that we collect today could tell us when which user clicked on what, what flights they booked, what hotels they booked, where they are travelling to, and when they are going. Maybe we even have user profiles that contain demographic information: where users live, what age they are, and so on. We collect data about everybody and everything.

But communicating and presenting key metrics to clients or internal stakeholders who aren’t familiar with the data can be pretty complicated. Not everybody is a data scientist. Not everybody knows how to pull and combine data from different sources into one view or chart. And even if you are a data scientist shouldn’t you be spending more time analysing the data rather than building dashboards.

Enter Google Looker Studio (formerly Google Data Studio).

In short Google Data Studio is an excellent tool that can be used to shrink the analytics process and make quick, beautiful, interactive dashboards with minimal effort.

Google Looker Studio helps you to make sense of your data.

Google Looker Studio (Data Studio) naturally collects data from Google Ads, Google Analytics, and YouTube. However, you can also import data from various third-party services like Facebook, Twitter, MailChimp, Salesforce, and MySQL, just to name a few. By consolidating all this information in one dashboard, digital marketers on one side and business owners on the other side are able to gain valuable insights into their operations, finances, and marketing campaigns.

Similar to other applications within Google Drive, Looker Studio makes collaboration with coworkers and clients nearly seamless. To share the reports that you build, all you have to do is send your coworkers, clients or stakeholders a link and give them permission to comment and edit the information. The best thing is they don’t need to install local software on their computers.

Google Data Studio is fully cloud-based, meaning authorized users can access your reports from any approved internet browser.

“Data is the new oil’ and I completely agree with this statement. But similar to oil if data it’s unrefined it cannot really be used to power your digital marketing engine. It has to be changed into gas, plastic, chemicals, etc to create a valuable entity that drives profitable activity; so must data be broken down, analyzed for it to have value.

Nick Wilsdon at Benchmark Search Marketing Conference 2019
Nick Wilsdon at the Benchmark Search & Digital Conference 2019

Video Transcript: How to create Google Data Studio Dashboards for SEO

Hello everyone. You’re waiting for dinner. So yeah, I will try and make it exciting.

 So yeah. Firstly, who am I? Yeah, so I’m the SEO lead at Vodafone group so I manage 22 markets, 560 million mobile customers. I’ve been an SEO for 20 years. But I’ve developed complete love for Data Studio. And I know Omi has already talked to you about this so this is kind of slightly different spin on that which I think hopefully you’ll find interesting. 

And it’s really based on this because SEO is a lot about execution. And this is something that Kevin said on Twitter and I hundred percent agree with him. These are great ideas. But how do you actually execute these? How do you get these working in your businesses? So we’re going to try to do this talk is give you some of the business case about how we rolled out Data Studio across the organisation. And hopefully, you can take these tips and use them with your clients or within your companies. 

 So these were the challenges that we faced on data. Firstly, we needed a single report for multiple people. These are for senior stakeholders, these are for operational teams. We had lots of different people around the business that wanted data that wanted visibility on that data. So how could we come up with a strategy that met the needs for all of those people? The next challenge we had is 22 markets. So how do we kind of use this project to standardise a lot of the reporting that’s going on around the business? Because this was a real opportunity to kind of up our game when it came to data and reporting and and we could use this as a vehicle to kind of standardise everything. 

We needed to be data-driven. We’ve learned that SEO is got very complicated now. When I started twenty years ago, it was all about gut feeling and the occasional forum post. And now it really is data-driven. You need data to make these decisions. So how do we get hold of that data? How do you make that accessible to all the teams? And how do we automate everything, because, with the sheer amount of data, automation is an absolute must? If it’s manually done, if it’s manual reporting for 22 markets, you’ll find that you’re just reporting all the time. And this is very common. There’s a lack of internal resource for doing this kind of data projects. There aren’t enough data scientists out there and the ones that we have got an incredibly busy doing, you know, thousands of really top-level projects. So it’s very hard to get their attention their resource on something like SEO. So and yeah, the very common problem that everybody has money. Yeah. How do you do this without a lot of money? What do you know? How do you do this without massive expense on a data team and data partners? 

So what I did is I went looked around in the market and I very quickly decided that Data Studio was the way to go. There is this free tool from Google. How many people here are using Data Studio now? Quite a few of you. That’s brilliant. So, and you’ll know it when the reason I used it for the same reason you started using it. It already accessed common data sets, like Google data, Search Console, Google Sheets – we didn’t have to do a lot of programming to make that work. It’s got a lot of preset connectors. So you don’t need to be technical to make Data Studio work. And I can’t emphasise that enough, you really do not need to be technical to do Data Studio. It’s got shared components, we can share between teams. Once we’ve established data sets we can then use on other reports other teams were accessing, and it’s just easy to build. I’m not kidding, if you are left with Data Studio and you got two, three weeks, you’re gonna be making something it’s pretty good at the end of that two, three week period. So it’s amazingly accessible. And Google obviously has designed it that way. And what it does, it allows you to pipeline data around your organisation. So all these tools up here, a lot of these will be familiar to you. They’ve all got data in them. They’re all processing data in their own interesting ways. But how can we take that data or analytics data and put it into a sort of common visualisation layer, which is Data Studio, that’s the power of it. It is pipelining. 

So the first set we looked at, probably the first set everyone looks at Search Console. This is the best way to get into Data Studio. Everyone here has a Search Console account. It’s completely accessible, the UI for Search Console isn’t very good. So you can actually make something better in Data Studio then you actually have through the native Google Search Console interface. And that that’s a big part of your business case, so you’ve actually added value. You’re not just reproducing data And I’ll show you how we did that. And it justifies getting new domain properties. How many people here have done domain properties in Search Console? Very few. This is a new way of registering your account, authorising your account with Google.

And the benefits of this is you have to do on the DNS level. But the benefits are that it will then hold all of your data. So you don’t need to worry about your HTTP version, HTTPS version, your www version. And it will also hold all of your subdomain data and any future subdomain. That’s incredibly useful for some of the tools that we’ve created on this, like a Subdomain Finder. Because you know, it’s always someone in the PR department is launching a new subdomain. And we now have a very basic report that then pops it up on screen and says that a new subdomain has just appeared. So you can do some quite interesting things with that new form of authentication. So I’d definitely recommend you do that. And it only has to be done once. 

Sam Marsden and we talked a lot about Data Studio, there’s a lot of Data Studio geeking going out. And this is kind of basic stuff that you would have through Data Studio. You gonna have your site trends, it’s a good place to start, you know, this is basically replicating Google Search Console data, the basic level. You can see your top-performing pages in search, your top-performing queries, you know, it’s nothing too exciting, but yeah, it’s good but it’s probably not more than you’re used to. And you can see your your biggest wins, big losses. I’ve put links in here and I’ll share this deck. So this a good place to start, as you’re kind of tutorials to getting into this. Follow Sam’s instructions and just rebuild these screens. And the really cool thing about Data Studios, you can literally go and you’ll find people are sharing these reports. So you can literally go and share, report, copy it, and then take these assets into your Data Studio instance. And again, you don’t need to be technical. It is very, very non-technical. 

You can filter performance down to brand a non-brand, which obviously is very useful for the SEO team, because in a lot of things in SEO, your, your brand performance is actually very, very, yes, a huge amount of volume. And you need to show as an SEO department that you’re pushing your generic your non-brand, fully. 

 So this is my view of Search Console. And I’ve basically, you know, all the same things, got Google clicks. I’ve tried to use very easily understood titles. Because I don’t want to make this overly complicated for people using it. You know, how many clicks, average position. 

And this allows you to do more than you can do in the default Search Console screen because I can put in a single keyword, I can put in a date, I can put my mobile, and I can right click on any of these charts and download the data to Excel. You can’t download that data by date, by specific date within the Search Console interface at the moment. So you’ve added new functionality, which is great. And that means a team are more likely to use this than to use Search Console directly. 

And then once you’ve done the basics, then get into your third-party tools. And this is completely changed the way that I look at SEO tools. I only look now at the API’s. I see what data can they give me. I treat them as a data partner, as a data processor. And that’s very important because when you’re trying to sign off SEO tools, there’s only a few people in your SEO team who are using it and it’s very hard to explain the value of that around the business. 

If you start talking to them about an SEO tool as a data partner, someone who’s processing data that makes the sign off much easier. An example of that is with SEOmonitor who’ve done a lot of work on this. Since 2013 Google hit the referrer. So we no longer knew the keyword that drove the search. 

So the basic function that a lot of SEO tools do, but SEOmonitor does it very well, is to just deanonymise that data. So it tracks it back to the keyword level and associates a keyword with a landing page across your site. So that’s the processing they’re doing. So allowing me to understand what keywords are driving the traffic to which landing pages. And that gets interesting where you start to then understand orders. And you can then tie orders, to sets of keywords. So you’re no longer thinking about a set of keywords that are just interesting to the business or, you know, your CEO thinks are important you then have a set of keywords that drive commercial value for your business. You know, we created a natural commercial case for SEO. The very basic, but we’ve tied money to keywords to activity. 

And again in SEOmonitor, all this can be pulled through for free. So they don’t charge for the API into your Data Studio instance. They will probably start charging for the API after I talked about this today.

So, but that’s, that’s not enough. We’ve done some basics once and what we can do with Data Studio, but if you’re gonna get it working, especially in enterprise, you need to think larger than that. And the way you do that is through a framework. And this kind of dealt with my question of ‘how do I deal with a report that serves different needs and different stakeholders through the organisation?’ So I created a framework, very basic one, which revolved around performance, planning, control and action. These four icons, everyone likes icons, and you make very pretty reports with icons. So this clearly demark different parts of the report and showed who that part of the report was for.

And you’ll see why in a second. So there’s an ambition for SEO. And I think a lot of us are still reporting on keywords and visibility and rankings. But we really don’t want to be doing that. We want to be moving that up. We want to make that more robust. We need to be reporting on traffic, orders, you know, CPA, revenue. This is where the SEO team needs to be. It’s a much more robust place for the SEO team to be. And if you want to get resources for SEO, you need to be making this connection. So we had to deal with each reporting KPIs, we had to raise the game to this level. But we had to align with how reports were currently working. Because, because teams were still looking at rankings, and that’s where it came into us incorporating those kind of idea of rankings against orders. So we still had some idea of rankings, but we had the commercial element of rankings. 

So this is kind of how we fit it. I will give some examples of the KPIs that I put into each of these sections. The first one is performance. Terribly small, sorry about that. These are performance metrics, KPIs, and these around as you’d imagine track orders. You know, these are commercially viable metrics. And I divided this into two sections: lead and lag metrics. And the idea of that is, you’ll often find senior leadership want to see, lag metrics. That’s what they are used to. And if to explain what lead and lag metric was, if you had a building site, and you had a lot of accidents, accidents is your lag metric. And it doesn’t really tell you a lot. It doesn’t help you solve the problem of accidents on your building site. What helps you with your accidents at you building site is a lead metric, like how many people are wearing hard hats every day. These are the metrics that actually change your business. But senior leadership often want to see lag metrics. So lag metrics are largely confined to the performance section. But this this, then with this iconography told them these are the these are the charts they could look at. These are the ones that weren’t open to interpretation. They were just cold, hard commercial metrics. And we put them all into one section. And this is an example. So got a performance up on the top right, you’ll see that we got kind of buttons navigation. You can make Data Studio into an application. It’s not a single report, you have pages in it, you can make links in it. So you don’t think about it as one single page. Think about it as an entire application that you build. And this this has become the entire framework. Where each one of these is is, you know, 20 plus screens large now, and growing all the time. 

We then looked at planning metrics. This is forecasting. SEOmonitor will give you SEO forecasting and sets of keywords. We bring in the development here. You can even, you can even look at holding an Excel sheet with your team holidays or your things that are happening across the SEO project for the next month or, or large, impactful changes that happen on technical SEO over that period. And then you can have that Excel sheet visualising the table in the screen. So anything that’s going to help your team plan better. ‘Search trends’ is something else we put in there. 5g adoption in different markets. That again was something that seems really wanted to have on our fingertips. But this this section is for the teams. So everything outside the performance section is not allowed to be viewed by senior leadership. And what that’s done is it’s meant that we no longer deal with a lot of requests around ‘why has this keyword changed position?’ What does this mean? I’ve got a visibility graph from Sistrix. Why have we gone up, why we come down?

A lot of these metrics are very open to interpretation. So by clearly sectioning the report, we then stopped a lot of these queries coming our way. 

We then had a control section. Increasingly, SEO can be controlled through automated checks. You got Little Warden out there, which is a great tool, which will check to see whether your redirects are working with the HTTP, your sitemap is valid. You know, there’s a lot of automated checks. I’m pretty sure that Search Console is going to start integrating some of these checks and allow you to pull them out into Data Studio in the future version. So anything here that you can put will give you alerts. This is where you put them in the section. 

And finally, the ‘action section’, which is an area I got really excited about, which was to build our community. It was a section where different teams could come up with new reports, put them in this area, and then share them. So this was our playground for SEO reports. Because there’s always new data sources. Once you get into this, you’ll find there is a 1000 different data sources, thousand different visualisations you can do.

This is an example, something that Sam came up with in terms of checking Fraggle performance. There’s a good one that Hannah Butler has done in terms of cannibalisation. You can have a cannibalisation tool. So you kind of getting the picture now that this, this report is an application. It’s actually your it’s actually an SEO tool now. It’s a base level SEO tool for each of the teams. It’s not just a report, it’s something that can actually have actions off the back of.  

And we’re doing that across international SEO. Here’s an example for Page Speed with the Chrome experience report, which I know they’re actually fixing for us. But this one allows you to see page performance against competitor. And again, this is a free API. I’ve given the the link there to set it up. Something you will set up in a few minutes. And you can have an idea of how fast your competitors are getting compared to you. Which is a much better argument for page performance, than naturally pointing at your own page performance. Pointing at how fast your competitors are. 

So yeah, this is kind of become an actionable toolset now. And it’s even more actionable by the fact that you can actually right click any of the charts and download the data to Excel. So a lot of the teams want to work in Excel. So they can take this data in, especially from third-party tools, where you have seat restrictions. So maybe you only allowed four people to log into your third party SEO tool. You bring that data into this, all the teams can right click and download and manipulate it. So you’re massively using that API in a much more efficient way. 

So what’s the plan to take us the next level? Well, we talked about pipelining. So obviously, your pipeline in these these sources in but you’ll realise – when really when you’re working through this – that performance is an issue, and that you need to do more complicated calculations. So the next stage in this roadmap is to add a database layer. And most people kind of choose to go for Big Query. This, this allows you and it’s a lot of advantages to this, I’ll tell you. The the main advantages are you can do more with the data. Google Search Console only let you have the data for 16 months rolling. So by putting into a database, you then have all your data, you know, infinitely forever. So it’s obviously a big advantage there. It allows you to improve performance, performance of the report itself, because you’ll find a lot of these free API’s, everyone is using them, everyone is hammering them. And their speeds are obviously suffering from that. So moving into BigQuery then allows you to have much, much faster reports. 

And lastly, you can do kind of more complicated attribution models, because you’re they’re merging your data. And what you’re effectively doing with the strategies you’re building an SEO ‘data lake’. That’s what we’re trying to do. We’re trying to move all of our SEO data into one place. So we can start to do some much more insightful work on that data. We were never going to put all our data into every single SEO tool that lives out there. We never going to login to every tool that lives out there. So you bring it into a database, you then have access to all this data to answer some of those questions across the business that you really want to. Like, you know, what are the main landing pages that make a difference to us commercially? What are the journeys that people take on SEO then that matter? The SEO team ‘which URL shouldn’t be changed?’ These kind of questions. 

So yeah, so how do we deliver on our four challenges? Firstly, it was a framework, it wasn’t just a report. This was more ambitious than that. It gave us the the flexibility to have different ports for different people in the organisation for them to use this in their own way. And it’s also given us a roadmap to develop. Because people are developing new reports all the time to put into the action section. So I’m very, very happy that. 

It’s allowed us to make our commercial metrics much more robust because everybody wants to have this performance screen with we’ve actually got a revenue model that basis on the back of that performance model. And I actually I mean, you can fake it till you make it as well with Data Studio. If you want to put together a very simplistic data model in Google Sheets, you can do that. And you can get different teams to fill out different bits in Google Sheets to provide your revenue model. And you can put that into Data Studio. So you can almost give a feeling of automation, while you actually get these data sources pipelined and dealt with properly through organisation to get them to fill out different parts manually. But yeah, it’s really kind of raised the standard. It saved us time because we’re not doing manual reporting anymore.

We’re trying to automate or reporting. And it’s provided the business case for pipelining other data. And I can’t emphasise how important that is. If I want to do a local SEO strategy, and I go to try and get footfall data if, I’m lucky, I’ll get a snapshot of a certain week across the stores, how much footfall there was. What I need is I need that data to be pipelined you know, I need that lot to be a live feed. So I need that with part of the organisation to realise that they need to make that data live in order for me to actually carry out some more insightful research with it. But having the, having this framework in the first place provided that business case.

And it’s allowed us to self serve. So we’re not overly reliant on the data teams. We got, we got different members who are trying to learn this. I think I think to be honest, every SEO has to learn Data Studio. I’ll go on a limb and say that.

So coming up the framework like this is really encouraged learning between in our community within Vodafone. So we have different teams talking about reports, sharing reports, you know, so it’s very good not and I’ve seen that in the wider sense as well through the community through the SEO community. There’s a lot of very good SEO reports and Data Studio reports that are available. You can literally copy and just learn from them. And this API approach – API led approach – has made it very cost-efficient on tools. Because we’re looking we’re looking at the API usage of these tools rather than individual seats. How many people here have Sistrix? Oh, sorry, Sistrix. Two.

Sistrix is a very good tool for working out visibility but they have restrictions within the programme that only lets you have a certain number of people logging in. Only lets you visualise say four competitors against each other. So, so using an API, first means that we can bring in all these competitors, all these different views into a Google Sheet and use that tool data much more efficiently. And you’ll find that with all of your SEO tools. So start and start really hammering the API and see what you can get through the API, rather than the tool interface itself.

And I’ll give you an extra tip here. If you are managing multiple markets, or or even kind of your multiple parts of business, you can actually stick Google Analytics into your Data Studio reports. And providing you’ve got a good way of naming your individual screens, you’ll see the usage of each of those reports. So you can prove the usage across the organisation. Cause you see how many people logged in, how frequently they logged in, and how they’re actually interacting and engaging with the reports that you’re making?

So where do we go next? Next question. 

Well, like I say we become a use case for data pipelining. SEO has become a use case for data pipelining. We’re now getting access to data that we never got before. As I said, footfall data incredibly useful for local SEO. Cost per store visit this is a metric that Google’s been pushing, where they can tie the cost per store visit to a certain keyword through paid paid search. So we’re getting that data pipelined into the system. We’re getting internal search, we’re getting log files that we’re now uploading. We put copies of Screaming Frog into Google Cloud. And you’re then running crawls automatically and then importing that CSV information into your database to have information on your URL level. So you can start to bring in all kinds of data into this framework. 

And we’re using this framework in our tactical development in our tactics within SEO. So when I get a challenge around Page Speed, or Edge SEO, or something like this, I’m using this framework as the basis for these strategies. We’re working out which reports we can create in the action section that will support the SEO PAC or activity we’re going to do. And then we visualise again, so we’re providing the data measuring right from the start. So it’s really supporting this whole idea of kind of, you know, measure, test, learn, which is future-proofing and really advancing our SEO in the organisation. 

So yeah, I hope that’s interesting. If you got any questions and please grab me, I’ll be more willing to talk to you about them. But thank you.

Events in Google Analytics 4

Comments

5 responses to “How to create Google Looker Studio Dashboards for SEO”

  1. […] I’m just creating a simple Data Studio, just one table. And I have all the queries, landing pages corresponding with queries, impressions, […]

  2. […] A good example of using the CONCAT function would be when you have multiple domains or sub-domains within your website and you want to see the full URL of the page in your Google Data Studio Dashboards.  […]

  3. […] taking a data-driven approach to enterprise SEO, you’ll be able to win the long game and build a sustainable, […]

  4. […] benefit of data-driven marketing is that it allows for more targeted and personalized messaging. By understanding who your […]

  5. […] about your website traffic and marketing channels, provide deep-dive analytics with custom dashboards, and get reports that are now easier to […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.