XML Sitemap Audit Template

Technical SEO Toolkit, template 5 of 6

XML Sitemap Audit Template

Reconcile sitemap URLs against status, canonical, robots and noindex to see what to add, remove or fix.

Which URLs should be removed from your XML sitemap (or fixed), which eligible URLs are missing from it, and which are fine.

  • Excel workbook (.xlsx), opens in Google Sheets
  • PDF field guide, 9 to 11 pages
  • Blank and worked CSV templates
  • DataForSEO import script

Free, no email required. Version 3.0, updated November 2026.

XML Sitemap Audit Template.xlsx
K13fx=IF(I13="","",IF(I13<>"Ready","n.a.",IF(AND(B13=1,J13=0),"Review removal or fix",IF(AND(B13=0,J13=1),"Review inclusion","Monitor"))))

Start HereDashboardWorkspaceDelivery QAField Guide

The formula above is the real one from cell K13. Scroll it sideways to read the whole rule.

Connected to the DataForSEO API

This template is designed around one DataForSEO endpoint. Pull the data, convert it with one command, and the workbook does the rest.

Setup steps

DataForSEO On-Page APIA crawl of your site: URLs, status codes, canonicals, inlinks and sitemap membershipon_page/task_post

Save the JSON responseThe full API response, kept as your source of truth.response.json

Run the import scriptOffline. No API calls, no credentials.dataforseo_import.py import --template 17

Paste at A13Dashboard, checks and action list update instantly.XML Sitemap Audit Template.xlsx

Column map for this template:Filled by the APIAudit trail (automatic)You add

Page URLListed in sitemapHTTP statusCanonical URLRobots allowedNoindex presentIntended indexableSource reference
5 of 8 input columns come straight from DataForSEO

What the XML sitemap audit template decides

An XML sitemap is a list of URLs you're asking Google to treat as canonical and indexable. When it includes redirects, 404s, noindexed pages and parameter duplicates, you're sending mixed signals and making Search Console's sitemap reports useless.

This XML sitemap audit template reconciles each URL's sitemap membership with what a crawl observed. A URL is eligible when it returns 200, is self-canonical, crawlable, not noindexed and intended for indexing. The template flags sitemap URLs that aren't eligible (Review removal or fix) and eligible URLs missing from the sitemap (Review inclusion).

Who it's for

  • Technical SEOs cleaning up sitemaps
  • Developers generating sitemaps from a CMS
  • Migration teams validating the new sitemap
  • Sites with indexing gaps in Search Console

Where it fits in your workflow

The XML sitemap audit template answers one question. These three templates answer the questions around it:

  • Indexability Audit Template: which pages that should be indexed have a technical conflict, which are technically fine but still not indexed, and which were never meant to be indexed.
  • Crawl Budget Analysis Template: which URL patterns soak up crawl requests without returning value, how much crawl activity each wastes, and what to do about it.
  • Redirect Mapping Template: whether each planned redirect actually reaches the approved destination in one permanent hop, on an indexable page whose canonical agrees, with a human sign-off.

What's inside the download

One ZIP with the workbook, an 8-column input template (blank and filled), the PDF field guide and an offline script that converts saved DataForSEO responses into the input CSV. The workbook has five tabs.

Workspace. The working table. 8 input columns, 3 calculated columns, room for 1,000 rows. Dropdowns, Yes/No fields and range checks catch bad inputs as you paste.
Start Here. What it decides, a four-step setup and the colour key.
Dashboard. KPI cards, charts, a ranked Top 10 and an automatic action list.
Delivery QA. Pass, fail or exclude each validation step with evidence, owner and date.
Field Guide. Every field explained, plus each formula in plain English.

How the XML sitemap checker calculation works

Eligible = 1 when status is 200, canonical equals URL, robots allows, noindex is 0 and intended indexable is 1. In sitemap but not eligible = Review removal or fix. Eligible but not in sitemap = Review inclusion. Otherwise Monitor.

The key formula, exactly as it appears in the workbook

=IF(I13="","",IF(I13<>"Ready","n.a.",IF(AND(B13=1,J13=0),"Review removal or fix",IF(AND(B13=0,J13=1),"Review inclusion","Monitor"))))
Result column Plain-English formula What it tells you
Observed sitemap eligibility IF(AND(HTTP status=200,Canonical URL=Page URL,Robots allowed=1,Noindex present=0,Intended indexable=1),1,0) Observed condition for inclusion in this sitemap plan.
Sitemap action IF(AND(Listed in sitemap=1,Observed sitemap eligibility=0),"Review removal or fix",IF(AND(Listed in sitemap=0,Observed sitemap eligibility=1),"Review inclusion","Monitor")) Suggests reconciliation based on intention and crawl evidence.

The results you will see

Review removal or fixReview inclusionMonitor

Green meets the checks, red needs fixing first, amber needs a human decision and blue marks an opportunity. Rows with missing or out-of-range inputs are held back in the Input check column until they're fixed, so a half-filled row never sneaks into a total.

How to use the XML sitemap audit template

  1. Parse your sitemapsExtract every URL from the sitemap index and child sitemaps. Mark in_sitemap = 1.
  2. Add crawled URLs not in the sitemapFrom a full crawl, add indexable URLs that aren't listed. Mark in_sitemap = 0.
  3. Record observed signalsStatus code, canonical, robots allowed, noindex and whether you intend the page to be indexed.
  4. Fix at the generatorMost sitemap errors come from the generator logic. Fix the rule, not the file.

The worked example in the Workspace tab. Clear the lime input cells, paste your CSV at A13 (values only) and every result recalculates.

Fill the XML sitemap audit template with DataForSEO

Every template in this library is built to run on DataForSEO data. This one uses DataForSEO On-Page API: a crawl of your site: URLs, status codes, canonicals, inlinks and sitemap membership.

Most of the data columns fill automatically. Add the business inputs listed below, then paste.

  1. Request the dataSend this request with your DataForSEO API login (HTTP Basic auth).
    POST https://api.dataforseo.com/v3/on_page/task_post [ { "target": "yourdomain.com", "max_crawl_pages": 1000 } ]

    Post the crawl task, wait for it to finish, then request on_page/pages with {"id": "<task id>", "limit": 1000} and save that response.

  2. Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
  3. Convert it with the included scriptIt runs offline, makes no API calls and never sees your credentials.
    python3 Automation/dataforseo_import.py import --template 17 --input response.json --output my-inputs.csv
  4. Add what the API can't knowRobots allowed, Noindex present, Intended indexable
  5. Paste at A13Clear the demo rows, paste the CSV values at Workspace cell A13 and the dashboard updates.
Fills automatically from DataForSEO You add
Page URL, Listed in sitemap, HTTP status, Canonical URL Robots allowed, Noindex present, Intended indexable

Source reference fills automatically with the task ID, endpoint, location, language, device and snapshot time, so every row is traceable. Before a big pull, estimate the cost with the DataForSEO pricing calculator. The same steps, plus an example request file, are in the download's Automation folder.

Worked example: Northbeam Outdoor

Every template ships filled with data from Northbeam Outdoor, a fictional outdoor-gear retailer that appears across all 40 workbooks, so you can see what useful output looks like before you add your own. This workbook has 16 example rows.

16Records
0Rows with input issues
3URLs needing inclusion review
9Eligible URLs

The example sitemap contains a sort-parameter URL, a 301, a closed store page that 404s, a noindexed tag page and a robots-blocked category. Two valuable guides and a product page are missing entirely.

Result breakdown

Result Rows Share
Review removal or fix 6 38%
Review inclusion 3 19%
Monitor 7 44%
Input issues 0 0%

Input fields

Keep the columns in this order when you prepare a CSV. A missing observation stays blank; use 0 only for a measured zero or a verified No. Fields marked DataForSEO can be filled automatically by the included script.

CSV field Column Meaning and rule Source
url Page URL Exact absolute URL. Keep case and query parameters unless a documented normalization rule applies. DataForSEO: url
in_sitemap Listed in sitemap 1 when the exact URL is present in the sitemap under review. 1 = Yes, 0 = No. DataForSEO: from_sitemap
status_code HTTP status Observed response for the sitemap URL. DataForSEO: status_code
canonical_url Canonical URL Resolved declared canonical. Enter MISSING when absent. DataForSEO: canonical_url
robots_allowed Robots allowed 1 when the intended bot is allowed to fetch the page. 1 = Yes, 0 = No. Your records
noindex Noindex present 1 if a blocking noindex is present. 1 = Yes, 0 = No. Your records
intended_indexable Intended indexable 1 for URLs intentionally eligible for indexing. 1 = Yes, 0 = No. Your records
source_ref Source reference Task ID, export filename and observation scope. Retain the untouched source response separately. Optional. DataForSEO: source_ref

Reading the result honestly

Use a union of sitemap and crawl inventories. A crawl that never discovers a URL cannot establish that it is absent from a sitemap.

Limits. DataForSEO from_sitemap records crawler discovery context. Verify actual XML membership when auditing a specific sitemap.

XML sitemap checker mistakes to avoid

Listing redirected URLsSitemaps should contain final destination URLs only.
Including noindex pagesAsking Google to index a page that says noindex is a contradiction.
Updating lastmod on every buildOnly change lastmod when content meaningfully changes, or Google will learn to ignore it.

Tips from practice

  • Split sitemaps by type (products, categories, articles) to read indexing rates per type in Search Console.
  • Re-run after every migration and major release.
  • Use the indexability audit to investigate URLs that are eligible but not indexed.

Frequently asked questions

What should be in an XML sitemap?

Only canonical, indexable URLs that return 200 and that you want in search results.

Should noindex pages be in the sitemap?

No. Remove them, or remove the noindex if you want them indexed.

How many URLs can a sitemap have?

Up to 50,000 URLs or 50 MB uncompressed per file. Use a sitemap index for more.

Does a sitemap guarantee indexing?

No. It helps discovery and signals preference, but Google still decides what to index.

Part of the Technical SEO Toolkit

Get all 6 Technical SEO templates in one ZIP, or the complete set of 40 SEO and GEO templates with the 401-page field handbook.

Download the Technical SEO Toolkit

See the complete SEO & GEO toolkit

Jourdan Rombough

Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.

More on digital marketing in our blog

See all posts