Sitemap vs Index Coverage Audit

A systematic spreadsheet workflow that cross-references sitemap URLs, Google Search Console Index Coverage data, URL Inspection API results, and crawl signals reveals exactly which pages Google knows about and why it refuses to index them — enabling targeted remediation instead of guesswork.

The Problem

Founders and SEO leads pump content into sitemaps and wait. Weeks pass. Google Search Console’s Index Coverage report shows “Submitted URL not indexed” or “Crawled – currently not indexed” for hundreds of pages. Meanwhile, the sitemap shows “Success” and the page is technically live. The gap between submission and indexing is a black box.

The root cause is almost never a single issue. It’s a tangle of conflicting signals: sitemap URLs might contain parameters or trailing slashes that differ from what Google actually crawled; the canonicals may point to a different page; a soft 404 or a noindex tag might be served only to Googlebot; or the page might be an orphan that Google discovered through external links but never saw in the sitemap. Patching one signal without cross-referencing the others wastes cycles.

Founders lack a repeatable, transparent methodology to normalize all data sources into a single spreadsheet and systematically isolate the blocking factor per URL. Most audits rely on incomplete reports or manual spot-checks. Without a normalized, joinable dataset, you cannot distinguish between a technical error (broken HTTP status), a policy signal (noindex), or a quality filter (thin content). The playbook below closes that gap.

Core Framework

Key Principle 1: Normalization Equals Joinability

Every URL signal lives on its own island. The sitemap XML uses one string, the URL Inspection API returns another, the Index Coverage CSV export uses a third, and your server logs a fourth. Differences in case, trailing slash, URL encoding, and fragment identifiers break exact matches. You must apply a deterministic normalization function — lower case, strip fragments, remove trailing slashes (or keep consistently), and decode percent-encoding — to every URL before joining datasets. Without this, the audit is blind.

For example, a sitemap contains https://example.com/Product?Color=Red%20#section while Googlebot crawled https://example.com/product?Color=Red. After normalization both become https://example.com/product?Color=Red. Now you can match them.

Key Principle 2: Signals Are Hierarchical, Not Flat

When Google decides not to index a URL, it’s because one signal dominates. The hierarchy, from strongest to weakest, is:

• HTTP status: 4xx/5xx override everything → Google will not index.
• Robots.txt disallow → blocks crawling, thus indexing.
• noindex meta tag or X-Robots-Tag → explicit exclusion.
• Canonical mismatch → Google may choose a different canonical, discarding the submitted URL.
• Redirect chain: 301/302 to another URL → the target is indexed, not the source.
• Soft 404 or quality filter (thin content) → “Crawled – currently not indexed” often means Google crawled but considered the page too thin or duplicate.

Your spreadsheet must flag the highest-priority signal for each URL. Only after resolving those can you investigate quality feedback (which requires URL Inspection API inspection).

Key Principle 3: Orphans Reveal Hidden Index Inventory

URLs that are indexed but absent from the sitemap are “orphans” to your submission strategy. They may be valuable pages that Google found naturally (through links, backlinks, or Google Discover). Keeping them out of the sitemap means you lose control over their canonical signals and can’t monitor their index status through the sitemap report. Conversely, orphans that are indexed correctly can be added to the sitemap to reinforce importance. Orphans that are indexed but have poor content should be consolidated or removed.

Step-by-Step Execution

Step 1: Collect All Raw Data Sources

You will need four data sources. Export each as CSV or extract through API.

1. Google Search Console (GSC) Sitemap report – Download the “Submitted URLs” list from the Sitemaps section. If you have multiple sitemaps, download each and merge.
2. GSC Index Coverage report – From the “Pages” tab, export “Full report” (download as CSV). This includes indexed, not indexed, and excluded categories.
3. URL Inspection API responses – For every URL in the sitemap plus a sample of indexed orphans, query the URL Inspection API (part of Google Search Console API). Collect fields: indexingState, crawlStatus, verdict, canonical, robotsTxtState, pageFetchState, googleCanonical, ampIndexing, duplicateTags.
4. Server crawl or log data – Extract HTTP status codes for each URL from server logs or a crawl tool (e.g., Screaming Frog, Sitebulb). If logs aren’t available, run a lightweight crawl targeting only the sitemap URLs and note status codes.

Note on API rate limits: The URL Inspection API allows up to 2000 queries per day per property (Search Console quotas may be lower for free tier). Prioritize sitemap URLs first; then a random sample of indexed orphans.

Step 2: Normalize All URLs in Your Spreadsheet

Create a new column “normalized_url” in each sheet. Apply the following transformation (example formula for Google Sheets):

=LOWER(REGEXREPLACE(REGEXREPLACE(A2, "#.*$", ""), "(?<!/)$", ""))

This: - Lowercases the scheme, host, and path. - Removes fragment identifiers (everything after #). - Strips trailing slash unless the path is root (/). (Optional: you can choose to keep trailing slash consistently; just be consistent across all sources).

Importantly, do not remove query parameters — they are part of the identity of the page. But you may need to sort/deduplicate parameter order. Use a custom function to sort parameters alphabetically. Example Apps Script:

function sortQueryParams(url) {
  let [base, qs] = url.split('?');
  if (!qs) return base;
  let params = qs.split('&').sort();
  return base + '?' + params.join('&');
}

Apply this after lowercasing/fragment removal.

Step 3: Merge Datasets on Normalized URL

Use a VLOOKUP or INDEX/MATCH to bring the following columns into a master sheet:

• From Sitemap sheet: present (yes/no), last submitted date.
• From Index Coverage sheet: coverage status (indexed, not indexed, excluded), reason (if any).
• From URL Inspection API sheet: indexing state, canonical URL, crawl status, robots-txt state, page fetch state, verdict.
• From crawl sheet: HTTP status code.

Set up a master row per unique normalized URL. Deduplicate by keeping the row with the most recent data (or take the union of sitemap + Index Coverage – Index Coverage often has more rows because it includes URLs not submitted).

Step 4: Classify Each URL into a Signal Bucket

Add a “Status” column that evaluates the highest-priority signal. Use nested IF statements or a lookup table:

When multiple conditions apply (e.g., a 404 also has noindex), the top priority wins. You need to resolve them in order.

Step 5: Deep Dive on “Not Indexed” URLs Using URL Inspection API Details

For URLs flagged as “not indexed” (any status except “Healthy”), open the URL Inspection API response columns:

• inspectionResult.indexingState – can be INDEXING_ALLOWED, BLOCKED_BY_ROBOTS, BLOCKED_BY_4XX, BLOCKED_BY_NOINDEX, BLOCKED_BY_OTHER.
• inspectionResult.verdict – shows PASS, FAIL, NEUTRAL.
• inspectionResult.canonical – the URL Google considers the canonical. Compare with submitted URL.
• inspectionResult.googleCanonical – the page Google decided to index instead.
• inspectionResult.duplicateTags – list of duplicate URLs Google found.

Create a column “Root Cause” that synthesizes the most granular reason from the API and status. For example, if indexingState = BLOCKED_BY_NOINDEX but the HTTP status is 200, the root cause is noindex. If indexingState = INDEXING_ALLOWED but verdict = NEUTRAL and status is 200, then it’s likely thin content.

Step 6: Catalog Orphans (Indexed but Not in Sitemap)

From your merged data, filter rows where: - Sitemap = “not present” - Index Coverage status = “Indexed” (or any positive indexing state)

These are orphans. Add a column “Source” = “Orphan”. For each orphan, note its canonical URL from the API. If the canonical matches the URL, the page is standalone and should be added to the sitemap. If it points to a different URL, the orphan is actually a canonicalized duplicate — consider either making it the canonical or removing it.

Step 7: Build the Actionable Output Table

Generate a final sheet with only actionable rows. Columns:

Add a “Priority” column (P0 = blocking search traffic, P1 = reducing index coverage, P2 = informational). Export to CSV and share with the team.

Common Mistakes

• ❌ Not normalizing fragments and case. A sitemap URL https://example.com/About#section will never match Google’s https://example.com/about. Result: false positive “not in sitemap” or missed matches.
• ❌ Trusting the Index Coverage report alone. The report groups URLs by reason, but the reasoning is opaque. Always cross-check with the URL Inspection API, which gives the actual verdict and canonical it chose.
• ❌ Ignoring HTTP status from server logs. A page might return 200 to you but 404 to Googlebot due to cloaking or geo-blocking. The URL Inspection API’s pageFetchState shows Google’s perspective.
• ❌ Assuming sitemap URLs are correct. A sitemap may contain URLs that redirect or return errors. The Index Coverage report will flag these but the sitemap report might still say “Success”. You must check both.
• ❌ Forgetting to handle soft 404s. A page that looks like a 404 (little content) but returns a 200 is treated as a soft 404. The URL Inspection API’s verdict will be NEUTRAL or FAIL. Don’t mistake it for a healthy page.

Metrics to Track

• Sitemap Indexation Rate (SIR): (Number of sitemap URLs that are indexed) / (Total sitemap URLs). Target: >90% for most sites; <50% signals systemic issues.
• Orphan Indexed Pages Rate (OIR): (Number of indexed URLs not in sitemap) / (Total indexed URLs). Target: <20%. High orphan rate suggests missing sitemap coverage or accidental duplicate content.
• Noindex Rate in Sitemap (NRS): (Number of sitemap URLs with noindex) / (Total sitemap URLs). Target: 0%. If >5%, you have a contradiction: submitting URLs you tell Google to ignore.
• Canonical Mismatch Rate (CMR): (Number of sitemap URLs where canonical differs from submitted URL) / (Total sitemap URLs). Target: <2%. Mismatches signal that Google is ignoring your canonical hint for those URLs.
• Median Time to Index (MTI): For URLs that eventually index, time between submission and first confirmed indexing (from URL Inspection API’s lastCrawlTime). Benchmark depends on site authority; longer than 2 weeks suggests quality or technical issues.

Checklist

• [ ] Export sitemap URLs from Google Search Console (all sitemaps).
• [ ] Export Index Coverage full report from Google Search Console.
• [ ] Crawl all sitemap URLs with a tool that records HTTP status (Screaming Frog, Sitebulb, or custom script).
• [ ] Query URL Inspection API for each sitemap URL (rate-limit aware).
• [ ] Query URL Inspection API for a random sample of indexed orphans (optional but recommended).
• [ ] Normalize every URL: lowercase, remove fragment, sort query parameters, decide on trailing slash policy.
• [ ] Merge all data into a master spreadsheet using normalized URLs as keys.
• [ ] Classify each row into a signal bucket using the priority table.
• [ ] For each “not indexed” row, extract root cause from URL Inspection API fields.
• [ ] Identify orphans (indexed but not in sitemap).
• [ ] Build final action output table with explicit remediation steps.
• [ ] Prioritize P0 issues (noindex on sitemap URLs, canonical mismatches, blocked by robots.txt).
• [ ] Implement fixes: remove noindex tags, update sitemap, correct canonicals, fix redirects.
• [ ] Re-run audit after 2 weeks to measure improvement.

Using NQZAI for This Playbook

NQZAI provides an integrated platform that automates the most tedious parts of this audit:

• Bulk URL Inspection API calls – NQZAI’s connector handles Google Search Console API authentication and rate limiting, returning all fields for up to 10,000 URLs per day without manual scripting.
• Automatic URL normalization – The platform runs a configurable normalization pipeline (lowercase, fragment removal, parameter sort, trailing slash consistency) before joining data sources.
• One-click merge – Upload your sitemap XML, Index Coverage CSV, and crawl log (or connect to NQZAI’s built-in crawler). The merge engine deduplicates by normalized URL and aligns timestamps.
• Signal classification engine – NQZAI applies the priority hierarchy and produces the “Root Cause” column automatically, flagging contradictory signals (e.g., URL in sitemap but returns 404).
• Orphan detection and sitemap re-submission – NQZAI generates a list of orphans with their canonical status and, with one click, creates a new sitemap that includes them (or removes invalid URLs from the existing sitemap).
• Reporting dashboard – Visual breakdown of indexation rate by signal bucket, trend over time, and recommendation list sorted by impact.

By using NQZAI, you cut the manual spreadsheet work from 4–8 hours to under 30 minutes, and eliminate normalization errors that corrupt the audit.

How to Perform the Audit Using Google Sheets and Apps Script (Manual Method)

If you prefer full control without a platform, follow this sparse walkthrough:

1. Set up Google Sheets with seven sheets: raw_sitemap, raw_index_cov, raw_inspection, raw_crawl, normalized_master, classification, action_items.
2. Import sitemap URLs into raw_sitemap. Use IMPORTXML or a script to parse the sitemap XML.
3. Import Index Coverage CSV directly into raw_index_cov.
4. Run Apps Script that loops through each URL in raw_sitemap, calls the URL Inspection API via UrlFetchApp, and writes results to raw_inspection. Example snippet:

function inspectUrls() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('raw_sitemap');
  const urls = sheet.getRange('A2:A' + sheet.getLastRow()).getValues().flat();
  const apiKey = 'YOUR_API_KEY';  // from Google Cloud Console
  const siteUrl = 'sc-domain:example.com';  // or the property URL
  const results = [];
  urls.forEach(function(url) {
    const endpoint = `https://searchconsole.googleapis.com/v1/urlInspection/index:inspect?key=${apiKey}`;
    const payload = JSON.stringify({inspectionUrl: url, siteUrl: siteUrl});
    const response = UrlFetchApp.fetch(endpoint, {method: 'POST', contentType: 'application/json', payload: payload});
    results.push([url, response.getContentText()]);
  });
  // write results to raw_inspection sheet
}

1. Normalize using formulas or a custom function as shown in Step 2. Create a helper column in each raw sheet.
2. Merge using QUERY or a script that does VLOOKUP across normalized columns. Build a single normalized_master table.
3. Classify with nested IFs referencing the conditions table.
4. Extract root cause by parsing the inspection JSON with JSON.parse in a script.
5. Generate action items with FILTER and SORT.
6. Repeat weekly.

Frequently Asked Questions

Why does Google list a URL as “Submitted URL not indexed” even though it returns a 200?

The most common reasons: a noindex tag, a canonical pointing elsewhere, or a soft 404 (thin content). The URL Inspection API will tell you the exact verdict. Do not trust the status code alone — Googlebot may have received a different response than your browser.

Should I remove URLs from the sitemap if they are not indexed?

Not automatically. First identify the blocking signal. If it’s a temporary quality filter that you can fix (e.g., add more unique content), keep the URL in the sitemap and resolve the issue. If the URL should never be indexed (e.g., a thank-you page), remove it from the sitemap and add a noindex tag.

How often should I run this audit?

Weekly for sites with over 10,000 URLs or frequent content updates. Monthly for smaller, stable sites. Each run should compare the previous week’s metrics to spot regression (e.g., new sitemap URLs suddenly blocked).

Can I use the URL Inspection API for URLs not in my property?

No. The API only works for verified Search Console properties. You must use the exact site URL you verified (with or without sc-domain: prefix).

What is a healthy Sitemap Indexation Rate?

Above 90% for most informational sites. E-commerce sites with many similar product pages may naturally be lower (60-80%) due to Google’s duplicate-content filtering. Monitor the rate over time — a sudden drop often signals a technical issue like accidental noindex or robots.txt block.

My sitemap has 1000 URLs but only 50 are indexed. What should I do first?

Run this audit immediately. High-priority signals to check: unusual HTTP status (e.g., all returning 302), a global noindex on the site (e.g., via staging server), or a site-wide robots.txt disallow. If none of those, sample a few URLs with the URL Inspection API to see if they’re blocked by index coverage status or quality.

Sources

1. Google Search Central, Sitemaps overview – Official documentation on sitemap XML structure and submission.
2. Google Search Central, Consolidate duplicate URLs (canonical) – Guidelines on rel="canonical" and its effect on indexing.
3. Google Search Central, URL Inspection API reference – Full API endpoint description and response fields.
4. Google Search Central, Index Coverage report – Explanation of indexing statuses and reasons in Search Console.
5. Google Search Central, Robots.txt specifications – How robots.txt directives affect crawling and indexing.
6. IETF RFC 3986, Uniform Resource Identifier (URI): Generic Syntax – Standards for URL normalization (case sensitivity, percent-encoding, fragments).