Almost all businesses today have an online presence. With rapidly growing data volumes, it’s crucial to capture relevant data through your website and gain valuable insights from it.

Google Search Console is an effective tool to optimize your website’s performance and maximize its benefits. However, if you want to dive deep into emerging trends and patterns through data analysis, it is best to load datasets into cloud warehouses like BigQuery.

This article will guide you through the steps to move your data from Google Search Console to BigQuery.

What is Google Search Console?

Originally launched as Webmaster Tools, Google Search Console is a free platform designed to monitor and optimize the organic presence of your website. You can utilize this tool to gain valuable insights about your website’s performance, most viewed pages, impressions, and keywords. 

It is important to note that you cannot make modifications to your website directly through the Google Search Console. However, you can leverage the functionalities to boost your website’s visibility. The reports generated by the platform provide crucial information to help you address domain-wide errors, ensuring a healthier web presence.

Some of the helpful reports generated by Google Search Console are:

• Page Indexing Report: Provides insights into the indexing status of your websites’ URLs in the Google index. The two major components of the report include:
  
  
  • Summary Page: Offers an overview of your indexed and non-indexed web pages. Additionally, there are tables that highlight why certain URLs were not indexed and suggestions for increasing the number of indexed pages.
  • Details Page: Features a graph that demonstrates the number of affected pages as well as an examples table that shows specific indexing issues for particular URLs.
    
• Sitemaps Report: A sitemap is a structured file that provides Google and other search engines with information about various content types on your website. Google Search Console’s Sitemap Report helps you inform Google about your new sitemaps. You can view the status of all sitemaps you have submitted and any errors that may have occurred during the parsing of sitemaps.
  
• Performance Report (Search): The Performance Report is a powerful tool that displays important website metrics, like your click-through rate, allowing you to see the trends and changes in your website’s search traffic. It highlights specific search queries that boost your website’s visibility, separating them from queries made through mobile platforms. 

What is Google BigQuery?

Image Source

BigQuery is an enterprise-grade, fully managed cloud data warehouse. It has a wide range of built-in functionalities, including machine learning, geospatial analysis, and business intelligence capabilities.

Data is stored in a columnar format for enhanced query performance. You can use ANSI SQL queries to perform analytical and aggregation functions on your data. This ensures BigQuery’s infrastructure is optimized for conducting intricate data analysis so you don’t have to manage the complexities of resource allocation. 

Let’s look at some of the key features that BigQuery provides:

• Machine Learning: You can create and run machine learning models within your database through GoogleSQL queries. The BigQuery ML functionality can be used through:
  
  
  • Google Cloud Console
    
  • BigQuery’s REST API
    
  • BigQuery’s Integrated Colab Enterprise notebooks
    
  • Command-line tool bq
    
  • External tools such as Jupyter notebook
    
• BigQuery BI Engine: The BigQuery BI Engine can accelerate SQL queries from various data visualization tools and allows clustering and partitioning techniques on large tables in your data.
  
• Third-Party Visualization Tools: You can easily integrate business intelligence tools in BigQuery to boost the performance of your projects. BigQuery supports third-party visualization tools like PowerBI, Tableau, and Looker Studio, which help you create interactive dashboards and reports for quick ingestion and distribution of data.
  
• BigQuery Sandbox: If you want to try BigQuery before making any purchases, you can use the Sandbox tool. This free tool has many of the features that BigQuery offers, allowing you to conduct testing and experimentation for smaller projects.

Methods to Load Data from Google Search Console to BigQuery

Now that we’ve looked at some of the functionalities of Google Search Console and BigQuery, let’s dive into the practical methods for transferring your data. Here are two easy-to-follow methods:

Method 1: Using Estuary Flow to Migrate Data from Google Search Console to BigQuery

The challenge of transferring large data volumes has been significantly simplified, thanks to no-code ETL tools like Estuary Flow. This platform comes with an extensive range of pre-built connectors to help you move data from sources like Google Search Console to popular destinations such as BigQuery without writing a single line of code.

Estuary Flow has a cloud-native design that makes use of connectors to migrate data from the source to the destination. 

To load data from Google Search Console to BigQuery through Estuary Flow, create a free account or log in to your Estuary Flow account and follow these steps:

Step 1: Set up Google Search Console as the Source

• On the Estuary Flow dashboard, click the Sources tab, which is located on the left side navigation bar. 

• On the Sources page, click the + NEW CAPTURE button.

• Type Google Search Console in the Search connectors box.
  

• Once you find the connector, click the Capture button to set it as your source.
  
• On the Create Capture page, under the Capture Details section, provide a unique Name. 

• For the Endpoint Config, provide the Start Date. It is also recommended that you fill in the End Date and Custom Reports fields. 
  
• There are two ways to authenticate your Google Search Console account. You can either use OAuth2 or manually authenticate by generating a Service Account Key Authentication.

• Once you have correctly filled the fields, click NEXT, and then SAVE AND PUBLISH.

Step 2: Set up BigQuery as the Destination

• Go back to the Estuary dashboard and click the Destinations tab.
  
• On the Destinations page, click the + NEW MATERIALIZATION button.

• Type BigQuery in the Search connectors box.
  
• Once you find the connector, click the Materialization button to set it as your destination.

• On the Create Materialization page, fill in the mandatory fields under Materialization Details and Endpoint Config. These include a unique Name, Project ID, Service Account JSON, Region, Dataset, and Bucket.

• If the data captured from Google Search Console was not filled in automatically, you can add it by clicking the Link Capture button under the Source Collections section.

• Finally, click NEXT and then SAVE AND PUBLISH.

With just two straightforward steps, you have successfully created a data pipeline from Google Search Console to BigQuery using Estuary Flow! To learn more about the pipeline setup, you can refer to Estuary Flow’s detailed documentation:

• Google Search Console Capture Connector
  
• BigQuery Materialization Connector

Method 2: Using Bulk Data Export to Migrate Data from Google Search Console to BigQuery

Bulk data export is a new feature from Google to help you move your data from Search Console to BigQuery. The export includes all performance data, excluding anonymized queries that are filtered out for privacy reasons. Here are the steps required to start a new bulk data export:

Prerequisites

• Set up a Google Cloud project with billing.
  
• Enable API and BigQuery Storage API by visiting the APIs & Services tab in the Cloud Console sidebar.

Step 1: Configure your Google Cloud Console Account

• Open your Google Cloud Console and choose the Google Cloud project where you want to export the data.

Image Source

• Go back to the sidebar and click on the IAM and Admin tab.

Image Source

• A new page that displays Permissions for your project will open.
  
• Click + GRANT ACCESS, which will then open a side panel, Ad Principals.
  
• Under the New Principals section, paste the service account name:

search-console-data-export@system.gserviceaccount.com

• Grant this account two roles: BigQuery Job User and BigQuery Data Editor.
  
• Confirm all the changes by clicking Save.

Image Source

Step 2: Configure your Google Search Console

• After setting up your Google Cloud project, open your Google Search Console account.
  
• Click on Settings and go to Bulk Data Export.
  

Image Source

• Only copy the project ID for your Google Cloud Console project in the Cloud project ID field.
  
• Choose a suitable name for the dataset you are migrating. Remember, all dataset names must start with the string searchconsole.
  
• Select the location you want for your dataset, but be aware that you cannot change it once your data export is underway.
  
• Click Continue to begin the scheduled data exports.

Image Source

The first data export will occur within 48 hours after you successfully configure the Search Console with BigQuery. If there are non-persistent errors, such as dataset location mismatch or missing permissions in the Cloud project, the migration will be pushed to the following day. You cannot change your dataset’s schema during or after the migration process because that may cause the export to fail.

Takeaways

Google Search Console offers powerful insights for free, and BigQuery’s pay-as-you-go model makes it a cost-effective choice. These combined capabilities enable you to enhance your website’s performance while taking advantage of robust features.

In this article, we explored two methods of moving data from Search Console to BigQuery. However, if you want to save time and capture data changes in real time, Estuary Flow is the ideal choice. All materialization destinations with Estuary Flow are equipped to receive and update changes in your databases without any re-syncing. This gives you accurate information almost instantly, allowing you to make sound decisions for your business. 

Sign up for Estuary Flow today and transfer your data from Google Search Console to BigQuery in just a few minutes!

Discover more insights on integrating Google Search Console data with other destinations:

• Google Search Console to Snowflake
• Google Search Console to Redshift