Google Search Console to BigQuery: bulk data export setup and SQL

What the bulk export gives you that the interface does not

• All the rows. The Performance report shows and exports up to 1,000 rows. The export writes every query and page combination Google recorded. On the property used for this guide, 28 days came to 744,361 rows.
• History you keep. Search Console holds 16 months. Tables in BigQuery stay until you delete them or set an expiration.
• Joins. Once the data is in a warehouse, a landing page can be joined to its sessions, leads or revenue.
• Your own definitions. Brand against non-brand, page groups, 28-day periods: all of it is SQL you write once.

One thing the export does not give you: the past. Google does not backfill. Data starts on the day the export is set up, so it is worth switching on before you need it.

Before you start

You need:

• A Google Cloud project with billing enabled. The BigQuery pricing guide explains what storage and queries cost.
• Owner permission on the Search Console property.
• Rights in the Cloud project to enable an API and grant IAM roles.

Step 1: prepare the Google Cloud project

These steps follow Google’s bulk data export setup page, checked in October 2026.

1. In the Google Cloud console, open APIs & Services, then Enabled APIs & services, and enable the BigQuery API if it is not on the list.
2. Open IAM & Admin, then IAM, and click Grant access.
3. In New principals, paste search-console-data-export@system.gserviceaccount.com.
4. Give it two roles: BigQuery Job User and BigQuery Data Editor. Save.

Step 2: switch on the export in Search Console

1. In Search Console, choose the property and open Settings, then Bulk data export.
2. Enter the Cloud project ID. It is the ID, not the project name.
3. Keep the dataset name searchconsole, or add a suffix. A name always starts with searchconsole, and exporting several properties into one project needs a different name for each.
4. Choose the dataset location.
5. Click Continue, review, and confirm with Set up export.

Check the location twice. Google says it cannot easily be changed once exports have begun.

The first export lands within 48 hours. If a daily export fails, Search Console tries again the next day.

What the export creates

Three tables appear in the dataset. The descriptions come from Google’s table reference.

Both data tables are partitioned by data_date, which is in Pacific Time. A filter on data_date is what keeps a query cheap: BigQuery then reads only those days. More on that in partitioned tables.

Four things that catch people

1. Position is a sum, and it starts at zero. The site table has sum_top_position, the URL table has sum_position. Average position, as the interface shows it, is:

SUM(sum_position) / SUM(impressions) + 1

2. Anonymized queries have no text. For rare queries, Google sets is_anonymized_query to true and leaves query empty. They are not a rounding error. On our property in September 2026 they were 965,490 of 1,948,029 impressions and 2,384 of 3,372 clicks. A report filtered to query IS NOT NULL leaves most of the clicks out, so take totals from unfiltered rows.

3. The two tables do not add up to the same number. The site table counts a search once per property. The URL table counts it once per URL, so a results page showing two of your URLs gives two impressions. For the same 28 days we got 1,891,632 impressions in the site table and 1,948,029 in the URL table. Use the site table for totals and the URL table for anything by page.

4. Rows are not pre-aggregated. Google’s advice is to aggregate almost always. A single query and page can occupy many rows per day, split by country, device and appearance.

To see how fresh the data is, read the log:

SELECT namespace, data_date, publish_time
FROM `your-project.searchconsole.ExportLog`
ORDER BY publish_time DESC
LIMIT 3;

On 6 October 2026 the latest row was data for 4 October, published at 12:46 UTC. Expect a lag of about two days.

SQL for the questions SEO teams ask

Replace your-project with your project ID. Each result below comes from the owox.com export for 1 to 28 September 2026.

Top non-brand queries and pages by clicks

SELECT
  query,
  url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-09-01'
                    AND '2026-09-28'
  AND query IS NOT NULL
  AND query NOT LIKE '%owox%'
GROUP BY query, url
ORDER BY clicks DESC
LIMIT 5;

The url column holds the full address; the tables here show the last part. The NOT LIKE line removes searches for our own name. Put your brand there, and you have a non-brand report, which the interface can only approximate with a filter you re-enter each time.

For a report that moves with the calendar, replace the dates with DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY) and DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY). That is 28 full days, ending before the days the export has not delivered yet. The date functions reference covers the rest.

Pages on page one that nobody clicks

This is the list the interface cannot give you in one view: queries where a page sits in positions 4 to 10 and earned no click.

SELECT
  query,
  url,
  SUM(impressions) AS impressions,
  ROUND(SUM(sum_position)
    / SUM(impressions) + 1, 1) AS position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-09-01'
                    AND '2026-09-28'
  AND search_type = 'WEB'
  AND query IS NOT NULL
GROUP BY query, url
HAVING position BETWEEN 4 AND 10
   AND SUM(clicks) = 0
ORDER BY impressions DESC
LIMIT 5;

Thirty-five thousand impressions and no clicks is a title and snippet problem, or a results page where the answer is shown before the links. Either way it is a page to look at first.

Performance by device

SELECT
  device,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ROUND(SUM(clicks)
    / SUM(impressions) * 100, 2) AS ctr,
  ROUND(SUM(sum_position)
    / SUM(impressions) + 1, 1) AS position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-09-01'
                    AND '2026-09-28'
  AND search_type = 'WEB'
GROUP BY device
ORDER BY impressions DESC;

One query, week by week

SELECT
  DATE_TRUNC(data_date, WEEK(MONDAY)) AS week,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ROUND(SUM(sum_position)
    / SUM(impressions) + 1, 1) AS position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-09-01'
                    AND '2026-09-28'
  AND query = 'bigquery query history'
GROUP BY week
ORDER BY week;

The first and last weeks are partial, because the date range cuts them. Compare full weeks only.

What these queries cost

The URL table for this property is 10.35 GiB. The page-one query above read 87 MiB of it, because it touches only the September partitions and five columns. A query without a data_date filter reads the whole history every time it runs, and a dashboard that refreshes hourly multiplies that. Always filter on data_date.

From a query to a report that refreshes itself

A query in the BigQuery console answers the question once. The SEO lead who asks every Monday needs the same numbers again next week, with the same definition of “non-brand” and the same date logic, and should not need BigQuery access to get them.

OWOX Data Marts is built for that step. You connect the warehouse where the export lands, publish the table or a query on it as a Data Mart, and deliver it to Google Sheets on a schedule. OWOX Data Marts runs your SQL as written, in your BigQuery project. It does not transform the data.

1. Connect BigQuery as a storage

In your OWOX Data Marts project, open Storages, click New Storage and choose Google BigQuery. Enter the project ID and location of the Search Console dataset. The account you connect with needs the BigQuery Data Editor and BigQuery Job User roles; the storage setup guide has the details.

2. Create a Data Mart on the export table

Click New Data Mart, give it a title and choose the storage. On the Data Setup tab, set the definition type:

• Table, with the full name your-project.searchconsole.searchdata_url_impression, to publish the export as it is.
• SQL, to publish one of the queries above, for example the page-one list with no clicks.

For a table this size, SQL with a date filter is usually the better choice: each scheduled run then reads only the days it needs.

Then fill in the Output Schema: an alias and a description for each column. This is where you write down that position is one-based and that anonymized queries are excluded. The descriptions travel into Google Sheets as notes on the header cells.

3. Deliver it to Google Sheets

Add Google Sheets as a destination once. On the Data Mart’s Destinations tab, create a report and choose the spreadsheet and tab to fill.

The result is an ordinary sheet. Each header carries the column’s description as a note, with the Data Mart it came from and the time of the import:

4. Schedule the refresh

On the Triggers tab, add a report run trigger. The export publishes once a day, so a daily run after the export time is enough; more often only spends query budget. Run History lists each run with its status.

The same Data Mart can feed other destinations, so the sheet the SEO lead reads and the dashboard the content team reads start from one definition. The full walkthrough is in how to connect BigQuery to Google Sheets.

Search Console data in Power BI, Tableau, Redshift and other tools

Search Console has one native warehouse export, and it goes to BigQuery. There is no built-in export to Power BI, Tableau, Qlik, PostgreSQL or Amazon Redshift. The usual routes are:

• A BI tool: connect Power BI, Tableau or Qlik to BigQuery and read the export tables, or better, an aggregated query on them.
• Another database: export query results from BigQuery to files, or copy them with your own pipeline.
• A one-off file: in the BigQuery console, Save results writes a query result to CSV or JSON.

In each case BigQuery is the place the data lands first.

Keeping the cost down

• Filter on data_date in every query.
• Select the columns you need. BigQuery bills on-demand queries by the columns read, and the URL table has dozens.
• Set a partition expiration if you do not need unlimited history. Google asks for at least 14 days; without one, data accumulates indefinitely.
• Aggregate once, read many times. Point reports at a daily or weekly summary, not at the raw table.
• Check who is running what in the BigQuery job history.

Where to go next

If you also export GA4, the GA4 to BigQuery guide covers that link. With both in one project, a landing page’s search queries and its sessions can sit in the same report.

Google BigQueryBigQuery partitioned tables: how to create and query themOctober 6, 2026→Google BigQueryHow to load data into BigQuery: every method and when to use itOctober 6, 2026→AI in AnalyticsHire AI ChatGPT for Generating SQL Queries 50x FasterJune 8, 2024→

See all articles →