---
title: "Search Console API and BigQuery: automating performance…"
description: "Why automate Search Console The Search Console interface is great for exploration but limited for scale: the Performance report shows up to 1,000 rows in…"
url: https://optimizeall.com/learn/technical-seo-mastery/search-console-api-and-bigquery-automation
updated: 2026-10-05
---

Technical SEO Mastery · Migrations, auditing workflow and monitoring · lesson 18 of 18 · 16 min

# Search Console API and BigQuery: automating performance and indexing data

## Why automate Search Console

The Search Console interface is great for exploration but limited for scale: the Performance report shows up to 1,000 rows in the UI, you can't join it with revenue or crawl data, and you can't alert on it. The **Search Console API** and the **bulk data export to BigQuery** fix that. This lesson builds three things: an API pull for query-page data, a bulk URL inspection sample (Lesson 1.1 showed the basics), and BigQuery SQL for brand/non-brand and page-group reporting.

## Access and authentication

- Create a Google Cloud project, enable the **Google Search Console API**, and create a **service account**. Add the service account's email as a user on the Search Console property (restricted access is enough for reading).
- Store the key file path in an environment variable — never commit it. For personal scripts, OAuth user credentials also work.
- Scope: `https://www.googleapis.com/auth/webmasters.readonly`.

## Hands-on 1: Search Analytics API with pagination

```python
# pip install google-api-python-client google-auth pandas
import os, pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build

creds = service_account.Credentials.from_service_account_file(
    os.environ["GSC_KEY_FILE"], scopes=["https://www.googleapis.com/auth/webmasters.readonly"])
gsc = build("searchconsole", "v1", credentials=creds, cache_discovery=False)

def query_all(site, start, end, dims=("query", "page"), search_type="web", row_limit=25000):
    rows, start_row = [], 0
    while True:
        body = {"startDate": start, "endDate": end, "dimensions": list(dims),
                "type": search_type, "rowLimit": row_limit, "startRow": start_row, "dataState": "final"}
        resp = gsc.searchanalytics().query(siteUrl=site, body=body).execute()
        batch = resp.get("rows", [])
        rows += batch
        if len(batch) < row_limit:
            break
        start_row += row_limit
    df = pd.DataFrame([{**dict(zip(dims, r["keys"])), "clicks": r["clicks"], "impressions": r["impressions"],
                        "ctr": r["ctr"], "position": r["position"]} for r in rows])
    return df

df = query_all("sc-domain:example.com", "2026-08-01", "2026-08-28")
df["segment"] = df.page.str.extract(r"^https://www\.example\.com/([^/]+)/")[0].fillna("home")
df["brand"] = df["query"].str.contains(r"example ?brand|examplebrand", case=False, regex=True)
print(df.groupby(["segment", "brand"])[["clicks", "impressions"]].sum().sort_values("clicks", ascending=False))
```

Notes: the API returns at most the rows Google stores for the property and omits anonymised queries; use `dataState: "all"` if you need the freshest (incomplete) days; respect quota limits and back off on `429` errors.

## Hands-on 2: BigQuery bulk export

In Search Console → Settings → **Bulk data export**, link a Google Cloud project. Google then exports daily tables to a BigQuery dataset, including `searchdata_site_impression` (property-level aggregation) and `searchdata_url_impression` (URL-level). Exports start from the day you enable them — no backfill — so turn it on early.

```sql
-- Non-brand clicks and impressions by page group, last 28 complete days (URL-level table)
SELECT
  REGEXP_EXTRACT(url, r'^https://www\.example\.com/([^/]+)/') AS page_group,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 AS avg_position
FROM `my-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  AND search_type = 'WEB'
  AND NOT is_anonymized_query
  AND NOT REGEXP_CONTAINS(LOWER(query), r'example ?brand')
GROUP BY page_group
ORDER BY clicks DESC;
```

Position fields in the export are zero-based sums, hence `+ 1` after dividing by impressions. Check the export schema documentation for current field names before building dashboards. Connect the dataset to Looker Studio (or another BI tool) for page-group dashboards with release annotations.

## URL Inspection at scale: sample, don't sweep

The URL Inspection API (Lesson 1.1) has per-property daily and per-minute quotas, so it can't inspect a large site exhaustively. Use it as a **sampling instrument**: for each page group, inspect a random sample (for example 50-100 URLs) monthly, store verdict, coverage state and Google-selected canonical in BigQuery next to the performance data, and chart the share of each group that is indexed with a matching canonical. For whole-site coverage, rely on the Page indexing report filtered by sitemap segment.

## Hands-on 3: an alert on page-group clicks

Schedule a query that compares the last 7 complete days with the previous 7 by page group, and alert when a group falls more than a threshold you choose (for example 30%) with enough volume to matter. Pair it with the change monitor from Lesson 7.2: most sudden drops coincide with a template, robots or redirect change.

## Worked example: a UK publisher's brand/non-brand truth

A UK recipe publisher (illustrative) reports "organic up 10%" in the UI, but the BigQuery split shows branded clicks up (a TV mention) and non-brand clicks flat, with the "quick answer" page group down. The team uses the data to prioritise deeper content on the declining group and to report honestly to leadership, annotating the TV appearance.

## Measuring success

- Reports refresh automatically; no one copies data from the UI.
- Brand/non-brand and page-group trends are available within minutes.
- Alerts fire within days of material drops, and each is traced to a cause.

## Common mistakes

- Summing average position across rows (always weight by impressions).
- Forgetting anonymised queries when reconciling totals.
- Enabling bulk export too late — there is no historical backfill.
- Committing service account keys to a repository.

## Video lecture: Search Console API and BigQuery: automating performance and indexing data

Lecture coming soon · 14 chapters · about 8 minutes. Read the full transcript below.

1. Search Console API + BigQuery
2. Access
3. Hands-on 1: Search Analytics API
4. Gotchas
5. Hands-on 2: bulk export to BigQuery
6. The SQL
7. Hands-on 3: the drop alert
8. Example 1: the Monday email
9. Example 2: brand vs non-brand truth (illustrative)
10. Measures and mistakes
11. Indexing data at scale
12. Watch me do it: zero to page-group table
13. Governance
14. Recap and try this now

## Lecture transcript

### Search Console API + BigQuery

Search Console's interface is brilliant for exploring. It's terrible for scale. It shows a limited number of rows, you can't join it with revenue or crawl data, and it won't tell you on a Monday morning that a page group dropped thirty percent last week. In this lecture you'll automate Search Console with its API and the BigQuery bulk export, split brand from non-brand properly, and set up an alert that catches drops within days.

### Access

First, access. Create a Google Cloud project, enable the Search Console API, and create a service account. Then add that service account's email address as a user on your Search Console property. Restricted access is enough for reading. Keep the key file path in an environment variable and never commit the key to a repository. The scope you need is webmasters read-only. For personal scripts, OAuth user credentials work too.

### Hands-on 1: Search Analytics API

Hands-on one: the Search Analytics API. The function in the lesson text calls search analytics query with a date range, dimensions like query and page, the web search type, a row limit of twenty-five thousand, and a start row. It keeps paging until a batch comes back smaller than the limit. Then it builds a pandas data frame. From there, the script extracts a page group from the URL, flags branded queries with a regex, and sums clicks and impressions by group and brand.

### Gotchas

A few details that trip people up. The API omits anonymised queries, so query-level totals won't match page totals. Recent days are incomplete; use data state final for stable reporting, or all if you need the freshest partial data. Respect quotas and back off when you get four-twenty-nine errors. And never average the average position column across rows. Weight it by impressions, or better, use the BigQuery export, which gives you sums.

### Hands-on 2: bulk export to BigQuery

Hands-on two: the bulk data export. In Search Console settings, open Bulk data export and link a Google Cloud project. Google then exports daily tables into BigQuery, including a site-level impressions table and a URL-level impressions table. There's one crucial detail: exports start from the day you enable them. There's no backfill. So switch it on early, even if you won't use it for months.

### The SQL

The SQL in the lesson text computes non-brand clicks, impressions, CTR and average position by page group for the last twenty-eight complete days. It extracts the page group with a regular expression, filters to web search, excludes anonymised queries and branded terms, and calculates average position as the sum of position divided by impressions, plus one, because the export stores zero-based position sums. Connect the dataset to Looker Studio or another BI tool for dashboards with release annotations.

### Hands-on 3: the drop alert

Hands-on three: an alert. Schedule a query that compares the last seven complete days with the previous seven, by page group. Alert when a group falls by more than a threshold you choose, say thirty percent, and only when volume is large enough to matter. Pair it with the change monitor from the previous lesson, because most sudden drops coincide with a template, robots or redirect change. The alert tells you where; the change monitor usually tells you why.

### Example 1: the Monday email

Worked example one, simple. A Leeds accountancy firm's marketing manager spends two hours every month copying Search Console data into a spreadsheet. A thirty-line script using the API now pulls the same data, groups it by service page, and emails a table every Monday. It saves time, but more importantly, the numbers are consistent every month, which the manual process never was.

### Example 2: brand vs non-brand truth (illustrative)

Worked example two, with illustrative details. A UK recipe publisher reports organic up ten percent in the interface. The BigQuery split tells a different story. Branded clicks are up, thanks to a TV mention, while non-brand clicks are flat, and the quick-answer page group is down. The team uses the data to prioritise deeper content for the declining group, and reports honestly to leadership, with the TV appearance annotated on the chart.

### Measures and mistakes

How do you measure success? Reports refresh automatically, and nobody copies data out of the interface. Brand versus non-brand and page-group trends are available in minutes. And alerts fire within days of material drops, with each one traced to a cause. Common mistakes: summing or averaging average position without weighting, forgetting anonymised queries when reconciling totals, enabling bulk export too late, and committing service account keys to a repository.

### Indexing data at scale

What about indexing data? The URL Inspection API has daily and per-minute quotas per property, so you can't inspect a large site exhaustively. Treat it as a sampling instrument. Each month, inspect a random sample of fifty to a hundred URLs per page group, store the verdict, coverage state and Google-selected canonical in BigQuery next to your performance data, and chart the share of each group that's indexed with a matching canonical. For whole-site coverage, use the Page indexing report filtered by sitemap segment.

### Watch me do it: zero to page-group table

Watch me do it. I'll get from zero to a non-brand page-group table. Step one: in Google Cloud, I create a project, enable the Search Console API, create a service account, and download its key into a folder outside the repository. I set the GSC key file environment variable to that path. Step two: in Search Console, Users and permissions, I add the service account's email with restricted access. Step three: I run the Search Analytics script for the last twenty-eight days with query and page dimensions. It pages through three batches of twenty-five thousand rows. Step four: I check the output against the interface: page-level clicks for the top page match; query totals are lower, as expected, because anonymised queries are omitted. Step five: in Settings, I turn on bulk data export to the same project. Nothing appears today; tables start tomorrow, with no backfill. Step six: a few days later, I run the SQL from the lesson in BigQuery and get non-brand clicks, CTR and weighted average position by page group. Step seven: I connect it to Looker Studio and add annotations for releases. Now leadership has one official view.

### Governance

And a word on governance. Once Search Console data lives in BigQuery, it becomes company data. Decide who can access the dataset, how long you keep it, and which dashboards are the official source of truth, so marketing, product and leadership stop arguing over different numbers. Document the brand regex and page group rules in one place, and version them, because changing a regex silently changes every trend. Small discipline, big payoff in trust.

### Recap and try this now

Recap. Use the API for flexible pulls with pagination, and the bulk export for complete daily data in BigQuery. Split brand and non-brand, group by page type, weight position properly, and alert on real drops. Try this now. Turn on bulk data export today, even if you won't use it yet. Then run the API script for the last twenty-eight days, and look at non-brand clicks by page group. That's the view your leadership actually needs.

## Key takeaways

- The API and BigQuery bulk export overcome the UI's row limits and let you join, segment and alert.
- Authenticate with a service account added as a Search Console user; keep keys out of code and repositories.
- Page through Search Analytics results with startRow; anonymised queries are omitted; weight position by impressions.
- Bulk export has no backfill — enable it early; compute average position as Σ sum_position / Σ impressions + 1.
- Alert on page-group drops and pair alerts with change monitoring to find causes quickly.

## Try it

Enable bulk data export, run the Search Analytics script for 28 days, and produce a non-brand clicks table by page group for your stakeholders.

- [Previous: Auditing workflow, tools and Search Console monitoring](https://optimizeall.com/learn/technical-seo-mastery/auditing-workflow-and-search-console-monitoring)
- [All lessons of Technical SEO Mastery](https://optimizeall.com/learn/technical-seo-mastery)