Log File Analysis for Programmatic SEO: Finding Crawl Waste Before It Costs You Rankings

If your programmatic site has more than a few thousand URLs, Google Search Console will lie to you by omission. It shows you what got indexed and what got queried, but not the boring middle: what Googlebot actually spent its time crawling. That gap is where rankings quietly die, and log files are the only honest source of truth.

This is a working guide to log file analysis for programmatic SEO — the schema we use, the queries we run, and the crawl waste patterns that show up on almost every site we audit.

Why log files still matter in 2026

Google's crawl scheduler is smarter than it was five years ago. It backs off faster on low-value URLs, it respects Last-Modified more consistently, and it will happily ignore huge chunks of a site it deems uninteresting. That last part is the problem.

On a programmatic site with 200k pages, you might have:

• 40k pages that drive 95% of organic traffic
• 80k pages that are technically fine but never get crawled deeply
• 60k pages that Googlebot hits repeatedly for no reason (old parameters, redirect chains, faceted junk)
• 20k pages that should exist but Google has never seen

GSC's Crawl Stats report gives you a summary. Logs give you the URL-level detail you need to actually fix it.

What you need before you start

• Raw access logs from your edge (Cloudflare, Fastly, CloudFront) or origin (Nginx, Apache)
• At least 30 days of history, ideally 90
• A way to store and query them — we default to BigQuery, but ClickHouse or DuckDB work fine
• A reliable method to verify Googlebot (reverse DNS, not just user agent)

That last point matters. Somewhere between 15% and 40% of hits claiming to be Googlebot in our audits are fake — scrapers, competitors, or security scanners. If you skip verification, every conclusion downstream is contaminated.

A minimal log schema that actually works

Do not try to keep the raw log line as your primary record. Parse once, store structured. Here is the shape we use for programmatic clients:

CREATE TABLE seo_logs.crawl_events (
  event_ts        TIMESTAMP,
  ip              STRING,
  user_agent      STRING,
  bot_name        STRING,        -- googlebot, bingbot, gptbot, etc.
  bot_verified    BOOL,          -- rDNS check passed
  method          STRING,
  url_path        STRING,
  url_query       STRING,
  status          INT64,
  bytes_sent      INT64,
  response_ms     INT64,
  referer         STRING,
  page_template   STRING,        -- joined from your routing table
  page_cluster    STRING,        -- e.g. 'city-service', 'category', 'product'
  is_indexable    BOOL           -- joined from your CMS/DB
)
PARTITION BY DATE(event_ts)
CLUSTER BY bot_name, page_template;

The two joined columns — page_template and page_cluster — are what turn logs from a curiosity into a decision tool. Without them you are staring at URL strings. With them you can say "Googlebot spent 38% of its budget on our /compare/* template, which drives 2% of revenue" and act on it.

Verifying Googlebot properly

User agent strings are trivially spoofed. The only reliable check is reverse DNS to a googlebot.com or google.com host, then forward DNS back to the original IP. Do it once per unique IP, cache the result for 30 days, and store bot_verified on every row. Everything that follows assumes you filter WHERE bot_verified = TRUE.

The five crawl waste patterns we always find

After enough audits you start to see the same leaks. These are the ones worth checking first.

1. Parameter explosion on canonical pages

Run this and brace yourself:

SELECT
  REGEXP_EXTRACT(url_path, r'^(/[^/]+)') AS section,
  COUNT(*) AS hits,
  COUNT(DISTINCT CONCAT(url_path, url_query)) AS unique_urls,
  COUNT(DISTINCT url_path) AS unique_paths
FROM seo_logs.crawl_events
WHERE bot_verified AND bot_name = 'googlebot'
  AND DATE(event_ts) >= CURRENT_DATE() - 30
GROUP BY section
ORDER BY hits DESC;

When unique_urls is 5x or 10x unique_paths, Google is crawling parameter variants you probably do not want indexed. Common culprits: tracking parameters that leaked into internal links, session IDs, sort/filter combinations that were never blocked in robots.txt.

Fix order: strip parameters from internal links, add rel=canonical if you have not, and only then consider robots.txt disallow rules. Blocking a URL Google already knows about does not remove it from the index — it just makes Google stop checking whether it should.

2. Redirect chains eating budget

SELECT status, COUNT(*) AS hits
FROM seo_logs.crawl_events
WHERE bot_verified AND bot_name = 'googlebot'
  AND DATE(event_ts) >= CURRENT_DATE() - 30
GROUP BY status
ORDER BY hits DESC;

If 3xx responses are more than about 10% of Googlebot hits, something is off. Trace the top redirect sources. On programmatic sites the usual causes are trailing-slash inconsistencies, http-to-https loops behind a misconfigured CDN, or old slugs that redirect through two or three hops before landing.

Every hop is a wasted request. Collapse them at the edge with a rewrite rule so the redirect is always one 301 to the final destination.

3. Templates that get crawled but never earn traffic

This is the join that makes log analysis actually useful. Bring in GSC clicks per template:

WITH crawl AS (
  SELECT page_template, COUNT(*) AS bot_hits
  FROM seo_logs.crawl_events
  WHERE bot_verified AND bot_name = 'googlebot'
    AND DATE(event_ts) >= CURRENT_DATE() - 30
  GROUP BY page_template
),
traffic AS (
  SELECT page_template, SUM(clicks) AS clicks
  FROM seo_logs.gsc_by_page
  WHERE date >= CURRENT_DATE() - 30
  GROUP BY page_template
)
SELECT
  c.page_template,
  c.bot_hits,
  COALESCE(t.clicks, 0) AS clicks,
  SAFE_DIVIDE(c.bot_hits, NULLIF(t.clicks, 0)) AS hits_per_click
FROM crawl c
LEFT JOIN traffic t USING (page_template)
ORDER BY c.bot_hits DESC;

Any template with high bot_hits and near-zero clicks is a candidate for pruning, consolidating, or noindex. This is where the hard conversations start with content teams, because "we spent six months building this template" is not an argument Googlebot cares about.

4. Orphan crawling

The inverse problem: URLs that Google crawls but that are not in your sitemap or internal link graph. These usually come from external backlinks to old URLs, or from your own emails and social posts pointing to dead paths.

Join your logs against your sitemap dump. Anything hit repeatedly by Googlebot that is not in the sitemap deserves a decision — either bring it back into the graph properly or serve a clean 410.

5. AI crawler tax

GPTBot, ClaudeBot, PerplexityBot, and their cousins now account for a meaningful share of crawl traffic on content-heavy sites — sometimes more than Googlebot itself. They are not inherently bad, but they hit the same origin and can inflate your infrastructure bill without any SEO upside.

Decide your policy explicitly. Block them in robots.txt, rate-limit them at the edge, or serve them a lighter version of the page. Whatever you choose, measure it in the same table so you know what you are trading.

Making this a habit, not a project

A one-off log audit finds problems. A recurring pipeline prevents them. The setup we recommend:

• Stream logs to storage continuously (edge → object store → warehouse)
• Refresh the parsed crawl_events table hourly or daily
• Build a Looker Studio or Metabase dashboard on top with three views: crawl by template, status code mix over time, and hits-per-click by cluster
• Set alerts on two things: 4xx/5xx spike relative to a rolling baseline, and any template whose crawl share moves more than 20% week-on-week

The alerts matter more than the dashboard. Dashboards get ignored. A Slack message that says "Googlebot is now spending 22% of its budget on /tag/* pages, up from 6% last week" gets action.

What we'd do first

If you have a programmatic site and have never looked at logs seriously, start narrow. Pull one week of edge logs, verify Googlebot properly, and just answer three questions: what percentage of hits are non-200, which template gets the most hits, and which of your top revenue templates is under-crawled. That is a two-day exercise and it will almost certainly change your next quarter's technical SEO roadmap.

If you want a hand building the pipeline or interpreting the output, our SEO and growth engineering team does this work regularly, and we have written more on the analytics side in our blog.