Are you feeling FOMO when it comes to Google BigQuery? Getting anxious when you see article headlines talking about Google Analytics 4 and BigQuery? Here's an intro article to help you figure out if the switch to Google Cloud Platform and the bulk data export are worth your time.
First, let's start with why you'd even want to consider adding BigQuery to your list of SEO tools: Google Search Console (GSC) frustrates you. SEO experts often face limitations with Google Search Console: from row limits to unhelpful average position metrics, navigating through GSC can feel like a tedious chore. It involves clicking, some more clicking, using the Google Sheets export option and figuring it all out. Sometimes, some of us would dump the data in Google Data Studio (Looker Studio but I am still salty about that name change so humor me).
Common GSC challenges
This SEO tool UI has several drawbacks that can hinder your efforts:
Row limit: a maximum of 1,000 rows per report so your understanding of the site's search traffic is truncated.
Anonymous query data: lack of detailed insights into queries.
Data retention: only 16 months of data is available.
Data sampling: Google keeps some of the data from you.
Average position metric: often unhelpful for precise SEO analysis.
The BigQuery advantage
BigQuery addresses these limitations by offering:
Unlimited data retention: analyze historical trends beyond GSC's 16-month limit.
No row limit: you get all the available data from Google
Anonymous query data: you get data regarding those queries.
Advanced calculations with SQL: perform complex data manipulations that are cumbersome within GSC’s interface (and even, in some cases, in exports or with the API).
Data integration: combine GSC data with other sources (or even potentialy from multple GSC properties) for deeper insights.
If you get a lot of organic traffic and want to get serious about your content strategy, I strongly recommend you consider the Google Search Console Bulk Export. It offers a robust solution, allowing for extensive data analysis, unlimited data retention, and seamless integration with other data sources.
If you have a large website, we're talking enterprise levels, you should absolutely be using BigQuery. Set up a bulk export from GSC to BQ, it is free to set up and allows you to bypass GSC’s interface limitations and run complex queries directly on your data. by leveraging SQL. You can segment your data and perform detailed analysis without being an SQL pro.
Practical applications of BigQuery in SEO analytics
This opens up a plethora of opportunities for SEO analysis:
Analyzing page performance: calculate the percentage of pages with zero clicks.
Understanding query coverage: determine how many queries google is hiding from you.
Historical data analysis: BigQuery enables long-term trend analysis by storing and processing historical SEO data beyond standard tool limitations
Keyword rank tracking at scale: BigQuery integration with Google Search Console allows for more granular keyword data analysis beyond the 1,000 row export limit in GSC.
Custom reporting and visualization: Data from BigQuery can be easily exported to tools like Looker Studio (formerly Data Studio) or Tableau for creating comprehensive SEO dashboards
The stuff you need to understand about any Google Search Console property
Understanding how tables work in Google Search Console (GSC) is crucial before attempting to build custom reports.
The official documentation provides valuable information on this topic. Here's what you need to know if you use Looker Studio reports or BigQuery:
Two primary tables are generated: searchdata_site_impression and searchdata_url_impression. The first one aggregates data by property, so if two pages show up for the same query, it counts as one impression. The URL table is aggregated by URL, so it's a more granular view. Let's break it down for you:
searchdata_site_impression: This table gives you an overview of how your entire website is performing in Google Search. For example, if someone searches for “best sausage dog costume” and two pages from your website appear in the results, this table will count it as one impression (or one view) for your entire site rather than two separate views for each page.
searchdata_url_impression: This table is more detailed and focuses on individual web pages. Using the same example of “best sausage dog costume,” if two pages from your site show up in the search results, this table will count it as two separate impressions, one for each page that appears.
Another thing to keep in mind: not all types of data are exported. Coverage reports and other specialized reports are not. So make sure you know what is included in the daily export and what is not.
Get a cookbook
In the context of BigQuery and SEO, a "cookbook" refers to a collection of pre-defined, practical solutions, techniques, best practices, and example queries that can be used to perform various SEO analyses. This resource serves as a valuable guide for your team, helping them to quickly and efficiently extract insights from large datasets without needing to start from scratch each time.
A BigQuery cookbook is a curated set of SQL queries and analytical methods designed to address common SEO challenges and tasks. It includes:
Pre-written Queries: Ready-to-use SQL queries for specific SEO analyses, such as identifying high-performing pages, calculating unique query counts, and tracking performance over time.
Best Practices: Guidelines on how to structure and optimize queries for performance and accuracy.
Techniques: Detailed explanations of various analytical techniques and how to implement them using BigQuery.
Examples: Concrete examples that illustrate how to apply these queries and techniques to real-world SEO scenarios.
Benefits of a BigQuery Cookbook
Efficiency: Speeds up the analysis process by providing ready-made solutions.
Consistency: Ensures that all team members use standardized methods and queries.
Knowledge Sharing: Facilitates knowledge transfer within the team, making it easier for new members to get up to speed.
Scalability: Helps manage large datasets effectively, leveraging BigQuery's capabilities.
Accuracy: Reduces the likelihood of errors by providing tested and validated queries.
Training and further resources
Want to learn how to use the BigQuery Data Editor? Need to kickstart advanced analysis for a client? Get in touch! Augustin Delporte, one of our trainers, did the BrightonSEO BigQuery training and can help you too.
If you want to get started on your own, we recommend you get started with Robin Lord's SQL game.
Check Myriam Jessier's series on Search Engine Journal to get started.
Download the Sheffield DM slides that inspired this article.
Comments