Search Engine Optimization (SEO) often involves looking at large data sets like web crawl data, or user traffic data exported from a program like Google Analytics. Keyword research, for example, can sometimes involve combing through tens of thousands of keywords, positions, volumes, SERP features, and more! It’s time consuming to sort through all that data to find the right nuggets that lead to better SEO. Plus, even the most careful person can still make mistakes when reviewing large amounts of data.
Luckily, Microsoft Excel can do a lot of the heavy lifting for you. Excel is a spreadsheet program that allows users to organize, analyze, and visualize data. It is designed to store and organize large amounts of data in a structured format. This allows users to create custom formulas to perform complex calculations on their data. In addition, there are a number of tools that help users visualize data trends and patterns. All these functions can be used to perform SEO research. By learning how to use Excel for SEO, you can save time and improve your results. We will review how the CONCAT and VLOOKUP features work in this article. Note, this article is specifically about Excel, but all the tasks can also be completed using Google Sheets.
How to Use SEO Tools in Excel
Let’s look at how Excel can be used to complete a task that will help you determine a site’s high and low performing pages. Say you want a list of all the URLs on a site and how many pageviews each one has had in the last six months. You can view pageview data over a selected timeframe in Google Analytics (GA). However, GA won’t show you pages that didn’t receive any pageviews for your timeframe. On the other hand, a web crawler can provide a list of all the indexable pages on a site, but it won’t include pageviews. So how do you get a complete list of URLs with pageviews? If you export your data sets from GA and your web crawler, you can use Excel to combine them into one spreadsheet.
First, export your pageview data from GA and your crawl data from the web crawler. You will have two different Excel (or CSV) files. Both will contain extraneous data that you don’t need for this exercise. All you need are the indexable URLs from the web crawl and the URL slugs and pageviews from GA. I will be referring to these spreadsheets as web crawl spreadsheet and GA spreadsheet.
Let’s start with the data from GA. You will have a table that shows the URL slug with corresponding pageviews. However, your crawl has full URLs. For this exercise, you need the URLs to match. You can do this by using the CONCAT formula to create full URLs in your GA spreadsheet.
The CONCAT Formula in Excel
The CONCAT function in Excel is used to join two or more text strings together. It is a very versatile function and can be used for a variety of purposes, including:
- Combining first and last names to create a full name
- Creating product descriptions by combining information from different columns
- Merging data from multiple worksheets into a single worksheet
To use the CONCAT function, you simply need to specify the text strings that you want to join together. A text string in Excel is a sequence of characters that are entered into a cell. Text strings can include letters, numbers, symbols, and spaces. Text strings are often used to store names, addresses, phone numbers, and other types of data that is not intended to be used in calculations.
Here is an example of a CONCAT formula:
=CONCAT(“First Name: “, A2, “, Last Name: “, B2)
This formula will join the text strings in cells A2 and B2 together, separated by a comma and a space. The result will be a string that looks like this:
First Name: John, Last Name: Doe
The CONCAT function is a powerful tool that can be used to save you a lot of time and effort when working with text data.
Using the CONCAT Formula
In our example, we will use CONCAT to add the missing part of the URL to the URL slugs from our GA spreadsheet. First, create a new column and add the missing part of the URL to the first cell. In our example, the domain is HTTPS://www.example.com:
Using AutoFill in Excel
Now, use AutoFill to copy the contents of that cell to the remaining rows. AutoFill allows you to automatically fill cells with data. To use AutoFill: position the mouse pointer over the bottom right-hand corner of the cell until it’s a black plus sign:
Click and hold the left mouse button and drag the plus sign down the cells you want to fill. The series will be filled in automatically using the AutoFill feature:
Now that we know how to use AutoFill, we can complete our CONCAT function. Select the next column (cell D2 in our example), add the CONCAT formula: =CONCAT(C2,A2). In this example, C2 is the first part of the URL while A2 is the second part of the URL.
Starting the CONCAT formula
What the full formula will look like:
Add the close parenthesis to the formula and click Enter or the check mark by the formula bar. The URL in cell D2 should be the full URL now. We can use AutoFill to complete the rest of the URLs. Hover the mouse cursor over the lower right-hand corner until it changes to the black plus sign. Complete the AutoFill to fill the cells with the full URL. Our table should look like this:
Now that we have the full URLs and the pageviews, we are ready to use the VLOOKUP formula to layer in the pageview data from the GA spreadsheet to the web crawl spreadsheet.
The VLOOKUP Formula in Excel for SEO
VLOOKUP is an Excel function that allows you to look up a value in a table and return a corresponding value from another column in the table. It can be used for a variety of tasks.
- Finding product prices
- Looking up employee contact information
- Matching customer orders to inventory records
To use VLOOKUP, you need to specify four arguments:
- Lookup value: The value that you want to look up in the table.
- Table array: The range of cells that contains the table that you want to search.
- Col_index_num: The column number in the table array that contains the value that you want to return.
- Range_lookup: A logical value that specifies whether you want an exact match or an approximate match. TRUE returns the first approximate match, and FALSE returns the first exact match.
Here is an example of a VLOOKUP formula:
=VLOOKUP(A2,B2:E25,2,FALSE)
This formula will look up the value in cell A2 in the table range B2:E25. If it finds an exact match, it will return the value in the second column of the table (column B). If it cannot find an exact match, it will return an error.
VLOOKUP is a powerful function that can save you a lot of time and effort when working with large datasets.
Using the VLOOKUP Formula
In our scenario, we have two sets of data – one from a web crawler that has our site’s full URLs and one from Google Analytics that has pageview data. Both data sets now have full URLs, thanks to the CONCAT feature. Because the data sets have a common text string (the full URLs), we can integrate them using VLOOKUP.
Open the web crawl spreadsheet and create a new worksheet (often called a tab). Navigate back to the GA spreadsheet and select the Full URL and Pageview data. Copy it and paste it into the new worksheet in the web crawl spreadsheet. We will want to use “paste values” in this case. Otherwise, we will paste the CONCAT formula rather than just the text of full URLs.
Navigate back to the worksheet with the web crawl URLs. Select a new column and call it Pageviews. Start the VLOOKUP formula:
Now navigate to the new sheet with the pageview data. Select the range (i.e.: all of the cells you want to include in the formula) and add the appropriate column number in the formula. In our example, the number is 2 because the pageviews are in the second column (i.e.: column B). The last step to creating a VLOOKUP is the exact or approximate match. We want an exact match because we’re matching URLs.
The formula should be:
=VLOOKUP(A2,Sheet2!A1:B30,2,FALSE)
Now that we have the formula configured, click Enter or the check mark next to the formula bar. We should see the result on the web crawl sheet.
So we’ve completed a VLOOKUP for one row, but we have multiple rows. We can use AutoFill for the remaining rows but there is one more step that needs to be taken to do this.
Using AutoFill with a VLOOKUP Formula
Using AutoFill with a formula like VLOOKUP can create problems because of how cells relate to each other. We can solve this by using the $ in the formula. With the appropriate cell selected (B2 in our example), click into the formula bar and add a $ in front of the column (A,B,C, etc.) and row (1, 2, 3, etc) references in the range (i.e.: step 2 of the formula).
This creates an absolute cell reference rather than a relative cell reference. It allows us to use AutoFill for the VLOOKUP formula.
Once the rows have been filled, some will have an N/A rather than a pageview number. This means that Excel couldn’t find that URL in the GA pageview sheet and so returned a result of N/A. In other words, Google Analytics didn’t record any pageviews for that URL and it wasn’t included in the data we exported from GA.
We can add a filter to the pageview column to select all the cells with N/A and change them to zeros. Now we have a full set of URLs from the web crawl that includes pageview data from Google Analytics.
You can sort your data by pageviews so that you can determine which pages are performing well and which need to be reviewed:
Excel Improves SEO Research
This is one way Excel can be used to complete an otherwise time consuming task. It is a powerful tool that can be used to improve SEO research. The Excel functions we’ve reviewed in this article can be applied to many different scenarios. You could use the same method to pull pageview data into keyword research data to view keywords, position, volume, URLs, and pageviews in one spreadsheet. By using Excel, you can organize and analyze data, identify trends, and track your progress over time. This can help you to make better decisions about your SEO campaigns and to improve your website’s ranking in search engine results pages.