Tutorial: Connecting Google Search Console to BigQuery

Biq Query, Advanced SEO, Google Search Console, Tutorial

Adding Google Search Console (GSC) to BigQuery allows you to analyze SEO data (e.g., analyze brand vs. no brand traffic) more efficiently and combine it with other data sources. This tutorial guides you step by step through the process.

Prerequisites

Google Account: Here is the tutorial. Google Search Console Access: You need access to a GSC property that you want to link with BigQuery. ( Here to the tutorial ) Google Cloud Platform (GCP) account: Make sure you have access to a project in the Google Cloud Platform BigQuery API enabled: Make sure the BigQuery API is enabled for your GCP project: https://support.google.com/webmasters/answer/12917675?hl=de.

Step-by-Step Guide

1. Enable GSC Data for BigQuery

Log in to Google Search Console ( Link to GSC ). Go to the property you want to link. Navigate to Settings > Associations . Select BigQuery and click Add Association . Select the GCP project to which the data should be exported. Important: Only after this connection is established will the data be exported daily. It does not work retroactively! 2. Check the Data in BigQuery Open BigQuery in the Google Cloud Console. Navigate to your dataset. There you will find tables with the exported GSC data, e.g., site_impression or site_url .

Example: Data Analysis in BigQuery

After the data has been imported, you can create SQL queries to perform specific analyses. Here is a simple example:

Query: Top Keywords by Clicks

SELECT
  query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  AVG(position) AS avg_position
FROM
  `your_project_id.search_console_data.site_impression`
GROUP BY
  query
ORDER BY
  total_clicks DESC
LIMIT 10;

Tip: Replace your_project_id with the ID of your GCP project.

Analysis with Python

Generic (No Brand) vs Brand Traffic

This traffic comes from search queries that do not contain brand names or specific product names. Instead, these queries are based on general terms , categories, or product descriptions.

Further Links:

Python Tutorial: Here is the link to Data Science SEO with Python Overview of conducting SEO analyses

Important Notes

Costs: BigQuery charges for stored data and queries. Keep an eye on the Google Cloud cost calculator . Automatic Updates: GSC data is updated daily in BigQuery. Data Control: Delete unused datasets to save costs.

Conclusion

By linking Google Search Console and BigQuery, you can gain valuable insights from your SEO data and make informed decisions. Use SQL queries to create reports or use the data for dashboards in tools like Data Studio. If you need support, feel free to schedule a consultation here . 📖 Part of the Google Search Console & Google Tools series: Google Search Console Login Setting up a Google account – Guide Add users to Google Search Console – Step by step Google Search Console Indexing Performance Checker (Script) Google Search Console & BigQuery connect – Guide Google Search Console – An Overview Technical SEO: Find 404 errors with Google features Page with redirect in Google Search Console Google Analytics 4 Login