DEV Community

seotto.com
seotto.com

Posted on Edited on

How to find the URLs Googlebot crawls most but that never get search traffic (access logs + Search Console, in Python)

Automated technical SEO checks that catch indexing blocks and deploy regressions — seotto.com

Two data sources most sites already have, and rarely put side by side:

  • Your access logs record every request Googlebot makes: which URLs, how often, what status code it got.
  • Search Console's Performance report shows which pages earn impressions and clicks.

Each one alone is useful. Joined together, they answer a question neither can answer alone: which URLs is Google spending crawl effort on that never show up in search? That list is usually where the cheap technical SEO wins are: faceted URLs nobody meant to expose, endless calendar pages, old redirect chains, and thin templates that don't deserve to be crawled at all.

This post walks through a short Python script that does the join, and how to interpret the result.

What you need

  1. Access logs for the last 28 days or so, in a common format (Nginx and Apache "combined" format work as-is). If you're behind a CDN, use the CDN's logs, since many Googlebot requests never reach your origin.
  2. A Search Console export of pages. In Search Console, go to Performance, choose the same date range, switch to the "Pages" tab and export. You get a CSV with a "Top pages" column plus clicks and impressions.

One caveat on the export: the web interface exports at most 1,000 rows. For larger sites, use the Search Analytics API or the bulk data export to BigQuery to get the full list. The script works the same way with any CSV that has a URL column and a clicks column.

The script

import csv, gzip, re, sys, glob
from collections import Counter, defaultdict
from urllib.parse import urlsplit

LOG_GLOB, GSC_CSV, HOST = sys.argv[1], sys.argv[2], sys.argv[3]  # e.g. "logs/access.log*" pages.csv example.com
line_re = re.compile(r'"(?:GET|HEAD) (\S+) HTTP/[\d.]+" (\d{3}) .*"([^"]*)"$')

def opener(p):
    return gzip.open(p, "rt", errors="replace") if p.endswith(".gz") else open(p, errors="replace")

hits = Counter()
statuses = defaultdict(Counter)
for path in glob.glob(LOG_GLOB):
    for line in opener(path):
        m = line_re.search(line.rstrip())
        if not m or "Googlebot" not in m.group(3):
            continue
        url, status = m.group(1), m.group(2)
        hits[url] += 1
        statuses[url][status] += 1

clicks, impressions = {}, {}
with open(GSC_CSV, encoding="utf-8-sig") as f:
    for row in csv.DictReader(f):
        u = urlsplit(row.get("Top pages") or row.get("Page") or row["page"])
        key = u.path + (("?" + u.query) if u.query else "")
        clicks[key] = int(str(row.get("Clicks", 0)).replace(",", ""))
        impressions[key] = int(str(row.get("Impressions", 0)).replace(",", ""))

def bucket(url):
    p = urlsplit(url)
    if p.query:
        return "params:" + ",".join(sorted(k.split("=")[0] for k in p.query.split("&")))
    parts = [s for s in p.path.split("/") if s]
    return "/" + (parts[0] if parts else "") + "/*"

rows = []
for url, n in hits.most_common():
    rows.append((n, clicks.get(url, 0), impressions.get(url, 0),
                 dict(statuses[url]), bucket(url), url))

total = sum(hits.values())
wasted = [r for r in rows if r[1] == 0]
print(f"Googlebot requests: {total}, unique URLs: {len(hits)}")
print(f"Requests to URLs with 0 clicks: {sum(r[0] for r in wasted)} "
      f"({100 * sum(r[0] for r in wasted) / max(total, 1):.0f}%)\n")

by_bucket = Counter()
for r in wasted:
    by_bucket[r[4]] += r[0]
print("Top zero-click groups by crawl requests:")
for b, n in by_bucket.most_common(15):
    print(f"{n:7d}  {b}")

print("\nTop 30 zero-click URLs:")
for n, c, i, st, b, url in wasted[:30]:
    print(f"{n:6d}  impr={i:<6d} {st}  {url}")
Enter fullscreen mode Exit fullscreen mode

Run it:

python3 crawl_vs_clicks.py "logs/access.log*" pages.csv example.com
Enter fullscreen mode Exit fullscreen mode

The Googlebot check matches on the user agent. Plenty of scrapers pretend to be Googlebot, so for anything you'll act on, verify the IPs with a reverse DNS lookup first. Our earlier post on telling real Googlebot from fakes covers that in detail.

Want these crawl-vs-traffic gaps flagged automatically? seotto.com runs technical SEO checks that catch indexing problems before they sit unnoticed in your logs.

Reading the output

The script prints three things: the share of Googlebot requests going to URLs with zero clicks, those requests grouped by pattern, and the top individual URLs.

A zero-click URL isn't automatically a problem. Some pages are new, some are legitimately niche, and some exist for users rather than search (account pages, carts). What you're looking for is patterns with a lot of crawling and no value:

Parameter groups

If params:color,size,sort shows up near the top, Google is crawling combinations of your filters. Each combination is a separate URL. Fix by not linking crawlable URLs for filter combinations you don't want indexed, canonicalising to the unfiltered page, and keeping the parameter URLs out of your sitemap.

Redirects and 404s

URLs where the status counter is mostly 301 or 404 are being requested because something still points at them: internal links, your sitemap, or old external links. Update internal links and the sitemap. External links you can't fix are fine to leave, as long as the redirect is a single hop.

Calendar, archive and pagination traps

Groups like /events/* or /archive/* with thousands of requests and no clicks often mean an infinite space: "next month" links that go on forever, or tag archives multiplied by page numbers. Cap the range you link to, and stop linking to empty pages.

Pages with impressions but no clicks

These are different: Google thinks they're relevant enough to show, but nobody clicks. That's a title and snippet problem, not a crawling problem. Rewriting the title and meta description is the first thing to try.

Running it regularly

Run the script monthly with the same date range for both sources, and keep the outputs. The interesting number is the trend in "requests to URLs with 0 clicks". If a release suddenly adds a new parameter group to the top of the list, you'll see it the following month instead of a year later. Tools like seotto.com can track this kind of crawl-versus-value picture continuously, but the script is enough to start.

Related reading

What did the biggest zero-click group turn out to be on your site? On e-commerce sites it's almost always sort and filter parameters.

Top comments (6)

Collapse
 
durim255 profile image
Durim •

Joining logs with Search Console is such a good idea, most people only look at one of the two. One tip: verify Googlebot by reverse DNS before counting hits. On a few sites I checked, a big share of "Googlebot" requests in the logs were fake user agents from scrapers, which made some URLs look much more crawled than they really were. It would also be interesting to run the same join for AI crawlers like OAI-SearchBot and PerplexityBot, to see which pages they fetch versus which ones actually get cited.

Collapse
 
seotto profile image
seotto.com •

Good call on reverse DNS. Fake Googlebot traffic can easily double the hit count on the URLs scrapers like most, which skews exactly the list this script produces. Running the same join for OAI-SearchBot and PerplexityBot is a fun idea, though the citation side is harder without anything like Search Console. Have you found a decent way to see which pages they actually cite?

Collapse
 
durim255 profile image
Durim •

Not perfectly, but two things got me most of the way. Bing Webmaster Tools now has an AI Performance report that shows which URLs get cited in Copilot answers. On my own tool site that's how I found out the most-cited pages were small color reference pages, not the tools I expected. For ChatGPT I just watch referrals in analytics: chatgpt.com adds utm_source=chatgpt.com to cited links, so landing pages with that source are basically your citation list. Pair that with OAI-SearchBot hits from the logs and you can see which crawled pages actually turn into citations. Perplexity is the blind spot so far.

Thread Thread
 
seotto profile image
seotto.com •

Didn't know Bing had added an AI Performance report, that's going on my list. Pairing utm_source=chatgpt.com landings with OAI-SearchBot hits is a neat way to connect crawled to cited. Funny that the color reference pages won, small factual pages seem to be what these answers like to quote.

Thread Thread
 
durim255 profile image
Durim •

Same pattern here: the pages that get quoted give a clear answer in the first lines, a hex code or a short definition, instead of a long intro. Thanks for the article, the logs + Search Console join is going into my routine.

Thread Thread
 
seotto profile image
seotto.com •

That matches the color pages winning: a hex code in the first lines is something an answer can lift, a two-paragraph intro isn't. I'll start looking at whether the crawled-but-untrafficked URLs fail that test before I assume they're just thin. Glad the log and Search Console join is useful.