BigQuery & SQL for SEO

If you’re an SEO, you’re undoubtedly conducting plenty of audits and analyses. Content audits, technological audits, internal link analysis, on-page audits, keyword gap studies, and so on are all available. You must be utilising numerous SEO tools to create these audits and analyses. Each tool has a specialised application (which is why you need so many).

Ahrefs or SEMRush are used for keyword and backlink data, Screaming Frog and Sitebulb for crawl data, Google Analytics for session and UX metrics, Google Search Console for query and click data, and so on. These types of technologies are useful out of the box, but the insights they can provide are restricted. Because these tools are designed for big audiences with diverse use cases, it is just not viable for a business like SEMRush or Ahrefs to incorporate every single analytic and reporting view into their product.

The true worth of these tools is in their data. Fortunately, most of these tool providers allow you to export their raw data in some fashion. Once you have these raw datasets, if you know how to integrate them, filter them down, aggregate them, and then construct custom logic, the level of analysis you can perform is far greater than if you use the UI of each tool.

SQL (Structured Query Language) comes into play here. Now, before you get terrified of learning a programming language (if you can call it that), let me calm your fears.

By writing SQL in BigQuery, you can automate 80% of your agency’s outputs. It will save you numerous hours of labour and can be a significant influence on the growth of your company.

In this article, you’ll discover why knowing basic SQL is such a valuable skill for an SEO to possess, as well as how to get started with it.

What Is A Database?

Let’s define a database before we get into SQL. A database is merely a repository for structured information (i.e. data).

Consider a folder on your PC that contains two Excel files. The folder serves as the database, and each Excel file serves as a table. An Excel file can also be thought of as a database. In this example, each sheet would be a table.

What Is SQL And Why Should You Care?

SQL is a simple programming language that allows you to access and manipulate one or more databases as well as the data contained within those databases’ tables.

It’s a lot like using an Excel formula to refer to distinct cells in one or more sheets. It’s simply more vibrant and potent.

How can you benefit from BigQuery & SQL

BigQuery & SQL can assist you as an SEO in automating repetitive operations, speeding up procedures and workflows, and ultimately getting more out of the data you already have. Here are some things you can do with SQL:

The Website Quality Audit

A website quality audit is a tool created by The Blueprint Training that combines a content audit, a technical audit, and an overall website quality report. It is constructed by combining six different datasets, manipulating the data, and then linking them together on a URL.

There are numerous programmes available that offer outstanding service and may create a single website quality audit report for a very minimal cost. This is an excellent solution for many organisations.

However, we would like to be able to personalise the report and incorporate it into various workflows and processes inside our business. For this, we should be able to be rebuilt the tool in BigQuery. 

For example, instead of using the SEMRush keyword dataset, we might use the Ahrefs Keyword and GSC query datasets. We can also automate some of the manual tasks associated with categorising pages and URL actions.

Other things that can be done include adding new columns to the tool, such as the date a blog post was published. You may also incorporate keyword research for each URL into the website quality audit and then add rank tracking data to ensure that the correct URLs were ranking.

To give you an idea of how quickly you can put create useful tools like the website quality audit once you know SQL, it takes about 5 hours. It is now a process that an experienced person may complete in less than 30 minutes.

Other Reports You Can Automate With SQL

There will always be manual tasks in SEO (at least in the foreseeable future).

There is no way to prevent it, at least for the time being. However, with a little SQL knowledge, you can largely automate the majority of the analysis and reporting you do as an SEO.

In addition to the website quality check, we’ve created BigQuery tools for the following:

• Keyword GAP Analysis
• Internal Link Analysis
• Competitor Backlink Analysis
• URL Prioritization
• Various link building processes
• SEO Opportunity Model
• Editorial Calendar

SQL is more concerned with teaching you how to think about data and databases. It will be difficult to stop the ideas from coming to you once you have a strong understanding of how they work!

Specific SQL Automation and Analysis Examples

SQL allows you to connect data and create useful reports and tools. But it doesn’t end there.

SQL can also be used for analysis and the creation of various automations. Once you’ve completed a website quality audit, you can import the final dataset into BigQuery and use it for analysis and automation. The purpose is not to have you understand the real SQL, but to demonstrate the power of a tiny bit of code.

1. Automate Category Tagging

You must manually tag pages by page category as part of the website quality assessment. Is the URL, for example, a blog post, landing page, blog category, or something else? Much of this procedure may be readily automated by using simple IF-THEN login. In SQL, IF statements are referred to as CASE statements.

2. Automate URL Action Rules

The URL action portion, which is effectively a content audit, is another manual component of the website quality audit.

Based on several data factors, you decide what to do with a URL. Instead of manually inspecting each URL, we can write some rules to automate this process. We’ll create rules within a CASE statement, identical to the one in the previous section.

If a URL receives a lot of traffic, we’ll want to tag it with “Update On-Page” so we can look at the page in more depth, make on-page modifications, and increase traffic.

3. Check For Duplicate Page Issues

Duplicate pages might harm your search engine rankings. We may create a simple query to look for duplicate pages.

This query counts the number of page titles that are the same when the status code is 200 and the pages are indexable from the final website quality audit data set.

The TotalPageTitles column is then ordered descendingly, so the pages with the most repeated pages are displayed at the top.

Final Thoughts

SQL allows you to create your own SEO tools, process automation, and workflows. You will no longer need to rely on third-party tools for guidance. You can use their datasets to gain the information you need to make judgments.

Instead of being confused trying to master all the details of tools like SEMRush, Screaming Frog etc, (only to receive meaningless or misleading information), export your raw data, dump it into a database like BigQuery, and then write some SQL to get answers of specific questions.

Credits – https://codingisforlosers.com/why-learn-sql-for-seo/