Keep your Search Console data forever in BigQuery

Search Console is a window, not an archive

Google keeps sixteen months of performance data. Today the window starts about sixteen months back, and every day the oldest day drops off. Not hidden, not archived. Gone, at least from your side.

Here is why that hurts. In February 2025, one of my shops had its best month ever in Search Console: 1,392 clicks. In February 2026, the same month brought 85. The page that earned 917 of those clicks fell from position two to fifteen in February 2026 and never came back. 917 clicks became 2.

In the current Search Console window, that page never shows more than about 250 clicks in a month. The months where it did nine hundred are outside the window. You would never know what you lost, which means you also can't ask what changed.

A change in November only makes sense next to last November. And the November before that. Sixteen months gives you one November. That is the whole argument for this setup.

Set up the bulk export

Two places, five steps. The Cloud part takes about ten minutes, most of it waiting for billing to activate.

search-console-data-export@system.gserviceaccount.com

Once the first tables exist, set the partition expiration (see the costs section below). And leave the table schema alone: adding a column to those tables makes the export fail.

The two tables, and why you never add them up

The export lands in your dataset as three tables. Two of them sound like they contain the same thing. They don't.

The same click shows up in both data tables, once at property grain and once at URL grain. Add the two totals together and you count every click twice. Pick the table that answers your question and stay in it.

One more trap. Search Console writes each day's data incrementally, so the tables contain rows with repeated keys: the same query, same date, split over several rows. Never read raw rows and trust what you see. Always SUM your metrics and GROUP BY the dimensions you actually want. Google's own query guidelines repeat this for a reason.

Why one trend line across API data and export data lies

Search Console hides rare searches. Google calls them anonymized queries: queries that were not issued by more than a few dozen people over a couple of months. Privacy, fair enough. But the way that hiding works differs per route, and that difference is exactly where bad charts come from.

• In the report and the API, anonymized queries are only in the chart totals. They have no rows. Sum the rows and you get less than the total showed.
• In the bulk export, they do have a row: is_anonymized_query is true and the query field is an empty string. The URL table even keeps the landing page, so you can't see what someone typed, but you can see which page earned the click.

On my own export, this is not a rounding error. From 8 June to 17 October 2026, on my own sites, 60.6 percent of web clicks sat in rows without a search term: 19,152 of 31,606 clicks. On one small site it was 97.1 percent. Those are my numbers from one setup, not a benchmark, but they show the size of the blind spot.

Now the trend line problem. Your API-pulled months contain no anonymized rows. Your export months do. Draw one line across the boundary and it jumps, not because anything happened on your site, but because the export suddenly counts the clicks the API never gave you per row. Compare like with like. And when you load API history into BigQuery, put a source column on it so no query ever mixes the two silently.

Costs: the free level, and the expiration you should set

BigQuery has a free usage level, and a small Search Console export usually stays inside it for storage. Above the free level, storage and queries are charged, and a busy site can grow fast. Three things keep it cheap:

• Set a partition expiration. The tables are kept forever by default. Google recommends putting an expiration on the partition instead of the table, at least 14 days: a month, six months, twelve months, whatever fits your needs. An expiration on the whole table deletes everything, so don't. On the partition it means old days get dropped and the export just keeps working.
• Filter queries on the date. The tables are date-partitioned, and a WHERE clause on the date range cuts how much BigQuery scans. On big tables that is real money over a year.
• Leave the schema alone. Adding a column to the export tables makes the export fail.

Check what your own setup spends in the Cloud Console billing page after a month. That is your real number, not mine.

Three queries to start with

Replace your-project with your project ID and the site_url with your property: a domain property looks like sc-domain:example.com, a URL-prefix property is the full URL as it is defined in Search Console.

Clicks and impressions by day, properly aggregated

The daily trend for one property. Note the SUMs and the position formula: position is stored zero-based, so the average is the sum divided by impressions, plus one.

SELECT
  data_date,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(clicks) / SUM(impressions) AS ctr,
  SUM(sum_top_position) / SUM(impressions) + 1.0 AS avg_position
FROM `your-project.searchconsole.searchdata_site_impression`
WHERE site_url = 'https://www.example.com/'
  AND search_type = 'web'
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND CURRENT_DATE()
GROUP BY data_date
ORDER BY data_date DESC;

Top pages, including the clicks without a search term

This is the query the interface can't give you: pages that earn clicks on rare queries. Those rows have an empty query but the URL table keeps the page, so long-tail pages show up here that never appear in your query report.

SELECT
  url,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE site_url = 'https://www.example.com/'
  AND search_type = 'web'
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND CURRENT_DATE()
GROUP BY url
ORDER BY clicks DESC
LIMIT 20;

Average position per real query

When you want the query list, filter the anonymized rows out, or the empty-string query will sit at the top of your list like a ghost keyword.

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(sum_position) / SUM(impressions) + 1.0 AS avg_position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE site_url = 'https://www.example.com/'
  AND search_type = 'web'
  AND is_anonymized_query = FALSE
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND CURRENT_DATE()
GROUP BY query
ORDER BY clicks DESC
LIMIT 20;

The sixteen months you can still save

The bulk export handles tomorrow. The API handles the past, while it is still there. I'm not going to paste a backfill script I didn't run for you today, so here are the steps instead; they fit in an evening:

The window only shrinks. Sixteen months from your setup day, every day you did not save is gone for good.