bulk data export of Search Console data to BigQuery

/ Uncategorized / By

What is bulk export of GSC data to BigQuery?

Announced in February 2023, this bulk data export feature allows you to export data from Google Search Console (GSC) to Google BigQuery on an ongoing basis.

The bulk data export is not affected by the daily data row limit, meaning you can get the biggest amount of data whereas data on GSC is sampled.

Why should SEO use the bulk data export feature?

  • No row limits using BigQuery: When comparing with other available ways to export GSC data, Bulk Data Export is the most powerful way as you can get the biggest amount of data as Google explains. While you can only access up to 1,000 rows of data in the GSC dashboard and 5,000 rows of data via a Looker Studio integration, you’ll get all the queries and pages available on GSC with this new feature. This is great news to large websites that usually look to access full dataset for new business opportunities and accurate reporting.
  • Data analysis: Data can be exported to another system or further analyzed by running SQL in Google BigQuery. (More sample queries will be discussed below)
  • Highly affordable: Start with a free package which is already pretty generous.
  • Data retention: Data will accumulate indefinitely unless you set an expiration time, making it easier to retrieve historic data for analysis purposes.
  • No API setup
  • Automated daily bulk export of GSC data: That means your GSC data exports should arrive at your BigQuery project every day without you needing to manually export the data.

How to set up bulk data export

While there are a good few of steps, the process is pretty easy-to follow and quick. Check the guide here that involves tasks in both Google Cloud and GSC. Bulk automated export will start in the next 48 hours after your successful configuration.

Sample queries

Example 1: Find URLs by clicks or impressions

This is a straightforward yet very useful query to quickly see the high-performing URLs by click in a selected date range. You can modify ”data_date” and ”order_by” to display the data in your way.

SELECT
  url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks
FROM
  `searchconsole.searchdata_url_impression`
WHERE
  data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
  url
ORDER BY
  clicks DESC

Example 2: Find queries by clicks or impressions

Similar to example 1, you can see the high-performing queries by click in the selected date range by replacing ”url” with ”query” in line 2.

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks
FROM
  `searchconsole.searchdata_site_impression`
WHERE
  data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
  query
ORDER BY
  clicks DESC

Example 3: Find high intent queries by clicks/imps

I find this one very useful – not only can you find the high-intent queries such as software/platform relate queries, but also change line 10-11 to find any other query topics. For instance, if you add a new service or product to your business, you may want to know the performance of the new offering.

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks
FROM
  `searchconsole.searchdata_site_impression`
WHERE
  data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND query like '%software'
  OR query like '%platform'
GROUP BY
  query
ORDER BY
  clicks DESC

Example 4: Find high intent query with 0 click

Based on the last sample, I added one more criteria to see any high-intent queries receiving zero clicks. From there, you may then prioritize to work on this group of queries in the hope of getting more traffic from high-intent keywords. 

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks
FROM
  `searchconsole.searchdata_site_impression`
WHERE
  data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND query like '%software'
  OR query like '%platform'
  AND clicks = 0
GROUP BY
  query
ORDER BY
  impressions DESC

Example 5: Classify branded vs non-branded KWs

WITH gsc_data AS (
SELECT query, SUM(clicks) AS clicks, SUM(impressions) AS impressions
FROM `searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY query
)
SELECT query,clicks,impressions,
 CASE
    WHEN regexp_contains(query,"query1|query2|query3") then "Branded_KW"
    else "Non-Branded KW"
 END AS Branded_or_NonBranded
FROM gsc_data
ORDER BY clicks DESC;

Example 6: Compare query month over month

Saw traffic drop this month vs last month? This one can help you find out more..

SELECT
  query,
  SUM(CASE WHEN data_date BETWEEN '2024-01-15' AND '2024-01-21' THEN clicks ELSE 0 END) AS clicks_15_to_21,
  SUM(CASE WHEN data_date BETWEEN '2024-01-22' AND '2024-01-28' THEN clicks ELSE 0 END) AS clicks_22_to_28,
  SUM(CASE WHEN data_date BETWEEN '2024-01-22' AND '2024-01-28' THEN clicks ELSE 0 END) - SUM(CASE WHEN data_date BETWEEN '2024-01-15' AND '2024-01-21' THEN clicks ELSE 0 END) AS click_difference
FROM
  `searchconsole.searchdata_site_impression`
WHERE
  data_date BETWEEN '2024-01-15' AND '2024-01-28'
GROUP BY
  query

Google also provides us with a couple of sample queries to get started. Want to retrieve any other kind of data from BigQuery but are not familiar with SQL? ChatGPT can help you generate GSC BigQuery queries without requiring any SQL knowledge. 

I only started exporting data since Q4 last year but have already discovered many untapped keywords, which is incredibly inspiring. With the help of ChatGPT or other tools, there is no excuse for me to refrain from leveraging this invaluable resource to its fullest potential. Since it’s easy an quick to set up without any initial cost, I highly recommend setting it up as soon as possible because you will only be able to access the data from this point forward.

Scroll to Top