In this episode, we are going to be looking at six powerful functions within Google Sheets that every SEO needs to know. Google Sheets is a powerful tool that lets us look at our data, manipulate our data, and dive deeper in to get a better understanding of the things that we need to do in the areas that we need to improve in. I’m going to show you some cool functions that I use almost every single day in order to help us grow here as an agency and help surface new opportunities for our clients as well. If you’ve got a favorite function and we don’t cover it in this video, feel free to share. We’d love to continue that conversation here in the community and help everybody be the best they absolutely can be.
Table of Contents
Video Transcript:
In this video, we’re going to be talking about six Google Sheets functions that every SEO should know. I’m going to walk you through each of the functions. Then I’m going to show you examples of how we can use those within Google Sheets. This can really speed up your time and also help you combine a number of different data points, which is very useful when you’re trying to really understand what’s going on with a specific project or a specific dataset.
6 Google Sheets Functions for SEO
The first one is probably the one I use the most and it’s VLOOKUP. VLOOKUP is going to be used for pulling data from one spreadsheet into another, though, there is a caveat to this. You have to have at least a shared value within each of the Sheets. You need to have either a keyword on each of the Sheets or a URL on each of the Sheets, because it needs to have that vertical lookup in order to find the information. The syntax is relatively easy.
It starts with equal sign VLOOKUP. Next, you’ll have the search key. This would be the vertical list of cells that you want to use as the key. Next, you’re going to have the range or the range of cells you’re going to be looking at on the other Sheet, the index, so the actual bit of information you need, and then how you’re going to sort that information. Let’s take a look at how we can leverage VLOOKUP. In this dataset, I have a list of top queries that we pulled from Search Console. Search Console is giving me the clicks, the impressions, the click-through rate, and the average position that we rank for each of these search queries. Now, this is really helpful information, but what if I want to know the search volume for these queries as well? I can go into my favorite SEO tool and I could pool all the search data like we’ve done here and then compare it Sheet against Sheet.
VLOOKUP
Used for: Pulling data from one spreadsheet into another spreadsheet ~ as long as the two sheets share a common value.
Function: =VLOOKUP(search_key, range, index, is_sorted)
Now, this is helpful, but honestly, copying and pasting the volume over could be a pain in the rear end. This is why VLOOKUP is so powerful. What we need to do is start on this page, the search queries page, the page we want to add the search volume to. We go ahead and click here and insert a column and we’ll call this column volume. In order to find the search volume for this term, which is located on this second Sheet here, we want to use VLOOKUP. So we’ll go equal sign VLOOKUP. As you can see, Sheets will actually pull it out for you. Then I’m going to select my key, which would be the A column comma and now I want to go over to the Sheet here and I want to select my range. Now, since I’m only looking to find volume, I only need to go to the volume column here.
I don’t need to go all the way to the end. Now, if I wanted to pull more and more information, if I wanted to pull a competitive score or maybe the cost per click, I could go all the way down as well. I just want to get the volume. I’m going to go to there and I’m going to push the comma key again. Now I want to find the index and I want to find all the values in the second column. So one here, two. I’m going to say the second column two, and I want the exact value and what I need to add there is false. This will give me the exact value, which is in this column. I hit enter and now it’s giving me an N/A for this one, because there wasn’t a search volume for this query, but if I want to get all the top 100, I just double click this right here.
As you can see, it just fills all that out for me. Now, I actually know the different volumes or how much traffic each one of these queries gets without having to copy and paste back and forth with my data.
LEN
Used for: Counting number of characters in a cell
Function: =LEN(insertcell)
The next function we’re going to look at is LEN. LEN is used for counting the number of characters within a cell. This could be helpful to know how long a title is if you’ve got a bunch of titles within a Sheet. It also can help you count how long your keywords are, things like that. This is very helpful, and it’s a relatively easy function to use. It’s equal sign LEN, and then you use the cell. Let’s take a look at how to leverage this. Using the same data, if we wanted to see how long these queries were, we could add an additional column to the right, and we could call it length.
Here we’re just going to use the LEN function, so L-E-N, LEN, length of a string. We’ll select the string here in that parenthesis. It tells us exactly how long that specific string is. A string is just a set of characters like text characters. This can be helpful, like I said, when we’re looking at title tags, things of that, where we want to optimize around specific length. It’s an easy function. It’s easy to add extra data in and help us to get, honestly, a lot more information out of our research. So far we know how long each of these keywords are. We’ve got some volume data and we’ve combined this with the data that we’ve already pulled from Search Console. It’s combined two pieces of data. Now we have a lot more information to work with. But let’s talk about another function that we can leverage.
SPLIT
Used for: Splitting data around a specified character or string, and puts each fragment into a separate cell in the row.
Function: =SPLIT(insertcell, delimiter)
Sometimes we import our data and we have a number of data points within a single cell. In order to get it out, we need to use the SPLIT function and the SPLIT function will take that data and use a specific character in order to pull it into fragments and separate it into cells in a row. In order to do this, we use the SPLIT function and we can use that by selecting a specific cell and using the delimiter and breaking that cell up. Let’s see how this works. Here, we’ve got a number of pieces of data that we’ve exported from our tool. As you can see here, we’ve got trends, but all of the trend line data is actually in a single cell. In its current form, it’s not extremely useful. It’s honestly hard to differentiate and really get some value out of.
We need to split this information up. The way we would do that is equal sign SPLIT and we click on that. Then we select our cell. Then what is the delimiter? We are looking at it here and we can see that it’s split up by a comma. We would use this syntax. If it was a semi-colon, we would use that, but we used the quotes and then what is splitting the data up, and then we would end the quote there. That way it knows how to break up the data for us. Hit enter. As you can see, it’s split all this information out. A quick hack, if you just double click the corner, it’ll actually bring all that information down below you. Now we’ve got all of the trend data split out into individual rows and cells.
SPARKLINE
Used for: Creating a quick sparkline graph right within your sheet.
Function: =SPARKLINE(insertcell range)
The SPARKLINE function works really well after we just used the SPLIT function. A lot of times, I’ll get data like that. That is pulled from SEMrush. Now I want to actually see that trend line data. If I’m inside of SEMrush, I can see it, but if I actually export the data and I put it into a Sheet, I just see a bunch of numbers. Sheets has this really cool function, which allows us to create a quick SPARKLINE graph right within a cell. This can actually help us see the data and visualize the data, which can allow us to get a lot more meaning from it. Let’s look at the SPARKLINE function. Now we have all this trend data and we’ve pooled it out here, but again, it’s still not super useful if we’re just looking at it. It can be very overwhelming.
We really just want to see this trend data in the form of a SPARKLINE. We use the equal sign and start typing in SPARKLINE and Sheets will bring it up for you. Then you just pull the range of data that you want to be used within the SPARKLINE and close it. Now you can see, I’ve got this really cool little graph here that shows me the trend line. This is really helpful to know which keywords are trending up as well as which ones are trending down. We don’t have to mess with all of this extra data. We can see it right here within our Sheet. And now we know very quickly, which terms maybe we should target now as opposed to not worry about as much or at least understand the trends with the data itself.
What’s really nice about using Sheets is you can do a lot of analysis from within one document. You don’t have to jump all around and go to different tools if you know how to really leverage the Sheets. Once you have your data, you can manipulate it quite a bit. We’ve seen this with VLOOKUPS and splitting our data and creating SPARKLINES and looking at how long characters are.
IMPORTXML
Used for: Pulling data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
Function: =IMPORTXML(url, xpath_query)
ImportXML is another great tool that we can use. This can actually be used to scrape data or pull in data from a number of structured data type, including XML, HTML, and quite a few others. The function, again, it’s not very difficult, but you do need to understand the different XPath queries you can use. Now, I don’t remember all of these, and this is why the internet is such an amazing thing.
We can look those up so that we can really leverage this awesome function. You would type in ImportXML, you’d have a URL or a cell, and then you would look at the XPath query. Let’s look at a couple of ways that we could leverage. I pulled some URLs from a search result, and now I want to learn a little bit more about them. I want these all in a Sheet, and I want to make sure that I have their title and their meta description. Let’s say I’m doing some keyword research, and I want to understand the SERPs a little bit better. In order to get the title, I’m going to use ImportXML. We’re going to go import, now make sure you choose the right one. This is ImportXML, and now I’m going to need a URL.
This is very important. I click on this cell because the cell’s a URL. I’m going to use quotations and inside the quotations, I’m going to go forward slash, forward slash, title. Now I make sure I close this and hit enter. What it’s done is it’s looked up that URL, and it’s given me back the title tag. Again, I can get all the title tags for these pages by double-clicking that, or just pulling that function down. Pretty cool, right? You can do that very quickly. You don’t have to go back and copy and paste and do all that stuff that’s extra hard.
You can get rid of duplicates. Now you can understand for this query, which was like best screen recorder or something for Mac, then we can look at what pages are ranking and then what type of content, too. Right here, we can see that for all of these different queries, it seems to be listicle articles that are ranking really well. If we’re trying to rank for this, or we’re trying to match user intent, we might need to create a listicle piece of content as well. Let’s say we want to get a little more information here and we wanted to find the meta description.
We’re going to use the exact same function that we did before, ImportXML. And we’ll go ahead and use the URL again. Now I don’t have the meta description one memorized. And like I said, this is where Google is really great, but this guy has some pretty cool stuff on his site, but he’s given us the function right here. I can copy that. Again, I want to put this in double-quotes, paste it in, and there we go. Now we’ve got meta descriptions.
We can pull that down. By using ImportXML, I took this list of URLs I already had and I found all the titles and all the meta descriptions. Again, now we can use a little bit of the other functions that we talked about before. Let’s say I want to know how long these title tags were. I could do title length and I can see how long the characters are, using LEN, right? So you can actually do a ton of analysis while you never leave Sheets, right? Never once have I left Sheets and I pulled all this information. I can do the same thing for description length. So while having a crawler obviously is really good, Screaming Frog, or Sitebulb or anything like that, these Sheets that Google has for us are actually really helpful as well to do things very quickly if we need to do some quick analysis or we want to combine our data.
IMPORTDATA
Used for: Pulling in data from APIs
Function: =IMPORTDATA(url)
The last function we’re going to talk about is called IMPORTDATA and I usually use this to pull data from APIs directly into Sheets. The function is relatively simple. It’s just IMPORTDATA. Then you have your URL, the API URL, the requests that you’re making. Then based on that request, you’ll get certain data points back. Let’s look at how we can leverage this. For this example, I’m going to be using SEMrush’s API. All APIs are a little bit different, so it may not work perfectly for you in the exact same way, but I would look at some other ideas and some other things that people are doing with IMPORTDATA if you can’t do it the exact way that I am showing you here. But this is a really cool way to import lots of information into your Sheet, again, without having to go to other tools.
Once again, we’re here at this last Sheet where we’ve got URLs and we pull our titles and our metas, but let’s say I want to get some more SEO metrics about these specific URLs. This is where I could use IMPORTDATA. Now there’s a little function that I’ll typically use ahead of this, just because a lot of times APIs will stack data down and that’s going to break once we do that over and over again, because it’s going to be replacing the data. I like to start all of these with TRANSPOSE and this is a really cool one, too. This is actually seven, I’m giving you an extra one. TRANSPOSE will actually tell it, instead of returning the data downward, I want you to transpose it. It actually goes left to right. You can stack these functions. Now that I have TRANSPOSE, I can also now use IMPORTDATA.
This is the function that we’re talking about. You’re going to need a URL to do that, and we’ll go get that next. SEMrush has an API. It is very filtered, I would say, but it can be really helpful if you’re trying to get some quick metrics and understand how things work. In this case, we’re actually going to be looking at URL reporting. We can look at specific URLs and I want to see some organic keyword metrics around these specific pages. You’re going to need an API key in order to make this work. If you don’t have an API key, you’re not going to get data, but it’s a relatively simple request. I would copy this and I would paste it in here. What this is saying, here’s the API key would be in here, I want to display a maximum of 10 results.
I’m looking at a number of different features that we can do here. Now, I don’t need all these. I just really want to look at keywords and I want to look for a specific URL. In order to make this work, we don’t want to do SEO book every single time. We want to actually run the cell so you have to do double quotes and then you have to do double ampersands and then it allows you to fetch this from a specific cell. I’m going to run this cell in one second with my API key, and I’ll show you exactly how the data works. Now I’ve put in my API key, and now you can see that we have a couple of data points.
We have keywords, position, and search volume, which will all show up here and there going left to right. Now I’ve got 10 keywords. What keyword they’re ranking for, what position they’re in in the search volume. I can do this for my top 10 pages and it might take some time, it just depends on how the API runs, but now I’ve got a ton of keyword data on each one of these URLs. I’ve got some of the top terms they’re ranking for, the positions they’re ranking for them in, and the search volume. That was all done from IMPORTDATA. I used a little caveat as we remember, I use the TRANSPOSE function in order to allow me to push that data from left to right, because otherwise it would have gone straight down and it would repeat it over itself.
These are six powerful functions that we can leverage in order to get more work from Google Sheets. As an SEO, you probably work in Sheets quite a bit. You probably work in these different data points, and this is going to help you use your data more efficiently and more effectively. If you have any questions about what we talked about today, please comment below. I’d love to continue that conversation with you and until next time, happy marketing.
