Use Google Sheets to quickly find pages we can optimize. Using data directly from Google Search Console. Also check out the simple formula combination: AND() with IF()
Academy ↓
Hey there stranger!
Optimize SEO with Google Sheets and Google Search Console
About this Tutorial
Video Transcript
0:00 So maybe you're sitting at your Google search console and you're really wondering like how do I optimize? How do I SEO?
0:06 Search Engine Optimization. What can I do? And I'm going to show you how to use Google Sheets in order to optimize your SEO or search engine.
0:13 So we're going to export, we're going to go to Google Sheets and I'm going to open a brand new Google Sheet from this export.
0:18 It's going to do it automatically here in a second. Now ah what I have here are queries, on the second page I have pages and what I'm looking for are unoptimized things.
0:30 So all of these ones that have a, high clicks, high impressions, and the position is like okay, right? Um, each of these can be fixed.
0:40 Uh, anything between 1 and 10 is probably okay. 10 to say 20 is probably- highly optimizable, and then anything from 20 to 50 is also probably highly optimizable.
0:55 But I want to make sure that I have enough juice, meaning, uh, is there enough juice- if I squeeze, will we get uhm.
1:04 something good. Uh, so what I'm going to do is try to filter this page. Oops. Uh, try to filter this page for the things that I want.
1:14 And what I can do is, I can go here and I can say, okay, definitely, uh, yes, if the page position is something like 10 to, uh, actually let's do 5.
1:25 5 to 20. And we're going to go So, if, logical expression, if this is greater than, 5, I'm going to wrap this with and, you might want to be able to see this better, and E2 is less than 21.
1:50 Okay, and if it's true, I'm going to say yes. There. And so, we get some falses, let's actually add, if it's false, just a little blank.
2:01 So these are all yes. But that's not the only thing. I want to make sure that I have actually one click.
2:07 So. At least one click. Clicks, yay. Uh, equals, if, logical expression is this, is over zero. And if it's true, I'm gonna be like, yes.
2:20 If not, I want nothing. Alright, we got a bunch of clicks. And of course, everything, everything down here is, we got zero clicks here.
2:28 That's okay. We don't need any of these. We can actually look through these at some other time and be like, okay, these pages are cool.
2:35 Maybe a position 42 that has 622 impressions. Yeah, I can definitely optimize that, but I want to see things that have at least one click.
2:45 So, cool. We got those. Um, impressions. Well, let's look at this. Uh, what we can sort of figure out, this is a, this is very crude work here.
2:56 But, we can say potential impressions. And what we can do is take this impressions that we're already getting, and say we want to increase it.
3:10 Uh, we want to see, what would we get if we were able to position one. So, a crude way to do that is to take the existing impressions actually, let's say equal this, take the existing impressions we're getting, divide it by, one divided by the position.
3:29 And that's a very crude way of doing it, but we get a really interesting ah potential here, right? And they're ranked differently than if we were just looking at these impressions straight up.
3:41 And so what we want to see here is, anything else? The thing where yes is both here, let's look at that and let's say we're in a filter.
3:53 So we want a through h of pages equals filter a through h pages a colon h condition is this equals yes and clicks equals yes and we get all those cool and now we can sort by gonna say sort by one two three four five six seven eight column eight do we want it ascending we want it t descending so we want
4:33 this to be false and there we go so these are pages that are within five to twenty uh we're definitely optimizable and this gives me a really good shot of like hey I can go to this can work on it I can see now I can take this and go back to my SEO uh search and search console so I go to the page and
4:59 we're just going to look at one page and I can see what queries are we already ranking. four right that's cool right there we're already ranking for those we can see position as well let's see if we have any recommend I'm in bold positions all in number one.
5:17 That's awesome. Here's number four for Goshi Truefall formula anything outside of 45 count if here true false google sheets we might be able to add this true false google sheets to the just to the keywords or maybe add it to the text in a little bit to sort of bump it up.
5:35 up. in the top five because this looks like a really great we're already getting a lot of uh impressions for it at the seventh position so if we moved up we'd get way more.
5:47 uh look for low positions maybe even yeah 1931 count if formula. So maybe add a little what we can do is add a little section to that blog post about the count if formula and how it how it works with this page and so this is something that I would do in SEO but using Google She's It's in a few little
6:09 formulas just to look at the pages that definitely could be optimized the pages that might be optimized and we're using Google Search Console which is totally free uh an available Google Sheets totally free and available especially if you have Google Workspace and just a few filters just using Google
6:24 Workspace. Using Sort and Filter here and a couple of ifs and a little bit of math we find some really great potentials here and can work through instead of having to like just search through this list and be like ok let's go down one by one.
6:39 We have a nice little. of the, filter here wow over a hundred pages we can definitely do something on and optimize for.
6:47 Really cool.
0:06 Search Engine Optimization. What can I do? And I'm going to show you how to use Google Sheets in order to optimize your SEO or search engine.
0:13 So we're going to export, we're going to go to Google Sheets and I'm going to open a brand new Google Sheet from this export.
0:18 It's going to do it automatically here in a second. Now ah what I have here are queries, on the second page I have pages and what I'm looking for are unoptimized things.
0:30 So all of these ones that have a, high clicks, high impressions, and the position is like okay, right? Um, each of these can be fixed.
0:40 Uh, anything between 1 and 10 is probably okay. 10 to say 20 is probably- highly optimizable, and then anything from 20 to 50 is also probably highly optimizable.
0:55 But I want to make sure that I have enough juice, meaning, uh, is there enough juice- if I squeeze, will we get uhm.
1:04 something good. Uh, so what I'm going to do is try to filter this page. Oops. Uh, try to filter this page for the things that I want.
1:14 And what I can do is, I can go here and I can say, okay, definitely, uh, yes, if the page position is something like 10 to, uh, actually let's do 5.
1:25 5 to 20. And we're going to go So, if, logical expression, if this is greater than, 5, I'm going to wrap this with and, you might want to be able to see this better, and E2 is less than 21.
1:50 Okay, and if it's true, I'm going to say yes. There. And so, we get some falses, let's actually add, if it's false, just a little blank.
2:01 So these are all yes. But that's not the only thing. I want to make sure that I have actually one click.
2:07 So. At least one click. Clicks, yay. Uh, equals, if, logical expression is this, is over zero. And if it's true, I'm gonna be like, yes.
2:20 If not, I want nothing. Alright, we got a bunch of clicks. And of course, everything, everything down here is, we got zero clicks here.
2:28 That's okay. We don't need any of these. We can actually look through these at some other time and be like, okay, these pages are cool.
2:35 Maybe a position 42 that has 622 impressions. Yeah, I can definitely optimize that, but I want to see things that have at least one click.
2:45 So, cool. We got those. Um, impressions. Well, let's look at this. Uh, what we can sort of figure out, this is a, this is very crude work here.
2:56 But, we can say potential impressions. And what we can do is take this impressions that we're already getting, and say we want to increase it.
3:10 Uh, we want to see, what would we get if we were able to position one. So, a crude way to do that is to take the existing impressions actually, let's say equal this, take the existing impressions we're getting, divide it by, one divided by the position.
3:29 And that's a very crude way of doing it, but we get a really interesting ah potential here, right? And they're ranked differently than if we were just looking at these impressions straight up.
3:41 And so what we want to see here is, anything else? The thing where yes is both here, let's look at that and let's say we're in a filter.
3:53 So we want a through h of pages equals filter a through h pages a colon h condition is this equals yes and clicks equals yes and we get all those cool and now we can sort by gonna say sort by one two three four five six seven eight column eight do we want it ascending we want it t descending so we want
4:33 this to be false and there we go so these are pages that are within five to twenty uh we're definitely optimizable and this gives me a really good shot of like hey I can go to this can work on it I can see now I can take this and go back to my SEO uh search and search console so I go to the page and
4:59 we're just going to look at one page and I can see what queries are we already ranking. four right that's cool right there we're already ranking for those we can see position as well let's see if we have any recommend I'm in bold positions all in number one.
5:17 That's awesome. Here's number four for Goshi Truefall formula anything outside of 45 count if here true false google sheets we might be able to add this true false google sheets to the just to the keywords or maybe add it to the text in a little bit to sort of bump it up.
5:35 up. in the top five because this looks like a really great we're already getting a lot of uh impressions for it at the seventh position so if we moved up we'd get way more.
5:47 uh look for low positions maybe even yeah 1931 count if formula. So maybe add a little what we can do is add a little section to that blog post about the count if formula and how it how it works with this page and so this is something that I would do in SEO but using Google She's It's in a few little
6:09 formulas just to look at the pages that definitely could be optimized the pages that might be optimized and we're using Google Search Console which is totally free uh an available Google Sheets totally free and available especially if you have Google Workspace and just a few filters just using Google
6:24 Workspace. Using Sort and Filter here and a couple of ifs and a little bit of math we find some really great potentials here and can work through instead of having to like just search through this list and be like ok let's go down one by one.
6:39 We have a nice little. of the, filter here wow over a hundred pages we can definitely do something on and optimize for.
6:47 Really cool.