Reading server logs to find out what crawlers actually do

# Reverse DNS, then forward DNS on the result. Real Googlebot resolves
# to googlebot.com or google.com, and the forward lookup returns the
# original IP. Anything else is impersonating.

verify() {
  host=$(dig +short -x "$1" | sed 's/\.$//')
  case "$host" in
    *.googlebot.com|*.google.com)
      dig +short "$host" | grep -q "^$1$" && echo "$1 verified ($host)" \
                                         || echo "$1 FORWARD MISMATCH" ;;
    *) echo "$1 NOT Googlebot ($host)" ;;
  esac
}

# Check the busiest self-declared Googlebot IPs in the log.
awk '/Googlebot/ {print $1}' access.log | sort | uniq -c | sort -rn \
  | head -20 | while read -r count ip; do verify "$ip"; done

# Most-crawled URLs. If parameter or filter URLs dominate the top of
# this list, that is your crawl waste, quantified.
awk '/Googlebot/ {print $7}' access.log \
  | sort | uniq -c | sort -rn | head -40

# Crawl distribution by section — which parts of the site get attention.
awk '/Googlebot/ {split($7, p, "/"); print "/" p[2]}' access.log \
  | sort | uniq -c | sort -rn

# How much crawl goes to URLs carrying query strings at all.
awk '/Googlebot/ {print $7}' access.log \
  | awk '{if ($0 ~ /\?/) q++; else c++} END {
      printf "clean: %d (%.1f%%)  parameterised: %d (%.1f%%)\n", \
      c, 100*c/(c+q), q, 100*q/(c+q) }'

# Status code distribution for verified crawler traffic.
awk '/Googlebot/ {print $9}' access.log | sort | uniq -c | sort -rn

# Every URL that served a 5xx to a crawler — fix these first, since
# server errors actively suppress crawl capacity.
awk '/Googlebot/ && $9 ~ /^5/ {print $9, $7}' access.log \
  | sort | uniq -c | sort -rn | head -30

# 404s receiving crawler traffic: either unmapped redirects from a
# past migration, or links pointing at pages that never existed.
awk '/Googlebot/ && $9 == 404 {print $7}' access.log \
  | sort | uniq -c | sort -rn | head -30

# Extract sitemap paths and crawled paths, then diff the two sets.
curl -s https://example.com/sitemap.xml \
  | grep -oE '<loc>[^<]+' | sed 's|<loc>||' \
  | sed -E 's|https?://[^/]+||' | sort -u > sitemap-paths.txt

awk '/Googlebot/ {print $7}' access.log \
  | sed 's/?.*//' | sort -u > crawled-paths.txt

# In the sitemap but never crawled — discovery or demand problem.
comm -23 sitemap-paths.txt crawled-paths.txt | head -40

# Crawled but not in the sitemap — often orphans, old URLs, or
# URLs you did not know were indexable.
comm -13 sitemap-paths.txt crawled-paths.txt | head -40

# Distinct days on which each URL was crawled, over the log period.
# A commercially important page crawled once in a month is a signal.
awk '/Googlebot/ {
  split($4, d, ":"); gsub(/\[/, "", d[1]);
  print $7, d[1]
}' access.log | sort -u | awk '{print $1}' \
  | uniq -c | sort -rn | head -30