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:

1. 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.
2. 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.

Posted by Daniel Waisberg, Gaal Yahas, and Haim Daniel, Search Console team