Automating Search Console sitemap indexing data into BigQuery

Google Search Console shows how Google indexes a site, and for large sites one view is especially useful: the Page Indexing report filtered by sitemap. It tells you how many URLs were submitted, how many are indexed, how many aren't, and why.

That's easy for one sitemap. This project had 569. Opening every sitemap's report by hand and copying the numbers wasn't practical, so I built a small data pipeline instead:

The result was a reusable dataset of sitemap-level indexing performance, including Google's reasons for not indexing.

The problem

For every sitemap, I wanted the same set of numbers. For example:

The most valuable part was the breakdown of the URLs that weren't indexed:

With this for every sitemap in BigQuery, indexing problems could be analysed with SQL instead of clicking through Search Console.

Why browser automation?

The sitemap-level Page Indexing numbers were available in the Search Console interface, but not through the API in the form this analysis needed. So browser automation became the collection layer:

• Python for orchestration and parsing.
• Playwright to control a signed-in Chromium browser.
• Google Search Console as the data source.
• BigQuery for structured storage and analysis.

To be clear: Playwright wasn't crawling Google's search results. It automated reports inside a Search Console property I had permission to access.

How the pipeline works

It starts with a registry of all the sitemaps to analyse, and processes each sitemap on its own:

1. Stay signed in to Search Console

Automating the Google sign-in over and over is a bad idea. Instead, I used Playwright's persistent browser context: I signed in by hand once, and the saved Chromium profile kept the session for later runs.

from playwright.sync_api import sync_playwright

PROFILE_DIR = "gsc_browser_profile"

with sync_playwright() as p:

    context = p.chromium.launch_persistent_context(
        PROFILE_DIR,
        headless=False
    )

    page = (
        context.pages[0]
        if context.pages
        else context.new_page()
    )

The profile folder itself stays out of source control:

gsc_browser_profile/

2. Go straight to each sitemap's report

A useful discovery during development: the selected sitemap is part of the Page Indexing report's URL. So instead of clicking through menus for every sitemap, Python builds the report URL and Playwright opens it directly.

from urllib.parse import urlencode

def build_report_url(property_url, sitemap_url):

    params = {
        "resource_id": property_url,
        "pages": "SITEMAP",
        "sitemap": sitemap_url,
    }

    return (
        "https://search.google.com/search-console/index?"
        + urlencode(params)
    )

3. Wait for the report to load

Search Console renders its reports with JavaScript, so a loaded page doesn't mean the report has finished loading. The script waits for the report before reading anything.

While building the parser, the most useful debugging step was simply printing the page text withpage.locator("body").inner_text(). It showed exactly how Search Console writes labels like “Indexed”, “Why pages aren't indexed”, “Crawled - currently not indexed” and “Server error (5xx)”.

4. Turn the report into structured data

Each report became a record like this:

{
  "sitemap_url": "https://example.com/sitemaps/example-001.xml",
  "submitted_urls": 10000,
  "indexed_urls": 605,
  "not_indexed_urls": 9395,
  "index_rate": 0.0605,
  "indexing_reasons": [
    { "reason": "Crawled - currently not indexed", "reason_url_count": 6615 },
    { "reason": "Discovered - currently not indexed", "reason_url_count": 2777 },
    { "reason": "Server error (5xx)", "reason_url_count": 2 },
    { "reason": "Not found (404)", "reason_url_count": 1 }
  ]
}

At that point, numbers that only lived inside the Search Console interface had become data.

5. Check the numbers before loading them

Simple consistency checks caught parsing problems before bad data reached the reports. For the example above:

6. Model the data in BigQuery

An important decision was how to store the reasons. One row per reason would repeat the sitemap's totals on every row. Instead, each sitemap snapshot is one row, with the reasons stored inside it as a repeated record:

snapshot_date
sitemap_url
sitemap_name
sitemap_family

submitted_urls
indexed_urls
not_indexed_urls
index_rate

indexing_reasons          (repeated record)
    ├── reason
    └── reason_url_count

source
created_at

That keeps the totals clean and still lets you query across reasons:

SELECT
  r.reason,
  SUM(r.reason_url_count) AS affected_urls
FROM `project.dataset.sitemap_indexing_daily` t
CROSS JOIN UNNEST(t.indexing_reasons) r
GROUP BY r.reason
ORDER BY affected_urls DESC;

Scaling to 569 sitemaps

The proof of concept worked for one sitemap. Running hundreds of Search Console reports back to back wasn't reliable, so the script moved to controlled batches, for example BATCH_SIZE = 20 withDELAY_SECONDS = 90 between them.

More importantly, it became resumable. Before each batch, the script asks BigQuery which sitemaps are already done, and only processes the rest:

SELECT DISTINCT sitemap_url
FROM `project.dataset.sitemap_indexing_daily`;

Restarting the script never meant starting the whole collection again.

Handling rate limits

During the full run, Search Console eventually answered with 429 Too Many Requests. Rather than retrying aggressively, the script stops the batch safely:

if "Too Many Requests" in body_text:

    print("429 detected.")
    print("Stopping this batch safely.")

    break

Everything collected so far was already in BigQuery, so the run could resume later without losing progress or repeating work.

A good automation pipeline isn't one that never fails. It's one that can stop safely and continue from where it left off.

The result

The dataset ended up covering Page Indexing data for the complete sitemap inventory, and the workflow changed completely:

The dataset can answer questions like these:

Adding page variants

The project went one step further. We already had data describing the four page layouts in use, so by mapping sitemap families to page variants and joining that with the indexing data, the reports could compare indexing performance between layouts:

This is where the project became more than exporting Search Console data: it became a dataset for technical SEO experiments.

Final architecture and stack

• Python
• Playwright
• Chromium
• Google Search Console
• Google Cloud
• BigQuery
• SQL
• JSON

No complex infrastructure was needed. The hard part was making the process reliable across hundreds of dynamically rendered Search Console reports.

What I learned

1. Data that only exists in a UI can still become analytical data

When a useful report isn't available through the API in the form you need, careful browser automation can be a practical collection layer.

2. Data modelling matters as much as extraction

Storing the reasons as nested records kept sitemap totals from being duplicated and made every later query simpler.

3. Design automation to be interrupted

Expired sign-ins, UI changes, network failures and rate limits are normal with browser automation. Building in resumability from the start makes a huge difference.

Closing thoughts

This started with a simple question: how can I analyse Page Indexing data across hundreds of sitemaps without checking every report by hand?

The answer wasn't just “scrape Search Console”. It took browser automation, validation, resumable processing, data modelling and SQL, working together as a small pipeline. The result turned hundreds of separate reports into one BigQuery dataset that can be analysed by sitemap, sitemap family, indexing reason and page variant.

That's the part of technical SEO automation I enjoy most: turning a repetitive investigation into a reusable data system.