Search Console keeps 16 months: monthly export, BigQuery, or just the UI?

Search Console only keeps 16 months of performance data, and the UI just drops anything older. Most teams only notice when someone asks for a year-over-year comparison on a seasonal site and half the baseline is gone. The usual fixes are a monthly CSV export, the BigQuery bulk export, or pulling through the API on a schedule. BigQuery doesn't backfill, so it only starts collecting from the day you switch it on. My view is that the bulk export is worth setting up now for anything you'd have to defend in a board deck, and a monthly export is fine for a smaller site. Is anyone doing it through the API instead, and how do you handle the row limits on big sites?

Before you pick, know that neither the bulk export nor the API includes anonymized queries, so query-level rows won't add up to the click totals the UI shows. On a long-tail heavy site that gap can be a big slice, and it hits every method equally. Because of that, year-over-year on total clicks holds up better than year-over-year on individual queries. For the API, each request returns at most 25,000 rows, so on a big site you page through with startRow and merge the results on your own schedule. Are the board numbers mostly totals or specific queries?

Paging with startRow only works if the order is stable between requests. Sort explicitly, by clicks descending with query and page as tiebreakers, otherwise rows can repeat or go missing across pages and your merged totals drift without any error. I'd also keep site-level daily totals in a separate table. The anonymized share moves when your query mix changes, so a flat year-over-year on query rows can hide a shift in what's actually being counted.

I think the bulk export does keep anonymized rows. From what I can tell in the schema, they come through with the query field null and is_anonymized_query set to true. So if you sum clicks and impressions across a day, the totals should match the site numbers, which means you can measure the gap instead of guessing at it. The API doesn't expose that flag, so an API-only setup won't tie query rows back to totals unless you also pull the site-level numbers. That's where Elena's daily table earns its place.

Same thing in the schema, so the gap is measurable. And since the null-query rows are still broken out by page, you can see which URLs carry most of the anonymized clicks, not just the total.

Also check your date matching before you compare daily sums to the UI. Search Console reports days in Pacific Time, so if your warehouse or reporting runs on UTC, a few days will look off by a shift even though nothing is actually missing.

I was wrong earlier about the bulk export leaving out anonymized queries. Jun's schema point holds, so the gap can be measured. I still wouldn't lean on the page breakdown of those null-query rows to rank URLs by anonymized clicks, though. Those rows are aggregated before they reach you, and I'm not confident the per-page split is precise enough for that. Treat it as a rough signal, and check the top pages against the UI before anyone builds a decision on it.