Tutorial: Connecting Google Search Console to BigQuery

Tutorial: Connect Google Search Console with BigQuery (google search console data to bigquery)   Minutes reading time remaining By Antonio Blago November 28, 2024

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

E-commerce SEO Case Study for PURELEI

E-commerce SEO Case Study: PURELEI.com +100% visibility in 8 months In October 2024, it started

Read  more

Case study, SEO, Strategies

E-commerce SEO Case Study for PURELEI

E-commerce SEO Case Study: PURELEI.com +100% visibility in 8 months In October 2024, it started [...]

Case study, SEO

Case Study: How We Generated Over €1.2 Million in Revenue with Targeted Blog Content

Brief overview A fast-growing brand was able to generate over €1.2 million in revenue within 12 months through targeted content strategies and [...]

Automation, AI, SEO

Finally Accessible: Efficiently Fill ALT Texts with AI

5 (1) Practical example in Shopify In my job as an SEO freelancer [...]

Analysis, AI, SEO, SEO Tools

AI Prompt Keyword Mapper

0 (0) How to analyze prompts automatically Nowadays, prompts – that is [...]

AI, AI Tools, SEO Tools

ChatGPT German: Use Chat GPT for Free Without Registration

ChatGPT, the advanced language model from OpenAI, is revolutionizing the way [...]

SEO, Shopify

Part 2: Automating Shopify Redirects: Connecting Python Sitemaps, Excel & Matrixify

0 (0) 📖 Part of a series: Why missing redirects cost you traffic [...]

Use my SEO roadmap to get on page 1 of Google!

Sign up for my newsletter and get access to free guides, checklists, and tools.