Most of the SEO industry use Excel to analyse the data, which isn’t the best tool at our disposal.
As SEOs, the amount of data we are exposed to is growing rapidly every year. However, Excel is not great with big data and you’re limited to just over 1 million rows per workbook.
In addition, the way data is handled in spreadsheets makes SEO analyses slow and cumbersome. Analyses affect the data itself, and they are difficult to perform, repeat and share.
And that’s why I recommend that all SEOs, especially in-house, pick up SQL (and also Python).
What are Excel’s limitations
If you’ve ever used a spreadsheet, then you know:
- It’s easy to accidentally change something that breaks your data.
- It’s hard to replicate an old analysis on new data.
- It’s slow when datasets contain over a couple hundred thousand rows.
- It’s cumbersome to share giant spreadsheets with other people.
Why switch from Excel to SQL?
If you switch to SQL from Excel, all the problems I mentioned above go away. And you get a couple of extra benefits:
- SQL is faster than Excel. What takes a few hours in Excel can be completed in a few minutes in SQL.
- SQL separates analysis from data. When you use SQL, the data your analysing is stored separately. This means that you can send your colleagues a small code file to access your analysis. They can rerun the analysis without ruining your data. And all your code is reusable.
What is SQL?
SQL is the standard language used for extracting and analysing data stored in databases.
Here’s an example of SQL syntax:
You can understand this SQL syntax without even knowing the language:
- Selects all the columns
- From a table (data source)
- Where a column equals ‘some value’
It’s the same as adding a filter in Excel.
Why SEOs should learn SQL
SQL opens the door to the ability to handle more data. SEOs, and especially technical SEOs, find increasing value in huge datasets, and in combining large datasets. For example, if you think about log files, crawl data and other technical datasets, they all exceed the limit of Excel.
And to handle this data we should be using a tool that is built for analysing data at scale. This–analyzing data at scale–is one area where SQL excels.
Just like Excel, SQL can wrangle a dataset to create new columns using aggregate functions or conditionals that makes the data easier for consumption. However, it uses a logic that is closer to programming, which also makes it a good introduction to the technical side of things for SEOs interested in improving their technical skills.
Practical applications of SQL for technical SEOs
Now I will share some examples of where SQL performs better than Excel for SEO data analysis.
Before we start, remember that to utilise SQL to analyse data, you need to have data stored in a database. This isn’t a guide on how to achieve that but here are some pointers:
- Reach out to your data team and see what they already have in the data warehouse.
- Or do it yourself by following this guide on Moz: “how to use big query for large-scale SEO”.
Log file analysis
Log files are a case of big data. Log files easily exceed 1 million rows, so you won’t be able to analyse the data in Excel without sampling. And sampling can introduce biases or errors.
However, if you have the data in a database (such as Big Query) you can analyse it with SQL.
Here are a few common questions we can answer easily with SQL:
- How frequently does Googlebot visit my website?
- Which Googlebot user agent is crawling my site?
- What % of requests hits return a non-200 response?
- What is the % of requests for each directory or site section?
On my blog, I’ve covered log file analysis in detail and if you want to set it up in a database check out this guide by Distilled on log file analysis in big query.
Crawl data analysis
If you’re crawling large websites they will also easily surpass Excel’s 1 million row limit per workbook.
Even websites that theoretically only have a couple thousand URLs can creep up into the millions because of poor implementation, parameter use, legacy data from migrations, and many other reasons.
SQL lets you analyse the full dataset from crawling software, such as Oncrawl, without sampling the data. This means you can use the products to their full potential without having to worry about how you’ll analyse it in Excel.
[Case Study] Handling multiple site audits
Google Analytics analysis
If you’ve ever worked with a site getting more than a 5 digit visits per month then you have probably found Google Analytics to be very slow.
Using SQL to analyse the data can speed things up, so you don’t have to slowly navigate around the user interface waiting a long time for data to load.
And as with other data sources, SQL lets you analyse your Google Analytics data without sampling and can save you a $250,000 on upgrading to premium.
Search Console analysis
The data in Google Search Console interface is great but to filter / customise the data you don’t have many options. It also limits you to the first 1000 lines of data.
It’s best that you export the data into Excel, but why not take it further and analyse it with SQL!
A syllabus for SEOs to learn SQL
SQL for SEO data analysis isn’t complicated. It’s much easier than Excel to pick up.
For SEO purposes, you should focus on learning to use the following fuctions::
- SELECT and FROM
- Comments
- LIMIT
- WHERE
- Comparison Operators
- Logical Operators
- ORDER BY
- GROUP BY
- Aggregate Functions
- CASE
- JOIN
First, I’d recommend taking the SQL for Data Analysis course on Udacity.
Then move on to the Mode Analytics SQL tutorial. You can apply your knowledge from the Udacity course to datasets in their public data warehouse.
Finally, you can continue practicing by taking a course on Codecademy or Datacamp.
Each of these platforms have practical challenges you can use and add to your portfolio.
Helpful resources:
The bottom line
If you want to stay ahead of the pack as an SEO, it’s time you start to polish your data skills with tools like SQL and Python.
SQL is a great entry point, it’s easy to pick up and will give you a nice intro to coding. Once you’ve locked down SQL you can start to learn Python.
