Every serious SEO analysis eventually hits the same two walls: the 16-month retention window and the 1,000-row UI export cap. Both are removable, and doing so changes what questions you can ask.
What you are building
Three layers, none of them exotic:
Extraction. Get data out of Search Console, analytics, and your crawler on a schedule. Storage. Put it somewhere with no retention limit and a query language. Analysis. SQL, joined across sources.
For most sites this is a weekend of setup and near-zero ongoing cost.
The easy path: the BigQuery bulk export
Search Console can write directly to BigQuery on a daily schedule. Settings → Bulk data export → configure a project and dataset. Google writes daily partitions to three tables — one for URL-level impressions, one for site-level, one for the export log.
This is the single highest-value thing in this article. It requires no code, it starts accumulating history immediately, and it captures data at a granularity the UI never exposes. Costs are typically a few dollars a month for storage on a mid-sized site.
The critical property: it only captures data from the day you enable it forward. There is no backfill. Anyone who expects to care about SEO history should enable it now rather than at the point of needing it, because the alternative is waiting a year for the history to exist.
The API path, for what the export misses
The bulk export does not cover everything — notably it has its own aggregation behavior, and you may want the query dimension shaped differently. The Search Console API fills gaps and lets you pull historical data back to the retention limit.
from google.oauth2 import service_account
from googleapiclient.discovery import build
creds = service_account.Credentials.from_service_account_file(
"service-account.json",
scopes=["https://www.googleapis.com/auth/webmasters.readonly"],
)
gsc = build("searchconsole", "v1", credentials=creds)
def query(site, start, end, dimensions, start_row=0):
return gsc.searchanalytics().query(
siteUrl=site,
body={
"startDate": start,
"endDate": end,
"dimensions": dimensions,
"rowLimit": 25000,
"startRow": start_row,
},
).execute()Two things to get right. Paginate — loop startRow in increments of 25,000 until a response returns no rows, or you will silently truncate. And query day by day rather than over a range, because Search Console aggregates across the requested window and you lose daily granularity you cannot recover later.
What to add alongside
Analytics data for sessions and conversions by landing page, joined to Search Console on URL. This is what lets you say "these queries produce revenue" rather than "these queries produce clicks."
Crawl data — a scheduled crawl written to the same warehouse, with status codes, titles, meta descriptions, word counts, canonical tags, and internal link counts per URL. Joining crawl data to performance data is where the highest-value analyses live.
Rank tracking, if you use it, for the query set you care about.
The analyses this unlocks
These are the ones that justify the setup, because none of them are possible in the UI:
Multi-year seasonality. Three or four years of daily data by query cluster shows you when demand actually rises in your category, which is when content needs to be live rather than when it needs to be commissioned.
Striking-distance at full depth. Every query in positions 5–15 with meaningful impressions, across the entire tail rather than the top 1,000 rows. On a large site this list is where most of the available upside sits.
Cannibalization detection. Group by query, count distinct URLs receiving impressions for it, and flag queries where the ranking URL changes frequently. That instability is the signature of two pages competing.
Content decay at scale. Year-over-year comparison for every URL at once, ranked by absolute clicks lost.
Internal links versus performance. Join crawl data to performance data, and find pages with strong impressions, weak position, and few internal inbound links. This query produces a prioritized fix list more reliably than any audit tool.
Real forecasting. Actual CTR by position from your own data, applied to your own impression trends, instead of borrowed curves.
The scale threshold
Below a few hundred URLs and a few thousand monthly clicks, the UI is genuinely adequate and this is over-engineering. The exception is retention — even a small site benefits from turning on the BigQuery export purely so the history exists later.
Above that, the constraint stops being tooling and starts being questions. Most teams find the first month of having the data produces a backlog of analyses they had not been able to ask about, which is the real return: not better dashboards, but a shift from reporting what happened to finding what to do next.