Indexation Audit Spreadsheet: Track Coverage, Errors & Recovery

An indexation audit spreadsheet is the operational control panel for tracking which of your URLs Google has indexed, which are stuck in "Discovered - currently not indexed" or "Crawled - currently not indexed" limbo, and whether your remediation efforts are actually moving pages into the primary index over time. Google Search Console's Coverage report (now called the "Pages" report) provides the raw data — but it resets views daily, offers no historical tracking, and can't calculate week-over-week recovery rates. A structured spreadsheet that pulls these coverage categories into a time-series format gives you the trend visibility that GSC lacks.

This guide provides the complete spreadsheet architecture — column structure, status category taxonomy, formulas for indexation rate and error velocity, and the weekly snapshot workflow that transforms GSC's point-in-time data into an actionable recovery tracker.

The GSC Coverage Category Taxonomy

Google Search Console's Pages report classifies every submitted URL into one of seven status categories. Understanding the exact definition of each category is essential before building any tracking system:

💡 Critical Distinction: "Crawled - currently not indexed" means Google saw your content and rejected it. "Discovered - currently not indexed" means Google hasn't bothered to look yet. The remediation strategies are different: the first requires content quality improvements, the second requires crawl priority improvements (internal linking, sitemap signals).

Spreadsheet Architecture: Column Structure

Sheet 1: Weekly Coverage Snapshot

This is the primary tracking sheet. Each row represents a weekly snapshot date, and columns track the count of URLs in each GSC status category:

Column A: Snapshot Date (YYYY-MM-DD)
Column B: Total Submitted URLs (from sitemap)
Column C: Indexed (Valid)
Column D: Indexed, Not Submitted in Sitemap
Column E: Crawled - Currently Not Indexed
Column F: Discovered - Currently Not Indexed
Column G: Duplicate Without User-Selected Canonical
Column H: Duplicate, Google Chose Different Canonical
Column I: Not Found (404)
Column J: Soft 404
Column K: Server Error (5xx)
Column L: Blocked by robots.txt
Column M: Noindex Tag
Column N: Other Excluded

Computed Metrics Columns

Add these calculated columns to transform raw counts into actionable rates:

Column O: Indexation Rate = C / B × 100
         Formula: =ROUND(C2/B2*100, 1)

Column P: Error Rate = (I + J + K) / B × 100
         Formula: =ROUND((I2+J2+K2)/B2*100, 1)

Column Q: Exclusion Rate = (G + H + L + M + N) / B × 100
         Formula: =ROUND((G2+H2+L2+M2+N2)/B2*100, 1)

Column R: Pending Rate = (E + F) / B × 100
         Formula: =ROUND((E2+F2)/B2*100, 1)

Column S: Week-over-Week Indexed Change = C2 - C1
         Formula: =C2-C1 (starting from row 3)

Column T: Recovery Velocity = (C2 - C_first) / weeks_elapsed
         Formula: =ROUND((C2-C$2)/((A2-A$2)/7), 1)

Sheet 2: URL-Level Issue Tracker

For the specific URLs that need remediation, create a detailed tracker:

Column A: URL
Column B: GSC Status (dropdown: the 11 categories above)
Column C: Date First Detected
Column D: Root Cause (dropdown: Thin Content, Missing Canonical,
          Redirect Loop, Robots.txt Block, Server Error,
          Duplicate Content, Parameter URL, Orphan Page)
Column E: Assigned To
Column F: Fix Applied (description of remediation)
Column G: Fix Date
Column H: Current Status (dropdown: Open, In Progress, Fixed,
          Verified, Won't Fix)
Column I: Days to Resolution = G - C
         Formula: =IF(G2<>"", G2-C2, TODAY()-C2)
Column J: Notes

Exporting Data From Google Search Console

GSC does not offer an API for the Pages (Coverage) report directly, but you can export the data manually or via the Google Search Console API for URL inspection:

Manual Export Workflow (Weekly)

1. Open Google Search Console → Pages
2. Click each status category tab
3. Export the URL list (CSV download)
4. Paste URL counts into your Weekly Coverage Snapshot sheet
5. Import specific URLs into the URL-Level Issue Tracker

Automated Export via GSC API + Python

For sites with thousands of URLs, automate the weekly snapshot with a Python script that queries the URL Inspection API:

import csv
from datetime import date
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build

def fetch_coverage_snapshot(
    property_url: str,
    credentials_path: str
) -> dict[str, int]:
    """
    Fetch indexed/error counts from GSC URL Inspection API.
    Note: The API inspects URLs individually — batch via sitemap URLs.
    """
    creds = Credentials.from_authorized_user_file(credentials_path)
    service = build("searchconsole", "v1", credentials=creds)

    # Read sitemap URLs to inspect
    with open("sitemap_urls.txt") as f:
        urls = [line.strip() for line in f if line.strip()]

    status_counts: dict[str, int] = {
        "indexed": 0,
        "crawled_not_indexed": 0,
        "discovered_not_indexed": 0,
        "error_404": 0,
        "error_server": 0,
        "excluded_canonical": 0,
        "excluded_noindex": 0,
        "excluded_robots": 0,
    }

    for url in urls:
        result = service.urlInspection().index().inspect(
            body={
                "inspectionUrl": url,
                "siteUrl": property_url,
            }
        ).execute()

        verdict = result["inspectionResult"]["indexStatusResult"]["verdict"]
        coverage = result["inspectionResult"]["indexStatusResult"].get(
            "coverageState", ""
        )

        if verdict == "PASS":
            status_counts["indexed"] += 1
        elif "not indexed" in coverage.lower():
            if "crawled" in coverage.lower():
                status_counts["crawled_not_indexed"] += 1
            else:
                status_counts["discovered_not_indexed"] += 1
        elif "404" in coverage:
            status_counts["error_404"] += 1

    return status_counts


def append_to_spreadsheet(counts: dict[str, int], csv_path: str):
    """Append today's snapshot to the tracking CSV."""
    today = date.today().isoformat()
    row = [today] + list(counts.values())
    with open(csv_path, "a", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(row)

💡 API Rate Limits: The URL Inspection API has a quota of 2,000 inspections per day per property. For sites with 10,000+ URLs, inspect a stratified sample (all error URLs + a random subset of indexed URLs) rather than the full URL set.

Building the Recovery Progress Dashboard

Indexation Rate Trend Chart

Plot Column O (Indexation Rate) against Column A (Snapshot Date) as a line chart. A healthy site shows a flat or upward trend above 90%. A declining trend signals content quality issues, technical regressions, or manual actions.

Error Velocity Tracking

Track the week-over-week change in total errors (404 + Soft 404 + 5xx). Error velocity — the rate at which new errors appear — is more actionable than absolute error count:

Error Velocity = This Week's Total Errors - Last Week's Total Errors

Positive velocity → New errors appearing faster than you fix them
Zero velocity     → Error count stable (fixing at same rate as new ones appear)
Negative velocity → Net error reduction — recovery is working

Recovery Time Estimation

With 4+ weeks of data, calculate the average recovery velocity and estimate time to target:

Avg Weekly Recovery = Average of Column S (WoW Indexed Change)
                      over last 4 weeks

Pages Remaining = Target Indexed Count - Current Indexed Count

Estimated Weeks = Pages Remaining / Avg Weekly Recovery

This gives stakeholders a concrete timeline: "At current recovery velocity, we'll reach 95% indexation in 6 weeks."

Common Indexation Issues and Spreadsheet-Driven Remediation

Issue: "Crawled - Currently Not Indexed" Growing Week-Over-Week

This status means Google crawled the page and decided the content wasn't worth indexing. The remediation checklist:

1. Content quality audit — Pages with fewer than 300 words of unique body text are high-risk for this status. Expand thin pages or consolidate them into comprehensive parent pages.
2. Internal link audit — Pages with zero or one internal link receive minimal crawl priority and PageRank. Add contextual internal links from topically related pages.
3. Duplicate content check — If a near-duplicate page exists with stronger signals, Google may index the stronger version and exclude yours. Run a SimHash comparison across your content corpus.

Track each remediated URL in Sheet 2 with the fix date and monitor whether it transitions to "Indexed" in subsequent weekly snapshots.

Issue: "Duplicate Without User-Selected Canonical" Spiking

This means Google found duplicate content across multiple URLs and couldn't determine the correct canonical because you didn't set one. Common causes:

• HTTP and HTTPS versions both accessible
• www and non-www versions both resolving
• Trailing slash and non-trailing-slash variants
• URL parameters creating content-identical pages

# Quick check: Does your site resolve on all four protocol/subdomain variants?
for url in \
  "http://example.com" \
  "https://example.com" \
  "http://www.example.com" \
  "https://www.example.com"; do
  echo -n "$url → "
  curl -sI "$url" | grep -i "location\|HTTP/"
done

All three non-canonical variants should 301 redirect to the single canonical origin. If any returns a 200, Google sees duplicate content.

Issue: 404 Count Increasing Despite No Intentional Deletions

New 404s appearing without page deletions indicate:

• Broken internal links pointing to URLs that were restructured
• External backlinks to URLs changed during a migration
• CMS-generated URLs from draft/preview pages that leaked into the crawl graph

Export the 404 URL list from GSC and cross-reference against your access logs to identify the referrer (internal page or external backlink) driving Googlebot to the dead URL:

# Find the referrer for a specific 404 URL in access logs
grep "GET /old-page-url" /var/log/nginx/access.log | \
  grep "Googlebot" | \
  awk -F'"' '{print $4}' | sort | uniq -c | sort -rn

How BugViso Automates Indexation Coverage Analysis

Building and maintaining an indexation audit spreadsheet manually works for small sites, but falls apart at scale. BugViso's multi-page site crawl engine replicates much of this analysis automatically by crawling your site through the same headless browser rendering pipeline that Googlebot uses.

During every crawl, BugViso audits the indexability signals that determine GSC coverage status. The AI Search Readiness engine flags noindex via <meta name="robots"> or the X-Robots-Tag header — the directive that pushes pages into the "Noindex tag" excluded category. The canonicalization module compares each page's URL against its <link rel="canonical">, detecting the protocol mismatches and homepage-canonicalization errors that generate "Duplicate without user-selected canonical" warnings. The concurrent link validator probes every internal link, catching the broken references that generate 404 errors in GSC's Coverage report.

The duplicate content detection engine runs SimHash near-duplicate analysis across all crawled pages, surfacing the content-similarity patterns that cause Google to choose unexpected canonicals. The internal link graph engine measures click depth and identifies orphan pages — the pages most likely to be stuck in "Discovered - currently not indexed" because they receive zero internal link equity and minimal crawl priority.

Advanced Spreadsheet Techniques

Conditional Formatting for Status Changes

Apply conditional formatting to the WoW change column (Column S) to instantly visualize trends:

Green background: Value > 0 (indexation increasing)
Yellow background: Value = 0 (indexation stable)
Red background: Value < 0 (indexation declining)

Pivot Table for Root Cause Analysis

Create a pivot table from Sheet 2 (URL-Level Issue Tracker) with:

• Rows: Root Cause (Column D)
• Values: Count of URLs, Average Days to Resolution
• Filters: Current Status

This reveals which root causes produce the most issues and which take the longest to resolve — data that drives engineering prioritization.

Automated Alerts

In Google Sheets, use the =IF() function with email notifications (via Google Apps Script) to alert when metrics breach thresholds:

// Google Apps Script: Alert when indexation rate drops below 85%
function checkIndexationRate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Weekly Coverage");
  const lastRow = sheet.getLastRow();
  const indexationRate = sheet.getRange(lastRow, 15).getValue(); // Column O

  if (indexationRate < 85) {
    MailApp.sendEmail(
      "seo-team@company.com",
      `⚠️ Indexation Rate Alert: ${indexationRate}%`,
      `The site indexation rate has dropped to ${indexationRate}%. `
      + `Check the audit spreadsheet for details.`
    );
  }
}

Set a time-driven trigger to run checkIndexationRate() every Monday after the weekly data import.

Frequently Asked Questions

How often should I update the indexation audit spreadsheet?

Weekly is the standard cadence. GSC data has a 2–3 day processing delay, so Monday snapshots capturing the previous week's data provide the most consistent baseline. For sites recovering from a penalty or major migration, daily snapshots for the first 30 days provide higher-resolution recovery tracking.

What's a normal indexation rate for a well-maintained site?

A healthy site with clean technical SEO should have 90–98% of submitted (sitemap) URLs indexed. The 2–10% gap accounts for intentionally excluded pages (pagination, filtered views, staging URLs that leaked). An indexation rate below 70% on a site with substantial content indicates systemic technical issues.

Should I track all GSC status categories or just the important ones?

Track all 11+ categories. "Excluded" categories like "Blocked by robots.txt" and "Noindex tag" should be stable and intentional. If these counts change unexpectedly, it signals an accidental robots.txt edit or CMS plugin adding unintended noindex tags. Tracking everything catches regressions that targeted monitoring would miss.

How do I prioritize which "Crawled - currently not indexed" pages to fix first?

Prioritize by business value × fix effort. Pages targeting high-volume commercial keywords with thin content need expansion first. Pages with strong backlink profiles that Google refuses to index need content quality investigation. Use your keyword research data to rank the "crawled not indexed" URLs by their target keyword's search volume.

Can I automate the entire spreadsheet with the GSC API?

Partially. The GSC URL Inspection API provides per-URL indexation status, but the aggregate coverage counts shown in the GSC UI (total indexed, total errors) are not available via API. You can automate URL-level checks for your sitemap URLs (up to 2,000/day) and aggregate the counts yourself.

What's the difference between "Discovered" and "Crawled" not indexed?

"Discovered - currently not indexed" means Google found the URL (via sitemap, internal link, or external link) but hasn't allocated crawl budget to fetch it yet. The page has never been rendered. "Crawled - currently not indexed" means Google fetched and rendered the page but decided the content quality or signals weren't sufficient for inclusion in the primary index. The first is a crawl priority problem; the second is a content quality problem.

Conclusion

An indexation audit spreadsheet transforms GSC's static, point-in-time coverage data into a time-series recovery tracker with trend visibility, velocity metrics, and root-cause attribution — and when paired with the automated crawl-level indexability checks that a BugViso site audit runs across canonical tags, noindex directives, duplicate content, and orphan page detection, the spreadsheet becomes the monitoring layer while BugViso handles the diagnostic depth.