SEO involves many moving parts.
As a result, it can be difficult to understand what’s working and what isn’t.
That’s where super-detailed and up-to-date reports come into play.
Since SEO can be seen as a bit of a black box, it’s hard to prove you’re working on anything at all as an agency. Customers often ask for updates but want them in easy-to-understand reports.
By using Google Sheets to your advantage, you can provide these updates and satisfy your customers.
We’ve used a ton of automation, all revolving around Google Sheets, to better understand our customers’ SEO improvements for their websites.
This article will explain how you can use spreadsheets to better understand your SEO efforts. All of the examples will be shown with Google Sheets as the interface of choice.
Why choose Google Sheets over Excel?
If you put Google Sheets and Excel side by side, they accomplish the same goals.
In theory.
When you put these two against each other in practice, I’ve found from personal experience that Google Sheets wins every day of the week.
The fact that Google Sheets is always up-to-date and allows others to collaborate on the same sheet at the same time will always give Google an edge.
Microsoft eventually added this functionality, but it’s behind a paywall and you must be a Microsoft 365 subscriber (at $100 a year or more). It’s tough to beat Google’s free pricetag here.
Also, the general user experience and functions are easier in Google Sheets than they are in Excel.
While either takes a lot of time to master (and is honestly an ongoing journey), both solutions can give you what you need at the end of the day. The difference is how much frustration you want along the way.
Before we look at SEO, let’s first consider the major differences between the two.
Major differences between Google Sheets and Excel
Microsoft Excel Online and Google Sheets both have similar service offerings for the two applications. They also offer 15GB of complimentary cloud-based storage via their associated drives, OneDrive for Excel Online and Google Drive for Sheets.
Both web-based applications have cross-browser compatibility, meaning they will function across all major web browsers.
Microsoft Excel Online lacks an offline component, and can only be used when an internet connection is available. Google Docs has an offline component via working offline in the Chrome browser, and users can work on documents even when a connection is not available.
Another important difference lies within the types of files each application supports.
The types of files you can view and edit in Excel Online are fairly limited compared to Google Sheets. Commonly used file types like .xls (newer than Microsoft Office 95), .csv, and .txt are supported by Google Sheets, but not by Excel Online.
Excel Online’s menu and layout are very similar to the desktop version, but there are many key differences when it comes to using a workbook in the browser versus the program itself.
Google Sheets has more of the key functions that Excel desktop users are accustomed to, but the layout and menu will certainly look a little different. Google Sheets also has more customizable options thanks to its add-ons.
How do I choose?
Oftentimes, your choice comes down to comfort and brand loyalty. If you use Office 365 and OneDrive, Excel Online is an excellent choice.
If you are more likely to use Google Apps, Gmail, and Google Drive, Sheets may be a more natural fit. If availability is important, Sheets is also the better choice because of its Offline Mode.
Familiarity may play a key factor in your decision as well. Some users may have a difficult time moving from the desktop version of Excel to Sheets since the menu system is laid out differently. Some items are named similarly but are not exactly the same as Excel.
Essentials you need to use Google Sheets
Now that we know the basics of the spreadsheet platform we will be using, let’s look at what you need to know to get started.
Out of the box, Google Sheets is nothing more than a place to put your data. This data for SEO includes keywords you’re ranking for, organic traffic your website receives each month, and more.
You don’t want to overwhelm your customers by giving them an enormous amount of data. Instead, you want to dissect and visualize this data so that it is easy to make decisions and take action.
Here’s an example of a dashboard created in Google Sheets. The charts are easy to read and make it simple to assess the data. If it weren’t for the columns and rows on the top and side, you might not even believe this was created in Google Sheets.
This should be the end result you strive for in your reporting.
To get there, you’ll need to have a basic understanding of some formulas.
Formulas
This article will not be going in-depth on everything you can do with Google Sheets formulas because the sky is the limit.
What we will show you are the main formulas we use to help display data for our customers.
Query
The query function is the powerhouse of all formulas. It can do anything that every other formula can do. It can Sum data (replacing the sum function), count cells (replacing the count and counta function), and find and organize data (replacing the vlookup function).
Mastering this takes a lot of time and is an ongoing journey, but if you spend a few weeks studying and taking courses, you’ll discover amazing results.
(My favorite course to learn Query is by Ben Collins).
Here’s an example of it in play.
We have a formula that pulls data from Screaming Frog in our automated SEO audit. Below we see all of the meta titles of our site that have less than 30 characters or more than 65 characters.
This is the formula that helps us get there. If this is the first time you’ve ever seen this formula, you may be confused. However, most of the language is written in a pretty easy-to-follow format.
I’ve highlighted some portions to make it more clear what’s happening.
We first select the organic data we want, then look for pages that are not backend pages (wp-admin) and ignore pages that are 301 or 404 (=200).
Then, we only look at the length of the meta title where it’s greater than 65 or less than 30 characters.
=query(‘Raw html’!$A:$BA,
“select A, G, H
where not A contains ‘wp-‘
and C = 200
and (H > 65 or H < 30)
order by A”)
What we’re left with is a formula that will automatically tell us the problem pages from our SEO audit so we can diagnose and fix them on our actual site.
Pretty cool, huh?
Vlookup
Vlookup provides a way to look up data based on criteria.
Let’s say I have data that shows all of my organic traffic and I have thousands of rows.
I can use Vlookup to find information based on what page I’d like to see.
This formula can be useful in a pinch, but something more robust is the index match match custom function.
Index match match
Index match match is a more powerful version of vlookup. It takes a little time to master, but the basic concept is to find information based on two different sets of criteria.
What you’re doing is nesting two match functions within an index function. The index function gives you a horizontal and vertical lookup of data. You use one match to identify the horizontal lookup and a second match function to look up the vertical match.
What you’re left with is the intersection of these two data points.
This example below pulls data from Google Search Console (GSC) (via Supermetrics) into my report.
Since GSC has my data, I can create a lookup that searches for the page in question (the first cell) and then matches it to the column in my GSC data (each column at the top).
For clicks, I’m looking through all of my GSC data for the column “clicks” and finding the number associated with this blog (https://milesit.com/blog/semrush-guide). What I end up with is 18 clicks over the last 3 months for this post.
I now have an auto-updating report that gives me the click data on a rolling basis.
Here’s the formula I’m using to give more context:
=index(‘GSC import’!$A:$J,
match($A3, ‘GSC import’!$A:$A, 0),
match(O$1, ‘GSC import’!$A$1:$J$1, 0)
)
Len
Len is super helpful for counting characters in cells.
I often set up a simple =len(a1) formula to count characters when I’m rewriting meta titles and descriptions.
Then, I set conditional formatting to tell me if the character count that len determines is greater than or less than a specific number.
This is useful for ad copy that needs to meet a specific threshold or meta titles and descriptions that need to stay within specific confines.
If and array formulas
I often use the if formula in conjunction with array formulas to extend data.
Because most of my spreadsheets automatically populate with new data via Supermetrics, I end up with new columns of data at the bottom of my spreadsheets.
This means any static formulas I’ve put into my sheet will not do anything for the new data.
I solve this problem by nesting an if function within an array formula.
This example looks for the year, month, and day based on specific data in column E. The combination of array formula and if means that any time a new row of data is added below row 2, cells A, B, C, and D will automatically be populated with the year, month, and day.
Here’s the formula I’m using:
=ArrayFormula(if(E3:E = “”, “”, year(E3:E)))
Charts
Google Sheets has many charts you can use out of the box to visualize your data.
Columns, bars, pie charts, and scorecards are the most common ones that I use.
This example shows a combination of scorecards (total leads in the top left) as a summary of data.
The two bar charts show a breakdown of the types of leads (IT, software, and marketing) and the referral source from a specific time frame.
Without looking at any data in cells, I can see how we’re doing in this defined time frame.
Organic benchmarks can also be visually shown in a year-over-year fashion with line charts.
Here’s the total trending traffic for a customer from the last three years.
When to make a report static and when to automate the data
I’ve already mentioned a few times how I pull data from different sources. This is because I crave automation to make my job easier, so I can focus on analyzing data rather than spending all day finding data.
However, there are some instances where you want to export data from a specific source and use Google Sheets to diagnose that data.
This comes up sometimes in Google Search Console when I’m in the interface and I want to export some data to answer a specific question.
Always be sure to think about when you need to automate and when it’s simpler to export a specific set of data you need for analysis.
I’m guilty of spending time trying to automate something that I’ll only use once when it would have been easier to just export the data.
Let’s move on to how you can pull the data I’ve been mentioning with some amazing tools.
Understanding how to pull data automatically (with Zapier, Supermetrics, and APIs)
The reports I create are often pulled from other sources, so I don’t have to manually export data and recreate charts every time I open Google Sheets.
Below are some of my favorite ways to push data to Google Sheets.
Supermetrics
Supermetrics is by far my favorite way to get data automatically pushed into Google Sheets.
I use this daily to pull fresh data from Google Analytics, Google Search Console, SEMRush, and more.
This example pulls data by month from Google Analytics. With the data pulled in this format, I’m able to easily create charts that visualize the data.
You can see I’m also pulling keyword data from SEMrush and organic traffic from Google Search Console all in one report.
This data pushes to Google Sheets at 2 am every day so it’s ready for me by the time I make my commute from my bed to my home office (I hate the traffic every day!).
Zapier
Zapier is a freemium tool that allows you to have systems talk to each other.
For example, I have a zap that looks for new rows in Google Sheets (when data is pushed to it from an API in our ERP).
It then finds that data and creates or updates a contact in HubSpot, the marketing software we utilize for attribution.
Finally, I get an email alert when the zap is successful.
This is just one way you can utilize your data to make sure it’s all updated automatically.
Screaming Frog
Screaming Frog is the most essential tool for improving your SEO.
I’ve created reports that run crawls daily, weekly, or monthly for our customers’ websites.
This data is then pushed to Google Sheets automatically and sorted by meta titles and descriptions, technical SEO data, organic traffic trends, h1s and h2s, and more.
All of this data gives me an up-to-date SEO audit that runs automatically. I can then diagnose problems with my website quickly. When I fix them, I wait for the audit to run again and check if my updates took effect.
APIs
APIs are complicated, but allow you to integrate two systems and have them speak to each other.
One of the reasons Google Sheets is so useful is that you can import data from custom software via APIs and scripts.
It’s definitely very advanced, and I’m not the one to talk to about this highly technical topic. Luckily, we have an amazing team of software developers here that help with this work.
They created a custom JSON function that gets the data I need from our CRM, Striven, into Google Sheets so I can diagnose it further.
If you just pasted this function into your sheet, it wouldn’t do anything without the custom function, but I’ll show what it looks like nonetheless.
=ImportJSON(DataEntry!B3,”/data”,”noTruncate,noInherit”, DataEntry!E1)
The main point of this section is that anything is possible. You can pull data from literally anywhere into Google Sheets so you can visualize that data.
Types of reports you should have in place (using Supermetrics)
I cannot stress enough how important it is to pull your data automatically to Google Sheets via a tool like Supermetrics.
Once you have the data here, you’ll want to start segmenting your reports to answer specific questions.
This looks different for every business, because every business may have different questions.
The next section shows some of the most important questions we ask ourselves for our customers and their data. This helps us frame what data we want to collect and what answers the charts and graphs should display.
Organic traffic
- Which pages are trending in which direction? (Google Analytics)
- Which pages are most popular via clicks and impressions? ( GSC)
- What are our most popular pages and are people converting from these pages? (Google Analytics)
- What events are occurring that guide the user down the conversion path? (Google Analytics)
Keywords you’re ranking for
- Which keywords are the least efficient at bringing in traffic? (SEMrush)
- Which keywords from specific pages bring in the most traffic? (SEMrush)
- Which search terms do people use to find my website? (GSC)
User Experience
- What are the top interactions on each page of my website? (Google Analytics)
- How many people are clicking phone numbers on various pages? (Google Analytics)
- What impact do blogs have on conversions? Are people reading them and scrolling all the way to the end? What is the time on page for blogs? (Google Analytics)
- Which parts of my navigation menu are people using and not using? (Google Analytics)
Summing Up
This article discussed how you can push data to Google Sheets automatically and shared some tips for organizing that data.
Of course, not everything can be shown in one article, as it takes years to master this type of reporting technique.
Hopefully, I’ve inspired you to venture down the path of data visualization. It can help take the guesswork out of marketing and give you actionable data points to see how you can improve.
If you’re interested in learning more about any particular dashboard, just send me a message and I’d be happy to further discuss how it was created in a future blog.
