In this episode, we’re going to be six highly effective features inside Google Sheets that each SEO must know. Google Sheets is a strong instrument that lets us have a look at our information, manipulate our information, and dive deeper in to get a greater understanding of the issues that we have to do within the areas that we have to enhance in. I’m going to point out you some cool features that I exploit virtually each single day as a way to assist us develop right here as an company and assist floor new alternatives for our purchasers as nicely. If you’ve bought a favourite operate and we don’t cowl it on this video, be happy to share. We’d like to proceed that dialog right here locally and assist everyone be the perfect they completely could be.
Video Transcript:
In this video, we’re going to be speaking about six Google Sheets features that each SEO ought to know. I’m going to stroll you thru every of the features. Then I’m going to point out you examples of how we are able to use these inside Google Sheets. This can actually pace up your time and likewise enable you to mix plenty of completely different information factors, which may be very helpful while you’re making an attempt to essentially perceive what’s occurring with a particular challenge or a particular dataset.
The first one might be the one I exploit probably the most and it’s VLOOKUP. VLOOKUP goes for use for pulling information from one spreadsheet into one other, although, there’s a caveat to this. You should have a minimum of a shared worth inside every of the Sheets. You must have both a key phrase on every of the Sheets or a URL on every of the Sheets, as a result of it must have that vertical lookup as a way to discover the data. The syntax is comparatively simple.
It begins with equal signal VLOOKUP. Next, you’ll have the search key. This could be the vertical listing of cells that you simply need to use as the important thing. Next, you’re going to have the vary or the vary of cells you’re going to be on the opposite Sheet, the index, so the precise bit of knowledge you want, after which the way you’re going to type that info. Let’s check out how we are able to leverage VLOOKUP. In this dataset, I’ve a listing of high queries that we pulled from Search Console. Search Console is giving me the clicks, the impressions, the click-through charge, and the typical place that we rank for every of those search queries. Now, that is actually useful info, however what if I need to know the search quantity for these queries as nicely? I can go into my favourite SEO instrument and I may pool all of the search information like we’ve achieved right here after which evaluate it Sheet in opposition to Sheet.
VLOOKUP
Used for: Pulling information from one spreadsheet into one other spreadsheet – so long as the 2 sheets share a standard worth.
Function: =VLOOKUP(search_key, vary, index, is_sorted)
Now, that is useful, however actually, copying and pasting the amount over may very well be a ache within the rear finish. This is why VLOOKUP is so highly effective. What we have to do is begin on this web page, the search queries web page, the web page we need to add the search quantity to. We go forward and click on right here and insert a column and we’ll name this column quantity. In order to search out the search quantity for this time period, which is situated on this second Sheet right here, we need to use VLOOKUP. So we’ll go equal signal VLOOKUP. As you’ll be able to see, Sheets will truly pull it out for you. Then I’m going to pick out my key, which might be the A column comma and now I need to go over to the Sheet right here and I need to choose my vary. Now, since I’m solely seeking to discover quantity, I solely must go to the amount column right here.
I don’t must go all the best way to the top. Now, if I needed to tug increasingly more info, if I needed to tug a aggressive rating or perhaps the fee per click on, I may go all the best way down as nicely. I simply need to get the amount. I’m going to go to there and I’m going to push the comma key once more. Now I need to discover the index and I need to discover all of the values within the second column. So one right here, two. I’m going to say the second column two, and I need the precise worth and what I would like so as to add there may be false. This will give me the precise worth, which is on this column. I hit enter and now it’s giving me an N/A for this one, as a result of there wasn’t a search quantity for this question, but when I need to get all the highest 100, I simply double click on this proper right here.
As you’ll be able to see, it simply fills all that out for me. Now, I truly know the completely different volumes or how a lot visitors every one among these queries will get with out having to repeat and paste backwards and forwards with my information.
LEN
Used for: Counting variety of characters in a cell
Function: =LEN(insertcell)
The subsequent operate we’re going to have a look at is LEN. LEN is used for counting the variety of characters inside a cell. This may very well be useful to know the way lengthy a title is in case you’ve bought a bunch of titles inside a Sheet. It additionally can assist you rely how lengthy your key phrases are, issues like that. This may be very useful, and it’s a comparatively simple operate to make use of. It’s equal signal LEN, and you then use the cell. Let’s check out leverage this. Using the identical information, if we needed to see how lengthy these queries have been, we may add a further column to the precise, and we may name it size.
Here we’re simply going to make use of the LEN operate, so L-E-N, LEN, size of a string. We’ll choose the string right here in that parenthesis. It tells us precisely how lengthy that particular string is. A string is only a set of characters like textual content characters. This could be useful, like I mentioned, after we’re title tags, issues of that, the place we need to optimize round particular size. It’s a straightforward operate. It’s simple so as to add additional information in and assist us to get, actually, much more info out of our analysis. So far we all know how lengthy every of those key phrases are. We’ve bought some quantity information and we’ve mixed this with the info that we’ve already pulled from Search Console. It’s mixed two items of information. Now we now have much more info to work with. But let’s speak about one other operate that we are able to leverage.
SPLIT
Used for: Splitting information round a specified character or string, and places every fragment right into a separate cell within the row.
Function: =SPLIT(insertcell, delimiter)
Sometimes we import our information and we now have plenty of information factors inside a single cell. In order to get it out, we have to use the SPLIT operate and the SPLIT operate will take that information and use a particular character as a way to pull it into fragments and separate it into cells in a row. In order to do that, we use the SPLIT operate and we are able to use that by choosing a particular cell and utilizing the delimiter and breaking that cell up. Let’s see how this works. Here, we’ve bought plenty of items of information that we’ve exported from our instrument. As you’ll be able to see right here, we’ve bought tendencies, however all the development line information is definitely in a single cell. In its present type, it’s not extraordinarily helpful. It’s actually onerous to distinguish and actually get some worth out of.
We want to separate this info up. The manner we might do that’s equal signal SPLIT and we click on on that. Then we choose our cell. Then what’s the delimiter? We are it right here and we are able to see that it’s break up up by a comma. We would use this syntax. If it was a semi-colon, we might use that, however we used the quotes after which what’s splitting the info up, after which we might finish the quote there. That manner it is aware of break up the info for us. Hit enter. As you’ll be able to see, it’s break up all this info out. A fast hack, in case you simply double click on the nook, it’ll truly convey all that info down beneath you. Now we’ve bought all the development information break up out into particular person rows and cells.
SPARKLINE
Used for: Creating a fast sparkline graph proper inside your sheet.
Function: =SPARKLINE(insertcell vary)
The SPARKLINE operate works very well after we simply used the SPLIT operate. Loads of occasions, I’ll get information like that. That is pulled from SEMrush. Now I need to truly see that development line information. If I’m within SEMrush, I can see it, but when I truly export the info and I put it right into a Sheet, I simply see a bunch of numbers. Sheets has this actually cool operate, which permits us to create a fast SPARKLINE graph proper inside a cell. This can truly assist us see the info and visualize the info, which might enable us to get much more which means from it. Let’s have a look at the SPARKLINE operate. Now we now have all this development information and we’ve pooled it out right here, however once more, it’s nonetheless not tremendous helpful if we’re simply it. It could be very overwhelming.
We actually simply need to see this development information within the type of a SPARKLINE. We use the equal signal and begin typing in SPARKLINE and Sheets will convey it up for you. Then you simply pull the vary of information that you simply need to be used inside the SPARKLINE and shut it. Now you’ll be able to see, I’ve bought this actually cool little graph right here that reveals me the development line. This is admittedly useful to know which key phrases are trending up in addition to which of them are trending down. We don’t should mess with all of this additional information. We can see it proper right here inside our Sheet. And now we all know in a short time, which phrases perhaps we should always goal now versus not fear about as a lot or a minimum of perceive the tendencies with the info itself.
What’s very nice about utilizing Sheets is you are able to do lots of evaluation from inside one doc. You don’t have to leap throughout and go to completely different instruments if you understand how to essentially leverage the Sheets. Once you may have your information, you’ll be able to manipulate it fairly a bit. We’ve seen this with VLOOKUPS and splitting our information and creating SPARKLINES and how lengthy characters are.
IMPORTXML
Used for: Pulling information from any of assorted structured information varieties together with XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
Function: =IMPORTXML(url, xpath_query)
ImportXML is one other useful gizmo that we are able to use. This can truly be used to scrape information or pull in information from plenty of structured data sort, together with XML, HTML, and fairly a couple of others. The operate, once more, it’s not very troublesome, however you do want to know the completely different XPath queries you need to use. Now, I don’t keep in mind all of those, and because of this the web is such an incredible factor.
We can look these up in order that we are able to actually leverage this superior operate. You would sort in ImportXML, you’d have a URL or a cell, and you then would have a look at the XPath question. Let’s have a look at a few ways in which we may leverage. I pulled some URLs from a search consequence, and now I need to study a bit of bit extra about them. I need these all in a Sheet, and I need to make it possible for I’ve their title and their meta description. Let’s say I’m doing a little (*6*), and I need to perceive the SERPs a bit of bit higher. In order to get the title, I’m going to make use of ImportXML. We’re going to go import, now be sure to select the precise one. This is ImportXML, and now I’m going to wish a URL.
This is essential. I click on on this cell as a result of the cell’s a URL. I’m going to make use of quotations and contained in the quotations, I’m going to go ahead slash, ahead slash, title. Now I make sure that I shut this and hit enter. What it’s achieved is it’s seemed up that URL, and it’s given me again the title tag. Again, I can get all of the title tags for these pages by double-clicking that, or simply pulling that operate down. Pretty cool, proper? You can do this in a short time. You don’t have to return and replica and paste and do all that stuff that’s additional onerous.
You can eliminate duplicates. Now you’ll be able to perceive for this question, which was like greatest display recorder or one thing for Mac, then we are able to have a look at what pages are rating after which what sort of content material, too. Right right here, we are able to see that for all of those completely different queries, it appears to be listicle articles which are rating very well. If we’re making an attempt to rank for this, or we’re making an attempt to match person intent, we would must create a listicle piece of content material as nicely. Let’s say we need to get a bit of extra info right here and we needed to search out the meta description.
We’re going to make use of the very same operate that we did earlier than, ImportXML. And we’ll go forward and use the URL once more. Now I don’t have the meta description one memorized. And like I mentioned, that is the place Google is admittedly nice, however this man has some fairly cool stuff on his web site, however he’s given us the operate proper right here. I can copy that. Again, I need to put this in double-quotes, paste it in, and there we go. Now we’ve bought meta descriptions.
We can pull that down. By utilizing ImportXML, I took this listing of URLs I already had and I discovered all of the titles and all of the meta descriptions. Again, now we are able to use a bit of little bit of the opposite features that we talked about earlier than. Let’s say I need to know the way lengthy these title tags have been. I may do title size and I can see how lengthy the characters are, utilizing LEN, proper? So you’ll be able to truly do a ton of research whilst you by no means depart Sheets, proper? Never as soon as have I left Sheets and I pulled all this info. I can do the identical factor for description size. So whereas having a crawler clearly is admittedly good, Screaming Frog, or Sitebulb or something like that, these Sheets that Google has for us are literally actually useful as nicely to do issues in a short time if we have to do some fast evaluation or we need to mix our information.
IMPORTDATA
Used for: Pulling in information from APIs
Function: =IMPORTDATA(url)
The final operate we’re going to speak about is known as IMPORTDATA and I normally use this to tug information from APIs instantly into Sheets. The operate is comparatively easy. It’s simply IMPORTDATA. Then you may have your URL, the API URL, the requests that you simply’re making. Then primarily based on that request, you’ll get sure information factors again. Let’s have a look at how we are able to leverage this. For this instance, I’m going to be utilizing SEMrush’s API. All APIs are a bit of bit completely different, so it might not work completely for you in the very same manner, however I’d have a look at another concepts and another issues that individuals are doing with IMPORTDATA in case you can’t do it the precise manner that I’m displaying you right here. But this can be a actually cool solution to import a lot of info into your Sheet, once more, with out having to go to different instruments.
Once once more, we’re right here at this final Sheet the place we’ve bought URLs and we pull our titles and our metas, however let’s say I need to get some extra SEO metrics about these particular URLs. This is the place I may use IMPORTDATA. Now there’s a bit of operate that I’ll usually use forward of this, simply because lots of occasions APIs will stack information down and that’s going to interrupt as soon as we do this over and over, as a result of it’s going to be changing the info. I like to start out all of those with TRANSPOSE and this can be a actually cool one, too. This is definitely seven, I’m providing you with an additional one. TRANSPOSE will truly inform it, as a substitute of returning the info downward, I need you to transpose it. It truly goes left to proper. You can stack these features. Now that I’ve TRANSPOSE, I may also now use IMPORTDATA.
This is the operate that we’re speaking about. You’re going to wish a URL to do this, and we’ll go get that subsequent. SEMrush has an API. It may be very filtered, I’d say, however it may be actually useful in case you’re making an attempt to get some fast metrics and perceive how issues work. In this case, we’re truly going to be URL reporting. We can have a look at particular URLs and I need to see some natural key phrase metrics round these particular pages. You’re going to wish an API key as a way to make this work. If you don’t have an API key, you’re not going to get information, nevertheless it’s a comparatively easy request. I’d copy this and I’d paste it in right here. What that is saying, right here’s the API key could be in right here, I need to show a most of 10 outcomes.
I’m plenty of completely different options that we are able to do right here. Now, I don’t want all these. I simply actually need to have a look at key phrases and I need to search for a particular URL. In order to make this work, we don’t need to do SEO e book each single time. We need to truly run the cell so you need to do double quotes after which you need to do double ampersands after which it means that you can fetch this from a particular cell. I’m going to run this cell in a single second with my API key, and I’ll present you precisely how the info works. Now I’ve put in my API key, and now you’ll be able to see that we now have a few information factors.
We have key phrases, place, and search quantity, which is able to all present up right here and there going left to proper. Now I’ve bought 10 key phrases. What key phrase they’re rating for, what place they’re in within the search quantity. I can do that for my high 10 pages and it would take a while, it simply will depend on how the API runs, however now I’ve bought a ton of key phrase information on every one among these URLs. I’ve bought among the high phrases they’re rating for, the positions they’re rating for them in, and the search quantity. That was all achieved from IMPORTDATA. I used a bit of caveat as we keep in mind, I exploit the TRANSPOSE operate as a way to enable me to push that information from left to proper, as a result of in any other case it will have gone straight down and it will repeat it over itself.
These are six highly effective features that we are able to leverage as a way to get extra work from Google Sheets. As an SEO, you in all probability work in Sheets fairly a bit. You in all probability work in these completely different information factors, and that is going that can assist you use your information extra effectively and extra successfully. If you may have any questions on what we talked about immediately, please remark beneath. I’d like to proceed that dialog with you and till subsequent time, joyful advertising.
