Use BigQuery (SQL) to create your own SEO tool

Omi says: “The best way to achieve provide unique SEO is by using Google BigQuery and SQL to create your own SEO tools, processes, automation, and workflows.

Why BigQuery?

“Every website and every business behind every website is unique. Every website belongs to a different niche and we know that Google treats different niches in different ways, hence the SERPs looking different in different niches. Last year, one of the best ways to do your SEO was to analyse the SERPs for your niche. This is an integral part of SEO strategy development. Every website, department, and business needs unique, boutique SEO.

This year it’s going to be BigQuery. 2-3 years ago Google released a new edition called Google Analytics 4. This version of Google Analytics is a completely new way of measuring performance.

Believe it or not, GA4 comes with free access to BigQuery. You don’t have to be a Google 360 user to have access to BigQuery. There are some limitations but every individual, every SEO, and every small company can use BigQuery successfully.

Why BigQuery? It’s a very robust business intelligence platform database. In simple terms, it allows us to gather information from all digital platforms, including Google Analytics, Search Console, any SEO tools that use APIs, CRMs, and even offline data. When you combine all of this data you’ll have a holistic view of your customers, their journeys, and why and when they buy from you. What are they buying after visiting your offline stores? How long does it take them to buy from you? What are the most profitable keywords? The possibilities are limitless. No single tool can do that for us, and there will probably never be a tool like this on the market - that can give us all the information, and a holistic view of our customers and their journey.

To summarise, every website needs unique SEO because the SERPs are different for every single niche. This is so natural and so normal for Google because, let’s not forget, we are not the customers; our searcher is the customer. Google has to satisfy that person - the person who is searching for our products. They don’t have to satisfy the SEOs. On one side, we require unique SEO and, on the other side, we’ve got access to a free tool from Google that can give us a unique angle. It can give us a holistic view of everything that’s happening with our website and our customers.”

In terms of SEOs getting started with BigQuery, what are the critical data sources you need to take in?

“It’s best to start with Google Analytics and learn how to use Google BigQuery, because there is a bit of a learning process. Learning SQL is quite easy. You can just go and learn SQL from W3Schools.com. This will take roughly a month. Connect Google Analytics 4 to SQL and start from there. See what BigQuery can give you.

Then, you can extract data from your SEO tools and create what’s called a data pool. Everybody within your business can have access to this data and you can visualise the data in Google Data Studio. The benefit here is that you’re going to have this holistic view that will be available to everybody. Imagine you’ve got access to Semrush, and now others within your company require work access too. You wouldn’t be able to grant this and you wouldn’t be able to teach them how to use it. However, you could easily give them access to your Google Data Studio reports.

Going back to the uniqueness of SEO, every website is different. Even every product is different. The way we present the same problems on websites is different. It’s important to appreciate that every product or service will be ranked differently by Google and in different scenes. Put in a unique SERP and that will be unique for your product. By embracing this, you’ll know that your website needs unique SEO.

If someone asks you to rank a page for your business, the first question you should ask is what is your SERP? Don’t just go to a conference and listen to what the speakers are telling you, because that might not apply to your business. You need a unique SEO approach that’s exclusively tailored to your business. This will be important to your success.

For example, a client comes to you and says they’ve invested a lot of money into ranking a page successfully. They’ve invested a lot of money into search content creation, images, etc and the page is now number 1. However, they’re not getting anything from this page. If they’re wondering what’s wrong with the meta description, copy, etc. you could simply go to the SERPs and perhaps see that Google is ranking a lot of headers and videos at the top of the page.

If you open the SERPs, you’ll probably see some good advertisements and then a lot of videos that actually answer the main topic question without having to navigate elsewhere. Who’s going to click through and visit a page if they can get the answer in the SERPs? If you type ‘how to cook the perfect medium rare steak’ and you see a video in front of you in the SERPs, you’re definitely going to watch the video and probably not click through to see if the copy is amazing. In the case of the client, you’d tell them they need unique SEO and to produce a video, try to rank this video, and include it in the SERPs. That will promote the best chance at success.”

It sounds like quite a manual process of checking the SERP, is it possible to automate the process of feeding what’s happening in the SERP to BigQuery as well?

“In Google BigQuery - if you’re good enough you can use SQL and even do the now famous machine learning - you can gather all the information from all your competitors. If you come up with a good algorithm, you’ll know what you need to rank well. This algorithm will gather all the information from all your competitors, including the way they rank your competitors. You can get this data from Semrush because it’s a competitor research tool. You can plug this into BigQuery, plug in the machine learning algorithm and you’re good to go.”

Where do you get started with this machine learning algorithm?

“It’s not easy. It’s best to start with GA4 and BigQuery, then think about the rest. It’s a process. You have to know GA4, and you have to know how to use BigQuery - which is a new product for those who don’t have Google 360 and have never worked with BigQuery before. Learn how to use the tool first, learn the basics and then start thinking about machine learning.

It’s not that difficult. You could make progress with BigQuery within a year, especially if you go to W3School.com. SQL is not a difficult language and there is an excellent article from Google about how to do machine learning with SQL. It’s hard to believe that they’re giving us BigQuery for free now.”

Is there any danger that they’ll be taking it away soon, and you’ll be in corporate loading all your data into that?

“That seems unlikely. They’ll want to draw people in so that - when they see how it works, and how good it is - they’ll start thinking about Google 360, which is relatively expensive.”

Once you take your data into a tool like BigQuery, will you have more historical data to play with so you can do comparisons?

“Yes. You can make clever comparisons from different tools and even offline data. Take all the data from your offline shop and plug it into BigQuery. You just have to find a match there because you’ll need something common between everything, like a product name, price, or something like that. You’ll even be able to find out know what people buy from you after they buy from your offline shop. This is crazy to think.

If you see that a lot of people go to your shop and buy a product and then go online and search for a cable for this product, you can accommodate this situation. You could publish a table about the cable and a lot of people are going to buy from you because you’ve got the data. However, you need unique SEO because it’s not the same for every single company, business, and website.

This is not just the future of SEO in 2023 but the future of SEO in general. It’s going to be about bespoke SEO and the usage of big data. Practical big data, which you can now access for free.”

What shouldn’t SEOs be doing in 2023? What’s seductive in terms of time, but ultimately counterproductive?

“In 2023 and beyond, SEO shouldn’t be applying general SEO statements and advice. When building an SEO strategy, you should tailor it to the business needs. It’s not only about rankings or about people coming to your website, but how you bring something to the table. Most of the time it’s about revenue. How do you bring revenue to the table?

If you listen to all of Google’s algorithms you’ll see they have machine learning elements to them. This is the future. It’s getting more and more difficult to apply general advice - that you’ve heard at either a conference or that you’ve seen somewhere on Twitter - without thinking about what’s actually important to the website, and the business. SEOs need to stop copying and pasting the same strategy and applying it to different clients.”

Omi Sido is a Senior Technical SEO at Canon Europe and you can find him at omisido.com.