Building a daily pipeline off the Search Analytics API and I keep hitting the 50,000-row response cap and the occasional 429. Sites have 200k+ query+page rows/day.
What's the right architecture in 2026 — pagination, batching, or something I'm missing entirely?
Two layers. If you just need the data, stop fighting the API — turn on the Bulk data export to BigQuery (Settings → Bulk data export). It's raw, unsampled, daily, with no row caps. That's the right answer for >50k rows/day.
If you need the API (ad-hoc or pre-export history): the 50k cap is per request, not per day. Paginate with startRow in 25,000-row steps, batch one request per date so result sets stay small and resumable, respect the 1,200 QPM / site quota, and add exponential backoff on 429/503. Land everything in BigQuery or Postgres — Sheets dies at this volume.
The 50k cap is per-request, not per-day. Paginate with startRow in steps of 25,000 and loop until you get fewer rows than the limit.
Batch by day — one request per date keeps each result set small and lets you resume cleanly if a job dies.
If you need everything, use the bulk export to BigQuery. Raw, unsampled, daily, no caps. The API is the wrong tool for full extracts.
Wait — there's a native BigQuery export now? Since when?
@newbie_nate yes, Settings → Bulk data export. It backfills nothing, so set it up before you need the history.
Quota is 1,200 QPM / 30,000 QPD per site. Add backoff on 429s or you'll get throttled mid-run.
Minimal backoff pattern we use:
for attempt in range(6):
try:
return svc.searchanalytics().query(...).execute()
except HttpError as e:
if e.resp.status in (429, 503):
time.sleep(2 ** attempt) # 1,2,4,8,16,32s
else:
raiseSet dataState='all' to include fresh (partial) data, or 'final' for stable numbers — just don't mix them in one job.
Watch sampling: summing API rows undercounts total clicks vs the UI. The BigQuery export avoids that entirely.
Dimensions drive cardinality: query+page+date explodes row counts fast. Pull only what you'll actually use.
For >50k/day you usually want page+date first, then join to a narrower query-level pull. Don't request the full cross-product.
Storing it where? Sheets falls over at this volume.
@newbie_nate BigQuery or Postgres. We land raw daily partitions in BigQuery and build views on top; Sheets is just the final dashboard.
Dedupe on (date, page, query) — retried jobs create overlaps. Upsert, don't blind-append.
Exactly what I needed — switching to the BigQuery bulk export and keeping the API only for ad-hoc pulls. Thanks everyone.
You're viewing the public preview. 172 more replies — including code samples, benchmarks and follow-ups — are visible to members. Joining is free.
Free account · no spam · sign in to reply and vote