ChatGPT answers, unedited

For prioritizing broken links, the strongest workflow is usually not just “find 404s” — it is a join between:

1. What is broken (crawl data)
2. How often it is hit (server/CDN logs)
3. Who is hitting it (users, bots, referrers)
4. How valuable the affected URL is (traffic, conversions, links, revenue)

A good setup looks like this:

Recommended workflow

1. Crawl the site to build the broken-link inventory

Use a crawler to collect:

• Broken internal links (source page → broken destination)
• Status codes (404, 410, 500, redirect chains)
• Anchor text
• Number of internal links pointing to the broken URL
• Page templates affected

Common tools:

• Screaming Frog SEO Spider — strong for finding broken links and exporting source URLs/inlinks. The GTM Exchange
• Sitebulb — useful if you want more visual audits.
• Ahrefs Site Audit — useful when you also care about backlinks and external references.

Output table:

2. Parse server/CDN logs to measure real impact

Your logs should include:

• timestamp
• requested URL
• HTTP status
• referrer
• user agent
• response time
• bytes sent
• IP/request ID if available

Then calculate:

• Total hits to each broken URL
• Human traffic vs bots
• Top referrers
• Googlebot hits
• Trend over time

For SEO-focused log analysis, tools like Screaming Frog Log File Analyser, OnCrawl, and Botify are commonly used because they combine crawl behavior with server log data. Ighenatt Samuel Ochoa

For engineering-heavy teams:

• BigQuery
• ClickHouse
• Elasticsearch + Kibana
• Datadog
• Grafana Loki
• AWS Athena

A typical SQL aggregation:

SELECT
  url,
  COUNT(*) AS hits,
  COUNTIF(user_agent LIKE '%Googlebot%') AS bot_hits,
  COUNT(DISTINCT referrer) AS referring_pages
FROM access_logs
WHERE status_code IN (404,410)
GROUP BY url
ORDER BY hits DESC;

3. Add business value signals

Join your broken URL list with:

• Google Analytics / GA4 sessions
• Search Console clicks/impressions
• Conversion events
• Backlinks
• Revenue data

Example priority scoring:

Priority Score =
(404 hits × 5)
+ (human visits × 10)
+ (Googlebot hits × 3)
+ (backlinks × 8)
+ (conversion value × 10)

This prevents spending time on:

• /random-old-image.jpg with 3 bot hits

while fixing:

• /best-selling-product receiving 20,000 visitors/month and returning 404

Practical stack recommendations

Small/medium site (<100k URLs)

• Screaming Frog SEO Spider
• Screaming Frog Log File Analyser
• Google Analytics
• Search Console
• Spreadsheet or Looker Studio dashboard

Simple monthly workflow.

Large site (millions of URLs)

• CDN logs (Cloudflare, Fastly, CloudFront)
• Data warehouse (BigQuery/Snowflake/ClickHouse)
• Scheduled crawler
• SQL models
• Dashboard in Looker/Tableau/Grafana

Pipeline:

CDN logs
   ↓
Log parser
   ↓
Broken URL table
   ↓
Join:
  - crawl data
  - analytics
  - backlinks
  - revenue
   ↓
Priority queue
   ↓
Jira/Linear tickets

Useful prioritization rules

I would usually rank fixes like this:

1. 404s receiving real user traffic
2. 404s linked internally from high-value pages
3. 404s with backlinks
4. 404s crawled frequently by search engines
5. Broken URLs repeated across templates
6. Low-volume cleanup items

Template-level problems deserve special attention: one broken link in a navigation component can create thousands of broken URLs.

A dashboard I would build

Columns:

This turns broken links from an SEO audit list into a ranked engineering backlog.

This turns broken links from an SEO audit list into a ranked engineering backlog.

Yes. The most useful workflow is to combine server-log evidence, a site crawl, and analytics/Search Console data, then calculate a priority score for each broken URL.

Recommended stack

• Server logs → BigQuery / ClickHouse / Athena: best for determining which broken URLs are actually being requested by humans and bots.
• Screaming Frog SEO Spider: good for discovering broken internal links and, importantly, identifying the source pages that contain each broken link.
• Google Search Console: useful for organic-search clicks/impressions, indexing problems, and Googlebot crawl behavior. Its Crawl Stats report exposes response codes, crawl volume, response times, and Googlebot types. Google Help Google Help
• GA4 or another analytics platform: useful for estimating the value/traffic of the pages containing the broken links.
• SQL + a dashboard (Looker Studio, Metabase, Grafana, etc.): turn the above into a repeatable prioritization pipeline.

The key idea: prioritize the broken link, not just the 404

Suppose you have:

I'd prioritize /pricing → /old-plans even if another URL has more raw 404s, because a broken link on a heavily trafficked page is a much stronger user-impact signal.

A practical scoring model

For every broken target URL, calculate:

priority =
    human_404_requests
  × source_page_traffic_weight
  × business_value_weight
  × link_count_weight
  × seo_weight

You don't necessarily need a sophisticated ML model. A weighted score is easier to explain and maintain.

I'd use roughly these inputs:

1. Human requests to the broken URL

• Requests from real browsers/users.
• Strongest direct evidence of user impact.
• Number of distinct source pages linking to it

• 1 broken link on a high-traffic page can matter more than 50 links buried in low-traffic pages.
• Traffic to source pages

• Pull sessions/users/pageviews from analytics.
• This captures the opportunity to fix the actual user journey.
• Organic-search value

• Search Console clicks/impressions for the source page and/or target URL.
• Search Console provides page-level performance data including clicks, impressions, CTR and position. Google Help
• HTTP status / failure type

• 404/410 isn't necessarily equivalent to a 500.
• A 5xx error deserves immediate attention because it may indicate an operational problem rather than a genuinely removed URL.
• Bot traffic

• Keep Googlebot/Bingbot separate from human traffic.
• A URL requested thousands of times by crawlers but never by humans shouldn't automatically outrank a URL breaking a major user journey.

The workflow I'd build

1. Crawl the site

Run Screaming Frog or a similar crawler and export something like:

source_url
target_url
anchor_text
status_code
redirect_chain
link_type

This gives you the relationship graph:

/page-A ─────┐
             ├──> /missing-page
/page-B ─────┤
             │
/page-C ─────┘

That relationship is crucial. A raw access log can tell you /missing-page gets 404s, but it doesn't necessarily tell you which internal pages are causing the problem.

2. Parse your access logs

Normalize every request into something like:

timestamp
host
method
path
status
user_agent
referrer
ip_hash
response_bytes
response_time

Then classify requests:

human
googlebot
bingbot
other_bot
monitoring
unknown

Be conservative about bot detection—user-agent strings alone aren't perfect.

Then aggregate:

SELECT
    path,
    COUNT(*) AS requests,
    COUNT(DISTINCT user_id_or_session) AS users,
    COUNTIF(is_bot = FALSE) AS human_requests,
    COUNTIF(is_googlebot) AS googlebot_requests
FROM access_logs
WHERE status = 404
GROUP BY path;

The exact SQL will depend on your log platform.

3. Join crawl data to logs

This is where it becomes particularly useful:

broken_target
    ↓
source pages
    ↓
source-page traffic
    ↓
404 requests
    ↓
bot requests
    ↓
organic/search value

For example:

/missing-product

Linked from:
  /category/shoes       31,200 monthly visits
  /blog/running-guide    8,400 monthly visits
  /archive/2019             120 monthly visits

404 requests:
  humans:     4,812
  Googlebot:  1,932
  other bots:   744

That gives you enough information to make an actionable decision.

One particularly useful table

I'd ultimately produce a table like:

This makes the output useful to engineering/SEO/content teams rather than just being another list of 404s.

Don't rely on Search Console alone

Search Console is valuable, but it shouldn't be your primary source for this particular analysis.

Google explicitly says its Links report is not a comprehensive list of every internal/external link, and its data is sampled/deduplicated. Google Help

Likewise, Crawl Stats represents Google's crawling activity rather than all user traffic, and Google notes that its crawl totals can differ from server logs. Google Help

So I'd think of the sources this way:

                 ┌── Server logs ── actual requests
                 │
Broken links ────┼── Crawler ────── link relationships
                 │
                 ├── Analytics ──── user/page traffic
                 │
                 └── Search Console organic/search impact

**Server logs tell you what's happening.
The crawler tells you why it's happening.
Analytics tells you how much users care.
Search Console tells you the search dimension.**

For a large site, I'd put the raw logs into BigQuery/ClickHouse, run the crawler on a schedule, and have a daily SQL job generate the prioritized broken-link table. That scales much better than trying to do the whole exercise in a spreadsheet.

If you tell me roughly how big the site is and what your logs look like (Nginx, Apache, Cloudflare, AWS ALB, etc.), I can lay out a concrete schema + SQL pipeline for it.

The exact SQL will depend on your log platform.

3. Join crawl data to logs

This is where it becomes particularly useful:

broken_target
    ↓
source pages
    ↓
source-page traffic
    ↓
404 requests
    ↓
bot requests
    ↓
organic/search value

For example:

For prioritizing broken links, the strongest workflow is usually crawl discovery + server log evidence + traffic/value signals. A crawler tells you what is broken; logs and analytics tell you what matters most. The GTM Exchange Screaming Frog

Recommended workflow

1. Build a broken-link inventory (crawl layer)

Run a full crawl and export:

• Broken destination URL
• HTTP status (404, 410, 500, timeout, redirect loop)
• Source page(s) containing the link
• Anchor text
• Number of occurrences
• Crawl date

Common tools:

• Screaming Frog SEO Spider — strong for technical audits, exports, inlinks, redirects, and large crawl inventories. The GTM Exchange
• Sitebulb — useful if you want more visual audit reporting.
• Ahrefs / Semrush — useful when you want broken links combined with SEO/backlink context.

Output example:

2. Parse server logs (traffic layer)

Your access logs answer:

"Are real users or bots hitting this broken URL?"

Extract:

• Requests to broken URLs
• Unique visitors (if available)
• Referrers
• User agents
• Timestamp trends
• Geographic/device data if relevant

Typical stack:

Small/medium sites

• Nginx/Apache logs → CSV → BigQuery/Postgres → dashboards

Large sites

• CloudFront / Fastly / Cloudflare logs
• BigQuery or Snowflake
• Datadog, Splunk, or Elastic

Useful fields:

timestamp
request_url
status_code
referrer
user_agent
response_time
bytes_sent

3. Join crawl data with traffic data

Create a priority score instead of fixing every 404 equally.

Example:

Priority Score =
(Organic Traffic Weight × 5)
+ (User Requests × 3)
+ (Internal Link Count × 2)
+ (Backlink Value × 4)
+ (Conversion Value × 5)

Then rank:

4. Add search-engine crawl data

For SEO-focused sites, compare:

• URLs Googlebot requested
• URLs returning errors
• Crawl frequency
• Important pages never crawled

Tools:

• Screaming Frog Log File Analyser — analyzes server logs and bot crawl behavior. Screaming Frog
• Enterprise platforms:

• Botify
• Oncrawl

A practical production setup

A common stack:

Crawler
(Screaming Frog / Sitebulb)
        |
        v
Broken links table
(Postgres / BigQuery)
        |
        +---- Server logs
        |
        +---- Google Analytics / Search Console
        |
        +---- Backlink data
        |
        v
Priority dashboard
(Looker Studio / Tableau / Grafana)
        |
        v
Tickets
(Jira / Linear / GitHub Issues)

Automate the fixes workflow

For teams maintaining large sites:

1. Nightly crawl
2. Daily log ingestion
3. Join broken URLs with traffic
4. Create tickets only above a threshold

Example rules:

• P0: 404 + >100 visits/month
• P1: 404 + internal links >20
• P2: 404 + backlinks
• P3: no traffic, no links

If you already have raw logs

A simple SQL pattern:

SELECT
  url,
  COUNT(*) AS hits,
  COUNT(DISTINCT user_id) AS users
FROM server_logs
WHERE status_code = 404
GROUP BY url
ORDER BY hits DESC;

Then join that against your crawler export:

SELECT
  c.broken_url,
  c.source_page,
  l.hits,
  c.internal_links
FROM crawl_errors c
LEFT JOIN log_404s l
ON c.broken_url = l.url
ORDER BY l.hits DESC, c.internal_links DESC;

For most teams, the sweet spot is:

• Screaming Frog (or similar crawler) for discovery
• BigQuery/Postgres as the joining layer
• Search Console + analytics + logs for prioritization
• Dashboard + ticket automation for ongoing maintenance

The exact setup changes a lot depending on whether this is a 10k-page marketing site, an ecommerce catalog, or a large app with millions of URLs.

Then join that against your crawler export:

SELECT
  c.broken_url,
  c.source_page,
  l.hits,
  c.internal_links
FROM crawl_errors c
LEFT JOIN log_404s l
ON c.broken_url = l.url
ORDER BY l.hits DESC, c.internal_links DESC;

For most teams, the sweet spot is:

• Screaming Frog (or similar crawler) for discovery
• BigQuery/Postgres as the joining layer
• Search Console + analytics + logs for prioritization
• Dashboard + ticket automation for ongoing maintenance

The exact setup changes a lot depending on whether this is a 10k-page marketing site, an ecommerce catalog, or a large app with millions of URLs.

Yes. The strongest workflow is to join three datasets at the URL level:

1. Server logs → what Googlebot/users actually requested.
2. Crawler data → what links are broken and which pages link to them.
3. Traffic/value data → which broken URLs or their source pages actually matter.

That lets you move from “we have 14,000 404s” to “these 37 broken URLs are responsible for most of the wasted crawl and lost user traffic.”

Recommended workflow

1. Crawl the site

Use Screaming Frog SEO Spider, especially if you want detailed source → broken-destination relationships.

It can identify 4xx/5xx URLs and export the pages containing the broken links. It can also pull Google Search Console clicks, impressions, CTR and position into the crawl. Screaming Frog Screaming Frog

Your basic crawl dataset should look something like:

2. Parse server logs

For SEO purposes, logs give you the particularly valuable dimension that a crawler can't: what actually happened.

For each URL, calculate:

• Googlebot requests
• Other search-bot requests
• Human requests
• 4xx count
• 5xx count
• Last request date
• Requests over the last 7/30/90 days
• Referring URL, if available
• Response time

Screaming Frog Log File Analyser is probably the easiest starting point. It can process log files, identify bot crawls, response codes, crawl frequency, errors, redirects, and combine log data with crawl data. Screaming Frog

For very large log volumes, BigQuery + SQL or an ELK/OpenSearch-style stack gives you considerably more flexibility. Over The Top SEO

3. Add traffic/value

This is where the prioritization gets interesting.

For every broken URL, join:

crawl data
      +
server logs
      +
Google Search Console
      +
GA4 / analytics
      +
internal links

I'd build a URL-level table approximately like:

url
status_code
source_url_count
googlebot_requests_30d
human_requests_30d
gsc_clicks_90d
gsc_impressions_90d
organic_sessions_90d
conversions_90d
internal_inlinks
external_backlinks
last_googlebot_request
replacement_url

Google's Crawl Stats report is also useful as a sanity check for Google's overall crawling behavior and server response issues. Google Help

The prioritization model

Don't simply rank by number of 404s.

I'd calculate something like:

Priority = traffic impact + crawl impact + link equity + business value

For example:

You can make the scoring more sophisticated by using logarithmic scaling so one URL with 100,000 visits doesn't completely dominate everything.

I'd also create severity buckets

P0 — Fix immediately

• High human traffic
• High organic traffic
• Important commercial URL
• Strong external backlinks
• Or 5xx rather than 404

P1 — High priority

• Frequently crawled by Googlebot
• Many internal links
• Significant organic impressions/clicks
• Obvious replacement URL exists

P2 — Normal

• Googlebot hits it occasionally
• Some internal links
• Little/no user traffic

P3 — Cleanup

• No traffic
• No meaningful crawl activity
• No valuable backlinks
• Old/intentional URLs

This prevents your team from spending two days fixing thousands of completely irrelevant historical 404s.

The really useful analysis

One particularly valuable report is:

Broken URL × traffic × Googlebot × source pages

For example:

Broken URL: /blue-running-shoes
404 requests:          4,821
Human requests:          917
Googlebot requests:    1,304
Organic clicks:          284
Internal source pages:    47
External backlinks:       12
Recommended action: 301 → /running-shoes
Priority: P0

That's a much better developer ticket than:

“There are 4,821 404s.”

Also distinguish broken destinations from broken source pages

This is an important nuance.

Suppose:

/page-A → /missing-product
/page-B → /missing-product
/page-C → /missing-product

You don't necessarily have three problems. You have one broken destination with three sources.

Aggregate the data by destination URL, then show all referring/source URLs underneath it.

Screaming Frog's current tooling explicitly supports combining crawl data with Search Console and exporting broken-link/source information, which makes this workflow fairly straightforward. Screaming Frog Screaming Frog

Tool choices

Best simple setup

Screaming Frog SEO Spider + Log File Analyser + GSC + GA4

I'd recommend this for most SEO teams. It's relatively easy to operate and gives you the crawl/log correlation without building infrastructure. Screaming Frog Screaming Frog

Best for enterprise-scale sites

BigQuery + SQL + crawler + GSC/GA4

Put normalized logs into BigQuery and create a URL fact table. This becomes particularly powerful when you're dealing with tens/hundreds of millions of requests and want automated daily scoring.

Best for an automated SEO platform

Tools such as OnCrawl, Botify, Seolyzer, or JetOctopus can automate more of the log/crawl correlation. They're particularly useful if you want dashboards and recurring monitoring rather than building your own pipeline. Ighenatt Over The Top SEO

If I were building this from scratch

I'd use:

                    ┌── Google Search Console
                    │
Server logs ────────┼── GA4
                    │
                    └── CDN/access logs
                           │
                           ▼
                    URL-level dataset
                           ▲
                           │
                 Screaming Frog crawl
                           │
                           ▼
                  Priority scoring
                           │
             ┌─────────────┼─────────────┐
             ▼             ▼             ▼
          P0 fixes       P1 fixes      Cleanup

For a large site, I'd put the normalized data in BigQuery, calculate the priority score daily, and expose a dashboard showing top broken URLs by estimated impact. That gives you a repeatable system rather than a one-off audit.

Yes. The strongest workflow is to join three datasets at the URL level:

1. Server/access logs → what real users and bots actually requested.
2. Crawler data → where the broken URL is linked from and what the crawler can discover.
3. Traffic/search data → how valuable the affected URL or linking page is.

That lets you prioritize a 404 based on actual impact, rather than simply counting broken links.

Recommended workflow

1. Crawl the site

Use something like Screaming Frog SEO Spider to export:

• Source URL
• Broken destination URL
• HTTP status
• Link type
• Anchor text
• Crawl depth
• Number of internal links pointing to the broken URL
• Page title/template/category

The important relationship is:

source page → broken URL

A 404 linked from 300 important pages should outrank a 404 linked from one obscure page.

Screaming Frog can also connect directly to Google Search Console and pull clicks, impressions, CTR and position into the crawl. Screaming Frog Screaming Frog

2. Parse your server logs

For each requested URL, aggregate something like:

This is where logs become particularly valuable: unlike a crawler, they tell you what was actually requested, and they give you historical frequency. Screaming Frog

I'd separate:

• 4xx vs 5xx
• human traffic vs bots
• Googlebot/Bingbot vs other bots
• requests vs unique IPs/session identifiers if available
• recent vs historical requests

3. Join the crawl and logs

This is the really useful part.

Create one URL-level table:

broken_url
status
internal_link_count
source_page_count
log_requests_30d
human_requests_30d
googlebot_requests_30d
last_requested
source_page_traffic
source_page_gsc_clicks
source_page_gsc_impressions
source_page_value
replacement_url

Screaming Frog's Log File Analyser actually supports importing crawl/URL data and matching it against log data, including identifying URLs found in logs but not in the crawl and vice versa. Screaming Frog Screaming Frog

For a large site, though, I'd consider putting everything into BigQuery, Snowflake, ClickHouse, or another warehouse and doing the joins with SQL.

4. Score the broken links

I wouldn't rank them simply by number of 404s.

A useful scoring model is:

priority =
    human_requests
  + (googlebot_requests × 2)
  + (internal_link_count × 10)
  + (source_page_traffic × 0.5)
  + (GSC_clicks × 5)

The exact coefficients aren't important initially. What's important is combining actual demand + SEO crawl activity + internal importance.

I'd ultimately classify them into buckets:

P0 — Fix immediately

• High human traffic
• High-value landing page
• Lots of internal links
• Significant Googlebot activity
• Known replacement URL exists

P1 — High priority

• Meaningful human traffic or
• Significant Googlebot crawling
• Multiple internal links
• Important template/category

P2 — Clean up

• Some internal links
• Little/no current traffic
• Occasional bot requests

P3 — Low value

• No meaningful traffic
• No search activity
• Few/no internal links
• Old/dead URLs

This prevents your team from spending a week fixing 10,000 inconsequential 404s while a handful of highly trafficked URLs remain broken.

5. Add the "why is it broken?" dimension

This is where crawler data becomes especially valuable.

For each broken URL, calculate:

number_of_source_pages
unique_templates
top_source_pages
anchor_texts
source_page_traffic

For example:

/blue-widget
404
17,842 human requests
2,410 Googlebot requests
143 internal links
72 source pages
$X estimated page value
replacement: /widgets/blue

That's an obvious redirect/fix candidate.

But:

/old-pdf-2009
404
3 requests
0 Googlebot requests
1 internal link

Probably not worth engineering time.

A particularly powerful signal

Googlebot hitting a 404 repeatedly is worth separating from ordinary 404 traffic.

Logs can show which URLs search engines actually requested and how frequently, and can therefore help expose crawl waste. Screaming Frog

I'd have a dashboard with at least:

• 404s by human requests
• 404s by Googlebot requests
• 404s by internal link count
• 404s by source-page traffic
• 5xx errors by requests
• 404s with an obvious replacement URL
• 404s receiving both human + Googlebot traffic

That gives your SEO/dev team a much better queue than a conventional crawler report.

Tool choices

Small/medium site:
Screaming Frog + its Log File Analyser + Google Search Console is probably the fastest route. Its Log File Analyser specifically supports 4xx/5xx analysis, crawl-frequency analysis, imported crawl data, and URL matching. Screaming Frog

Large site / lots of logs:
I'd use:

Nginx/Apache/CDN logs
        ↓
S3/GCS
        ↓
BigQuery / Snowflake / ClickHouse
        ↓
SQL transformations
        ↓
crawl export + GSC + analytics
        ↓
priority table
        ↓
Looker Studio / Tableau / Metabase

Highly automated SEO operation:
Make the warehouse table the source of truth and run the prioritization daily/weekly. Then expose something like:

URL                     Score   Human hits   Bot hits   Links   Action
────────────────────────────────────────────────────────────────────────
/old-product              984      18,420       2,104     143   301
/category/foo             731       9,820         840      91   Fix link
/legacy-guide             412       3,210         510      24   Restore
/old-pdf                   12           3           0       1   Ignore

One important caveat: don't treat log traffic as equivalent to SEO value. A URL can get lots of automated/bot requests without being important, while a URL with relatively little traffic can have substantial ranking or conversion value. That's why the best prioritization combines logs with crawl relationships and GSC/analytics data rather than using any one metric alone. Screaming Frog

If you tell me what your stack looks like (e.g. Cloudflare/Nginx + GA4 + GSC + Screaming Frog, or raw logs in S3), I can lay out the exact schema + SQL queries + scoring formula I'd use.

Googlebot hitting a 404 repeatedly is worth separating from ordinary 404 traffic.

Logs can show which URLs search engines actually requested and how frequently, and can therefore help expose crawl waste.

I'd have a dashboard with at least:

• 404s by human requests
• 404s by Googlebot requests
• 404s by internal link count
• 404s by source-page traffic
• 5xx errors by requests
• 404s with an obvious replacement URL
• 404s receiving both human + Googlebot traffic

That gives your SEO/dev team a much better queue than a conventional crawler report.

Tool choices

For prioritizing broken links, the best workflow is usually not just “find all 404s”. The useful signal comes from joining three datasets:

1. What is broken? (crawler data)
2. How much does it matter? (traffic/conversion data)
3. How often is it being requested? (server logs/bot crawl data)

A strong workflow looks like this:

1. Crawl the site to build the broken-link inventory

Use a crawler to collect:

• Broken internal links (source page → broken destination)
• HTTP status (404, 410, 5xx, redirect chains)
• Number of internal references
• Page depth
• Anchor text
• Templates/sections affected

Common tools:

• Screaming Frog — good for detailed crawls, internal link discovery, and combining crawl data with other datasets. The GTM Exchange TechRadar
• Sitebulb — useful if you want more visual audits and prioritization.
• Ahrefs — useful when you also care about backlinks pointing at dead URLs. Ahrefs

Export something like:

2. Add traffic impact

Join crawl output with analytics data:

Sources:

• Google Analytics / Adobe Analytics → pageviews, conversions
• Google Search Console → impressions, clicks, rankings
• Internal product analytics → signups, revenue events

Useful scoring fields:

Example priority score:

Priority =
(Organic Traffic × 0.4)
+ (Conversions × 0.3)
+ (Internal Links × 0.15)
+ (Bot Requests × 0.10)
+ (Backlinks × 0.05)

A 404 linked from your homepage with 50,000 monthly visits should outrank a 404 buried in an old blog post.

3. Add server log analysis

Logs tell you what users and crawlers actually hit, rather than what a crawler can find. They typically include requested URL, status code, timestamp, and user agent. Search Engine Land Ahrefs

Look for:

• 404s receiving lots of requests
• Googlebot repeatedly crawling broken URLs
• High-value pages returning errors
• Sudden spikes after releases

Tools:

Smaller sites

• Screaming Frog Log File Analyser — simple way to combine crawl and server log data. Ighenatt

Enterprise sites

• Botify
• OnCrawl

These combine logs, crawl data, and SEO performance signals. Ighenatt DMA Australia

Engineering-heavy stack

A common pipeline:

Nginx / Apache / CDN logs
        ↓
S3 / BigQuery / Snowflake / ClickHouse
        ↓
SQL transformations
        ↓
Dashboard (Looker / Tableau / Grafana)

4. Create a “broken link triage” table

Your final output should look more like this:

5. Automate the workflow

A practical recurring setup:

Daily

• Collect 404/5xx logs
• Alert on spikes

Weekly

• Crawl important sections
• Refresh broken-link inventory

Monthly

• Join: - crawl data
• logs
• analytics
• Search Console
• backlink data

Then generate a ranked repair queue.

My recommended stacks by site size

Small marketing site

• Screaming Frog crawl
• Google Analytics
• Search Console
• Spreadsheet scoring

Medium site (50k–1M URLs)

• Screaming Frog/Sitebulb
• Log files in BigQuery
• Scheduled SQL reports
• Looker Studio dashboard

Large ecommerce/docs platform

• Botify or OnCrawl
• CDN + origin logs
• Data warehouse
• Automated Jira tickets for high-priority errors

The key is to stop treating all 404s equally. The highest-value fixes are usually the broken URLs that have a combination of:

• real visitors,
• search visibility,
• backlinks,
• frequent crawler attention,
• important internal links. seojuice.com

Medium site (50k–1M URLs)

• Screaming Frog/Sitebulb
• Log files in BigQuery
• Scheduled SQL reports
• Looker Studio dashboard

Large ecommerce/docs platform

• Botify or OnCrawl
• CDN + origin logs
• Data warehouse
• Automated Jira tickets for high-priority errors

The key is to stop treating all 404s equally. The highest-value fixes are usually the broken URLs that have a combination of:

Yes. The strongest workflow is to treat broken-link prioritization as a data-joining problem, not just a crawler report.

Recommended workflow

Use four data sources:

• Server/CDN logs → what users and bots actually requested, status codes, referrers, frequency.
• Crawl data from Screaming Frog/Sitebulb/etc. → which pages contain the broken links.
• GA4 → how much real user traffic reaches the linking pages or encounters the broken URLs.
• Google Search Console → organic clicks/impressions and Google's crawl behavior. Search Console's Crawl Stats specifically exposes crawl requests, response types, and example URLs. Google Help

Then build a table roughly like:

The important distinction is that a 404 crawled 10,000 times isn't necessarily more important than one receiving 500 real users. You want to combine traffic, SEO value, internal-link impact, and crawl activity.

My preferred stack

1. Screaming Frog for discovering the broken links

Use it to crawl your site and produce:

source page → broken URL → anchor text → HTTP status

It's particularly useful because it tells you where the broken link exists, whereas logs primarily tell you that the broken URL was requested.

For recurring/large-scale analysis, Screaming Frog's Log File Analyser is also designed to combine log data with crawl data and identify things such as crawled URLs and orphaned URLs. seofordevs.dev

2. BigQuery as the central warehouse

If your site is reasonably large, I'd put the logs, crawl export, GA4 data, and GSC data into BigQuery.

Then you can do things like:

SELECT
  broken_url,
  COUNT(*) AS requests,
  COUNT(DISTINCT visitor_id) AS visitors,
  COUNTIF(user_agent LIKE '%Googlebot%') AS googlebot_hits
FROM server_logs
WHERE status_code = 404
GROUP BY broken_url
ORDER BY visitors DESC;

Then join that against your crawl table:

broken_url
    ↓
referring_page
    ↓
page traffic
    ↓
organic clicks
    ↓
404 requests
    ↓
Googlebot requests

This is much more powerful than looking at a Screaming Frog export by itself.

BigQuery is particularly useful when logs get large because you can query billions of requests without having to repeatedly download and manipulate raw files. seofordevs.dev

3. GA4 for actual human impact

I'd calculate at least:

• 404 page views
• unique users
• sessions
• conversions/revenue associated with the referring page
• internal referrer
• external referrer

If /checkout-old gets 50,000 bot requests but no humans, it may be a crawl-cleanup task.

If /pricing → /old-product generates 300 human visits/week, that's a much more urgent broken link.

4. GSC for SEO prioritization

Add:

• organic clicks to referring page
• organic impressions
• indexed status
• Googlebot requests
• URL type/template

Search Console's Crawl Stats is particularly useful as a validation layer against your server logs rather than as your sole source of crawl data. Google notes that its crawl data represents actual URLs requested by Google and includes response-code information, but it isn't necessarily a complete substitute for your own logs. Google Help

The prioritization formula

I'd create a score rather than simply sorting by 404 count.

For example:

Priority Score =
    0.35 × normalized human traffic
  + 0.25 × normalized organic value
  + 0.20 × normalized internal-link importance
  + 0.10 × normalized Googlebot requests
  + 0.10 × normalized conversion/revenue value

Then add categorical overrides:

P0 — fix immediately

• Broken link on navigation/header/footer
• High-traffic landing page
• Checkout/product/conversion path
• Large number of users hitting it
• Important page returning 5xx

P1 — fix this sprint

• High-organic-traffic referring page
• Many internal links pointing to the broken URL
• Googlebot repeatedly requesting it
• Obvious replacement URL exists

P2 — backlog

• Low human traffic
• Little/no organic value
• Few internal references
• Mostly bots/external noise

P3 — don't necessarily fix

• Random internet requests
• Old URLs with no internal references
• URLs intentionally removed
• Scanner/bot garbage

This matters because not every 404 needs fixing. Google explicitly notes that 404s can be perfectly appropriate when a page is genuinely gone and has no replacement. Google Help

One particularly useful metric

I'd add "404 traffic by source".

For every broken URL, distinguish:

internal traffic
external referral traffic
organic/search traffic
Googlebot traffic
other bots

That immediately gives you cases like:

/old-widget
12,000 requests
10,500 from internal pages
1,200 Googlebot
200 organic users
100 random bots

That's an obvious internal-link cleanup.

Versus:

/old-widget
12,000 requests
11,900 random external bots
50 users
0 internal links
0 organic traffic

That's probably not worth engineering time.

A practical architecture

For a serious site, I'd build:

                 ┌── Server/CDN logs ──┐
                 │                     │
Screaming Frog ──┤                     │
                 │                     ▼
GA4 ─────────────┤                  BigQuery
                 │                     │
GSC ─────────────┘                     │
                                       ▼
                              broken_link_scores
                                       │
                            ┌──────────┼──────────┐
                            ▼          ▼          ▼
                         Looker     Jira/Linear   Slack
                         Studio     backlog       alerts

Run the aggregation daily or weekly. One recent implementation pattern is to parse logs into a queryable dataset, calculate top 4xx URLs and crawl waste, and distribute the results automatically. seoautomationclub.com

If I were setting this up from scratch, I'd choose: Screaming Frog + server/CDN logs + GA4 + GSC → BigQuery → SQL scoring → Looker Studio/Jira.

If you tell me roughly how big the site is and where your logs live (Cloudflare, Nginx, Apache, AWS, GCP, etc.), I can give you a concrete schema + SQL queries for the broken-link scoring pipeline.

Yes. The most effective setup is to join crawl errors with real request traffic, then score each broken URL by business/SEO impact rather than treating every 404 equally.

Recommended workflow

Tool stack I'd use

For a typical site, Screaming Frog + raw access logs + BigQuery/PostgreSQL + GA4/Search Console is a very strong combination.

For example:

Screaming Frog
      ↓
crawl export ──────┐
                   ↓
server logs → normalize URLs → warehouse → priority scoring → dashboard
                   ↑
GA4 / GSC / backlinks

For a large site, I'd put the logs in BigQuery (or Snowflake/ClickHouse), normalize URLs there, and run the prioritization SQL rather than trying to do everything in a spreadsheet.

One important refinement

Don't prioritize solely by 404 traffic. A broken URL getting 50,000 bot requests may matter less than a 404 getting 2,000 requests from users who arrived through a high-converting product page.

I'd therefore produce two scores:

User impact

requests × user/session weighting × conversion value

SEO impact

Googlebot hits + organic clicks + backlinks + internal links

Then combine them into an overall priority. This gives you a much more actionable repair queue.

If you tell me what your logs look like (Nginx, Apache, Cloudflare, CDN, etc.) and roughly how many requests/URLs you're dealing with, I can lay out a concrete SQL schema + queries + scoring workflow for it.

Yes. The strongest workflow is to join crawl results with server-log traffic, rather than prioritizing broken links by crawl count alone.

Recommended workflow

Tools I'd consider

• Screaming Frog — excellent for crawling and exporting detailed link/status relationships.
• Sitebulb — particularly good if you want visualization and prioritization around site architecture.
• BigQuery — my preferred option for large server-log datasets and joining logs with crawl/GSC exports.
• Google Search Console — adds actual search demand to the prioritization model.
• GA4 — useful for downstream engagement/conversion signals, though server logs are generally better for measuring requests to broken URLs.
• Python/pandas or SQL — ideal for building a repeatable scoring pipeline.
• ELK/OpenSearch or Grafana/Loki — useful if you already have centralized logs and want ongoing monitoring rather than a one-off audit.

A particularly effective architecture

Server logs ───────┐
                   ├──> URL-level dataset ──> priority score ──> repair queue
Crawler ───────────┤
                   │
Google Search ─────┤
Console            │
                   │
Analytics/revenue ─┘

For a large site, I'd put the raw logs in BigQuery, normalize URLs there, load the crawl export into a table, and do the joins/priority scoring in SQL. That gives you a repeatable process you can rerun weekly rather than manually analyzing spreadsheets.

If you tell me roughly how large the site/logs are and what format your logs are in (Apache/Nginx/CDN, etc.), I can lay out a concrete schema + SQL/Python workflow for calculating the broken-link priority score.

Yes—this is a great use case for combining server-log analysis + crawler data + analytics/Search Console data rather than relying on a crawler alone.

Recommended workflow

The tooling I'd choose

Small/medium site:
Screaming Frog → server-log CSV → Python/pandas → Google Sheets/Looker Studio.

Large site:
Crawler → object storage → BigQuery/ClickHouse → SQL transformations → dashboard.

Continuous monitoring:
Server logs → streaming/log pipeline → warehouse → scheduled broken-link scoring → Slack/email alerts.

The key insight is that 404 count alone isn't a good prioritization metric. A broken URL with 10,000 real requests and strong backlinks is dramatically more important than 100 obscure 404s discovered by a crawler.

If you tell me roughly how many URLs/log lines you have and where the logs live (Nginx, Apache, Cloudflare, AWS, etc.), I can lay out a concrete architecture and SQL/Python workflow for calculating the priority score.

To prioritize broken links by actual user impact, the best workflows combine server logs (traffic evidence) + crawler data (link health) + analytics/search data (business value). A crawler alone tells you what’s broken; logs tell you what matters.

Recommended workflow

1. Crawl your site to build the broken-link inventory

Use a crawler to find:

• 404/410 URLs
• redirect chains
• broken internal links
• broken external links
• orphan pages
• canonical/hreflang issues

Common tools:

• screamingfrog.co.uk — very popular for technical SEO audits; exports URLs, status codes, inlinks, and crawl depth.
• sitebulb.com — strong visual reports and prioritization.
• ahrefs.com or semrush.com — useful if you already use SEO platforms.

Export at least:

2. Parse server logs for real traffic to broken URLs

Your web server logs are the strongest prioritization signal.

Look for:

• Requests returning 404
• Requests returning 410
• Referrers
• User agents
• Request counts
• Unique visitors (if available)

Example Apache/Nginx log fields:

timestamp
request_path
status_code
referrer
user_agent
response_time

A simple priority table:

Tools commonly used:

• goaccess.io — lightweight real-time log analyzer.
• elastic.co — powerful for large log volumes.
• splunk.com — enterprise log analytics.
• datadoghq.com — managed observability workflow.

3. Join crawl data + log data

Create a dataset like:

broken_url
status_code
monthly_404_requests
unique_users
internal_links_count
source_pages
organic_traffic
backlinks
conversion_value

Then score each URL.

Example scoring model:

Priority Score =
(404 traffic weight × 5)
+ (internal links × 3)
+ (organic visits × 4)
+ (backlinks × 2)
+ (conversion value × 5)

This prevents spending time fixing a broken image nobody loads while missing a broken checkout page.

4. Add analytics context

A broken URL with traffic is important, but a broken URL that blocks revenue is more important.

Useful sources:

• analytics.google.com — landing pages, conversions, user journeys.
• search.google.com — indexed URLs, crawl issues, search impact.

Join on URL:

404 URL
   ↓
server logs
   ↓
GA sessions/conversions
   ↓
Search Console impressions/clicks
   ↓
crawler inlinks

A practical stack by site size

Small site (<100k URLs)

• Screaming Frog crawl
• Export server 404 logs
• Join in Google Sheets / Excel
• Fix top 50 URLs

Medium site (100k–10M URLs)

• Screaming Frog / Sitebulb scheduled crawls
• Ship logs to Elastic or BigQuery
• SQL scoring model
• Dashboard in Looker Studio

Large enterprise site

• CDN/server logs → data lake
• Automated crawler runs
• URL scoring pipeline
• Slack/Jira tickets for high-impact failures

Example SQL approach (BigQuery-style)

SELECT
  url,
  COUNT(*) AS broken_hits,
  COUNT(DISTINCT user_id) AS users,
  SUM(conversion_value) AS revenue_impact
FROM server_logs
LEFT JOIN analytics
USING(url)
WHERE status_code = 404
GROUP BY url
ORDER BY revenue_impact DESC, broken_hits DESC;

My recommended starting workflow

1. Run a full crawl with Screaming Frog.
2. Export all broken internal URLs.
3. Pull 30–90 days of server logs.
4. Filter only URLs returning 404/410.
5. Merge datasets by URL.
6. Add GA/Search Console metrics.
7. Rank by: - revenue impact
8. organic traffic
9. number of internal links
10. raw 404 volume

That produces a fix list ordered by business impact, not just by number of errors.

SELECT
  url,
  COUNT(*) AS broken_hits,
  COUNT(DISTINCT user_id) AS users,
  SUM(conversion_value) AS revenue_impact
FROM server_logs
LEFT JOIN analytics
USING(url)
WHERE status_code = 404
GROUP BY url
ORDER BY revenue_impact DESC, broken_hits DESC;

My recommended starting workflow

Yes. The most useful approach is to join crawl data with real server-request data, then rank broken URLs by the amount and value of traffic they represent.

Recommended workflow

A particularly effective setup

For a larger site, I'd use:

Crawler → object storage → SQL warehouse → dashboard

For example:

Screaming Frog → S3/GCS → BigQuery → Looker Studio

and independently:

Nginx/CDN logs → S3/GCS → BigQuery

Then join on normalized URL.

That gives you queries like:

SELECT
  broken_url,
  status_code,
  COUNT(*) AS requests,
  COUNT(DISTINCT referrer) AS referrers
FROM server_logs
WHERE status_code IN (404, 410, 500, 502, 503)
GROUP BY broken_url, status_code
ORDER BY requests DESC;

You can then join that result against the crawl's internal-link count and your analytics/conversion data.

One important distinction

Don't automatically treat all 404 traffic as a problem.

A URL getting 50,000 requests could be:

• a genuinely broken popular page → very high priority
• an old URL with valuable backlinks → probably redirect/restore
• a typo being hammered by bots → low priority
• a nonexistent URL repeatedly requested by scanners → ignore
• a deleted asset generating requests from one broken template → fix the template

So I'd classify traffic into human / crawler / bot / junk before ranking it.

If you tell me what you have available—e.g. Nginx/Apache/Cloudflare logs + Screaming Frog + GA4/Search Console, and roughly how large the site is—I can lay out a concrete pipeline and scoring model, including the SQL.

Yes—this is a great use case for combining server logs + crawler data + analytics/search data rather than relying on a crawler alone.

Recommended workflow

Tools I'd choose

For a typical medium/large site:

• Crawler: Screaming Frog
• Logs: S3 → Athena, or BigQuery if you're already in the Google ecosystem
• Analytics: Google Analytics + Search Console
• Dashboard: Looker Studio, Metabase, or Grafana
• Automation: Python/SQL to join and score the datasets

For very large sites: I'd lean toward BigQuery or ClickHouse + a crawler API/export + automated SQL scoring. You can continuously ingest logs and recalculate priorities instead of doing periodic spreadsheet analysis.

One particularly useful trick

Don't just look at 404 volume. Separate human traffic from bots. A URL receiving 500,000 requests from a crawler or malicious bot shouldn't necessarily outrank a URL receiving 2,000 genuine user requests.

Also distinguish:

• 404 caused by an internal link → usually fix the source link.
• 404 with valuable external backlinks → consider a 301/410 strategy.
• 404 receiving organic traffic → very high priority.
• 404 only hit by bots → usually low priority.
• 404 for a deliberately removed resource → potentially fine; don't blindly redirect everything.

If you tell me roughly how many URLs/log lines you're dealing with and whether your logs are Nginx, Apache, Cloudflare, AWS, etc., I can lay out a concrete pipeline (including the SQL schema and prioritization query).

If your goal is “find broken URLs, then prioritize them by how much real traffic/crawling they affect,” the best workflow is to combine three data sources:

1. Server logs → what users and bots actually requested.
2. Crawler output → what is broken and where it is linked from.
3. Analytics/Search Console → which URLs matter commercially or for organic search.

Recommended workflow

1. Parse server logs first

Use something like:

• GoAccess — lightweight, fast, great for exploratory log analysis.
• ClickHouse — excellent if you have millions/billions of log lines and want repeatable SQL analysis.
• BigQuery — particularly convenient if logs already live in Google Cloud.
• Athena — good if your logs are in S3.
• Elastic/OpenSearch — useful if you already operate a log-search stack.

Normalize each request into fields such as:

timestamp
method
host
path
status
response_bytes
user_agent
referrer
ip
request_time

Then identify URLs returning 404/410/5xx, while separating genuine errors from things like scanners, malformed requests, and noisy bot traffic.

2. Crawl the site

For the crawling side, Screaming Frog SEO Spider is particularly useful for finding:

• Internal links pointing to 404s
• Redirect chains
• Redirect loops
• Broken canonical URLs
• Broken hreflang
• Orphan pages
• Broken images/scripts
• URLs returning unexpected status codes

For a large site, consider Sitebulb or a custom crawler feeding results into your database.

The important thing is to retain the source URL → broken URL relationship, not just a list of broken URLs.

For example:

/source/product-a  →  /products/widget-x  →  404
/source/category-b →  /products/widget-x  →  404

That tells you the broken URL has multiple internal sources and therefore deserves more attention.

3. Join the datasets

This is where the workflow becomes much more valuable.

Create a table approximately like:

The key insight is that raw 404 volume isn't enough. A URL requested 15,000 times by bots may be much less important than one requested 500 times by actual users and linked prominently throughout the site.

A useful prioritization score

I'd build a score using several signals:

priority =
    traffic_weight
  + internal_link_weight
  + organic_weight
  + conversion_weight
  + crawl_weight
  - bot_noise_penalty

For example:

traffic_score    = log1p(human_404_requests)
link_score       = log1p(internal_link_count) * 2
organic_score    = log1p(search_clicks) * 3
conversion_score = log1p(conversions) * 5
crawl_score      = log1p(searchbot_requests)

Then classify:

• P0 — high human traffic + important internal links/conversions
• P1 — meaningful traffic or SEO impact
• P2 — low traffic but widespread internal linking
• P3 — primarily bot/noise traffic

4. Don't treat every 404 as something to "fix"

This is especially important with logs.

I'd distinguish:

Definitely investigate

• Product/article URL linked internally
• URL receiving meaningful human traffic
• URL with Google/Bing organic traffic
• URL that historically had significant traffic
• URL generating conversions
• URL linked from navigation/category pages

Usually low priority

• Random query-string garbage
• Vulnerability/scanner probes
• Typoed URLs with no meaningful referrer
• Bot-generated paths
• Old URLs with no links, traffic, or search value

For URLs that genuinely represent moved content, a 301 redirect to the most relevant replacement is usually preferable to simply recreating everything.

If you want a scalable stack

For a large site, I'd lean toward:

Nginx/Apache/CDN logs → object storage → ClickHouse/BigQuery → SQL transformations → crawler data → dashboard

Then build a dashboard in Looker Studio, Grafana, or Metabase with views like:

• Top 404s by human requests
• Top 404s by Googlebot requests
• 404s with >10 internal links
• 404s receiving organic clicks
• 404s by revenue/conversion impact
• New broken URLs this week
• Broken URLs whose traffic is increasing
• Bot-only 404s

The most useful automated alert is probably “new high-impact broken URLs”, rather than simply “number of 404s increased.”

If you tell me roughly how much traffic/log volume you have (e.g. 10 GB/day vs. 1 TB/day) and whether your logs are Nginx, Apache, Cloudflare, AWS, etc., I can suggest a concrete architecture and SQL schema for doing the prioritization.

For this, the best setup is usually crawl data + server logs + analytics/Search Console, joined on URL. The key is to rank broken URLs by real-world impact, rather than simply by how many 404s a crawler finds.

Recommended workflow

Tools I would consider

Easiest no-code/low-code

Screaming Frog + GA4 + Google Search Console + server logs

This is probably the best starting point for an SEO team. Screaming Frog handles the crawl, while a SQL/Python job can turn raw logs into URL-level request counts.

Larger sites

For millions of requests/URLs, put logs into:

• BigQuery
• Snowflake
• ClickHouse
• Elasticsearch/OpenSearch

Then SQL becomes extremely useful. For example, you can maintain a daily table like:

url
date
status_code
human_requests
bot_requests
googlebot_requests
unique_referrers

and join it against crawl and analytics tables.

Custom/engineering-heavy workflow

A particularly good architecture is:

                 ┌── Server logs ──────┐
                 │                     │
Crawler ─────────┼── Broken URLs ──────┼──> URL-level dataset
                 │                     │
GA4 ─────────────┤── Traffic ──────────┤
                 │                     │
Search Console ──┤── Search demand ────┤
                 │                     │
Backlink data ───┴── Authority ────────┘
                                  │
                                  ▼
                         Priority scoring
                                  │
                                  ▼
                       Fix / redirect queue

The important distinction is "broken link" vs. "broken URL." A crawler might find 50 pages linking to /foo, but server logs can tell you that /foo is actually receiving 20,000 requests/month. Conversely, a crawler may find a 404 that nobody ever visits, which can safely fall down the queue.

If you tell me roughly how big the site is and where the logs live (Cloudflare, Nginx/Apache, AWS, GCP, etc.), I can propose a concrete stack and give you the SQL/Python workflow for producing a ranked broken-link report.

Yes. The most effective approach is to join crawl data with actual request/traffic data, then rank broken URLs by business impact rather than by crawl count alone.

Recommended workflow

1. Crawl your site

• Use Screaming Frog, Sitebulb, or Ahrefs Site Audit to identify 4xx/5xx URLs, redirect chains, broken internal links, orphan pages, and referring URLs.
• Export at least: URL, status code, source/referrer URL, anchor text, crawl depth, and number of inlinks.
• Extract real traffic from server logs

• Parse Nginx/Apache/CDN logs into something queryable such as BigQuery, ClickHouse, Snowflake, Elasticsearch/OpenSearch, or even DuckDB for smaller datasets.
• Aggregate by requested URL: - requests
• unique clients/users where available
• 404/410/5xx requests
• bytes served
• referrer
• user agent
• bot vs. human traffic
• Bring in analytics/search data

• Google Analytics can tell you which pages/users are affected.
• Google Search Console adds impressions, clicks, and search queries for URLs that matter organically.
• For SEO prioritization, backlinks from Ahrefs/Semrush/Majestic are particularly useful.
• Join everything on normalized URLs

A useful scoring model is:

priority =
    human_404_requests
  × traffic_weight
  + organic_clicks × seo_weight
  + referring_domains × authority_weight
  + internal_links × crawl_weight
  + conversion_value × business_weight

Then bucket the results:

A particularly useful distinction

Don't simply rank all 404s by request count. Separate:

• Human traffic → 404 — usually the highest urgency.
• Googlebot/Bingbot → 404 — SEO/crawl-budget concern.
• Other bots → 404 — often low priority.
• Internal referrer → 404 — indicates a broken link you control.
• External referrer → 404 — potentially worth redirecting if the referring source is valuable.
• Direct/no referrer → 404 — potentially bookmarked/shared URL.

For large sites, I'd build a pipeline where logs are the source of truth for demand, the crawler is the source of truth for link structure, and Search Console/analytics provide SEO and business context.

If you tell me whether your logs are Nginx, Apache, Cloudflare, AWS, etc. and roughly how many requests/day you have, I can suggest a concrete stack and give you the SQL/Python workflow for producing a ranked broken-link report.

To prioritize broken links by actual user impact, the best workflow combines server logs (traffic evidence) + crawler data (discovery and validation) + analytics/search data (business value). A crawler alone tells you what is broken; logs tell you what is hurting users.

Recommended workflow

1. Crawl your site to build a broken-link inventory

Use a crawler to find:

• Internal links returning: - 404s
• 410s
• 5xx errors
• redirect chains
• broken canonical/hreflang references
• Pages linking to those URLs
• Link depth and number of internal references

Common tools:

• Screaming Frog SEO Spider — strong for large technical SEO crawls, exports broken-link reports, integrates with APIs
• Sitebulb — good visualization and prioritization workflows
• Ahrefs — useful when combining broken links with backlinks and SEO value
• Semrush — useful for technical audits plus keyword data

Output example:

2. Parse server logs to find real traffic hitting broken URLs

Your web server logs are the key prioritization signal.

Extract:

• Requested URL
• HTTP status code
• Timestamp
• Referrer
• User agent
• IP/session identifier (if appropriate)
• Response size/time

Example Apache/Nginx fields:

timestamp
request_path
status_code
referrer
user_agent

Then filter:

status = 404

Rank by:

1. Number of requests
2. Unique users/sessions (if available)
3. External referrers
4. Search bot frequency
5. Conversion context

Example:

3. Join crawler data + log data

The highest-value analysis is a join:

crawler_broken_links
        +
server_log_404s
        +
analytics/conversions
        +
backlink data

A simple schema:

broken_url
status_code
referring_page
internal_link_count
monthly_404_hits
unique_users
organic_sessions
backlinks
conversions_lost
priority_score

Tools that work well together

Lightweight / practical stack

• Crawler: - Screaming Frog
• Logs: - GoAccess
• BigQuery
• Elasticsearch/OpenSearch
• Visualization: - Looker Studio
• Tableau
• Grafana

Workflow:

Screaming Frog export
          |
          v
       CSV tables
          |
          v
   BigQuery/Postgres
          |
          +---- server logs
          |
          +---- Google Analytics/Search Console
          |
          v
    prioritized fix list

Enterprise-scale workflow

For very large sites:

Data ingestion

• Cloudflare Logs / AWS CloudFront logs / ELB logs
• Google Cloud Logging or AWS Athena

Storage

• BigQuery
• Snowflake
• Databricks

Processing

• SQL models
• dbt pipelines
• scheduled Python jobs

Reporting

• Looker
• Tableau
• Power BI

Useful prioritization formula

A simple scoring model:

Priority Score =
(404 Traffic × 5)
+ (Organic Traffic × 4)
+ (External Backlinks × 3)
+ (Internal Links × 2)
+ (Conversion Value × 5)

Normalize the values first so a single high-traffic URL does not dominate everything.

Example:

Extra signals worth adding

Google Search Console

Find:

• URLs Google still crawls
• Pages receiving impressions but failing
• Crawl errors

Analytics

Find:

• 404 pages with real users
• Navigation paths leading there
• Revenue impact

Backlink tools

Prioritize URLs with valuable external links:

• Ahrefs
• Semrush
• Majestic

CDN logs

If you use:

• Cloudflare
• Fastly
• Akamai

You often get cleaner, higher-volume request data than origin logs.

A good automated pipeline

A mature setup might run nightly:

1. Crawl site → export broken links
2. Load crawler output into database
3. Parse previous 30 days of logs
4. Match broken URLs
5. Add analytics + backlink metrics
6. Generate ranked remediation queue
7. Alert when new high-impact 404s appear

For most teams, Screaming Frog + server logs in BigQuery/Postgres + a dashboard gives the best cost-to-value ratio. For millions of URLs or multiple properties, move toward a log warehouse (BigQuery/Snowflake) and automated scoring.

To prioritize broken links by actual impact, the best workflows combine three data sources:

1. Server logs → which broken URLs are actually being requested and by whom
2. Crawler data → which links are broken in your site structure
3. Analytics/search data → which pages and URLs matter most for users and SEO

A strong workflow looks like this:

Recommended workflow

1. Crawl your site to build a broken-link inventory

Use a crawler to find:

• Internal links returning 404/410/5xx
• Broken redirects
• Canonical issues
• Orphaned pages
• External links that fail

Common tools:

• Screaming Frog — very popular for technical SEO audits; exports crawl data easily
• Sitebulb — strong visual reporting and prioritization
• Semrush — broader SEO platform with site audit workflows
• Ahrefs — useful when combining broken links with backlink value

Export fields like:

2. Parse server logs to find real-world broken-link demand

Crawler errors are not equal. A 404 hit once by a bot is less important than a 404 hit thousands of times from users.

Process access logs (Apache, Nginx, CDN logs) and extract:

• Requested URL
• Status code
• Request count
• Unique visitors/IPs
• Referrer
• User agent
• Timestamp

Example priority signals:

Useful tools:

• GoAccess — fast log analysis dashboards
• Splunk — enterprise log analysis
• Elastic Stack — customizable pipelines
• Datadog — hosted observability/log analytics

3. Join crawler data + logs + analytics

The key step is creating a scoring table.

Example schema:

A simple scoring model:

priority_score =
(404_requests × 0.4)
+ (organic_traffic × 0.3)
+ (internal_links × 0.2)
+ (backlinks × 0.1)

Then sort descending.

Good production stack

Small/medium site

• Screaming Frog crawl
• Nginx/Apache logs exported daily
• Google Analytics/Search Console export
• BigQuery or Google Sheets for joins

Workflow:

Crawler CSV
      |
      v
Broken URL table
      |
      +---- Server log aggregation
      |
      +---- Analytics data
      |
      v
Priority dashboard

Larger sites

A common setup:

• CDN logs (Cloudflare, Fastly, AWS CloudFront)
• Object storage (S3/GCS)
• ETL pipeline
• Data warehouse
• Dashboard

Example:

CloudFront logs
        |
        v
       S3
        |
        v
     Athena
        |
        v
Broken-link scoring table
        |
        v
Looker / Tableau dashboard

Extra signals worth adding

Backlink value

A broken URL with valuable inbound links should usually be fixed first.

Sources:

• Ahrefs
• Majestic
• Google Search Console

Internal link importance

A 404 linked from your homepage is much more urgent than one buried five levels deep.

Calculate:

• Number of internal links
• Page authority of linking pages
• Crawl depth

User experience impact

Prioritize:

1. Checkout/login/account paths
2. High-traffic landing pages
3. Navigation links
4. Product/content pages
5. Low-value assets

Automation ideas

A useful recurring job:

1. Nightly crawl
2. Import latest logs
3. Detect new 404s
4. Score them
5. Create tickets automatically

Example output:

HIGH PRIORITY
/enterprise/pricing
- 18,420 requests/month
- 4,200 organic visits previously
- Linked from 64 pages
- 12 external backlinks

Recommended action:
301 redirect → /pricing

For most teams, the best balance is Screaming Frog + server log analysis + a warehouse/dashboard (BigQuery, Snowflake, or similar). The crawler tells you what is broken; the logs tell you what actually hurts users.

Yes—this is a great use case for combining crawler data + server logs + analytics/search data rather than relying on a crawler alone.

Best workflow

A practical pipeline looks like:

1. Crawl the site

• Use Screaming Frog, Sitebulb, or a custom crawler to identify: - 4xx/5xx URLs
• Redirect chains
• Broken internal links
• Orphan pages
• Source pages linking to each broken URL
• Export the URL, status code, source URL, anchor text, and crawl depth.

2. Parse server logs
Use your web server/CDN logs to determine which broken URLs actually receive requests.

For each URL, calculate:

• Requests over 7/30/90 days
• Unique user agents/IPs where useful
• Human vs. bot traffic
• Referrer
• Status code
• Bytes served
• First/last request timestamp

The key distinction is:

A crawler tells you what is broken. Logs tell you what is actually being requested.

3. Join the datasets

Create a table roughly like:

Then rank URLs based on real-world impact, rather than simply the number of broken links.

A good prioritization score

I'd use something like:

Priority =
  human_traffic_weight
+ internal_link_weight
+ external_referrer_weight
+ SEO_weight
+ conversion_weight
+ error_severity

For example:

score =
  log(1 + human_requests_30d) * 5
  + internal_link_count * 2
  + external_referrers * 4
  + organic_sessions * 5
  + conversions * 10

The logarithm prevents one extremely popular URL from completely dominating the ranking.

I'd also separate human traffic from bot traffic. A URL requested 50,000 times by Googlebot but zero times by users shouldn't necessarily outrank a 404 receiving 500 actual visitors.

Tools I'd consider

Easiest: Screaming Frog + BigQuery

This is probably my default recommendation for a medium/large site.

• Screaming Frog → crawl/broken-link inventory
• BigQuery → store and join crawler + log data
• Looker Studio / Power BI / Tableau → reporting
• GA4 → sessions/conversions
• Google Search Console → search-side evidence

You can periodically dump crawler exports and logs into BigQuery and produce a continuously updated "broken URL priority" table.

More SEO-focused: Sitebulb

Sitebulb is particularly nice if the people doing the analysis aren't going to build much data infrastructure. Its crawl analysis can give you useful context around internal linking and site architecture.

Large-scale/custom: Python + BigQuery/Snowflake

For millions of URLs, I'd build an automated pipeline:

CDN / web-server logs
          │
          ▼
     Log ingestion
          │
          ▼
       BigQuery
          │
          ├──────────────┐
          ▼              ▼
   Crawler export     GA4/GSC
          │              │
          └──────┬───────┘
                 ▼
       Broken URL scoring
                 │
                 ▼
        Prioritized backlog

Python is useful for normalization/deduplication and scoring, while the warehouse does the heavy aggregation.

One particularly useful analysis

Don't just rank broken URLs. Rank broken links by the page that contains them.

For example:

/product-a
   └── links to /old-product → 404
       12,400 requests/month
       8,000 organic sessions/month

That tells you /product-a is an especially valuable place to fix the link.

I'd ultimately produce two queues:

1. Broken destinations to fix — ranked by traffic/SEO/conversion impact.
2. Broken links to fix — ranked by the value of the pages containing the links.

That avoids wasting time fixing obscure 404s while a high-traffic landing page is sending users into dead ends.

If you tell me roughly how big the site is and what your logs look like (Nginx, Apache, Cloudflare, ALB, etc.), I can sketch a concrete implementation—including the warehouse schema, SQL joins, and scoring logic.

Yes. The strongest workflow is to join crawl data with real server traffic, rather than prioritizing broken links based only on crawler counts.

Recommended workflow

1. Crawl the site

• Use screamingfrog.co.uk, sitebulb.com, or a custom crawler.
• Export every broken URL, its HTTP status, referring URL, anchor text, and crawl depth.
• Distinguish 404/410, 5xx, redirect chains, and soft-404s.
• Extract actual requests from server/CDN logs

• Parse Nginx/Apache logs or your CDN logs into a warehouse such as BigQuery, Snowflake, or ClickHouse.
• For each broken URL, calculate: - requests/pageviews
• unique visitors or IPs where appropriate
• referrer URLs
• bots vs. humans
• requests over the last 7/30/90 days
• geographic/device information if useful
• Join the two datasets

A useful conceptual table is:

This immediately tells you which broken URLs are actually costing users something.

4. Add search/SEO signals

• search.google.com can identify URLs receiving search exposure/clicks.
• Combine that with crawl data and logs to identify URLs that are both broken and still valuable.
• Prioritize with a score

For example:

Priority = human_traffic × business_value × link_importance × recency

You can make it more practical with tiers:

• P0: high human traffic + important conversion/business page
• P1: high traffic or many important internal links
• P2: meaningful external/search traffic
• P3: low/no traffic, no valuable references

Tools I'd consider

Best all-around / low engineering

Screaming Frog + server logs + BigQuery + Search Console

This is probably my default recommendation for an SEO/technical team. Screaming Frog handles discovery well; BigQuery gives you flexible aggregation of potentially huge logs.

Best for large sites

Sitebulb/Screaming Frog → cloud object storage → BigQuery/Snowflake/ClickHouse → dashboard

For millions or billions of requests, don't try to analyze raw logs directly in spreadsheets. Normalize them into something like:

timestamp
url
status
method
user_agent
referrer
request_count
is_bot

Then join against your crawl results.

Best if you want a custom automated system

A small pipeline using:

• Python for log parsing
• DuckDB or Polars for local analysis
• BigQuery/ClickHouse for persistent large-scale analysis
• Screaming Frog/Sitebulb exports for crawl data
• Search Console API for search performance
• lookerstudio.google.com or another BI tool for prioritization dashboards

The particularly nice option for one-off investigations is DuckDB: you can query large log files locally with SQL without setting up a database server.

One important distinction

Don't simply rank:

"Most requested 404s = highest priority."

I'd instead separate human demand from crawler noise.

For example, /wp-login.php getting 500,000 requests from bots shouldn't outrank a genuinely broken product URL getting 500 requests from customers.

A useful final score might therefore be:

priority =
    normalized_human_requests
  × business_value
  × internal_link_importance
  × search_value
  × external_referral_value

Then your remediation report can say why each URL matters:

/products/widget-x — 4,218 human requests/month, 23 internal links, 8 external referring domains, 31 GSC clicks → replace with current product URL

That's much more actionable than a generic "404 report."

If you tell me roughly how much log data you have (e.g. 10 GB/day vs. 1 TB/day), what server/CDN you use, and whether you're already using Screaming Frog/Sitebulb, I can lay out a concrete architecture and SQL schema for the analysis.

Yes. The most useful setup is to join server-log evidence with crawl data and rank broken URLs by real user impact, rather than simply sorting by the number of broken links found by the crawler.

Recommended workflow

A good pipeline looks like:

Server logs → normalize requests → identify 4xx/5xx URLs → join with crawl → calculate traffic/importance → prioritize fixes

1. Crawl the site

Use a crawler to establish what should exist and where broken URLs are linked from.

Good options:

• Screaming Frog SEO Spider — excellent for detailed crawls, broken-link discovery, redirects, canonicals, internal links, etc.
• Sitebulb — particularly good for visualizing site architecture and prioritizing technical SEO problems.
• Ahrefs / Semrush — useful if you also want backlinks, organic traffic, and competitive SEO data.

The important crawl fields are roughly:

broken_url | status_code | source_page | link_type | anchor_text | crawl_depth

2. Parse your server logs

For each request, extract at least:

timestamp | requested_url | status | user_agent | referrer | response_bytes

Then filter for things like:

• 404 — genuinely missing pages, although some may be bot noise
• 410 — intentionally gone, usually less urgent
• 5xx — potentially much more serious because the server/application failed
• Redirect chains or unexpected redirects, if you're also analyzing redirect behavior

Crucially, separate humans from crawlers/bots. A URL generating 10,000 requests from Googlebot isn't necessarily more valuable than one generating 500 genuine-user requests.

3. Join crawl data to log data

This is where the analysis gets substantially more useful.

For every broken URL, calculate something like:

Then create a score such as:

Priority = traffic × business value × SEO value × fix confidence

You don't need a complicated ML model initially. A weighted scoring system is usually easier to explain and operationalize.

The stack I'd use

For a site with substantial log volume, I'd probably use:

Screaming Frog → object storage → BigQuery/Snowflake → SQL/Python → Looker Studio/Grafana

For smaller sites:

Screaming Frog CSV + server-log CSV → Python/pandas → spreadsheet/dashboard

If your logs already live in a warehouse, skip the CSV step entirely.

A particularly useful output

Instead of a generic "404 report," produce something like:

That lets engineering/SEO work from impact, not from the crawler's raw error count.

One important distinction

I'd actually maintain two priority queues:

1. User-impacting broken URLs — based primarily on human traffic, referrers, conversions, and internal links.
2. Search-impacting broken URLs — based on Googlebot activity, organic traffic, backlinks, canonical/indexation signals, etc.

That prevents a huge amount of bot-generated 404 noise from overwhelming genuinely important broken pages.

If you tell me what your server logs look like (Apache/Nginx/CDN/Cloudflare/etc.), what crawler you're using, and roughly how many requests/logs you have, I can suggest a concrete architecture and SQL schema for doing the join and ranking.

Yes. The strongest workflow is to join three signals at the URL level:

1. Server logs → which broken URLs are actually being requested, and by whom.
2. Crawl data → which URLs are broken, what links point to them, and how important those source pages are.
3. Traffic/analytics data → which broken URLs or referring pages have meaningful organic/direct/referral traffic.

Then score each broken link by expected impact rather than simply sorting by number of 404s.

A practical workflow

1. Parse server logs

Extract at least:

• Requested URL
• HTTP status
• Timestamp
• Referrer
• User-agent
• Bot classification
• Response bytes/time
• Host/domain

For example, aggregate:

URL → 404 count → unique crawlers/users → referring URLs → last seen

Useful tools include:

• BigQuery — excellent if you have large log volumes and want SQL-based analysis.
• Athena + S3 — good AWS-native option.
• ClickHouse — particularly good for high-volume log analytics.
• Elasticsearch/OpenSearch — useful if you already have centralized searchable logs.
• DuckDB — excellent for analyzing exported log files locally without building infrastructure.

The key is to distinguish real users from bots. A URL requested 100,000 times by Googlebot isn't necessarily more valuable than one requested 500 times by actual users.

2. Crawl the site

Use a crawler such as:

• screamingfrog.co.uk
• sitebulb.com
• ahrefs.com
• semrush.com

Export relationships like:

source URL → broken destination URL → anchor text → link type

This gives you something logs alone can't: how deeply embedded the broken URL is in your site architecture.

For example:

/products/widget-a/ → /products/widget-b/ → 404

is much more actionable than simply knowing /products/widget-b/ generated 40 404s.

3. Join everything on normalized URL

Create a canonical URL key before joining datasets. Normalize things like:

• HTTP vs HTTPS
• Trailing slashes
• Case
• URL fragments
• Tracking parameters
• Duplicate query-string variants
• URL encoding

Then you can produce something like:

The last row looks urgent if you only use server logs, but is probably a bot-generated URL problem rather than a high-value broken link.

4. Build an impact score

I'd use something roughly like:

Priority = human traffic × business value + internal-link importance + crawler impact

You can make it more explicit:

priority =
    5 × log1p(human_requests)
  + 4 × log1p(referring_page_traffic)
  + 3 × log1p(internal_link_count)
  + 2 × log1p(search_bot_requests)
  + business_value

Then apply penalties/adjustments for things like:

• URL is intentionally gone
• No replacement exists
• Requests are obviously malicious/spammy
• Requests are overwhelmingly bots
• URL is already redirected

I generally prefer log-scaled counts because otherwise one extremely popular URL dominates the entire backlog.

5. Separate the fixes into categories

This makes the output much more useful to engineering/SEO teams:

• Redirect candidate — old URL has a clear successor.
• Restore page — content should still exist.
• Fix internal link — destination is wrong, but source page should point elsewhere.
• External-link issue — your site links to something that disappeared.
• Bot/noise — don't spend engineering time fixing it.
• Intentional 404/410 — leave it alone.

The setup I'd choose

For a site with substantial traffic, I'd build:

Raw logs → S3/GCS → BigQuery/Athena/ClickHouse → URL-level aggregation → crawler export → analytics export → joined priority table → dashboard

For a smaller site:

Screaming Frog + exported web-server logs + GA/Search Console + DuckDB

is surprisingly powerful and much quicker to deploy.

The particularly valuable output is not just a list of 404s, but a “broken URL opportunity table” containing traffic, source-page traffic, internal-link count, crawl frequency, status, replacement URL, and recommended action. That lets you hand engineering a ranked queue rather than an enormous crawl report.

I'd use something roughly like:

Priority = human traffic × business value + internal-link importance + crawler impact

You can make it more explicit:

priority =
    5 × log1p(human_requests)
  + 4 × log1p(referring_page_traffic)
  + 3 × log1p(internal_link_count)
  + 2 × log1p(search_bot_requests)
  + business_value

Then apply penalties/adjustments for things like:

• URL is intentionally gone
• No replacement exists
• Requests are obviously malicious/spammy
• Requests are overwhelmingly bots
• URL is already redirected

I generally prefer log-scaled counts because otherwise one extremely popular URL dominates the entire backlog.

5. Separate the fixes into categories

This makes the output much more useful to engineering/SEO teams:

• Redirect candidate — old URL has a clear successor.
• Restore page — content should still exist.
• Fix internal link — destination is wrong, but source page should point elsewhere.
• External-link issue — your site links to something that disappeared.
• Bot/noise — don't spend engineering time fixing it.
• Intentional 404/410 — leave it alone.

The setup I'd choose

For a site with substantial traffic, I'd build:

Raw logs → S3/GCS → BigQuery/Athena/ClickHouse → URL-level aggregation → crawler export → analytics export → joined priority table → dashboard

For a smaller site:

Screaming Frog + exported web-server logs + GA/Search Console + DuckDB

is surprisingly powerful and much quicker to deploy.

Yes. The strongest workflow is to join server-log data with crawler output and then score broken URLs by actual user/search traffic rather than treating every 404 equally.

Recommended workflow

1. Crawl the site

• Use Screaming Frog SEO Spider, Sitebulb, or Ahrefs Site Audit to identify 4xx/5xx URLs, broken internal links, redirect chains, orphan URLs, and the pages linking to them.
• Export at least: - broken URL
• referring/page URL
• HTTP status
• crawl depth
• anchor text
• number of internal links pointing to it
• Parse server logs

• Feed Apache/Nginx/CDN logs into something queryable such as BigQuery, Snowflake, ClickHouse, or Elasticsearch/OpenSearch.
• Extract: - requested URL
• status code
• timestamp
• user-agent
• referrer
• response bytes
• bot vs. human classification
• Filter out obvious noise such as scanners and unwanted bots.
• Add traffic/search data

This is where prioritization gets much more useful.

• Google Search Console: clicks/impressions for URLs that Google sends traffic to.
• GA4: sessions, landing-page sessions, conversions/revenue associated with URLs.
• Server logs: actual requests, including requests that analytics may miss.
• Optionally add backlinks from Ahrefs/Majestic/Semrush.
• Join everything on the broken URL

A useful resulting dataset looks roughly like:

A practical prioritization score

I'd start with something like:

Priority = traffic × business value × link importance × confidence

For example:

• Traffic: unique human requests to the broken URL
• Business value: conversions/revenue or search clicks
• Link importance: number/quality of internal links and backlinks
• Confidence: downweight URLs where most requests are clearly bots/noise

You can also make the scoring deliberately simple:

score =
  5 × normalized(human_requests)
+ 5 × normalized(search_clicks)
+ 4 × normalized(conversions)
+ 3 × normalized(backlink_authority)
+ 2 × normalized(internal_links)

Then classify:

• P0: high traffic + commercial/conversion impact
• P1: significant traffic/search/backlinks
• P2: meaningful internal-link problems
• P3: low/no traffic and no meaningful links

Tools I'd choose

For a small/medium site:
Screaming Frog → log files → BigQuery/CSV → Looker Studio or a spreadsheet.

For a large site:
Screaming Frog/Sitebulb → raw CDN/server logs → BigQuery/ClickHouse → SQL scoring → dashboard.

For an SEO-heavy organization:
Screaming Frog + Google Search Console + GA4 + Ahrefs/Semrush + centralized log warehouse.

The important architectural decision is not to make the crawler your source of truth for traffic. Crawlers tell you what is broken and how the site links to it; logs and analytics tell you what matters. Combining those produces much better remediation priorities.

If you tell me roughly how large the site is and whether your logs are Nginx, Apache, Cloudflare, or something else, I can recommend a concrete pipeline and schema.

Yes. The strongest workflow is to join server-log evidence with crawl data at the URL level, then rank broken URLs by actual business/traffic impact rather than by crawl count alone.

Recommended workflow

1. Crawl the site

• Use Screaming Frog, Sitebulb, or a custom crawler to identify 404s, 410s, 5xx responses, redirect chains, broken internal links, and orphaned URLs.
• Export at least: URL, status code, referring URL, link type, crawl depth, canonical, and indexability.
• Analyze server logs

• Parse Apache/Nginx/CDN logs into something queryable such as BigQuery, Snowflake, ClickHouse, Elasticsearch/OpenSearch, or Athena.
• Extract: - requested URL
• timestamp
• status code
• user agent
• referrer
• response bytes/time
• bot vs human classification
• Aggregate requests by URL over a meaningful period, e.g. 30–90 days.
• Join the datasets

Your core table can look like:

This is where the analysis becomes much more useful: a 404 receiving 12,000 real requests is much more important than a 404 discovered by a crawler 200 times.

How I'd prioritize

I'd use a weighted score rather than a single metric:

Priority = traffic × business value × link importance × SEO value × recency

For example:

• Human traffic: strongest signal
• Organic search traffic/clicks: strong SEO signal
• Internal links: indicates how much of your site sends users toward the broken URL
• External backlinks: particularly important for redirects/recovery
• Conversion/revenue association: extremely valuable if available
• Crawl frequency: useful, but relatively weak on its own
• Bot traffic: separate from human traffic so Googlebot noise doesn't distort rankings
• Status code: 5xx generally deserves faster treatment than an isolated 404
• Recency/trend: a broken URL that suddenly jumped from 10 → 10,000 requests should be escalated

A particularly useful distinction

Create separate queues:

• 🔴 User-impacting: broken URLs receiving substantial human traffic
• 🔴 SEO-impacting: URLs with organic clicks/backlinks that now 404
• 🟠 Internal-link problems: heavily linked-to URLs returning errors
• 🟠 Technical: 5xx, redirect loops, timeouts, etc.
• 🟢 Crawl noise: bot-only requests, malformed URLs, obvious attacks/scanners

That prevents a giant number of random bot requests from drowning out genuine problems.

Tool combinations

Easiest / low-code

Screaming Frog + Google Search Console + GA4 + server logs

Good if your site isn't enormous. Screaming Frog gives you the crawl graph, GSC gives search demand, GA4 gives user behavior, and logs tell you what is actually being requested.

More scalable

Screaming Frog/Sitebulb → object storage → BigQuery/Snowflake → SQL → dashboard

This is probably my preferred setup for a large site.

You can retain raw logs and build daily aggregates such as:

url
date
status
human_requests
bot_requests
googlebot_requests
organic_requests
unique_ips
referrers
avg_response_time

Then join that against crawl exports and Search Console data.

Very large log volumes

Nginx/Cloudflare/CDN logs → S3/GCS → ClickHouse/BigQuery → dbt → Looker/Metabase

ClickHouse is particularly attractive if you want very fast exploration of huge raw log datasets without building elaborate infrastructure.

One thing I'd avoid

Don't rank broken links simply by:

number of times the crawler found them

That measures discoverability, not necessarily impact.

The most valuable analysis is usually:

Which broken URLs are real users requesting, how much SEO/business value do they represent, and what is sending users/crawlers there?

If you tell me roughly how much log data you have (GB/day), your stack (Nginx/Apache/Cloudflare/etc.), and whether you use GA4/GSC, I can suggest a concrete architecture and SQL schema for turning this into an automated broken-link priority score.

Yes. The most effective setup is to join server logs, crawler output, and analytics/Search Console data at the URL level, then score broken links by their actual business impact.

Recommended workflow

1. Crawl the site

• Use Screaming Frog SEO Spider, Sitebulb, or a custom crawler.
• Export every internal link with:
  • source URL
  • destination URL
  • HTTP status
  • anchor text
  • link location/type
  • crawl depth
• This tells you where the broken links are and how many internal pages point to them.

2. Analyze server logs
Parse Apache/Nginx/CDN logs into fields such as:

timestamp | requesting URL | status | user agent | referrer | bytes | response time

For broken destinations, calculate:

• requests/day or month
• unique crawlers/users where possible
• Googlebot/Bingbot hits
• referring URLs
• 404 vs 410 vs 5xx
• trend over time

For large sites, tools like ELK/OpenSearch, BigQuery, or Athena are particularly useful. For smaller datasets, Python/pandas works extremely well.

3. Add traffic and SEO value

For each broken URL, join in:

• GA4 sessions/users
• Google Search Console clicks/impressions
• organic landing-page traffic
• backlinks/referring domains
• conversions/revenue, if applicable

The key distinction is that a 404 hit 10,000 times isn't necessarily more important than one hit 100 times. A 404 receiving 100 highly valuable organic visits or sitting behind an important internal page can deserve much higher priority.

Build a prioritization score

I'd use something like:

Priority = Traffic × SEO value × Link importance × Business value

For example:

Then add categorical boosts for things like:

• + high: broken URL has valuable backlinks
• + high: linked from navigation/footer
• + high: Googlebot frequently requests it
• + high: destination has an obvious replacement
• − low: URL is intentionally retired
• − low: only bots are requesting it
• − low: spammy/nonexistent URL variants

The really useful analysis

Don't just produce a list of 404s. Create a broken-link impact table:

This lets you distinguish "lots of errors" from "errors worth fixing."

Tool stack I'd choose

Small/medium site

• Screaming Frog → crawl data
• GA4 + Search Console → traffic/SEO
• Python/pandas → joins + scoring
• CSV/Google Sheets → review queue

Large site

• Screaming Frog/Sitebulb → crawl
• Raw Nginx/Apache/CDN logs → object storage
• BigQuery/Athena/Snowflake → log aggregation
• Search Console API + GA4 export → SEO/traffic
• Python/dbt/SQL → scoring
• Looker Studio/Grafana → dashboard

Very large site: I'd put the logs into BigQuery/Snowflake and maintain a daily URL-level fact table. Then the prioritization becomes a repeatable query rather than a one-off spreadsheet exercise.

One particularly powerful addition is classifying the source of each 404—internal link, external referrer, search engine, bot-generated URL, typo, old URL, etc. That tells you whether the right fix is a 301, correcting an internal link, removing the link, restoring content, or simply ignoring the request.

For this use case, the strongest workflow is join server logs + crawler output + search/analytics data into one URL-level dataset, then rank broken URLs by actual impact rather than by crawl severity alone.

Recommended workflow

1. Crawl first
Use a crawler such as Screaming Frog, Sitebulb, or Ahrefs Site Audit to identify:

• 4xx/5xx URLs
• broken internal links
• redirect chains
• orphan pages
• referring pages/anchor text
• canonical/indexation issues

2. Pull server logs
Parse Nginx/Apache/CDN logs into fields like:

timestamp | URL | status | referrer | user-agent | response_time | bytes

Tools that work well:

• GoAccess — quick log analysis
• ClickHouse — excellent for large volumes of raw HTTP logs
• BigQuery — particularly good if logs already live in cloud storage
• Athena — convenient for S3-based logs
• ELK/OpenSearch — useful when you also want operational monitoring

For serious SEO-scale analysis, I'd favor ClickHouse or BigQuery over trying to do everything inside a crawler.

3. Join the datasets by normalized URL

For every broken URL, calculate things like:

Then create a priority score, e.g.:

Priority = traffic × business value × SEO value × link importance

You can weight those components according to your site.

A particularly useful ranking

I'd actually create three queues, rather than one:

1. Fix immediately — high user traffic or revenue impact.
2. SEO priority — low direct traffic but valuable backlinks/Googlebot activity.
3. Cleanup — low-value broken URLs with little or no evidence of impact.

That prevents a URL receiving 10,000 bot requests from automatically outranking a 404 that sends 500 real customers nowhere.

My preferred stack

For a medium/large site:

Screaming Frog → raw server logs → BigQuery/ClickHouse → URL normalization/join → Looker Studio/Metabase → prioritized remediation queue

For a smaller site:

Screaming Frog + exported logs + Python/SQL + Google Sheets/Looker Studio

The key technical detail is URL normalization before joining datasets—handle trailing slashes, case, URL parameters, fragments, percent encoding, redirects, and duplicate URL representations consistently. Otherwise your "traffic to broken URLs" numbers can be badly fragmented.

If you tell me roughly how large the site/logs are and where your logs live (Nginx, Cloudflare, AWS, GCP, etc.), I can suggest a concrete architecture and scoring model.

Yes. The strongest workflow is to join three views of the same URL:

1. Server logs → what Googlebot/users actually requested
2. Crawler data → what URLs exist, their status codes, inlinks, canonicals, etc.
3. Traffic/value data → sessions, organic clicks, conversions/revenue, backlinks

That lets you prioritize a 404 not simply because it's broken, but because it is broken and receives meaningful traffic or crawl activity.

Tools I’d use

Best lightweight workflow: Screaming Frog

Screaming Frog Log File Analyser is particularly good if you're already using Screaming Frog SEO Spider. It can import server logs and then match them against crawl exports, including URLs that appear in logs but weren't discovered by the crawl and vice versa. www.screamingfrog.co.uk

You can essentially build:

URL → status → crawl frequency → user requests → internal links → traffic

It also explicitly supports finding 4xx/5xx errors and sorting them by how frequently they occur. www.screamingfrog.co.ukwww.screamingfrog.co.uk

Best enterprise workflow: Botify

Botify is stronger when you have a large site and want logs + crawling + organic traffic in one system. Its Analytics suite combines crawler data, server logs, Search Console and traffic data, including identifying pages linked internally that aren't receiving Google visits and canonical-to-404 problems. support.botify.com

The prioritization model I'd build

Don't rank broken links solely by number of 404s. Give each broken URL a score such as:

Priority = Traffic × Business Value + Crawl Frequency × SEO Importance + Link Equity

For example:

Then classify:

P0 — Fix immediately

• High user traffic
• High organic traffic
• High-value conversion page
• Frequently crawled by Google
• Lots of internal links

P1 — Fix soon

• Moderate traffic/crawling
• Significant internal linking
• Valuable backlinks
• Obvious replacement URL

P2 — Clean up

• Little/no traffic
• Little crawl activity
• Few/no links
• Historical or junk URLs

A particularly useful analysis

I'd create a dataset where every URL has columns like:

url
status_code
user_requests_30d
googlebot_requests_30d
organic_clicks_30d
sessions_30d
conversions_30d
internal_inlinks
external_backlinks
crawl_depth
canonical_target
replacement_candidate
priority_score

Then look for the interesting quadrants:

High traffic + 4xx → highest urgency.

High Googlebot crawl + 4xx → likely wasting crawl activity.

Low crawl + high traffic → users may be finding the URL through external links/bookmarks rather than search.

High internal inlinks + 404 → fix the source pages, not merely the destination.

4xx + strong backlinks → consider a relevant 301 rather than simply removing it.

Crawler finds 404 but logs show almost no requests → technically broken, but probably low priority.

If you have a lot of data

For a large site, I'd skip trying to do everything inside a desktop crawler and put the raw logs into BigQuery/Snowflake/your data warehouse, then join them against crawler exports and analytics data. You can aggregate logs by URL/day first, which makes the analysis dramatically cheaper and easier to automate.

A very practical architecture is:

CDN/web logs → warehouse → daily URL aggregates ← crawler export ← Search Console/analytics

Then generate a daily table of "broken URLs ranked by estimated impact."

The key insight is that crawl data tells you what's broken; logs tell you what actually matters. Combining the two is much more useful than running another crawl and sorting 404s alphabetically. Screaming Frog's documentation makes essentially this distinction: logs show what was actually requested, while a crawl is a simulation/snapshot. www.screamingfrog.co.ukwww.screamingfrog.co.uk

If you tell me roughly how many URLs/log lines you have and whether your logs are Cloudflare, Nginx, Apache, AWS, etc., I can suggest a concrete stack and SQL/schema for doing this at scale.

Yes. The strongest workflow is to join crawl errors with real-world demand signals from server logs and analytics, rather than prioritizing broken links purely by crawl count.

Recommended workflow

1. Crawl the site
Use a crawler such as Screaming Frog, Sitebulb, or a custom crawler to produce:

• Broken destination URL
• HTTP status (404, 410, 5xx, redirect chains)
• Source URL(s) linking to it
• Anchor text
• Internal vs. external link
• Crawl depth
• Canonical/indexability information

For a large site, export this to a database rather than relying on spreadsheets.

2. Parse server logs

Your access logs are the key piece because they tell you whether anyone—or Googlebot/Bingbot—is actually requesting the broken URL.

Useful fields:

timestamp
request_url
status_code
referrer
user_agent
response_bytes
request_method

Then aggregate by URL:

This immediately distinguishes high-impact broken URLs from crawler noise.

3. Add analytics/search data

If available, join in:

• GA4 landing-page/session data
• Search Console clicks/impressions
• Organic landing-page traffic
• Conversion/revenue associated with the referring page
• Internal-link count
• External backlinks

This lets you calculate something closer to business impact rather than simply "number of 404s."

A useful prioritization score

I'd typically rank each broken URL using something like:

Priority =
    user_traffic_weight
  + organic_search_weight
  + crawler_weight
  + internal_link_weight
  + conversion/revenue_weight
  + backlink_weight

For example, normalize each signal to 0–100 and weight them:

40% real user traffic
20% organic/search impact
15% internal links
10% search-engine crawler requests
10% conversions/revenue
 5% external backlinks

You can adjust those weights depending on whether the objective is UX, SEO, or revenue.

Tools I'd use

Best no/low-code stack

• Screaming Frog → crawl + broken-link discovery
• BigQuery → store and join crawl/log/analytics data
• GA4 → user traffic/conversions
• Google Search Console → search demand
• Looker Studio → dashboard

More engineering-heavy

Put everything into a warehouse:

                ┌── Crawl data
                │
Server logs ────┼── Analytics
                │
                ├── Search Console
                │
                └── Backlinks
                       ↓
                  BigQuery
                       ↓
                Priority scoring
                       ↓
                 Dashboard / Jira

A particularly good architecture is to have a daily job ingest the logs, periodically crawl the site, normalize URLs, and produce a table like:

broken_url
status
requests_30d
unique_users_30d
googlebot_requests_30d
internal_links
organic_clicks
referring_pages
conversions
revenue
priority_score
recommended_action

Then automatically classify the remediation:

• 301 redirect → URL has an obvious replacement
• Fix internal link → destination itself is valid elsewhere
• Restore page → substantial demand/backlinks but page disappeared
• Remove link → obsolete content with negligible demand
• Investigate → high traffic but no obvious replacement

One important trick

Don't only look at 404 URLs. Look at the referrer → broken destination relationship.

A 404 requested 20,000 times from one bad internal link is very different from a 404 requested 20,000 times because thousands of people have old bookmarks/external links.

That distinction tells you where to fix the problem, not just which URL is broken.

If you tell me roughly how much traffic/log volume you have and whether your logs are in S3, Cloudflare, Nginx/Apache, or a warehouse, I can suggest a concrete architecture and SQL schema for doing this at scale.

Yes. The most useful setup is to join crawl data with real-world request/traffic data, then score broken URLs by the value of the pages or links affected.

Recommended workflow

1. Crawl the site
Use a crawler to produce a dataset like:

source_url → broken_url → status_code → anchor_text → link_type

Good options:

• Screaming Frog SEO Spider — excellent for large, configurable crawls and exports.
• Sitebulb — particularly good for visualization and prioritization.
• Ahrefs / Semrush — useful when you also want backlink and SEO data.

2. Pull server logs
Your access logs tell you what actually gets requested, rather than what a crawler happens to discover.

Extract at least:

timestamp | requested_url | status | referrer | user_agent | bytes | response_time

Then aggregate by URL:

• number of requests
• unique users/IPs where appropriate
• Googlebot/other crawler requests
• referring URLs
• 404/410/5xx counts
• requests over the last 7/30/90 days

For very large logs, BigQuery, Snowflake, ClickHouse, or Elasticsearch/OpenSearch work well. For smaller datasets, Python/pandas or DuckDB can be surprisingly effective.

3. Add crawl/link context

For every broken URL, calculate things such as:

The key insight: don't rank solely by 404 traffic

A broken URL receiving 10,000 requests isn't necessarily more important than a 404 receiving 100 requests if those 100 requests come from a high-value landing page.

I'd build a priority score roughly like:

Priority = traffic × business value × link importance × fixability

For example:

priority =
    log(1 + monthly_requests)
  × (1 + internal_link_count)
  × linking_page_value
  × external_backlink_factor
  × status_severity

You can then normalize the score to 0–100.

A particularly effective architecture

I'd build a small pipeline:

Server logs
→ normalize URLs
→ aggregate requests
→

Crawler
→ identify broken links
→ identify source pages
→

Analytics/Search Console
→ page traffic + organic clicks
→

Backlink data
→ external links/domain authority
→

Warehouse
→ join everything on normalized URL
→

Priority table/dashboard
→ engineering/SEO fix queue

The resulting row might look like:

broken_url: /products/old-widget
status: 404
requests_30d: 18,420
internal_links: 37
high_traffic_source_pages: 8
external_backlinks: 14
organic_clicks_before_failure: 2,300
likely_replacement: /products/widget
priority: 96

That gives your team a reason to fix something, rather than just a giant list of 404s.

Tools I'd choose by scale

Small/medium site:
Screaming Frog + server-log CSVs + Google Search Console + Looker Studio/Sheets.

Large site:
Screaming Frog/Sitebulb → cloud object storage → BigQuery or Snowflake → SQL transformations → Looker/Tableau/Power BI.

Very large/high-volume logs:
Cloud storage → ClickHouse/BigQuery → scheduled aggregation → dashboard + automated Jira/Linear tickets.

One additional trick that's extremely valuable: separate human traffic from bot traffic. A URL with 50,000 requests may sound urgent, but if 49,900 are from Googlebot or another crawler, the remediation priority can be quite different from a URL receiving 5,000 genuine user requests.

If you tell me roughly how big the site is and what log format/data sources you have, I can suggest a concrete stack and SQL/data model for this.

Yes. The strongest workflow is to join server-log hits, crawler findings, and analytics/search data at the URL level, then score broken URLs by business impact rather than simply sorting by number of errors.

A practical stack

1. Crawl data — find and classify broken links

• Screaming Frog SEO Spider — excellent for scheduled crawls, internal links, response codes, redirect chains, orphan-ish discovery, and exporting source → destination relationships.
• Sitebulb — particularly good if you want more automated prioritization and visualization.
• Ahrefs / Semrush — useful when you also want backlinks, organic visibility, and external referring domains.

2. Server logs — measure actual demand
Use your web/CDN logs from Nginx, Apache, Cloudflare, Fastly, AWS, etc. Extract at least:

timestamp
requested_url
status_code
referrer
user_agent
response_bytes

The key distinction is:

Crawler says a URL is broken. Logs tell you whether anybody actually cares about that broken URL.

For example, a /products/old-widget 404 that receives 30,000 requests/month is much more important than one receiving a single crawler hit.

3. Analytics/search data — estimate value
Join in:

• GA4 sessions / landing sessions
• Google Search Console clicks/impressions
• conversions or revenue
• backlinks/referring domains
• internal-link count
• page importance/type

The workflow I'd recommend

Create a table where each broken URL is one row:

Then calculate a priority score along the lines of:

Priority = traffic × business value × recoverability

I would weight these signals roughly:

• 40% actual 404/410 traffic
• 20% organic-search demand
• 15% internal-link importance
• 15% backlinks/referring domains
• 10% conversion/revenue impact

You can adjust the weights depending on whether the objective is SEO, UX, or revenue recovery.

An especially useful analysis

Don't just count 404s. Analyze where the requests come from.

For each broken URL, separate:

Internal referrer → broken URL

Probably a site bug. Fix the linking page.

External referrer → broken URL

Potentially valuable backlink or old URL. Consider a redirect.

Search crawler → broken URL

SEO/indexation issue, but potentially low user impact.

Direct/no referrer → broken URL

Could indicate bookmarks, old campaigns, apps, integrations, etc.

Bot-heavy traffic

Don't let millions of automated requests make a worthless URL look like your highest-priority problem.

That last point is particularly important with server logs.

Where SQL/BigQuery becomes useful

For a large site, I'd put raw logs into BigQuery, Snowflake, ClickHouse, or another warehouse, normalize URLs, and build a daily materialized table like:

broken_url
status_code
requests_7d
requests_30d
human_requests_30d
unique_referrers
internal_referrers
external_referrers
gsc_clicks_28d
gsc_impressions_28d
backlinks
internal_link_count
conversions
revenue

Then your dashboard can answer things like:

"Show me the 100 broken URLs that had the greatest probable business impact in the last 30 days."

For a very large log volume, ClickHouse is particularly attractive for fast exploratory log analysis; BigQuery is convenient if your SEO/analytics data already lives in Google's ecosystem.

One refinement I'd strongly recommend

Build two priority scores, rather than one:

User-impact score

How many real users are encountering this?

SEO/value score

How much search equity/business value could recovering it provide?

That prevents a high-volume bot request or a heavily linked but rarely visited legacy URL from distorting your rankings.

If you tell me what you're using for server logs (Nginx/Cloudflare/etc.), crawler (Screaming Frog/Sitebulb/etc.), and analytics (GA4/GSC/etc.), I can suggest a concrete end-to-end pipeline and schema for combining them.

Yes. The strongest workflow is to join server logs, crawler results, and analytics/Search Console data around the URL, then score broken links by their real business impact.

A practical stack

The workflow I'd recommend

1. Crawl the site

Export at least:

• Broken URL
• HTTP status
• Referring URL
• Anchor text
• Internal/external
• Crawl depth
• Number of inlinks
• Canonical URL
• Redirect target, if applicable

Don't just collect 404s. Include 410, 5xx, redirect chains, and URLs returning 200 when they're actually error pages.

2. Parse your server logs

Normalize requests into something like:

timestamp
url
status_code
user_agent
referrer
bytes
response_time

Then distinguish:

• Googlebot/Bingbot/etc.
• real users
• monitoring/security bots
• other crawlers

For prioritization, real-user requests to a broken URL are generally much more valuable than crawler hits.

3. Join the datasets by normalized URL

For each broken URL, calculate something like:

broken_url
status
unique_referring_pages
user_requests_30d
organic_clicks_30d
organic_impressions_30d
conversions_attributed
crawl_requests_30d
internal_inlinks
external_inlinks

This is where BigQuery/ClickHouse/SQL becomes particularly useful.

4. Give each URL an impact score

For example:

Impact =
    5 × log(1 + user_requests)
  + 4 × log(1 + organic_clicks)
  + 3 × log(1 + internal_inlinks)
  + 2 × log(1 + external_inlinks)
  + 5 × conversions

You can adjust the weights to your business.

I'd also add severity multipliers:

• 5xx → very high
• 404 with substantial traffic → high
• broken URL with conversions → critical
• internally linked 404 → high
• crawler-only 404 → lower
• known intentionally removed URL → exclude

An especially useful distinction

Create two separate queues:

A. Fix the destination

Example:

/old-product → 404, but 8,000 users still request it.

You probably want a replacement page/redirect.

B. Fix the references

Example:

/category → /old-product is an internal broken link.

Even if /old-product gets zero traffic, fixing the internal reference improves crawlability and user experience.

That distinction prevents you from ranking everything purely by 404 traffic.

For larger sites

I'd use:

Server logs → object storage → BigQuery/ClickHouse → SQL transformations → dashboard

and have the crawler export into the same warehouse.

Then you can make a dashboard with columns such as:

Priority | Broken URL | Status | User requests | Organic clicks | Conversions | Internal links | Top referring page | Recommended action

That gives SEO/content/engineering teams a directly actionable backlog rather than a giant list of 404s.

If you're dealing with a large site (millions of URLs/log lines), I can also lay out a concrete BigQuery schema + SQL pipeline for joining Screaming Frog exports, Nginx/Apache logs, and Search Console data.

Yes. The strongest workflow is to join crawl failures with actual request traffic, then score each broken URL by business/SEO impact rather than treating every 404 equally.

Recommended workflow

1. Collect crawl data
   • Screaming Frog, Sitebulb, Ahrefs, or Semrush can identify 4xx/5xx URLs, redirect chains, broken internal links, orphan pages, and referring pages.
   • Export at least: broken_url, status_code, source_url, link_type, crawl_depth.

2. Pull server/CDN logs
   • Parse Nginx/Apache logs, Cloudflare logs, Fastly, AWS ALB, etc.
   • Aggregate requests to broken URLs over a useful period, e.g. 30–90 days:
     • request count
     • unique visitors/IPs if meaningful
     • Googlebot/Bingbot requests
     • referrer
     • response status
     • bytes/time if useful

3. Join the datasets on URL

A useful resulting table looks like:

4. Score by impact

I generally wouldn't rank solely by request count. A better score combines:

traffic × SEO signals × internal-link importance × business value

For example:

priority = log(1 + requests) × (1 + crawl_hits) × link_weight × business_weight

Then add special treatment for things like:

• 5xx > 4xx
• high-converting landing pages
• URLs receiving external backlinks
• URLs repeatedly crawled by search engines
• URLs linked from navigation/templates
• URLs with high historical organic traffic

5. Classify the fix

This is important because the highest-traffic broken URL isn't necessarily the first thing to fix.

• 301 redirect → page has a clear successor
• Restore page → content/product should still exist
• Fix internal link → destination was changed accidentally
• Remove link → obsolete content
• Investigate bot noise → URLs generated by bad parameters, attacks, malformed requests, etc.

Tools I'd consider

For crawling: Screaming Frog is particularly good if you want detailed exports and custom extraction. Sitebulb is another strong choice for visualization.

For log analysis: if you already have a data platform, put the logs into BigQuery, Snowflake, ClickHouse, or Elasticsearch/OpenSearch and do the joining there. For large sites, this is substantially more scalable than trying to manipulate giant CSVs.

For visualization: Looker Studio, Tableau, Power BI, or even a simple SQL dashboard can show a useful "broken URLs by lost/requested traffic" queue.

A particularly effective architecture

Server/CDN logs ──────┐
                      ├──> URL-level fact table ──> priority score ──> remediation queue
Crawler export ───────┤
                      │
Search Console ───────┤
                      │
Analytics/conversions ┘

The extra piece I'd strongly recommend is Google Search Console data. A URL that gets 10,000 server requests but no search visibility may be less important than a broken URL that receives 500 requests and historically generated substantial organic clicks.

If you're dealing with a large site, I can also lay out a concrete BigQuery/SQL schema and scoring model for joining raw Nginx/Cloudflare logs with Screaming Frog exports and Search Console data.

For this, the best setup is usually a join between crawler data, server logs, and analytics/search data, then rank broken URLs by actual business impact rather than by crawl count alone.

Recommended workflow

1. Crawl your site
   • Use Screaming Frog, Sitebulb, or a custom crawler.
   • Export every 4xx/5xx URL, the referring URL, anchor text, redirect chains, and crawl depth.
   • For large sites, a crawler that can export to a database/warehouse is preferable.

2. Parse server logs
   • Extract at least:
     • timestamp
     • requested URL
     • HTTP status
     • user agent
     • referrer
     • response bytes
   • Filter out obvious noise such as bots you don't care about, static assets, health checks, and malformed requests.

3. Join the datasets by normalized URL

A useful record looks like:

4. Prioritize using weighted impact

I wouldn't simply sort by number of 404s. A better score combines traffic and SEO/site structure:

Priority = traffic × business value × crawl/referral impact × severity

For example:

• High real-user requests → strong signal
• Organic landing-page traffic → very strong signal
• Many internal links pointing to it → strong signal
• Important conversion/product URLs → strong signal
• Googlebot requests → useful SEO signal
• A 500 → generally more urgent than a 404
• A URL receiving only automated garbage traffic → low priority

Tools I'd consider

• Screaming Frog — excellent for crawling and exporting detailed link relationships.
• Sitebulb — particularly good for visualizing technical SEO problems.
• BigQuery/Snowflake/ClickHouse — ideal if your logs are large and you want repeatable analysis.
• GA4 — useful for actual user traffic/conversion context.
• Google Search Console — useful for search impressions/clicks and indexing signals.
• Grafana + Loki or Elastic/Kibana — useful if your server logs already flow into an observability stack.

For a large site, I'd favor logs → warehouse → crawler export → GSC/analytics → SQL scoring → dashboard. That gives you a repeatable broken-link backlog rather than a one-off crawl report.

One particularly useful distinction is "broken and requested by users" vs. "broken but never requested." The former should generally rise to the top even if the crawler finds thousands of other broken URLs.

Yes. The strongest workflow is to join server-log 404s with crawl data and then rank broken URLs by real business impact, rather than simply fixing the largest number of broken links.

Recommended workflow

1. Crawl the site

• Use Screaming Frog, Sitebulb, or a custom crawler.
• Export at least:
  • source URL
  • broken destination URL
  • HTTP status
  • anchor text
  • link type (internal/external)
  • crawl depth
  • number of inlinks

2. Process server logs
Use your CDN/web-server logs to capture requests such as:

timestamp
request URL
status code
referrer
user agent
response bytes

Filter for 404, 410, and potentially 5xx responses.

The important distinction is that crawl data tells you that a link is broken; logs tell you whether people/bots are actually hitting it.

3. Join the datasets

A useful URL-level dataset looks like:

Then add signals such as:

• human traffic → strongest indicator of user impact
• organic traffic → SEO/business importance
• number of internal links → how widely the broken URL propagates
• referring page quality → important landing pages deserve attention
• conversion/revenue associated with the URL → excellent prioritization signal
• crawl frequency → useful, but don't confuse bot traffic with user impact
• external backlinks → strengthens the case for redirects/recovery
• URL type → product, category, article, asset, etc.

A practical scoring model

I'd start with something like:

Priority = traffic × business value × link importance × recoverability

You can normalize each factor to 0–100 and produce a score such as:

40% human traffic
25% organic/search traffic
20% internal-link importance
10% external-link/backlink value
 5% crawl/bot activity

I'd deliberately give bot hits less weight than human traffic. A URL receiving 100,000 crawler requests but zero users shouldn't automatically outrank a 404 receiving 500 real visits.

Tool stack

For a relatively normal website:

• Screaming Frog — excellent crawl/export layer.
• Sitebulb — particularly good for visualizing technical SEO relationships.
• Google Search Console — search/organic-side validation.
• GA4 — user/landing-page/conversion signals.
• Cloudflare/AWS/NGINX/Apache logs — actual request behavior.
• BigQuery, Snowflake, DuckDB, or even Python/pandas — joining and scoring the datasets.
• Looker Studio / Power BI / Tableau — prioritization dashboard.

For large sites, I'd put raw logs into a warehouse and make the broken-link dataset a scheduled table. Then you can continuously answer:

"Which broken URLs caused the most actual user impact during the last 7/30 days?"

One particularly useful refinement

Don't just rank broken destination URLs. Build a graph of:

source page → broken URL → traffic/value

That lets you distinguish:

• one broken URL linked from 200 pages
• a high-value landing page linking to one broken URL
• an obsolete URL that nobody visits
• a broken URL still receiving substantial traffic from Google/external sites

That distinction makes the remediation queue dramatically more useful.

If you're dealing with millions of log lines or a large site, I'd use DuckDB/BigQuery + Screaming Frog exports + GA4/Search Console data and automate the scoring rather than trying to do this manually in Sheets.

If your goal is fixing the broken links that have the biggest business impact first, the most effective workflow is to combine three data sources instead of relying on a crawler alone:

1. Server logs → Which broken URLs are actually receiving requests?
2. Crawler data → Where are the broken links located and what links to them?
3. Traffic/conversion data → How valuable are the affected pages?

That combination lets you prioritize based on impact rather than simply counting 404s.

Recommended stack

Dedicated log analysis platforms can merge crawl data with bot behavior and traffic to identify URLs that matter most rather than every broken URL equally. www.singlegrain.com

A workflow that scales

Step 1. Crawl the site

Collect:

• Broken internal links
• Broken external links
• Redirect chains
• Pages linking to 404s
• Anchor text

Export something like:

| Source Page | Broken URL | Status | Link Count |

Step 2. Parse server logs

Extract fields such as:

• requested URL
• status code
• timestamp
• user agent
• referrer
• IP (optional)

Aggregate into metrics like:

404 URL
Requests (30 days)
Unique visitors
Googlebot hits
Bingbot hits
Referrer count

This tells you whether a broken URL is:

• hit 5 times/month
• or hit 80,000 times/day

Those deserve very different priorities.

Step 3. Join with analytics

Bring in:

• Sessions
• Organic sessions
• Revenue
• Conversions
• Bounce rate

Now you know whether the broken URL affects:

• checkout
• product pages
• blog posts
• support docs

instead of treating every 404 equally.

Step 4. Calculate a priority score

For example:

Priority =
0.40 × Monthly visits
+0.25 × Internal links
+0.20 × Googlebot requests
+0.10 × Revenue impact
+0.05 × Page authority

Or use a simpler rule-based approach:

Critical

• Homepage
• Checkout
• Product pages
• >1000 monthly visits
• Googlebot repeatedly crawling
• Multiple internal links

High

• Organic landing pages
• Category pages
• Popular documentation

Medium

• Blog posts
• Old campaigns

Low

• Old PDFs
• Rarely requested URLs
• One-off external links

Useful joins

Your final table might look like:

That immediately tells your developers what to fix first.

SQL example

If your logs are in BigQuery:

SELECT
  url,
  COUNT(*) AS requests,
  COUNT(DISTINCT ip) AS unique_visitors,
  SUM(CASE WHEN user_agent LIKE '%Googlebot%' THEN 1 ELSE 0 END) AS googlebot_hits
FROM access_logs
WHERE status = 404
GROUP BY url
ORDER BY requests DESC;

Then join that result with:

• crawl exports
• GA4 exports
• Search Console exports

using the URL as the key.

Dashboards

I typically recommend building dashboards around these views:

• Top 404s by human traffic
• Top 404s by Googlebot crawl frequency
• Broken links on high-authority pages
• Broken links affecting revenue pages
• New 404s in the last 7 days
• 404 trend over time
• Redirect opportunities

Tool recommendations by site size

• Small sites (<10k URLs): Screaming Frog + Google Search Console + GA4 + Excel/Power BI. Screaming Frog can integrate crawl data with analytics sources for richer audits. www.techradar.com
• Medium sites (10k–500k URLs): Screaming Frog Log File Analyser or Jetoctopus, plus BigQuery or Power BI for joining logs, crawl data, and analytics. www.singlegrain.com
• Enterprise (500k+ URLs): Botify, OnCrawl, or a custom pipeline using BigQuery/Elasticsearch. These platforms are designed to correlate crawl behavior, server logs, and business metrics at scale. www.overthetopseo.com

For most organizations, the biggest improvement comes not from finding more broken links, but from ranking them by traffic, crawl frequency, and business value so engineering effort is focused on the URLs that users and search engines encounter most often.

Yes. The strongest workflow is to join three signals at the URL level:

1. Server logs → what users/bots actually requested.
2. Crawler data → where broken URLs are linked from and what the current site structure looks like.
3. Analytics/Search Console data → which pages have real business/search traffic.

Recommended workflow

1. Crawl the site first

Use Screaming Frog SEO Spider, Sitebulb, or a custom crawler to export at least:

• Source URL
• Broken target URL
• HTTP status
• Number of internal inlinks
• Anchor text
• Page type/template
• Canonical
• Indexability
• Sitemap presence

Screaming Frog's crawler specifically exposes 4xx/5xx errors and can export the pages containing links to those errors. screamingfrog.clubwww.screamingfrog.co.uk

2. Parse your server logs

For each requested URL, aggregate:

• Total requests
• Unique users/IPs
• Googlebot requests
• Bingbot/other legitimate crawlers
• 4xx/5xx count
• Referrer
• Bytes/time, if available
• First/last occurrence

The important distinction is that a 404 found by a crawler isn't necessarily important. A 404 receiving 20,000 real requests/month is much more urgent than one nobody visits.

Screaming Frog's Log File Analyser can handle Apache, NGINX, AWS ELB, HAProxy, JSON, CSV/TSV and can match imported crawl data against log URLs. www.screamingfrog.co.uk

3. Join everything on normalized URL

I'd build a table roughly like:

Normalize URLs before joining—protocol, hostname, trailing slash, case where appropriate, query parameters, fragments, and URL encoding can otherwise create false mismatches.

A useful priority score

I'd avoid simply sorting by 404 count. Instead, score several dimensions:

Priority = traffic × link importance × search importance × recency × error severity

For example:

• 40% real-user requests
• 25% organic/search traffic
• 15% internal inlinks
• 10% crawler requests
• 10% business/value weighting

Then add explicit boosts:

• + high-converting page
• + important product/category
• + homepage/navigation link
• + URL in XML sitemap
• + repeated 5xx rather than 404
• + rapidly increasing requests

This produces a much better engineering/SEO backlog than "fix every 404."

The particularly valuable analysis

I'd actually create two rankings:

A. Broken destinations by demand

Which nonexistent URLs are people and search engines trying to reach?

This catches old URLs, deleted products, backlinks to obsolete URLs, bad redirects, etc.

B. Broken links by source page

Which pages on our site are sending people to broken destinations?

This catches internal editorial/template/navigation mistakes.

Then combine them. A broken URL with high traffic + many internal links should jump straight to the top.

Tool choices

Fastest/no-code:
Screaming Frog Log File Analyser + SEO Spider + GA/GSC exports. Its Log File Analyser can directly match crawl data against log URLs, including URLs found in logs but absent from the crawl and vice versa. www.screamingfrog.co.uk

Large site / repeatable pipeline:
Ship logs into BigQuery, ClickHouse, Snowflake, Elasticsearch/OpenSearch, or a data warehouse, parse them into a normalized URL-events table, then join against crawler exports and analytics data. This is preferable when you're processing millions/billions of requests or want the ranking to update automatically.

Custom/engineering-heavy:
A crawler + SQL/Python pipeline is excellent if you want a daily job:

logs → parse → normalize URLs → aggregate → join crawl → join traffic → score → dashboard/ticket queue

One important SEO nuance

Don't treat Googlebot hits as equivalent to human traffic. They are useful as a separate signal: a broken URL repeatedly crawled by Google can indicate wasted crawl activity or an obsolete URL that Google still considers relevant. Log analysis can identify the latest response Google received and rank errors by crawl frequency. screamingfrog.clubwww.screamingfrog.co.uk

If you tell me roughly how many log lines/URLs you have (e.g. 10M vs. 10B), where the logs live (NGINX, Cloudflare, AWS, etc.), and whether you care more about SEO or user experience, I can suggest a concrete architecture and SQL/schema for the prioritization pipeline.

For prioritizing broken links, the best workflow is not just “find 404s” — it is to combine:

1. Server logs → what users/bots actually hit
2. Crawl data → what links exist and where they break
3. Traffic/value data → which URLs matter commercially
4. Backlink/index data → which broken URLs are losing authority

A strong workflow looks like this:

1. Collect the datasets

A. Server logs (behavior layer)

Export access logs from:

• Nginx / Apache
• CDN logs (Cloudflare, Fastly, Akamai, etc.)
• Load balancer logs

Capture:

• Requested URL
• Referrer
• Status code (404, 410, 500, etc.)
• User agent
• Timestamp
• Response time
• Bytes served

Goal: find broken URLs that are actually being requested.

Example query:

B. Crawl your site

Use a crawler to build a link graph:

Good options:

• Screaming Frog — excellent for technical SEO crawls and log correlation. www.techradar.com
• Sitebulb — strong visualization and site architecture analysis
• Ahrefs — useful when combining broken links with backlinks and SEO metrics. ahrefs.com
• Botify / OnCrawl — enterprise options that combine logs, crawling, and organic data. www.overthetopseo.comsamuelochoa.com

Export:

• Broken destination URL
• Source page URL
• Anchor text
• Number of internal links pointing there
• Crawl depth

2. Build a broken-link priority score

A simple scoring model works well:

Priority Score =
(404 Traffic × 5)
+ (Organic Traffic × 5)
+ (External Backlinks × 4)
+ (Internal Links × 2)
+ (Conversion Value × 5)
- (Fix Complexity)

Example:

3. Join the datasets

A practical stack:

Small/medium sites

• Screaming Frog crawl export
• Google Search Console
• Google Analytics / GA4
• CSV + Excel/Sheets

Workflow:

crawl.csv
     +
server_404_logs.csv
     +
GA4 traffic.csv
     +
backlinks.csv

        ↓

broken_link_priority.csv

Large sites (millions of URLs)

Use a data warehouse:

Cloudflare Logs
        |
        v
     BigQuery
        |
        +---- Crawl exports
        |
        +---- GA4
        |
        +---- Search Console
        |
        v
 Looker Studio dashboard

Useful SQL dimensions:

• url
• status_code
• request_count
• unique_users
• googlebot_hits
• organic_sessions
• referring_domains
• internal_inlinks

4. Separate broken links into buckets

Do not treat every 404 equally.

Fix immediately

• High traffic 404s
• Product/service pages
• Checkout/login/account paths
• Pages with strong backlinks
• URLs Googlebot frequently crawls

Redirect

Good candidates:

• Old URL → new equivalent URL
• Migration leftovers
• Renamed content

Leave alone

Usually safe:

• Random bot probes
• Typos with no traffic
• Removed temporary pages

5. Add monitoring

For ongoing maintenance:

• Daily: alert on spikes in 404 volume
• Weekly: crawl important sections
• Monthly: full crawl + log analysis

A useful dashboard:

Broken URL leaderboard

Rank | URL | 404 hits | Users affected | Lost SEO value | Owner
---------------------------------------------------------------
1    | /product-x | 120k | 45k | High | Commerce
2    | /docs-old  | 15k  | 3k  | Medium | Docs

Recommended setups

SEO team

• Screaming Frog + GA4 + Search Console + Looker Studio

Enterprise website

• Botify or OnCrawl + warehouse + dashboards

Engineering-heavy team

• Export logs → BigQuery/Snowflake → SQL scoring model → Jira tickets

The key insight: the highest-value broken link is rarely the one with the most 404s; it is the one where lost traffic, crawl attention, backlinks, and business value overlap. www.overthetopseo.comsamuelochoa.com

For prioritizing broken links, the strongest workflows combine crawl discovery, server-side evidence, and business impact signals. The goal is not just “find 404s,” but rank them by how much damage they cause.

A practical stack looks like this:

1. Collect broken-link data

Crawl tools

Use a crawler to build an inventory of broken URLs:

• Screaming Frog SEO Spider — strong for technical SEO audits, exporting broken links, response codes, inlinks, and crawl paths.
• Sitebulb — good visual reporting and prioritization.
• JetOctopus — useful for very large sites because it combines crawling with log analysis.
• Ahrefs / Semrush — useful for backlink-driven broken URL discovery.

Export at least:

2. Add server log analysis

Your logs answer the question crawlers cannot:

“Are real users or search engines hitting this broken URL?”

Typical pipeline:

Web server logs → parser → warehouse → dashboards

Common tools:

• GoAccess — lightweight log analysis.
• Elastic Stack (Elasticsearch + Kibana) — flexible enterprise option.
• Splunk — large-scale observability.
• Google Cloud logging or Amazon Web Services CloudWatch if hosted in those environments.

Extract:

timestamp
request_url
status_code
referrer
user_agent
country
response_time
bytes_sent

Then filter:

status_code >= 400

3. Join crawl data + traffic data

The most useful table is something like:

A simple scoring model:

Priority Score =
(log traffic hits × 5)
+ (internal links × 3)
+ (external backlinks × 5)
+ (conversion value × 10)
+ (crawl depth penalty)

You can tune weights depending on whether SEO, UX, or revenue matters most.

4. Add analytics/conversion context

A 404 with 500 visits/day is not always the worst one.

Combine with analytics:

• Landing page sessions before the break
• Conversion rate of affected pages
• Revenue attributed to old URLs
• Search impressions/clicks

Example:

A deleted product page:

10,000 organic visits/month
+
$50k/month historical revenue
+
2,000 backlinks

= redirect immediately

5. Build a dashboard

Useful dashboards:

“Top broken URLs by user impact”

Columns:

• URL
• 404 requests last 30 days
• Top referrers
• Top user agents
• Lost conversions
• Recommended action

“Broken link clusters”

Group by:

• Same directory (/products/*)
• Same template
• Same migration event
• Same referrer

Often fixing one template fixes thousands of URLs.

6. Automation workflow

A common production setup:

Nightly crawl
     ↓
Export broken URLs
     ↓
Parse web logs
     ↓
Load into BigQuery / Snowflake / PostgreSQL
     ↓
Join on URL
     ↓
Dashboard in Looker Studio / Tableau / Grafana
     ↓
Create redirect tickets automatically

My recommended setups by site size

Small/medium site

• Screaming Frog crawl
• Google Search Console
• GA4
• GoAccess
• Spreadsheet or Looker Studio

Large ecommerce/content site

• JetOctopus or Screaming Frog API
• Raw server logs
• BigQuery/Snowflake
• Data warehouse join
• Tableau/Looker dashboard

Enterprise

• Log pipeline → Elasticsearch/Splunk
• Scheduled crawls
• Automated scoring
• Jira/Linear ticket creation

A particularly effective metric is “broken URL impact score”:

404 frequency × user traffic × SEO authority × conversion value

That moves the conversation from “fix all 404s” to “fix the 20 broken URLs causing 90% of the damage.”

For prioritizing broken links, the strongest workflows combine crawl discovery + server-log impact data + search/SEO signals. The key is not just “find 404s,” but answer: which broken URLs are hurting users, crawlers, revenue, or authority the most?

A practical stack looks like this:

1. Collect broken URL data

Crawl-based discovery

Use a crawler to build a complete broken-link inventory:

• Screaming Frog SEO Spider — excellent for enterprise crawls, exports, custom extraction, and combining crawl data with analytics.
• Sitebulb — strong visual audits and prioritization reports.
• Ahrefs / Semrush — useful if you care about backlinks and organic search impact.

Capture:

• Broken URL
• Source page linking to it
• HTTP status (404, 410, 500, redirect chains)
• Crawl depth
• Number of internal links pointing to it
• External backlinks (if available)

2. Mine server logs for real traffic

Your logs answer the most important question:

“Are real people or bots actually hitting this broken URL?”

Common sources:

• Nginx access logs
• Apache logs
• CDN logs (Cloudflare, Fastly, Akamai)
• Load balancer logs

Extract:

Typical pipeline:

Raw logs
   ↓
Log parser
   ↓
Warehouse (BigQuery/Snowflake/Postgres)
   ↓
Join with crawl exports
   ↓
Priority score
   ↓
Dashboard / tickets

Tools commonly used:

• Google BigQuery for large-scale log analysis
• Grafana for dashboards
• Elastic Stack (Elasticsearch + Kibana) for log search and analysis

3. Add SEO value signals

A broken URL with 5 visits/day but 200 backlinks may be more important than one with 100 visits/day.

Join crawl/log data with:

• Organic clicks
• Impressions
• Ranking keywords
• Backlinks
• Page authority
• Conversion data

Sources:

• Google Search Console
• Google Analytics
• Ahrefs / Semrush backlink exports

4. Build a prioritization score

A simple model:

Priority Score =
(log traffic × 5)
+ (organic clicks × 10)
+ (backlinks × 3)
+ (internal links × 2)
+ (conversion impact × 10)

Example:

5. Automate the workflow

A mature setup:

Daily

1. Export server/CDN logs
2. Parse 404/410/5xx responses
3. Aggregate by URL
4. Compare against yesterday

Weekly

1. Run crawler
2. Merge crawl + logs
3. Refresh priority score
4. Create Jira/Linear tickets

Example architecture:

Cloudflare/Nginx logs
        |
        v
      S3/GCS
        |
        v
    BigQuery/Snowflake
        |
        +---- Crawl exports
        |
        +---- Search Console
        |
        +---- Analytics
        |
        v
 Broken Link Dashboard
        |
        v
 Engineering Tickets

6. Useful dashboards

I would create views like:

“Top broken URLs by human traffic”

• 404 URL
• Requests last 30 days
• Unique visitors
• Referring pages

“SEO damage”

• Broken URL
• Backlinks
• Organic clicks lost
• Indexed status

“Broken internal architecture”

• Source page
• Broken destination
• Number of internal references
• Page importance

Recommended stack by company size

Small site

• Screaming Frog + Google Analytics + Search Console

Medium site

• Screaming Frog + BigQuery + Looker Studio dashboard

Large site

• CDN logs → data warehouse → crawler exports → automated scoring → Jira workflow

For most teams, the biggest improvement comes from joining server logs with crawl data. A crawler tells you what is broken; logs tell you what matters.

A strong workflow is to join three datasets at the URL level:

1. Server logs → how often URLs are actually requested, by humans/bots.
2. Crawler data → which URLs are broken, where they’re linked from, and crawl depth.
3. Analytics/Search Console → how much business or organic-search value the URL carries.

Then score each broken URL by impact, rather than simply sorting by number of 404s.

Recommended workflow

1. Normalize URLs first

• Lowercase hostnames
• Normalize trailing slashes, ports, fragments, etc.
• Separate query parameters where they represent distinct pages
• Resolve redirects/canonical URLs

2. Build a broken-URL table

The particularly useful signal is requests × importance of the referring page. A 404 requested 20,000 times by bots isn't necessarily more important than one receiving 500 visits from valuable users.

3. Segment traffic
Don't mix everything together. At minimum separate:

• Humans vs crawlers/bots
• Googlebot/other search crawlers
• Internal navigation
• External referrals
• Direct requests
• API/static-asset traffic

This prevents crawler noise from dominating your prioritization.

4. Score the URLs

A practical score could be:

Priority = traffic × referrer_value × SEO_value × brokenness

Where:

• traffic = human requests/visits
• referrer_value = number/importance of pages linking to it
• SEO_value = organic clicks/impressions or ranking signals
• brokenness = 404/410/5xx severity

Then bucket into fix now / fix soon / ignore or monitor.

Tools

For a relatively technical stack, I'd use:

• GoAccess, ClickHouse, BigQuery, Snowflake, or DuckDB for log analysis
• Screaming Frog, Sitebulb, or a custom crawler for link relationships
• Google Search Console for organic-search impact
• GA4 or your analytics platform for user traffic
• A SQL query/model to join everything on normalized URL

If your logs are large, ClickHouse or BigQuery + a crawler export is particularly effective. You can retain raw logs cheaply and build repeatable daily/weekly broken-link scores.

The key architectural idea is not to make the crawler the source of truth for traffic. Let logs tell you what is actually being requested, while the crawler tells you why the URL is broken and how deeply it's embedded in the site.

If your goal is to answer "Which broken links should we fix first?", the highest-value workflow is to combine crawl data (what's broken) with server logs (what users and bots actually request). That gives you prioritization based on impact rather than just error counts.

A practical scoring model looks like this:

A simple priority score might be:

Priority = (Human traffic × 5) + (Googlebot hits × 3) + (Backlinks × 4) + (Internal links × 2)

You can adjust the weights based on your goals.

Good tool combinations

Option 1: Screaming Frog + server logs (recommended)

This is one of the most common SEO workflows.

• Crawl the site with Screaming Frog.
• Import Apache/Nginx/CDN logs.
• Join on URL.
• Filter for:
  • 404s
  • 5xx errors
  • orphan URLs receiving traffic
  • URLs Googlebot keeps requesting

You'll quickly see situations like:

/products/widget-a
Status: 404

Users last 30 days:
2,150

Googlebot hits:
840

Internal links:
18

Priority:
Very High

Option 2: BigQuery + SQL

For large sites (millions of requests/day):

Logs
      \
       \
        ---> BigQuery
       /
Crawler

Create tables such as

• request logs
• crawl results
• internal links
• backlinks

Then use SQL to join them.

Example:

SELECT
url,
status,
COUNT(*) AS requests,
SUM(CASE WHEN user_agent LIKE '%Googlebot%' THEN 1 END) AS bot_hits
FROM logs
GROUP BY url, status

Then join against crawl exports.

This scales extremely well.

Option 3: ELK / OpenSearch

Ideal when you're already centralizing logs.

Pipeline:

Nginx
    ↓
Logstash
    ↓
OpenSearch
    ↓
Kibana dashboards

Useful dashboards include:

• Top requested 404s
• Googlebot 404s
• Mobile users hitting errors
• Broken redirects
• Referrers causing 404s

Option 4: Cloudflare Logs

If you're behind Cloudflare, export logs to:

• BigQuery
• R2
• S3
• Datadog

You get:

• cache status
• country
• bot detection
• response codes

This is useful for separating human traffic from automated scanners.

Option 5: Google Search Console + logs

This combination answers:

• Google is trying to crawl this URL.
• Is anyone else requesting it?
• Is it internally linked?

Those three questions usually determine whether to redirect, restore, or ignore a URL.

A workflow that scales well

1. Crawl the site.
2. Export all URLs with 404, 410, 500, and redirect chains.
3. Import the last 30–90 days of server logs.
4. Aggregate requests by URL.
5. Separate:
   • human traffic
   • Googlebot
   • Bingbot
6. Join crawl data to log data.
7. Calculate a priority score.
8. Fix the highest-scoring URLs first.

The resulting report might look like:

If you're comfortable with Python

A lightweight analytics stack works well:

• Pandas for log parsing and joins.
• DuckDB for querying multi-gigabyte log files without a database server.
• Polars for faster processing on very large datasets.
• Plotly or Streamlit for an interactive dashboard showing the highest-impact broken links.

This approach is often sufficient for tens of millions of log lines while remaining much simpler than maintaining a full ELK stack.

For most organizations, a strong progression is:

• Small sites: Screaming Frog + spreadsheet.
• Medium sites: Screaming Frog + DuckDB/Pandas.
• Large enterprise sites: BigQuery or OpenSearch with scheduled log ingestion and automated priority dashboards.

The key is to prioritize broken links by combining crawl findings with actual traffic and bot activity, so engineering effort goes toward the issues that affect users, search engines, or both.

The most effective setup is usually combining server logs with crawler data, because each tells you something different:

• Crawler data (from a site crawl) tells you what's broken and how the site's internal linking is structured.
• Server logs tell you what users and bots actually request and how often.
• Analytics (optional) tells you the business impact (sessions, conversions, revenue).

Instead of fixing every 404 equally, you can prioritize the ones that matter most.

A practical workflow looks like this:

Good tools

For crawling

• Screaming Frog (excellent for most sites)
• Sitebulb (strong visualization)
• JetOctopus (particularly good for large sites with log analysis)
• OnCrawl (enterprise SEO)

For log analysis

• GoAccess (fast and lightweight)
• ELK/OpenSearch (Elastic, Logstash, Kibana)
• Splunk
• BigQuery if logs are already in cloud storage

For very large sites (millions of URLs), BigQuery or Snowflake often becomes much easier than trying to analyze CSV files.

Priority scoring

Rather than sorting only by traffic, build a score such as:

Priority =
(log pageviews × 0.5)
+ (internal links × 0.2)
+ (Googlebot hits × 0.2)
+ (conversion value × 0.1)

Or something even simpler:

Priority =

Traffic × Severity

Example:

Notice that a URL with low human traffic but many Googlebot requests can still deserve attention because it may affect crawling efficiency or indicate stale links.

SQL example

If logs are stored in a warehouse:

SELECT
    url,
    COUNT(*) AS hits,
    COUNT(DISTINCT ip) AS users,
    SUM(CASE WHEN status = 404 THEN 1 ELSE 0 END) AS errors
FROM access_logs
GROUP BY url
ORDER BY hits DESC;

Then join that to your crawl export:

crawl_broken_links
LEFT JOIN
server_log_summary
ON crawl_broken_links.url = server_log_summary.url

Now every broken URL has traffic attached.

Python workflow

A common approach with pandas:

1. Load crawl CSV.
2. Load parsed server logs.
3. Aggregate hits by URL.
4. Merge on URL.
5. Sort by hits descending.
6. Export to Excel or build a dashboard.

Even a few dozen lines of Python can automate this process and refresh it regularly.

Recommended stacks by site size

• Small sites (<10k URLs): Screaming Frog + GoAccess + Excel or Google Sheets.
• Medium sites (10k–500k URLs): Screaming Frog + BigQuery + Looker Studio or Power BI.
• Enterprise sites (500k+ URLs): JetOctopus or OnCrawl + ELK/OpenSearch or Snowflake + Tableau/Power BI.

The key idea is to stop treating every broken link as equally important. By combining crawl results with server log traffic, you can rank issues by real user impact and search engine activity, ensuring the highest-value fixes are addressed first.

The most effective workflow is to combine crawler data (what's broken) with server logs (how often it's actually requested) and, if possible, analytics or Search Console data (whether people or search engines care about it). That lets you fix the highest-impact issues first instead of treating every 404 equally.

A practical prioritization pipeline looks like this:

Recommended workflow

1. Run a full crawl
   • Export all internal 404s.
   • Include:
     • source URL
     • destination URL
     • anchor text
     • crawl depth
     • inlinks

2. Parse server logs

Aggregate by requested URL:

URL
Requests
Unique visitors
Googlebot hits
Bingbot hits
404 count
Last seen

3. Join the datasets

Example:

4. Score each issue

A simple formula works well:

Priority Score =
(Human traffic × 5)
+ (Googlebot requests × 2)
+ (Internal inlinks × 1)
+ (Backlinks × 4)

Then sort descending.

Good tool combinations

Option 1: Screaming Frog + server logs (excellent value)

Screaming Frog can:

• Crawl the site
• Analyze log files
• Match crawl data with logs
• Show which broken URLs Googlebot actually visits

This is often enough for SEO teams.

Option 2: ELK Stack

If you have millions of requests/day:

Nginx
      ↓
Filebeat
      ↓
Logstash
      ↓
Elasticsearch
      ↓
Kibana

Benefits:

• dashboards
• trending 404s
• spikes after deployments
• top broken URLs
• Googlebot vs humans

Option 3: BigQuery

For larger organizations:

Logs
Analytics
Search Console
Crawler exports

        ↓

BigQuery

        ↓

SQL joins

        ↓

Looker Studio

This scales well for enterprise sites.

Metrics that matter most

Rather than simply counting 404s, prioritize based on:

• Human traffic to broken URL
• Googlebot crawl frequency
• Number of internal links pointing there
• External backlinks
• Conversion value of referring pages
• Whether the broken URL appears in navigation
• Whether it's linked from XML sitemaps

Example SQL

If logs are stored in SQL:

SELECT
    url,
    COUNT(*) AS requests,
    SUM(CASE WHEN status = 404 THEN 1 ELSE 0 END) AS not_found,
    COUNT(DISTINCT ip) AS unique_visitors
FROM access_logs
GROUP BY url
ORDER BY requests DESC;

Join that with your crawler export:

broken_urls.csv

on

URL

to produce a ranked report.

Automation

A common automated workflow is:

Nightly crawl
        ↓
Export broken links
        ↓
Import yesterday's logs
        ↓
Join datasets
        ↓
Calculate priority score
        ↓
Generate dashboard
        ↓
Create Jira tickets for highest-priority issues

This gives engineers a short, actionable list rather than hundreds of low-impact 404s.

My preferred stack

For most teams, I'd recommend:

• Crawler: Screaming Frog
• Log analysis: GoAccess for smaller sites, ELK or Datadog for larger ones
• Analytics: GA4
• Search data: Google Search Console
• Data processing: Python (Pandas) or SQL
• Dashboard: Looker Studio, Grafana, or Kibana

This approach lets you rank broken links by actual business impact—combining real user traffic, search engine crawl activity, and site structure—rather than fixing issues in arbitrary order.

The most effective approach is to combine server logs (actual user and bot traffic) with crawler data (all broken links), then score each issue based on business impact instead of fixing every 404 equally.

A practical workflow looks like this:

Recommended tools

For crawling

• Screaming Frog SEO Spider – Industry standard. Can import log files and combine crawl data.
• Sitebulb – Excellent visual reports and prioritization.
• Ahrefs Site Audit or Semrush Site Audit – Good for ongoing monitoring, although they don't have the depth of raw log analysis.

For log analysis

• Screaming Frog Log File Analyser – Easy way to merge crawler and server logs.
• Splunk – Excellent if you already have enterprise log management.
• Elastic Stack (ELK) – Great for large sites with millions of requests.
• Cloudflare Analytics or CDN logs if your site sits behind a CDN.

For joining everything

• Python (Pandas)
• SQL (BigQuery, PostgreSQL, Snowflake)
• Power BI or Tableau
• Looker Studio (for simpler dashboards)

A prioritization model

Instead of asking:

Which links are broken?

Ask:

Which broken links matter most?

Create a score like:

Priority Score =
(User Visits × 0.5)
+
(Googlebot Hits × 0.3)
+
(Backlinks × 0.2)

Or more advanced:

Priority =
Sessions
× Conversion Rate
× Revenue Per Visit
× Severity

Example:

Useful joins

You'll typically merge:

Crawler

Source URL
Destination URL
Status Code
Anchor Text

with

Server logs

Requested URL
Hits
Unique Users
Googlebot Requests
Status Code

with

Analytics

Landing Page
Sessions
Conversions
Revenue

This gives one table such as:

Broken URL
404s
Internal Links
User Visits
Bot Visits
Revenue
Recommendation

Automation workflow

Many SEO teams automate this:

Nightly Crawl
        ↓
Export broken links
        ↓
Import server logs
        ↓
Join in SQL/Python
        ↓
Calculate priority score
        ↓
Push dashboard
        ↓
Open Jira tickets automatically

If your site is large (100k+ URLs)

A scalable architecture is:

• Server logs → BigQuery or Snowflake
• Daily crawler exports → Cloud storage
• Python or dbt transformations
• BI dashboard (Looker, Power BI, Tableau)
• Scheduled alerts for new high-impact 404s

This avoids manually reviewing thousands of crawl issues.

What I'd prioritize first

If the goal is to maximize SEO and user experience with the least effort, I'd rank issues roughly like this:

1. Broken URLs with high user traffic.
2. Broken URLs frequently requested by search engine crawlers.
3. Broken URLs with many internal links pointing to them.
4. Broken URLs with valuable backlinks.
5. Broken URLs on key templates (navigation, category, product, checkout, etc.).
6. Low-traffic legacy pages and orphaned content.

That way, engineering effort is focused on fixes that improve both user experience and search engine crawling, rather than spending time on rarely visited broken links.

For prioritizing broken links, the strongest workflow combines crawl data (what is broken) with server logs/analytics (what matters). A good setup looks like this:

1. Crawl the site to build the broken-link inventory

Use a crawler to identify:

• Internal links returning 404/410/5xx
• Redirect chains
• Broken external links
• Pages with high numbers of broken references
• Orphan pages (URLs receiving traffic but not linked internally)

Common tools:

• Screaming Frog SEO Spider — very popular for enterprise SEO crawls; exports broken links, inlinks, response codes, and crawl paths.
• Sitebulb — strong visualization and prioritization reports.
• Ahrefs — useful when combining crawl findings with backlink impact.
• Semrush — combines technical audits with SEO metrics.

Typical crawl export fields:

2. Parse server logs for real-world impact

Your logs tell you which broken URLs are actually being requested.

Useful fields:

• Requested URL
• Status code
• Timestamp
• Referrer
• User agent
• Request count
• Response time
• Bot vs human traffic

Example query logic:

SELECT
  request_path,
  COUNT(*) AS hits,
  COUNT(DISTINCT ip) AS visitors
FROM access_logs
WHERE status_code IN (404, 410)
GROUP BY request_path
ORDER BY hits DESC;

This gives you:

"Which broken URLs are hurting users most?"

rather than:

"Which URLs are technically broken?"

Tools commonly used:

• Splunk — powerful for large log volumes.
• Elastic Stack (Elasticsearch + Logstash + Kibana) — flexible open-source log analysis.
• Datadog — good for teams already monitoring infrastructure.
• Google BigQuery — excellent for storing and analyzing large server logs.

3. Join crawl data + traffic data

The highest-value table is usually something like:

A simple scoring model:

Priority Score =
(log requests × 0.4)
+ (unique users × 0.3)
+ (internal links × 0.2)
+ (external backlinks × 0.1)

Adjust weights depending on whether your goal is:

• UX: weight human traffic heavily
• SEO: weight backlinks + internal links
• Conversion: weight URLs near revenue pages

4. Add analytics data

Combine with:

• Landing page sessions
• Conversion rate
• Revenue attribution
• Search impressions

For example:

A 404 page with 500 visits/month might matter more than one with 50,000 bot hits if those 500 visitors were trying to reach checkout pages.

Useful sources:

• Google Analytics
• Google Search Console

5. Automate the pipeline

A mature workflow:

Server logs
     |
     v
Log warehouse (BigQuery / Elasticsearch)
     |
     +----------------+
                      |
Crawler export ------>|
                      v
              Broken URL scoring
                      |
                      v
          Jira / spreadsheet / dashboard

Example stack:

Small/medium site

• Screaming Frog crawl
• Google Analytics export
• Google Sheets scoring model

Large site

• Cloudflare/CDN logs
• BigQuery
• Scheduled crawls
• SQL scoring job
• Looker Studio dashboard

Enterprise SEO

• Elastic/Splunk logs
• Screaming Frog API or custom crawler
• SEO platform data
• Ticket automation

6. Prioritization rules that work well

Fix first:

1. 404s with high human traffic
2. 404s linked from high-value pages
3. URLs with backlinks
4. Broken conversion funnel pages
5. Crawl budget waste from bots hitting dead URLs

Usually ignore:

• Random bot scans
• Missing tracking pixels
• Old image URLs with no traffic
• Typos with no inbound links

A practical first implementation is: Screaming Frog export → server log aggregation → join on URL → score → send top 100 broken URLs to engineering/content teams. This gives most of the value without building a full SEO data platform.