# search-console-bigquery

[![CI](https://github.com/Ayresmic/search-console-bigquery/actions/workflows/ci.yml/badge.svg)](https://github.com/Ayresmic/search-console-bigquery/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)
![Python 3.11+](https://img.shields.io/badge/python-3.11%2B-blue.svg)

**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](https://cloud.google.com/sdk/docs/install). 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](#costs-and-quotas).
- **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

```bash
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

```bash
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](docs/service-account.md) instead (`gsc-bq setup --key-file key.json`).

### 3. Check

```bash
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

```bash
gsc-bq backfill --days 7
```

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

```bash
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:

```sql
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](examples/example_queries.sql).

---

## Keep it fresh

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

| When | Command | Why |
|---|---|---|
| Daily | `gsc-bq backfill --incremental` | Re-pulls the last 5 days (`INCREMENTAL_DAYS`). |
| Weekly | `gsc-bq backfill --gaps` | Fills any day a daily run missed. Only days with no data in BigQuery are requested again (for a site younger than 16 months that includes the empty days before it existed, which return nothing and are quick). |

**macOS / Linux (cron):**

```bash
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:

```powershell
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](examples/github-actions-daily.yml) and [docs/service-account.md](docs/service-account.md).

---

## Use it from AI assistants

`gsc-bq mcp` starts an [MCP](https://modelcontextprotocol.io) 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`):

```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?"

<details>
<summary>All 39 MCP tools</summary>

| Group | Tools |
|---|---|
| Discovery | `describe_environment`, `list_known_sites`, `dataset_for_site`, `check_auth` |
| Analytics (BigQuery) | `summary`, `top_queries_history`, `top_pages_history`, `trend`, `compare_periods`, `rising_queries`, `losing_queries`, `rising_pages`, `losing_pages`, `new_queries`, `lost_queries`, `low_hanging_keywords`, `ctr_opportunities`, `cannibalization`, `brand_vs_nonbrand`, `country_breakdown`, `device_breakdown`, `position_distribution`, `page_detail`, `query_detail`, `query_history` |
| Live Search Console API | `query_search_analytics`, `top_queries`, `top_pages` |
| Indexing and sitemaps | `list_sitemaps`, `get_sitemap`, `submit_sitemap`, `sitemap_health`, `inspect_url`, `bulk_inspect`, `indexing_summary` |
| Core Web Vitals | `cwv_origin`, `cwv_url`, `pagespeed_insights` (need `GOOGLE_API_KEY`) |
| Composite | `site_health` |

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.

</details>

---

## Commands

| Command | What it does |
|---|---|
| `gsc-bq setup` | Guided setup. `--yes` for non-interactive, `--key-file` for a service account, `--local` to write `./gsc-bq.env`. Re-running keeps your existing property selection. |
| `gsc-bq doctor` | Check config, credentials, APIs, Search Console access and BigQuery, with a fix for each problem. |
| `gsc-bq sites` | List your properties, their exact strings, and their BigQuery tables. |
| `gsc-bq backfill` | Pull data. Options: `--site` (repeatable), `--days`, `--start/--end`, `--incremental`, `--gaps`, `--chunk-days`, `--dry-run`. |
| `gsc-bq link-bulk-export` | Combine Google's native bulk export with backfilled history. Dry run unless `--apply`. |
| `gsc-bq mcp` | Run the MCP server. |
| `gsc-bq config-path` | Show where `setup` saves the config. |

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](gsc-bq.env.example).

| Variable | Default | Meaning |
|---|---|---|
| `GCP_PROJECT` | — | Project that holds the data (required). |
| `GOOGLE_APPLICATION_CREDENTIALS` | empty | Path to a service-account key. Empty = your gcloud sign-in. |
| `GSC_SITES` | all visible | Comma-separated properties to sync. |
| `BQ_DATASET_PREFIX` | `search_console_history` | Dataset name prefix. |
| `BQ_LOCATION` | `US` | Dataset location (`US`, `EU`, `us-central1`, ...). |
| `INCREMENTAL_DAYS` | `5` | Days re-pulled by `--incremental`. |
| `GOOGLE_API_KEY` | empty | Only for the Core Web Vitals / PageSpeed MCP tools. |
| `DEFAULT_BRAND_TERMS` | empty | Brand terms for `brand_vs_nonbrand`. |

## Where the data lands

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

| Property | Dataset |
|---|---|
| `https://www.example.com/` | `search_console_history_www_example_com` |
| `https://example.com/blog/` | `search_console_history_example_com_blog` |
| `http://example.com/` | `search_console_history_http_example_com` |
| `sc-domain:example.com` | `search_console_history_domain_example_com` |

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

| Column | Type | Notes |
|---|---|---|
| `site_url` | STRING | The property. |
| `date` | DATE | Partition column — always filter on it. |
| `query` | STRING | `NULL` = anonymized by Google (bulk-export rows only). |
| `page` | STRING | |
| `country` | STRING | ISO 3166-1 alpha-3, lowercase (`usa`, `gbr`). |
| `device` | STRING | `DESKTOP`, `MOBILE`, `TABLET`. |
| `search_appearance` | STRING | Always `NULL`: the API can't combine it with other dimensions. |
| `clicks`, `impressions` | INT64 | |
| `ctr`, `position` | FLOAT64 | `position` is the average for that row; aggregate it weighted by impressions. |
| `fetched_at` | TIMESTAMP | When the row was loaded. |

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:

```bash
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](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.

| Symptom | Fix |
|---|---|
| `insufficient authentication scopes` | Your gcloud login lacks the Search Console scope. Run `gsc-bq setup` (it signs you in with the right scopes). |
| `User does not have sufficient permission for site` | The signed-in account isn't a user on that property. Add it in Search Console › Settings › Users and permissions, or sign in with the right account. `gsc-bq doctor` shows which account is in use. |
| `... API has not been used in project ...` | `gcloud services enable searchconsole.googleapis.com bigquery.googleapis.com --project YOUR_PROJECT` |
| `USER_PROJECT_DENIED`, `serviceusage`, or `requires a quota project` | API calls are billed to `GCP_PROJECT`; grant the account *Service Usage Consumer* on that project. |
| BigQuery `Access Denied` | Grant the account *BigQuery Job User* and *BigQuery Data Editor* on the project. |
| "already holds data for ..." | Two properties map to the same dataset name. Back one of them up with a different `BQ_DATASET_PREFIX`. |
| 404 for a property | URL-prefix properties need the exact scheme and trailing slash (`https://www.example.com/`); Domain properties are `sc-domain:example.com`. Copy the string from `gsc-bq sites`. |
| Numbers lower than the Search Console UI | Expected: the API leaves out anonymized queries, and Google applies privacy filtering. Trends and rankings are unaffected. |
| MCP tools say "not set up" | Run `gsc-bq setup`, then restart the AI client so the server reloads the config. |
| MCP tools don't appear at all | The client can't find `gsc-bq` on its PATH. Put the full path (`which gsc-bq` / `where.exe gsc-bq`) in the MCP config. |

## 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](docs/service-account.md).

## Contributing

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

## License

[MIT](LICENSE)