- Use cases
- Customer Success
- LOG IN
- Start free trial
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).
If you’ve ever used a spreadsheet, then you know:
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 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:
It’s the same as adding a filter in Excel.
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.
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:
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:
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.
In a few weeks, using OnCrawl has helped Evergreen Media with SEO quick wins regarding Google Featured Snippets, snippet optimization, rankings improvements for converting pages, 404 errors... Find out how OnCrawl can ease any SEO agency’s workflow when it comes to SEO audits.
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.
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!
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::
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.
Each of these platforms have practical challenges you can use and add to your portfolio.
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.