Tuesday, February 21, 2023
Today, we're announcing bulk data export, a new feature that allows you to export data from Search Console to Google BigQuery on an ongoing basis. (Note that the rollout will take approximately one week, so you may not have access right away.)
You can configure an export in Search Console to get a daily data dump into your BigQuery project. The data includes all your performance data, apart from anonymized queries, which are filtered out for privacy reasons; in other words, the bulk data export is not affected by the daily data row limit. This means you can explore your data to its maximum potential, joining it with other sources of data and using advanced analysis and visualization techniques.
This data export could be particularly helpful for large websites with tens of thousands of pages, or those receiving traffic from tens of thousands of queries a day (or both!). Small and medium sites already have access to all their data through the user interface, the Looker Studio connector (formerly known as Data Studio) or the Search Analytics API.
Setting up a new bulk data export
To configure a new report, you'll need to prepare your BigQuery account to receive the data and set up your details in the Search Console settings. Check the Help Center for a step-by-step guide, but in general, the process is divided into two stages:
- Prepare your Cloud project (inside Google Cloud Console): this includes enabling the BigQuery API for your project and giving permission to your Search Console service account.
- Set export destination (inside Search Console): this includes providing your Google Cloud project ID, and choosing a dataset location. Note that only property owners can set up a bulk data export.
Once you submit the information to Search Console, it'll simulate an export. If the export succeeds, we'll inform all property owners via email and your ongoing exports will start within 48 hours. If the export simulation fails, you'll receive an immediate alert on the issue detected; here's a list of possible export errors.
Data available on bulk data exports
Once the bulk data export is set up successfully, you can log in to your BigQuery account and start querying the data.
You can find detailed table guidelines and references in the help center; also check the explanation on the difference between aggregating data by property vs by page, as it'll help you understand the data better. Here is a quick description of the three tables that will be available to you:
searchdata_site_impression: This table contains data aggregated by property, including query, country, type, and device.searchdata_url_impression: This table contains data aggregated by URL, which enables a more detailed view of queries and rich results.ExportLog: This table is a record of what data was saved for that day. Failed exports are not recorded here.
If you need a little help to start querying the data, check the sample queries published in the help center, they can be handy to get up and running. Here's one example, where we pull the total query by URL combinations for pages with at least 100 FAQ rich result impressions over the last two weeks.
SELECT url, query, sum(impressions) AS impressions, sum(clicks) AS clicks, sum(clicks) / sum(impressions) AS ctr, /* Added one below, because position is zero-based */ ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position /* Remember to update the table name to your table */ FROM searchconsole.searchdata_url_impression WHERE search_type = 'WEB' AND is_tpf_faq = true AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) AND CURRENT_DATE() AND clicks > 100 GROUP BY 1,2 ORDER BY clicks LIMIT 1000
We hope that by making more Google Search data available, website owners and SEOs will be able to find more content opportunities by analyzing long tail queries. It'll also make it easier to join page-level information from internal systems to Search results in a more effective and comprehensive way.
And as always, if you have any questions or concerns, please reach out to us via the Google Search Central Community or on Twitter.