See the bigger picture through BigQuery

Priya says: “Gear up your SEO analysis through Google BigQuery.”

Why BigQuery in particular?

“SEO analysis in BigQuery is powerful because it lets you move beyond the existing tools like Google Search Console, Ahrefs, etc., and their limitations. It allows you to see the full picture at scale.

By analysing millions of queries alongside other data, you uncover deeper patterns, user behaviour insights, and real opportunities to grow your traffic. We are focussing on BigQuery because of a subtle limitation of the most widely used tool for SEO, Search Console: you can only look at the top 1,000 keywords.

For big businesses, that doesn't give a full picture. That's where tools like BigQuery come in. There are alternatives like Google Sheets plugins, where you can import 25,000 rows. However, if you are in an industry with thousands of keywords, that is still a huge limitation.

That's where bulk export comes in. By connecting your Search Console to BigQuery, through the BigQuery API and the BigQuery Storage API, you can capture and store all your data at scale, without hitting those raw limits.

This is where it really gets interesting, because the retention policy in Google Search Console is 16 months. After 16 months, the data is lost. In BigQuery, you can keep it for as long as you want.

Then, when you take those insights and combine them with your analytics data – in GA4, for example – suddenly you're not just looking at what people are searching for, but how they behave when they land on your site. You can connect the dots between search demands, journeys, and conversions, and it makes the game more interesting.

Not enough SEOs are aware of this. They just think of BigQuery as a database where you kind of store your data, which is true, but when you talk about the analysis of your historical data set, that's where it all changes.

When you go to legal, they will ask you questions about retention and what you are storing. You need to spread that knowledge around to say that this is something that could be changed in BigQuery, and not just for SEO, but for things like analytics as well.”

Are there ways to manipulate data within BigQuery that you can't do natively?

“Yes. As I said, BigQuery contains all your data in a tabular format. There is a table of all of the data from GA4 or Search Console, for example.

For Search Console, you will have all your rows: clicks, keywords, site URLs, impressions, etc. Now, you could also create calculated columns or metrics and look at those numbers, not only as absolute figures, but as a percentage as well. The same is true for GA4. You can create your own logic and metrics in BigQuery.

While that is possible in GA4, BigQuery gives you more flexibility because you get access to data at the hit level, so you don't have to work at the session level or the user level. That's where SQL comes into the picture, because you have to learn it to be able to input useful prompts and use appropriate logic.

To combine Google Search Console data with GA4, there has to be an identifier to connect these data sets, and it changes the entire game.”

Should SEOs start importing data to BigQuery as soon as possible, so that the data is at least saved, and then they can use it in the future?

“Definitely. The disadvantage of using BigQuery is that, when you connect it, the data doesn't become available retrospectively. You will only be able to see the data from the previous two days.

Make sure you connect it ASAP. Even if you don't use it, just store it. It would definitely help to have your team equipped with the language they need to work in BigQuery, but at least you will have the data to work on, and you won’t regret it later.

Google has provided a whole lot of documentation on how to make those connections. You just have to follow them, step-by-step.

For Search Console, the connection is not very direct. You just don't have a link for BigQuery in the UI that you can just click to connect the project. It doesn't work like that. You need to use the BigQuery API, and you need a little bit of technical knowledge to be able to do that.

In the businesses I have worked with, it's not the SEO team who does this; it's the service desk team or an implementation team that has a bit of technical background. It’s not a significant challenge.

In GA4, the connection is readily available once you have created a project in BigQuery. That is very easy.”

How do you blend Search Console and GA4 data in BigQuery?

“When I first started looking at SEO data, I remember being asked why Search Console clicks didn’t match the sessions in GA4. We need to understand that these two tools are very different to each other. While GA4 works on sessions, users, and events, Search Console works on clicks, impressions, and positions.

Search Console gives data based on the impressions for the results that a user sees on Google search results. Once the user clicks that link and lands on your website, that's when Google Analytics comes into the picture, and your sessions start. Those metrics are different.

If you’re comparing those metrics, sessions and clicks should more or less match. If there is a slight difference, you can ignore it. If the difference is significant, that's where you need to look at your analytics or the SEO configuration that you're working with.

It could be because of the cookie wall, where GA4 is not capturing data when you don’t give consent. Maybe a few of the landing pages are missing tags, or the time zone could be different in Google Analytics and Search Console. Search Console doesn't use any attribution; it just collects your organic search data, as and when the user clicks. GA4, however, uses three types of attribution: last touch, direct last touch, and data-driven, and that could affect your numbers.

You need to go back to the basics and understand those differences before you try to blend the data. When I say blend that data, it doesn't mean that you connect the Search Console data to Google Analytics and find out everything, bit by bit.

In SEO terms, it means looking at your organic and making sure that whatever channels you're looking at in GA4 are based on attribution models. You need to find the dimensions that you're looking at to be able to match those numbers. Those are some of the things that you want to keep in mind before drilling further into your journeys on the website.”

Is it worth exploring other sources of data to help you build a more holistic picture of what's happening with your digital marketing activities?

“Yes, why not? You can connect data from places like YouTube and even Google Ads, for example. It depends on the business’s needs. Most businesses just want to look at their SEO performance, and they want to have Google Analytics so they can understand behavioural performance on the website.

If you're using social media and you're posting your content on YouTube, that is coming from a different channel, which won’t necessarily be organic. If it’s paid, that performance analysis is going to be different. There is a decision that needs to be made on which sources can be combined and which can’t.

That’s not even mentioning offline data. If you're getting conversions through offline data, even that can go into BigQuery.”

How do you use BigQuery on a regular basis to provide business insight and share that with other stakeholders?

“The prerequisite you definitely need is SQL. Most of the data analysis part lies with a team like data engineering or data analysis, that already knows SQL.

You can approach it in parts. Pick one section of SQL, learn it, move to the next section, and then merge them together. Then you can get into BigQuery. Technical SEOs would know that already, but the other areas of marketing might not.

If you want to analyse data in BigQuery without too much dependency on other teams, you can just learn SQL. Now, we have AI. Google has just made the BigQuery game smarter because they have introduced an AI engine search inside it, where you can use AI to make queries for you in simple terms. You give the AI instructions, and it will give you SQL queries. You can even ask the AI to give you graphs or other things, using your data.

You just have to learn basic SQL, and then you will get faster insights, through less manual work, and achieve scale with your data. You don't have to depend on any other team. I myself started using BigQuery a year ago. I didn't know SQL, but I learned it.”

Can you use AI to walk you through the setup, or is it best to reach out to a BigQuery professional to begin with?

“Different industries come with different constraints. At CompareTheMarket, we have BigQuery, and I had never worked with it before. I knew SQL, but not the structure, and every company has a different structure for it. There’s always a question about where the ownership lies – whether an engineer needs to run it, can machine learning run it, etc.

First, get in touch with an expert who has set up BigQuery. Even if you're not technical, I'm sure there are great, experienced people in the organisation who can explain it to you in very simple terms. Understand the setup and the workflow for your organisation, and then dive into learning it by yourself.

I learned SQL at a very basic level, but then I used AI to give me queries at a deeper level. There are relatively basic things that I struggle with, so I use AI to help me write those. The tricky part is that you have to give the AI a proper prompt to get what you want. You can't just copy and paste. That's where basic SQL knowledge is required.

Reach out to an expert, and then dive in by yourself – and maintain consistency. Don’t use it for a week, forget about it, and then come back to it. It will not make sense. It's like your health. You have to go to the gym every day to tone your body. Keep practicing, and it gets better.”

Priya, what's the key takeaway from the tip you shared today?

“Your SEO is bigger than 1,000 keywords, and BigQuery is where the real story begins.

You have experts around you, and you have AI. BigQuery opens the doors to way more insights than Google Search Console, Google Sheets, and GA4. Upskill yourself, share what you learn, and let AI help you turn that data into action.”

Priya Verma is Senior Digital Analytics Implementation Engineer at CompareTheMarket. Find out more over at CompareTheMarket.com.