search-console-bigquery

Back up your Google Search Console data to BigQuery before Google deletes it, keep it fresh, and ask questions about it from Claude or any MCP-compatible AI assistant.

Google only keeps 16 months of Search Console performance data, the UI exports at most 1,000 rows, and Google's own BigQuery bulk export only starts on the day you switch it on. This tool pulls everything the API still has — every query × page × country × device × day — into BigQuery, and keeps it up to date.

• gsc-bq setup — a guided first run: Google sign-in, project, properties, done.

• gsc-bq backfill — pull up to 16 months of history; re-run any time, rows are never duplicated.

• gsc-bq backfill --gaps — fill only the days that are missing; safe to schedule.

• gsc-bq doctor — checks your sign-in, APIs, Search Console access and BigQuery, and tells you exactly how to fix what's wrong.

• MCP server — 39 tools for AI assistants: top queries, period comparisons, striking-distance keywords, cannibalization, URL inspection, sitemaps, Core Web Vitals and more.

• Claude Code plugin — slash commands and a skill on top of the MCP server.

• Works with Google's bulk export — combine its daily feed with the backfilled history in one table.

Quickstart (about 10 minutes)

What you need

• Python 3.11+

• The Google Cloud CLI (gcloud) — install it. It is only used to sign in.

• A Google Cloud project to hold the data, with billing enabled (BigQuery's free sandbox deletes tables after 60 days). Most sites stay inside BigQuery's free tier — see Costs.

• A Google account that is a user on your Search Console properties (Owner, Full or Restricted) and can use BigQuery in that project: project Owner/Editor, or the roles BigQuery Job User, BigQuery Data Editor and Service Usage Consumer.

1. Install

pipx install git+https://github.com/Ayresmic/search-console-bigquery

No pipx? uv tool install git+https://github.com/Ayresmic/search-console-bigquery works too, or pip install git+https://github.com/Ayresmic/search-console-bigquery inside a virtual environment.

2. Set up

gsc-bq setup

The wizard opens a browser to sign you in to Google (with the Search Console scope that a plain gcloud login leaves out), asks which project to use, offers to enable the two APIs it needs, lists your Search Console properties so you can pick them, checks BigQuery works, and saves everything to a config file.

The sign-in uses gcloud auth application-default login, so it replaces this computer's gcloud application-default credentials — other tools that rely on them will use the same account afterwards. To keep them separate, use a service account instead (gsc-bq setup --key-file key.json).

3. Check

gsc-bq doctor

No row should say FAIL; each one that does comes with the exact fix. (info rows are optional extras.)

4. Back up

gsc-bq backfill --days 7

A quick test run. When it works, pull everything Google still has:

gsc-bq backfill

A busy property takes 10–30 minutes for the full 16 months; small sites take a minute or two. Use --dry-run to see exactly what will be pulled and where it will land before writing anything.

5. Query it

Each property gets its own dataset. gsc-bq sites shows the exact table for each one:

SELECT query, SUM(clicks) AS clicks, SUM(impressions) AS impressions,
       SAFE_DIVIDE(SUM(position * impressions), SUM(impressions)) AS avg_position
FROM `my-project.search_console_history_domain_example_com.search_analytics`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND query IS NOT NULL
GROUP BY query
ORDER BY clicks DESC
LIMIT 25;

More in examples/example_queries.sql.

Related MCP server: mcp-search-console

Keep it fresh

Search Console keeps adjusting the last ~3 days, so schedule two jobs:

macOS / Linux (cron):

crontab -e

15 6 * * *  $HOME/.local/bin/gsc-bq backfill --incremental >> $HOME/gsc-bq.log 2>&1
30 6 * * 1  $HOME/.local/bin/gsc-bq backfill --gaps        >> $HOME/gsc-bq.log 2>&1

Windows (Task Scheduler), from PowerShell or Command Prompt:

schtasks /Create /SC DAILY /ST 06:15 /TN "gsc-bq incremental" /TR "gsc-bq backfill --incremental"
schtasks /Create /SC WEEKLY /D MON /ST 06:30 /TN "gsc-bq gaps" /TR "gsc-bq backfill --gaps"

No server? Run it on GitHub Actions with a service account — see examples/github-actions-daily.yml and docs/service-account.md.

Use it from AI assistants

gsc-bq mcp starts an MCP server over stdio. It uses the same config as the CLI, so run gsc-bq setup first.

Claude Code (plugin)

/plugin marketplace add Ayresmic/search-console-bigquery
/plugin install gsc-bigquery@search-console-bigquery

Restart Claude Code. You get the MCP tools plus slash commands: /gsc-bigquery:setup, /gsc-bigquery:doctor, /gsc-bigquery:gsc-backfill, /gsc-bigquery:gsc-gap-fill, /gsc-bigquery:gsc-incremental, /gsc-bigquery:gsc-analyze, /gsc-bigquery:gsc-query, /gsc-bigquery:gsc-issues and /gsc-bigquery:gsc-cwv. The plugin runs the gsc-bq command, so install the CLI first (step 1 above).

Or without the plugin: claude mcp add gsc-bigquery -- gsc-bq mcp

Claude Desktop, Cursor, and other MCP clients

Add this to the client's MCP config (for Claude Desktop: claude_desktop_config.json):

{
  "mcpServers": {
    "gsc-bigquery": { "command": "gsc-bq", "args": ["mcp"] }
  }
}

If the client can't find gsc-bq, use the full path from which gsc-bq (macOS/Linux) or where.exe gsc-bq (Windows).

The MCP server reads the config written by gsc-bq setup. It ignores generic .env files in the folder your AI client is opened in, so another project's settings can't leak in.

What you can ask

"How did example.com do over the last 28 days compared to the 28 before?" "Which queries are in striking distance (positions 4-20)?" "Find keyword cannibalization on the blog." "Are our top 15 pages indexed?" "What are our Core Web Vitals on mobile?"

Analytics windows end on the latest day that has data, comparison windows are equal in length, and average position is impression-weighted (as in the Search Console UI). query_history runs only a single read-only SELECT and caps the bytes scanned (10 GB by default). submit_sitemap needs write access to Search Console, which the read-only sign-in from gsc-bq setup doesn't grant: use a service account with Full permission on the property for it.

Commands

Every command has --help.

Configuration

gsc-bq setup writes these for you. Settings are read from (highest priority first) environment variables, the file in GSC_BQ_CONFIG, a gsc-bq.env file in the current folder, and the user config file (~/.config/gsc-bq/config.env, or %APPDATA%\gsc-bq\config.env on Windows). Every key is documented in gsc-bq.env.example.

Where the data lands

One dataset per property, named {BQ_DATASET_PREFIX}_{slug}:

Each dataset has search_analytics (partitioned by date, clustered by query, page) and sites (a log of runs). Columns:

Only web search is pulled (not image, video or news), with the date, query, page, country and device dimensions.

Using Google's bulk export too

If you've turned on Search Console's native bulk data export, it fills BigQuery from the day you enabled it. gsc-bq link-bulk-export joins it with the backfilled history so search_analytics covers everything:

gsc-bq link-bulk-export --site sc-domain:example.com --export-dataset searchconsole   # shows the plan
gsc-bq link-bulk-export --site sc-domain:example.com --export-dataset searchconsole --apply
gsc-bq backfill --gaps --site sc-domain:example.com                                   # fill the history before the export

Details and caveats: docs/bulk-export.md.

Costs and quotas

• Search Console API: free. Google limits it to roughly 1,200 queries per minute per property; the backfill makes one request per 25,000 rows and retries rate-limit errors with back-off.

• BigQuery storage: a busy property's full 16 months is typically a few GB, i.e. cents per month (the first 10 GB per month are free).

• BigQuery queries: loads are free; each 7-day chunk is merged with a query that only reads the days being written. The analytics tools filter on the partition column. Most users stay inside the free 1 TB of queries per month.

Troubleshooting

Run gsc-bq doctor first — it diagnoses all of these.

FAQ

Can I get more than 16 months? No. Google deletes older Search Console data; no tool can recover it. Back up now and history accumulates from here.

Does re-running duplicate data? No. Every load is MERGEd on date, query, page, country and device.

Is my data sent anywhere? No. It goes from Google's Search Console API into your own BigQuery project. Nothing else is contacted, except the CrUX and PageSpeed APIs if you use those tools.

Service account instead of my own account? Yes — see docs/service-account.md.

Contributing

Issues and pull requests are welcome — see CONTRIBUTING.md. For security issues, see SECURITY.md.

License

MIT