# Search Console Bulk Data Export to BigQuery for Marketers

> Set up Search Console bulk data export to BigQuery: permissions, tables, costs, sample SQL and common failures.

Source: https://mentionx.ai/learn/search-console/search-console-bigquery-bulk-export  
Author: Dipuck Jones, founder of MentionX  
Updated: 4 November 2026  
Level: Practitioner

**Bulk data export sends a daily copy of your Search Console data to BigQuery, without the 1,000-row limit.** It's the best route for large sites and long-term analysis.

*Checked 4 November 2026 (CET) against Search Console Help and Google Search Central documentation. Search Console reports and rollouts change often, and some features only appear for eligible properties, so confirm in your own property. "Our view" marks MentionX opinion. Ledgerline is a fictional company used for illustration.*

> **Key takeaways**
>
> - Needs a Cloud project with billing and two APIs enabled.
> - Grant the Search Console service account two roles.
> - First export within 48 hours; no backfill.
> - Data kept forever unless you set expiration.
> - Always aggregate rows.

## How does it work?

From Google's [bulk export setup guide](https://support.google.com/webmasters/answer/12917675), [table reference](https://support.google.com/webmasters/answer/12917991) and [management help](https://support.google.com/webmasters/answer/12919198):

| Detail | Value |
|---|---|
| Tables | searchdata_site_impression, searchdata_url_impression, ExportLog |
| search_type values | web, image, video, news, discover, googleNews |
| Anonymised queries | Flagged by is_anonymized_query |
| Failures | Retried for about a week; export stops after about a month of failures |

For GA4 exports see [GA4 BigQuery export on the free tier](https://mentionx.ai/learn/ga4/ga4-bigquery-export-free-tier).

*Our view:* set it up early; with no backfill, every month you wait is lost.

> **Pro move:** set partition expiration only if storage cost is a concern; long history is what makes the export valuable.

## Sample queries

Top queries last 28 days:

```sql
SELECT query, SUM(clicks) AS clicks, SUM(impressions) AS impressions
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND NOT is_anonymized_query
GROUP BY query ORDER BY clicks DESC LIMIT 100;
```

Average position by page:

```sql
SELECT url, SUM(sum_position)/SUM(impressions)+1 AS avg_position
FROM `project.searchconsole.searchdata_url_impression`
GROUP BY url;
```

(Use `sum_top_position` on the site table and `sum_position` on the URL table, as Google's reference describes.)

## Troubleshooting

| Problem | Cause | Fix |
|---|---|---|
| Export failing | Missing roles for the service account | Grant BigQuery Job User and Data Editor |
| No historical data | No backfill | Use reports or API for history |
| Export stopped | Failures for about a month | Fix and re-enable |
| Costs rising | Data kept forever | Set partition expiration |

## More SQL for marketers

Replace `project.searchconsole` with your project and dataset. Always filter on `data_date` to limit the data scanned and reduce cost.

### Non-branded clicks by week

```sql
SELECT DATE_TRUNC(data_date, WEEK) AS week,
  SUM(IF(REGEXP_CONTAINS(query, r'(ledgerline|ledger line)'), clicks, 0)) AS branded,
  SUM(IF(NOT REGEXP_CONTAINS(query, r'(ledgerline|ledger line)'), clicks, 0)) AS non_branded
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND NOT is_anonymized_query
GROUP BY week ORDER BY week;
```

### Question queries and the pages they land on

```sql
SELECT url, COUNT(DISTINCT query) AS questions, SUM(clicks) AS clicks
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND REGEXP_CONTAINS(query, r'^(what|how|why|which|can|does)\b')
GROUP BY url ORDER BY clicks DESC LIMIT 50;
```

### Pages losing clicks: last 28 days vs previous 28

```sql
WITH p AS (
  SELECT url,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS recent,
    SUM(IF(data_date < DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS prior
  FROM `project.searchconsole.searchdata_url_impression`
  WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY)
  GROUP BY url)
SELECT url, prior, recent, recent - prior AS change
FROM p WHERE prior > 0 ORDER BY change ASC LIMIT 50;
```

### Discover performance by page

```sql
SELECT url, SUM(impressions) AS impressions, SUM(clicks) AS clicks
FROM `project.searchconsole.searchdata_url_impression`
WHERE LOWER(search_type) = 'discover'
  AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY url ORDER BY impressions DESC;
```

Check the exact casing of `search_type` values in your own table before filtering; the table reference lists them, and a quick `SELECT DISTINCT search_type` confirms.

## Controlling cost

- **Partitioning.** Tables are partitioned by date; always filter on `data_date`.
- **Select only needed columns.** BigQuery charges by data scanned per column.
- **Preview before running.** The query editor estimates bytes processed.
- **Partition expiration.** If you only need a rolling window, set expiration (Google's minimum is 14 days), but remember there's no backfill.
- **Scheduled summary tables.** Run a daily scheduled query that writes small aggregates, and point dashboards at those.

## Edge cases

- **Repeated keys.** Several rows can share the same date, query and URL. Always `SUM` and `GROUP BY`.
- **Anonymised queries** have no query text but do have clicks. Include them for totals, exclude them for query analysis.
- **Location choice.** Choose the dataset location to match your other data; changing it later is hard.
- **ExportLog** records each successful export. Check it first when data seems missing.
- **Export stops after about a month of failures.** Usually a removed permission. Re-grant the roles and re-enable.

## Ledgerline walkthrough: setting it up

1. The data team created a Cloud project with billing and enabled the BigQuery and BigQuery Storage APIs.
2. They granted the Search Console export service account BigQuery Job User and BigQuery Data Editor on the project.
3. The SEO lead, a property owner, opened Settings > Bulk data export, entered the project ID and a dataset name starting with "searchconsole", and chose the EU location to match other data.
4. Two days later, ExportLog showed the first export. They ran the decliners query and built a scheduled summary table for Data Studio.

## Questions practitioners ask

**Can I export a Domain property?** Yes; the export works per property you own.

**Can I export several properties into one dataset?** Each property needs its own dataset; use a view or scheduled query to combine them.

**Does the export include generative AI report data?** The tables follow the documented schema. Check Google's table reference for any new fields rather than assuming.

**How do I stop it?** Deactivate it in Settings > Bulk data export; Google says it takes effect within about 24 hours, and existing data stays in BigQuery.

## More SQL recipes

Monthly clicks by section from the URL table:

```sql
SELECT
  FORMAT_DATE('%Y-%m', data_date) AS month,
  CASE
    WHEN url LIKE '%/blog/%' THEN 'Blog'
    WHEN url LIKE '%/product/%' THEN 'Product'
    ELSE 'Other'
  END AS section,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY month, section
ORDER BY month, section;
```

Share of impressions from anonymised queries:

```sql
SELECT
  SUM(IF(is_anonymized_query, impressions, 0)) / SUM(impressions) AS anonymised_share
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY);
```

## Comparing two periods in SQL

```sql
SELECT
  url,
  SUM(IF(data_date BETWEEN '2026-09-01' AND '2026-09-30', clicks, 0)) AS clicks_sep,
  SUM(IF(data_date BETWEEN '2026-08-01' AND '2026-08-31', clicks, 0)) AS clicks_aug
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-08-01' AND '2026-09-30'
GROUP BY url
ORDER BY clicks_sep - clicks_aug ASC
LIMIT 50;
```

This lists the 50 URLs with the biggest click drops between the two months. Swap the order for gains.

## Where this fits in the monthly routine

Once the export is running, analysts can answer questions the UI can't, such as 16-month comparisons or long-tail query themes. Our view: keep a small set of saved queries (section trend, period comparison, anonymised share) and run them in week 1.

## Keeping query costs predictable

Filter on `data_date` every time; the tables are partitioned by date. Select only the columns you need, write regular results to small summary tables with scheduled queries, and set a budget alert in Google Cloud billing.

## Quick checks before you move on

- The export is active and new tables arrive daily.
- You know which table to use for totals (site) and which for pages (URL).
- Saved queries for section trend, period comparison and anonymised share run without errors.
- A budget alert is set.

## Worked example: Ledgerline's export

*Ledgerline is a fictional accounts-payable software company. Details and numbers are illustrative.*

Ledgerline's data team enabled bulk export in a new Cloud project. After the first export arrived, the SEO lead ran the top queries SQL and matched it with the Performance report trend.

### Walkthrough

1. Create or choose a Google Cloud project with billing.
2. Enable the BigQuery API and BigQuery Storage API.
3. Grant `search-console-data-export@system.gserviceaccount.com` BigQuery Job User and BigQuery Data Editor.
4. In Search Console, open **Settings > Bulk data export**.
5. Enter the project ID, dataset name (starting with "searchconsole") and location.
6. Wait up to 48 hours for the first export.
7. Check the ExportLog table.

## Try it yourself

Set up bulk export (or plan it with your data team). Once data arrives, run the top queries SQL and compare it with the Performance report.

## Quick quiz

**1. Is there a backfill?**

*Answer:* No.

**2. Which tables are exported?**

*Answer:* searchdata_site_impression, searchdata_url_impression and ExportLog.

**3. Who can set it up?**

*Answer:* Owners.

**4. How is average position calculated?**

*Answer:* Sum of positions divided by impressions, plus 1.

## What should you check before the next lesson?

- Bulk export is configured or planned.
- You ran a sample query.
- Read [GA4 BigQuery export](https://mentionx.ai/learn/ga4/ga4-bigquery-export-free-tier).

Next: [The API and AI analysis with Claude and ChatGPT](https://mentionx.ai/learn/search-console/search-console-api-ai-analysis). Or return to the [Search Console for Marketers track](https://mentionx.ai/learn/search-console/search-console-for-marketers).

## Common questions

**Does it cost money?** BigQuery storage and queries may incur charges.

**Can I change the location later?** It is hard to change; choose carefully.

**Can I change the schema?** No.

## Sources

- Search Console Help, [Start a new bulk data export](https://support.google.com/webmasters/answer/12917675)
- Search Console Help, [Table guidelines and reference](https://support.google.com/webmasters/answer/12917991)
- Search Console Help, [Manage and monitor bulk data exports in Search Console](https://support.google.com/webmasters/answer/12919198)
- Search Console Help, [Query guidelines and sample queries](https://support.google.com/webmasters/answer/12917174)
- Google Search Central, [Bulk data export: a new and powerful way to access your Search Console data](https://developers.google.com/search/blog/2023/02/bulk-data-export)
- All accessed 4 Nov 2026.

## Frequently asked questions

### Does it cost money?

BigQuery storage and queries may incur charges.

### Can I change the location later?

It is hard to change; choose carefully.

### Can I change the schema?

No.

---

Written and reviewed by Dipuck Jones for MentionX Learn. The HTML version of this page: https://mentionx.ai/learn/search-console/search-console-bigquery-bulk-export