XML Sitemap Audit Template
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.
=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"))))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.
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
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.
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
- Parse your sitemapsExtract every URL from the sitemap index and child sitemaps. Mark in_sitemap = 1.
- Add crawled URLs not in the sitemapFrom a full crawl, add indexable URLs that aren't listed. Mark in_sitemap = 0.
- Record observed signalsStatus code, canonical, robots allowed, noindex and whether you intend the page to be indexed.
- 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.
- 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.
- Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
- 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
- Add what the API can't knowRobots allowed, Noindex present, Intended indexable
- 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.
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
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.
Use it with these templates
Each template answers one decision. These are the ones that feed into, or follow from, the XML sitemap audit template.
11Indexability Audit TemplateOn-Page API
Check status, robots, noindex and canonical on every page you want indexed, then see exactly what to fix first.
14Crawl Budget Analysis TemplateOn-Page API
Group verified Googlebot requests by URL pattern, measure crawl waste and pick the right fix for each pattern.
08Redirect Mapping TemplateOn-Page API
Map every old URL to its new home and verify each redirect: one permanent hop, a 200 page, no noindex, matching canonical.
12Hreflang Audit TemplateOn-Page API
Validate every hreflang relationship: alternate status, self-reference, return tags, canonical match and locale codes.
37Programmatic SEO ChecklistOn-Page API
QA every generated page for verified data, unique value, indexability, links and rendering before release.
18Schema Markup Audit TemplateOn-Page API
Check presence, syntax, required fields, visible-content match and policy for every structured data type you ship.
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.
Further reading on Optimization Theory
Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.






