ChatGPT answers, unedited

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.