Take a quick demo tour of Connect to see the added benefits you’ll have when you partner with Adswerve. Get access to hundreds of training videos, the latest industry news, your account and billing information and proprietary tools and applications to help you get more done—plus, so much more!
Subscribe to our monthly newsletter to get the latest updates in your inbox
Google recently announced a Search Console data export to BigQuery called the bulk data export. Previously exporting data from search ads was only possible via an API (or a service on top of it). This simplifies the lift to get access to the raw data powering the Search Console dashboard with just a few clicks. The export itself comes at no additional cost*.
*Once the new data is in BigQuery it is subject to BigQuery's storage and processing cost.
How to set up the export?
In your search console navigate to settings in the right menu.
2. Under General settings, select the Bulk data export
3. In your Google Cloud Project, you will have to add a new principal to the IAM page. Adding the search-console-data-export@system.gserviceaccount.com will allow Search Console to write new data into your BigQuery account. If at any point, the user gets removed, the export will stop. Make sure to add BigQuery Job User and BigQuery Data Editor permissions.
4. Confirm your export settings and set up the export. If permissions were set properly and the project id is correct, you will see a "Setup completed successfully" message.
6. As stated in the message it may take up to 48h for the export to start, however, you will see a new dataset (searchconsole), with a small easter egg from Google's team in your BigQuery.
Export Overview
Once the export becomes active, you will notice three new tables:
An export log (ExportLog), where you will find information about the export time and data date
Day partitioned site impressions table (searchdata_site_impressions), which includes impressions, clicks, and position sum as the three metrics and information about the search as the dimensions.
Day partitioned individual url impressions table (searchdata_url_impressions), which includes the same three metrics as the site impression table, but focuses on individual pages of your site. Beside the specific url it also includes page descriptors such as is_amp_story, is_video, etc.
Simple queries to get you started
1. Looking at total impressions, clicks and average position per day for the whole site:
3. Most popular pages (measured by click) outside of the US
1
2
3
4
5
6
SELECTurl, sum(clicks) asclicks
FROM`searchconsole.searchdata_url_impression`
WHEREcountry != "usa"
GROUPBY1
ORDERBY2 DESC
LIMIT 50
4. By exporting search console data and your GA4 property, you can now join the two on the page URL. In the query below we look at organic sessions, clicks and impressions for individual pages.
*For more information about the GA4 export visit our GA4 export guide.
As you can see from the few examples above, the export holds very useful information about your site's search performance and rankings. Joining this data with other sources, such as your site's analytics allows you to understand user behavior on and off the site in the same report. So make sure to turn the export on as soon as possible since it does not come with a backfill.
If you have any questions or would like us to help you set up the new export feel free to reach out.