On February 21, 2023, Google announced a feature, Bulk data export, to send Google Search Console data to BigQuery. With it, only verified property owners can export bulk data from Google Search Console to their BigQuery project.
A few months later, on October 24, 2023, Coupler.io added Google Search Console integration with their supported destinations, including BigQuery. This option is more flexible since it allows you to choose what you want to export, as well as specify the report period. However, you still need to be a verified property owner to use it!
These are basically the two most reliable methods to connect Google Search Console to BigQuery. In this guide, we’ll explore each of them and explain whether you can opt for any other solution.
Connect Google Search Console to BigQuery with Coupler.io
Let’s start with Coupler.io since it offers a more analytical-friendly way of exporting data from Google Search Console. It not only connects GSC to BigQuery and automates data refresh, but it also allows you to make your data analysis-ready before it lands in the specified BigQuery table.
1. Collect your Google Search Console data
To start using it click Proceed in the form below – this will create an automated importer to load data from Google Search Console to BigQuery.
After signing up for Coupler.io for free, log into your Google account and choose the Google Search Console property (sites) from where you want to extract data. You’ll also need to select the report type: performance of search results, discover, Google News, and URL index.
Set the date range for the report period. By default, the importer uses macros {{6monthsago}} as a start date and {{today}} as the end date. Feel free to modify the report period if needed.
Optionally, you can also select the dimensions like date, device, page, and others for your report, and add filters.
2. Organize and transform data
Check the data from the Google Search Console that you’re going to export. You can not only preview but also:
- Manage columns (rename, edit column data type, and hide the unneeded columns)
- Filter or sort columns
- Add custom columns using the supported formulas.
If your data looks correct, click Proceed.
Now you’re ready to connect your BigQuery account. For this, you will need to provide the Key file for your BigQuery project. Then, enter the names of the dataset and table where your Google Search Console data will be loaded. You can create a new dataset and table by typing new names. The Autodetect table schema feature is enabled by default. This allows you to structure your data correctly.
Note: I assume that you already have a BigQuery project with billing enabled. If you don’t, it makes sense to check out this BigQuery tutorial first and then get back here. This is an essential prerequisite if you want to load data from Google Search Console to BigQuery.
3. Schedule importer
The last step is meant to automate sending Google Search Console data to BigQuery at a custom frequency. Toggle on Automatic data refresh and configure the desired schedule. Coupler.io allows you to schedule data exports as frequently as every 15 minutes!
Well, that’s it, you’re now ready! Click Run importer to load data and actually connect Google Search Console to BigQuery. From now on, Coupler.io will automatically load Google Search Console data to BigQuery dataset. Check out what it looks like in BigQuery.
Closed-loop analytics of Google Search Console data in BigQuery
I’m pretty sure that you’re familiar with the concept of closed-loop analytics. It’s a process of collecting and analyzing the information brought together from multiple data sources.
In practice, you can load data from Google Search Console, Google Analytics, and Google Ads to BigQuery for a cross-analysis. This way, you can examine the performance of your URLs and assess the traffic that comes from organic search and paid ads.
Coupler.io is a perfect tool if you need to perform closed-loop analytics since it supports 50+ apps that you can connect to BigQuery. You can create separate importers for all the needed data sources, for example like this:
This way, you’ll get separate datasets in BigQuery.
Another option is to stitch data from different sources together. Coupler.io allows you to add multiple sources within one importer. The data from these sources will be stitched together and loaded to one selected destination.
With your datasets in BigQuery, you can combine the different types of metrics loaded from your SEO and marketing tools:
- From Google ads – clicks, ad spend, impressions, click-through rate (CTR), keyword performance, geographical data, and so on.
- From GA4 – conversions, website session length, page views, user demographics, traffic sources, and so on.
- From GSC – clicks, impressions, URL position, ctr, device, and so on.
Now, you can analyze all this data together and see, for example, the worst and best-performing URLs from the perspective of organic and paid traffic.
To ease your job, Coupler.io even provides you with ready-to-use dashboard templates designed in Looker Studio. For example, here is what the SEO dashboard for Google Search Console looks like.
The template already has a preset Google Search Console to Looker Studio, so you only need to make a few clicks to start using the dashboard for SEO analytics. At the end of the day, you’ll get an analytics solution that provides different insights into your organic performance, like click dynamics, best and worst-performing URLs, the number of indexed pages, etc.
Send data from Google Search Console to BigQuery using the Bulk Data Export
Now, it’s time to explain what Bulk Data Export is and how it works. In Google Search Console, go to Settings, then select Bulk data export.
Warning: Only owners or verified owners can carry out bulk data exports from Google Search Console.
You’ll see two steps to complete for the bulk data export:
- Prepare your Cloud project – it’s about setting up a Google Cloud project with billing enabled and turning on the BigQuery API. You can review the instructions if you hesitate about your Cloud project status.
- Set export destination – you need to enter your Cloud project ID, and specify the dataset location.
Click Continue.
Note: The dataset name for the exported tables is chosen by the Search Console. You cannot modify it.
The last step is to confirm your settings and launch the export.
In the next 48 hours, your data export will start and you’ll be notified about the successful export via email. Of course, if it fails, you’ll be informed as well.
Keep in mind that the first export includes data for the day of the export. If you want to see historical data that precedes your initial setup date, you can use Coupler.io, the Search Console API, or export reports from Google Search Console manually.
From the day you set up the Bulk data exports, Google Search Console will export each day’s data to your tables in BigQuery. If you want to cancel the bulk data export, you’ll need to navigate to the bulk data export settings page and click Deactivate export.
What data do you get with bulk data export by Google Search Console?
Bulk data export from Google Search Console to BigQuery loads data to the searchconsole data set in three tables:
- searchdata_site_impression: data aggregated by property, including query, country, type, and device.
- searchdata_url_impression: data aggregated by URL, which enables a more detailed view of queries and rich results.
- ExportLog: a record of what data was saved for a particular data. Failed exports are not recorded here.
With your Google Search Console data in BigQuery, you can start querying the data. Here is an example of a query to get all web stats by day for the last two weeks:
SELECT data_date AS date, sum(impressions) AS impressions, sum(clicks) as clicks, sum(clicks) / sum(impressions) AS ctr, /* Added one below, because position is zero-based */ ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position FROM searchconsole.searchdata_site_impression WHERE search_type = 'WEB' AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() GROUP BY data_date ORDER BY date desc LIMIT 1000
Explore more query samples in Search Console help center.
Can I load Google Search Console data to BigQuery via the API?
Yes, you can. We’ve already mentioned the Search Console API as the option to retrieve historical data from Google Search Console. You can also use this API to load performance report data per day.
The API is free and supports filtering, sorting, aggregation type, and all other features available in the reports. You can implement a custom interface for the Search Console API using many programming languages, including .NET, Python, and others.
Extracting data from Google Search Console via the API is only one part. The second part is to load data to Google BigQuery. You have a few options here:
- Load data to Google Cloud Storage from where you can transfer it to BigQuery with either load jobs, SQL, BigQuery Data Transfer Service, and other options.
- POST data directly to BigQuery through the JSON API.
Each of the options looks quite complex and they are for non-tech-savvy users. Therefore, think twice about whether it’s the best way for you to connect Google Search Console to BigQuery.
Which option is best to load data from Google Search Console to BigQuery?
Bulk data export is the most affordable option to connect GSC to BigQuery. However, it has a few drawbacks:
- No historical data available for export
- You need to master queries on a high level in order to derive insights into your Google Search Analytics.
- Only verified property owners can use it.
A custom integration from Google Search Console to BigQuery via the API looks like the most pricey solution. Why? Because you need to hire a data analyst or data engineer to set up and maintain this connection. It can be fine for big companies and enterprises but not for small to medium-sized businesses.
In view of this, Coupler.io is the optimum solution from the perspective of budget and quality. It provides the Google Search Console to BigQuery connection without any coding required. In addition, you get a number of other integrations out of the box. For example, you can connect Google Search Console to Redshift and more destinations, not to mention ready-to-use dashboard templates in Looker Studio and Google Sheets.
The choice is up to you. Good luck with your data!
