Export Search Console Queries Without Fooling Yourself

There is a particular kind of false confidence that arrives with a freshly downloaded spreadsheet. It has headers, neat numbers, and exactly 1,000 rows—so it feels complete. In Search Console, that feeling can be wrong.

Google calls these values queries, not a definitive keyword universe. Some rare searches are withheld for privacy, the interface shows representative rows, and different dimensions change aggregation. The honest goal is to export the most appropriate dataset for the decision you need to make, then keep its limits attached.

Choose the export by the question, not by habit

• A quick review or a client handoff: export the filtered Performance report to Google Sheets, Excel, or CSV. Expect no more than 1,000 table rows.

• Repeatable analysis for a small or medium site: use the Search Analytics API, request one day at a time, and paginate in 25,000-row pages.

• The most complete ongoing query dataset for a large site: configure Search Console bulk data export to BigQuery before you need the history.

• A dashboard without maintaining code: the Looker Studio connector can be useful, but it does not remove Search Console’s underlying privacy and data constraints.

The simplest export is often enough

1. Open the correct Search Console property and choose Performance → Search results.

1. Set the date range, search type, and any page, country, device, query, or search-appearance filters before exporting.

1. Select the Queries tab if search terms are the intended dimension.

1. Choose Export, then Google Sheets, Excel, or CSV.

1. Save the filters, property identifier, date range, search type, export time, and time-zone assumption beside the file.

The export contains the current report view, including chart and table data. The table is truncated to 1,000 representative rows. Its totals can still describe more data than the visible rows, which is why summing the exported query rows may not reproduce the headline total.

Before automating, decide what one row means

A row grouped only by query answers a different question from a row grouped by date, query, page, country, and device. Adding dimensions creates more combinations, increases row counts, and can expose less complete data. Start with the fewest dimensions required.

• query identifies the non-anonymized search text Google can report.

• clicks counts qualifying clicks from the selected Google surface.

• impressions counts eligible appearances under Search Console’s measurement rules.

• ctr is clicks divided by impressions for that aggregated row.

• position is an average—not a stable rank that every user saw.

• Most reporting dates use Pacific Time; recent data is normally delayed and can remain preliminary in fresh-data views.

Export paginated query rows with the API

The following example uses Application Default Credentials and the read-only Search Console scope. The signed-in identity still needs access to the requested property. For scheduled production jobs, use an approved workload identity and explicit access lifecycle rather than leaving a personal login on a server.

$ python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade google-api-python-client google-auth
gcloud auth application-default login --scopes=https://www.googleapis.com/auth/webmasters.readonly

What this authorization step changes

• The virtual environment keeps packages local to this project; activation affects only the current shell.

• google-api-python-client supplies the Search Console client and google-auth loads credentials.

• gcloud auth application-default login stores a local user credential for client libraries. It is appropriate for an authorized workstation, not a shared production host.

• The read-only scope permits retrieval but not Search Console mutations.

• This workflow contacts Google and writes credentials locally; it was documentation-validated, not executed against a Lynxbee Google account.

#!/usr/bin/env python3
"""Export daily Search Console query rows with honest pagination limits."""
 
import argparse
import csv
from pathlib import Path
 
import google.auth
from googleapiclient.discovery import build
 
SCOPE = "https://www.googleapis.com/auth/webmasters.readonly"
PAGE_SIZE = 25_000
 
 
def export_rows(site_url: str, start_date: str, end_date: str, output: Path) -> int:
    credentials, _ = google.auth.default(scopes=[SCOPE])
    service = build("searchconsole", "v1", credentials=credentials, cache_discovery=False)
    start_row = 0
    written = 0
 
    with output.open("w", newline="", encoding="utf-8") as stream:
        writer = csv.writer(stream)
        writer.writerow(["date", "query", "clicks", "impressions", "ctr", "position"])
 
        while True:
            request = {
                "startDate": start_date,
                "endDate": end_date,
                "dimensions": ["date", "query"],
                "type": "web",
                "dataState": "final",
                "rowLimit": PAGE_SIZE,
                "startRow": start_row,
            }
            rows = service.searchanalytics().query(
                siteUrl=site_url,
                body=request,
            ).execute().get("rows", [])
 
            if not rows:
                break
 
            for row in rows:
                date, query = row["keys"]
                writer.writerow([
                    date,
                    query,
                    row.get("clicks", 0),
                    row.get("impressions", 0),
                    row.get("ctr", 0),
                    row.get("position", 0),
                ])
            written += len(rows)
            start_row += len(rows)
 
    return written
 
 
if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--site", required=True, help="sc-domain:example.com or exact URL-prefix")
    parser.add_argument("--start", required=True, help="YYYY-MM-DD in Search Console's PT calendar")
    parser.add_argument("--end", required=True, help="YYYY-MM-DD in Search Console's PT calendar")
    parser.add_argument("--output", type=Path, default=Path("search-console-queries.csv"))
    args = parser.parse_args()
    count = export_rows(args.site, args.start, args.end, args.output)
    print(f"Wrote {count:,} rows to {args.output}")

Why this exporter deliberately stays plain

• The property string must match Search Console exactly: use sc-domain:example.com for a Domain property or the precise protocol and trailing slash for a URL-prefix property.

• rowLimit uses the documented maximum of 25,000; startRow advances by the number actually returned.

• The date and query keys follow the requested dimension order.

• dataState: final avoids deliberately including fresh partial data.

• CSV output uses UTF-8 and newline="" so Python’s CSV writer handles quoting and line endings.

• A zero-row response ends pagination. The resulting file is still constrained by the API’s daily limit and anonymization.

• The code was reviewed against the current request schema; no real property data or OAuth credential was accessed.

$ python export_search_console_queries.py \
  --site "sc-domain:example.com" \
  --start 2026-07-01 \
  --end 2026-07-31 \
  --output july-search-queries.csv

Wrote 12,345 rows to july-search-queries.csv

Read the run before trusting the file

• Replace example.com; the output shown is illustrative, not a Lynxbee execution result.

• The dates are inclusive and interpreted using Search Console’s Pacific Time calendar.

• For the most complete API extraction, Google recommends querying one day at a time, then combining daily files with a deduplication and provenance strategy.

• A small row count can be correct, permission-related, dimension-dependent, privacy-limited, or caused by data freshness. Compare against an ungrouped date query and report totals before diagnosing a bug.

Why one day at a time beats one giant request

The documented API ceiling is per day and search type. A month-wide request can return top rows across the whole range before you ever reach quieter days or long-tail combinations. Daily requests make the limit visible, simplify retries, and preserve the date needed for later comparisons.

• Run only after the date is finalized—typically data becomes available after two or three days.

• Persist the property, search type, requested dimensions, filters, aggregation type, extraction timestamp, and API data state.

• Treat web, image, video, news, Discover, and Google News as separate datasets where supported.

• Retry bounded transient failures, but do not silently replace a missing day with zero.

• Keep raw daily files immutable; build cleaned and aggregated tables downstream.

For durable history, start BigQuery export now

Bulk export is an ongoing daily feed, not a button that reconstructs all earlier history. Only a Search Console property owner can configure it. You need a Google Cloud project with billing, the BigQuery and BigQuery Storage APIs, and the documented Search Console service account granted BigQuery Job User and Data Editor roles.

1. Prepare the Cloud project and grant search-console-data-export@system.gserviceaccount.com the required roles.

1. In Search Console, open Settings → Bulk data export.

1. Enter the Cloud project ID, not its numeric project number.

1. Choose a dataset name and location carefully; the dataset name begins with searchconsole, and changing location later is not natively supported.

1. Continue and watch for the configuration test. The first daily export can take up to 48 hours.

1. Inspect ExportLog and the bulk-export status instead of assuming that silence means every date arrived.

1. Set a partition-expiration policy of at least 14 days if retention costs matter, and do not modify the exported table schema.

Aggregate BigQuery rows before ranking queries

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1 AS avg_position
FROM `YOUR_PROJECT.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
  AND query != ''
GROUP BY query
ORDER BY impressions DESC

The arithmetic matters more than the ORDER BY

• Replace YOUR_PROJECT and the dataset if its configured name differs.

• The data_date predicate limits scanned partitions and excludes the current incomplete day.

• Google warns that bulk rows are not guaranteed to be consolidated, so metrics are summed before ranking.

• SAFE_DIVIDE avoids an error when impressions are zero.

• Position sums are zero-based in the export; dividing by impressions and adding one produces the documented one-based average.

• query != '' removes anonymized query rows from the named-query list. Those rows still matter when reconciling overall totals.

• This SQL follows Google’s current schema and aggregation guidance but was not executed against a billable BigQuery project.

When totals refuse to reconcile

• The CSV stops at 1,000: that is the report export limit, not proof the site had only 1,000 queries.

• Chart clicks exceed summed queries: anonymized queries, truncation, filters, and aggregation can create the difference.

• API pages stop early: inspect the per-day 50,000-row limit, dimensions, search type, access, freshness, and whether the final response is empty.

• BigQuery query duplicates-looking rows: aggregate metrics; exported rows are not guaranteed to be pre-consolidated.

• Position is off by one: bulk-export position sums are zero-based before the documented + 1 calculation.

• Analytics sessions do not match clicks: Search Console measures Google result interactions; analytics tools measure visits under different identity, JavaScript, consent, attribution, and time-zone rules.

• Yesterday is missing: ordinary Performance data is typically delayed two or three days, while bulk-export failures and retries should be checked through status and ExportLog.

Turn an export into decisions, not a keyword dump

• Find high-impression queries whose landing page genuinely under-serves the intent.

• Separate branded and non-branded demand with an explicit, reviewed rule.

• Compare equivalent complete periods instead of a partial week against a full week.

• Inspect page-query pairs before assuming one page deserves every impression for a topic.

• Segment device, country, and search appearance only when the segment can change an action.

• Preserve raw metrics and calculate derived labels downstream so assumptions remain reversible.

• Protect search-query exports as potentially sensitive business data and grant access by need.

The spreadsheet is not the insight. The useful moment comes later, when a real query reveals that a page answers the wrong question, a title makes the right page hard to recognize, or a technical issue hides work readers would value. Export enough to see that moment—and keep enough context to know it is real.

Related Lynxbee work

• Add and verify a website property in Google Search Console.

• Repair a missing H1 without manufacturing an SEO myth.

Primary documentation

• Export a Search Console report

• Search Analytics query API reference

• Retrieve Search Console performance data with pagination

• Start a Search Console bulk data export

• Bulk-export table schema and aggregation rules

• Query guidelines and BigQuery examples