I have put this video together just to show you how you can use Google Search Console together with another free piece of software called Analytics Edge, I will show you how you can use the information that these tools provide to improve your ranking on Google and get more traffic through into your website. Google Search Console is a free tool that Google provides that shows you lots and lots of information about what’s happening with your website.
One of the tools most useful reports is this one here called the Search Analytics report. You’ll find this once you’ve set up Google Search Console on your website. You will then see it listed where it says, “Search Analytics.” You have a few different options here. You can show different parts of the data: clicks, impressions, CTR, position. I’ll explain what those are in a moment. And you can also set some filters .
So, for example, if your business is primarily targeting customers based in the United Kingdom or people searching in the United Kingdom, you can filter it to just UK visitors. You can also set the date range, type of device, all that kind of thing. Just to show you an example of what this report generates, this information is for a company that sells parts and accessories for VW vehicles. We can see that, for example, the search term , “VW beetle parts,” it’s showing me on the right-hand side that this website is ranking in position 1.2 on Google for that term.
What does that mean, position 1.2? Well, it means it’s usually in the first position. So if you do a search on Google and you look at the first page of organic results, the very first organic position is where you’ll normally find this website listed for a search for “VW parts”. 1.2, that just means that, occasionally, it might just slip down into the second position on Google. So this has nothing to do with pages. This is positions. There are 10 organic positions to a page. So, most of the time, it’s appearing first on Google.
It also shows me that over the last month, this website has been seen, that’s Impressions, which means it’s been seen 1,134 times. And then here, it’s showing me the number of times that people actually clicked on the listing and went through to their website. So 1,134 people saw it, 117 people clicked on it. And this column here, CTR, which stands for Click Through Rate, is just those two numbers divided by each other. So 10.32% of people who saw their listing actually went through and clicked on their website.
Now, how is this information useful? Well, first of all, if you see that there are some really good keywords where you’re ranking fairly low down the page, I’d have to scroll down a bit here, then…and you can also see that there’s a decent number of people searching for those keywords, then maybe it will then tell you that you either need to modify the page and make it look a little bit more relevant, or perhaps create a brand-new page for that term.
If, for example, you can see here, “big boys toys VW”. I suspect they have already got a very relevant page for this term, because they’re ranking number one on Google for it. But if you saw that the position was maybe 17, so they’re somewhere near the bottom of page 2 on Google, then that would give them a good reason to then go and create a page, all about “big boys toys VW”.
Now, this is all helpful. You can even download this information into Excel. The are two problems with this report. The first is, if we have a look down here, you can see that this is limited to the first 1,000 keywords. It doesn’t show us past 1,000 keywords. And for many websites, your site will be ranking for a lot more than 1,000 different keywords. The other problem with this report is, it’s only showing us the keywords. It doesn’t actually tell us which page on our website is ranking for that term, “VW parts”. It’d be really good to know if we weren’t ranking in such a high position. If we were ranking in maybe seventh position, it’d be good to see what page is actually coming up, so we could know whether we need to improve that page, make it look more relevant, or create a new page for that term.
So that’s where this second tool comes in, that I’m going to show you. And this is a free piece of software called Analytics Edge. And this actually works as an add-on for Microsoft Excel. So you will need to have Microsoft Excel in order to use this. Now, Analytics Edge comes in a free version and a paid version. I’ve looked at both of them, and unless you want to run some really complex reports, I can’t see any good reason to use the paid version. The free version will be absolutely fine for what you need.
So all you need to do is just click on the install button and that will then get added onto Microsoft Excel so you can use this. I’ll put a link to this software in the page below so you’ve got easy access to that. Once you’ve done all of that, you will then open up Microsoft Excel. You’ll now find that in your toolbar at the top, you have a brand-new ribbon called Analytics Edge added to Microsoft Excel.
Now, I’ve already added this, which is why it’s now appearing in my list of connectors. But on your version, when you first start using it, it won’t appear in your list here. It’s going to appear in this list of available services that you can add on. And you’ll see there’s a lot of different services here that you can use this software to connect to. Once you’ve done that, and you have Google Search appearing in your ribbon here, you need to click on that. Then just give it permission to access your Google account.
So you just give the account a name. Probably just the name of your website. It really doesn’t matter what you put in here, so I’m just going to put “test,” and then click “add account.”. And then it will ask you to log in to your Google account. Whatever Google account login details you use for Google Search Console, you need to enter them in here. So I’m going to put my Google email address in here. There we go. And then press “next.” And then you put your password. And then it’ll just ask your permission and that is all done.
Once that’s done, you get this ugly form coming up here. This Analytics Edge software is a little bit rough around the edges, but it does work very well. All you need to do, when you see this nasty-looking pink form is, just click “skip,” and that will all be done. Once you’ve done that, just click on the account you’ve created there. Click on “Make Default.” So you’re just going to make that the default account. And then in the little list down at the bottom here, just choose if you’ve got…you might have more than one website listed in your Google Search Console. So just down the bottom here, select the website that you’re going to use this with, and it will drop down on the bottom there. And that’s it. You’ve set that up. That’s all done. Okay.
So now I’m going to show you how we get information from Google Search Console into Microsoft Excel using this tool. So what we’re gonna do now is, we’re gonna click back on Google search here, and this time we’re gonna click on search analytics. And this will bring up another box. Let me just move this over here. And just make sure that the account that you selected, your default account, should appear here. But just in case it isn’t, just select that from the box. And then, once again, under site, just make sure you select the website from your Google Search Console account.
Now, if you notice, we’ve got three different tabs here, where we have various different options. The first one’s called Fields, and then Filters, and Dates. So I’m just going to show you what we need to do here. So in this first box here, which is Fields, we need to select two things here. On page, select that, and then press the arrows to bring that into the dimensions box. And then query and bring that into the dimensions box. So that really should be all you need here. Everything else we can leave as it is on the page there. Then we click on the next tab here for Filters.
And for this one, because this business is targeting people who are searching in the United Kingdom, so I’m going to filter this to only people in the United Kingdom. So if we scroll over to…it would be probably easier just to type it in here. Okay. Well, let me do that. Oh, there we go. Yeah, United Kingdom. Okay? Select United Kingdom. And then everything else can just stay as it is there. And then in the last tab here, Dates, we wanna get as much data as we possibly can. So we’re going to select the last 90 days’ worth of information that we can get there. Once that’s done, we’re finished.
I’m just going to quickly recap that again. Make sure that your account is in the box here. The website you’re searching for is in the box here. Make sure that on the Fields tab here, you’ve got page and query in the dimensions box by dragging them over across the top here. In Filters, I’ve set that to United Kingdom, but if you wanna check another country, you can do. And then in Dates, just make sure that that’s set to 90 days. That’s about it. Okay. Press “finish.” And because there’s gonna be quite a lot of data here, that’s gonna take a little while for this data to import. So I’m just gonna pause… Oh, there we go. It’s done. I didn’t need to pause the video. Okay.
And you can see that now we’ve got here…let me just open this up a little bit. What we’ve got is all the information that we had in Google Search Console listed here. It shows us the page and all the pages on the website that’s ranking on Google. Then here is all the different queries that those pages are ranking for: clicks, impressions, click through rate, and position. Now, a couple of things I would do here is, just to tidy this up a little bit.
Now, click through rate, you remember this was a percentage, so I’m just going to make this a little bit more useful by formatting it as a percentage in Microsoft Excel. So I’m gonna right-click format cells, and I’m just gonna change that to a percentage with two decimal places. And you don’t have to do that, but it obviously just makes the information a lot easier to see. And you’ll also see here, on position, you can see it’s given me a whole load of decimal places here. I don’t quite need all those decimal places. So, again, I’m just gonna drag that down, and I’m gonna format this. Format cells. And then I’m gonna say number, and I’m just gonna say, “Show me one decimal place.” That’s all I need. There we go. That’s a lot more tidy. Okay.
Now, this is all great. The only issue I have here is that you can see that this is not in any particular order. So I’ve got some keywords here that are ranking for the same page. You can see this is the homepage of their website, and you can see it’s ranking for lots and lots of different terms, but they’re all just kind of in a big list here. So what I wanna do is, get this into a really nice report where I can see each page on my website, and then all the separate search queries that that page is ranking for. So to do this, we’re gonna create a pivot table in Microsoft Excel. Now that might sound a bit complicated and scary, but I’m gonna show you just how easy this is to do.
So I’m going to click on the ribbon here where it says data, and you should be able to see this option here, pivot table, appearing in the ribbon somewhere like where my mind is here on the page. I click on pivot table, and you will see that it’s just asking what data we want to include in the pivot table. And it’s already assumed that we’re including all the data. You can see I’ve now got this dotted line around the whole table here. And that’s absolutely fine. Just go with the default option, and then click “okay.” We don’t need to change anything on that page there. Okay.
It then opens up this new worksheet, and I just need to tell it which information I wanna include in the pivot table report. So I’m going to click all of these now. So, page, query, clicks, impressions, CTR, and position. And now you can see that we have got a very useful report here, because what we’re seeing now is a list by page, so starting with the homepage of the website. This is now the homepage and all the search terms that that homepage is ranking for. If we scroll down, you’ll see… So here we’ve got another page on the website, all about some community show. There’s all the different things that that’s ranking for.
And then across the top – let me just scroll back to the top here – you can see it’s showing us how many clicks we’ve received for that particular term, how many impressions, the click through rate, the percentage. I’ve lost my formatting there, so I need to sort that out again. Let me just go and see what… Click. And where are we? Format cells. The percentage. There we go. I’ve got that into percentage, and then again the position that we’re ranking for it.
Now, what can you do with that information? Well, you know, one of the things that I would definitely want to be having a look here as I scroll down this report is…let me just scroll down to something where it’s kind of product related. Here we go. So, we can see that we’ve got here… Let’s just have a look at this row here. So this is a search query for VW gearbox codes, and we can see that there’s a decent number of people who have been searching for that. It’s 248 impressions in the last month, and 36 people clicked on our result. But you can see that our ranking for this is somewhere between positions four and five on Google. So there may well be certain things that I can do to this page to make it even more relevant for VW gearbox codes.
I can see that this page…and in fact, one of the things I would definitely wanna go and do is go and have a look at this page on my website, and I can see that this page has probably been optimised not for VW gearbox codes, but for engine gearbox codes. So one of the things I could do is just go and make that look a little bit more relevant for VW. So that is hopefully helpful to you. Have a play around with this. I’m sure you’ll find all sorts of good uses for it. And yeah, if you’ve got any queries or comments, just drop them in the box below. All right. Take care. Bye now.