The Cheapest Crawl Audit: Join Your Sitemap Against Your Logs

Most indexing investigations start in the wrong place: a third-party crawler, run against the site as it exists today, reporting what the site could look like to a bot. That is a simulation. You already have the observation.

Two lists you almost certainly have access to right now, the URLs in your sitemap and the requests in your access logs, answer three questions that no simulated crawl can answer, and they answer them in about twenty minutes.

The three questions

Submitted and never fetched. A URL sits in your sitemap and Googlebot has not requested it in the last 30 days. That is a discovery problem, and it is almost never a robots.txt problem; it is usually a linking problem. Nothing internal points at the page with enough weight for it to be worth scheduling.

Fetched and not submitted. Googlebot spends requests on URLs that are not in your sitemap at all. Faceted parameters, session artifacts, paginated archives, old redirect targets. That is a leak: crawl capacity you are paying for on inventory you did not intend to publish.

Fetched repeatedly and unchanged. The same URL requested dozens of times a week while its content has not changed in months. That is either a caching-header problem on your side or a scheduling judgement on Google’s side, and the response headers you send are the half you control.

Getting the two lists

The sitemap side is a fetch and a parse:

curl -s https://example.com/sitemap.xml \
  | grep -oE '<loc>[^<]+' \
  | sed 's/<loc>//' \
  | sort -u > sitemap-urls.txt

If you have a sitemap index, fetch the children first and concatenate. Do it against the live file over HTTP, not against the file in your repository: the point is to see what you actually publish, including whatever your CDN or a stale build is serving.

The log side depends on your stack, but the shape is always the same: filter to verified Googlebot, take the path, normalize it to the same form as your sitemap entries. Verification matters more than people expect. A meaningful share of traffic claiming to be Googlebot is not, and if you skip the reverse-DNS check you are auditing your scrapers, not your crawler. The full verification and analysis pipeline does this properly, against the published range files; the one-liner above is the version you run before you commit to building anything.

awk '$0 ~ /Googlebot/ {print $7}' access.log \
  | sed 's/?.*//' \
  | sort | uniq -c | sort -rn > bot-hits.txt

Normalize both sides the same way (trailing slashes, protocol, host, case) or the join will report differences that exist only in your text processing. This is the step that ruins the analysis, and it is the step everyone rushes.

The join

With both lists normalized, the join is a comm away, or a two-line query if you prefer SQL:

SELECT s.url,
       COALESCE(b.hits, 0) AS hits
FROM sitemap s
LEFT JOIN bot_hits b USING (url)
ORDER BY hits ASC;

The zero-hit rows are your discovery problem. Sort them by template and the pattern usually appears immediately: one section, one page type, one depth. Rarely is it random, and when it looks random you have a normalization bug.

Run the join the other way for the leak: bot hits with no matching sitemap row, sorted by request count descending. The top of that list is where your crawl capacity is going.

What this does not tell you

It does not tell you why. A URL with zero bot hits might be orphaned, might be buried at depth seven, might be one of forty near-duplicates where a different member of the cluster is getting the attention. The join narrows the population from “the whole site” to “these 340 URLs,” which is the part that saves the week. Explaining the 340 is still work.

It also says nothing about indexing. Fetched is not indexed, and a page can be requested regularly and still be selected against. Scheduling and selection are different systems with different failure modes, and conflating them is how people end up “fixing” crawl budget on a site whose actual problem is that its pages are not worth keeping.

Two lists, one join, three answers, no purchase required. Start here, then go looking for the why.