Query your site crawl with SQL

Download a scan, open it, and look around. Nothing is compressed or encoded.

sqlite3 scan.db
sqlite> .mode box
sqlite> .tables
advice   images   link_graph_nodes  pages      scan             urls
blobs    links    notifications     resources  security_review  sitemap_entries

Datasette is worth the two minutes if you would rather click than type, and it gives you a browsable, linkable copy of the whole crawl:

pipx install datasette
datasette scan.db

1. The scan itself. One row, and the first thing to check before trusting anything else.

SELECT domain, status, pages_fetched, assets_fetched, failed, skipped,
       total_bytes, limit_dropped, requested_at, completed_at
FROM scan;

2. Response codes at a glance. Where the 404s and redirects are hiding.

SELECT kind, status_code, COUNT(*) AS urls
FROM urls
GROUP BY kind, status_code
ORDER BY kind, urls DESC;

3. Anything that failed to fetch. Timeouts, refused connections, certificate problems.

SELECT url, error, attempts, fetched_at
FROM urls
WHERE state = 'failed'
ORDER BY url;

4. How deep the site goes. Pages more than three clicks from the home page get crawled less often.

SELECT depth, COUNT(*) AS pages
FROM urls
WHERE kind = 'page' AND state = 'fetched'
GROUP BY depth
ORDER BY depth;

5. Pages with no title.

SELECT url FROM pages
WHERE title IS NULL OR TRIM(title) = ''
ORDER BY url;

6. Duplicate titles, worst first. Usually a template that forgot a variable.

SELECT title, COUNT(*) AS pages, GROUP_CONCAT(url, char(10)) AS urls
FROM pages
WHERE title IS NOT NULL AND TRIM(title) <> ''
GROUP BY title HAVING COUNT(*) > 1
ORDER BY pages DESC;

7. Titles outside the usable range. Characters, not pixels, but close enough to sort by.

SELECT url, title, LENGTH(title) AS chars
FROM pages
WHERE title IS NOT NULL AND (LENGTH(title) < 30 OR LENGTH(title) > 60)
ORDER BY chars DESC;

8. Missing or duplicated meta descriptions.

SELECT COALESCE(NULLIF(TRIM(meta_description), ''), '(missing)') AS description,
       COUNT(*) AS pages
FROM pages
GROUP BY description
HAVING COUNT(*) > 1 OR description = '(missing)'
ORDER BY pages DESC;

9. Heading problems. No H1 at all, or several competing.

SELECT url, h1_count, h2_count, word_count, h1_text
FROM pages
WHERE h1_count <> 1
ORDER BY h1_count DESC, word_count DESC;

10. Every broken internal link, with the page that points at it. The single most useful query here.

SELECT l.from_url AS linking_page, l.to_url AS broken_url,
       u.status_code, u.error, l.text AS anchor
FROM links l
JOIN urls u ON u.url = l.to_url
WHERE l.internal = 1
  AND ((u.status_code >= 400 AND u.status_code != 429) OR u.state = 'failed')
ORDER BY l.to_url;

11. Redirect chains. Two hops joined together; add another join for three.

SELECT a.url AS first_hop, a.status_code AS first_status,
       b.url AS second_hop, b.status_code AS second_status,
       b.redirect_to AS ends_at
FROM urls a
JOIN urls b ON b.url = a.redirect_to
WHERE a.redirect_to IS NOT NULL AND b.redirect_to IS NOT NULL;

12. Orphan pages. Fetched, but nothing internal links to them.

SELECT p.url, p.title, p.word_count
FROM pages p
LEFT JOIN links l ON l.to_url = p.url AND l.internal = 1
WHERE l.to_url IS NULL
ORDER BY p.word_count DESC;

13. Which pages hold the internal links. Move a few from the top of this list to the pages you want ranking.

SELECT url, inbound, outbound, depth
FROM link_graph_nodes
ORDER BY inbound DESC
LIMIT 25;

14. The heaviest files, and how many pages carry each.

SELECT u.url, u.content_length AS bytes, u.content_type,
       COUNT(DISTINCT r.page_url) AS used_on_pages
FROM urls u
JOIN resources r ON r.url = u.url
WHERE u.kind = 'asset' AND u.content_length IS NOT NULL
GROUP BY u.url, u.content_length, u.content_type
ORDER BY bytes DESC
LIMIT 20;

15. Total asset weight per page. What a visitor downloads besides the HTML.

SELECT r.page_url, COUNT(*) AS assets, SUM(u.content_length) AS asset_bytes
FROM resources r
JOIN urls u ON u.url = r.url
WHERE u.content_length IS NOT NULL
GROUP BY r.page_url
ORDER BY asset_bytes DESC
LIMIT 20;

16. The slowest responses the crawler saw. Not a lab measurement, and it is real traffic to your server.

SELECT url, duration_ms, content_length, content_type
FROM urls
WHERE state = 'fetched' AND duration_ms IS NOT NULL
ORDER BY duration_ms DESC
LIMIT 20;

17. Images with no alt text, grouped by the page they are on.

SELECT page_url, COUNT(*) AS images_without_alt
FROM images
WHERE alt IS NULL OR TRIM(alt) = ''
GROUP BY page_url
ORDER BY images_without_alt DESC;

18. Pages telling search engines to stay away. Including the ones canonical to somewhere else.

SELECT url, robots_meta, canonical
FROM pages
WHERE (robots_meta IS NOT NULL AND robots_meta LIKE '%noindex%')
   OR (canonical IS NOT NULL AND canonical <> url);

19. Sitemap against reality, both ways.

-- crawled, indexable, and not in the sitemap
SELECT u.url FROM urls u
LEFT JOIN sitemap_entries s ON s.url = u.url
WHERE u.kind = 'page' AND u.state = 'fetched' AND u.status_code = 200
  AND s.url IS NULL;

-- in the sitemap, never successfully crawled
SELECT s.url FROM sitemap_entries s
LEFT JOIN urls u ON u.url = s.url AND u.state = 'fetched'
WHERE u.url IS NULL;

20. The page exactly as it was served. The reason the whole crawl is worth keeping.

SELECT CAST(b.body AS TEXT) AS html
FROM urls u
JOIN blobs b ON b.hash = u.body_hash
WHERE u.url = 'https://example.com/pricing';

And because bodies are content-addressed, byte-identical pages share a hash, which makes duplicate content a group-by:

SELECT body_hash, COUNT(*) AS identical_pages,
       GROUP_CONCAT(url, char(10)) AS urls
FROM urls
WHERE kind = 'page' AND state = 'fetched' AND body_hash IS NOT NULL
GROUP BY body_hash HAVING COUNT(*) > 1
ORDER BY identical_pages DESC;

Two scans a month apart are two files, so the diff is an attached database rather than two dashboards side by side:

sqlite3 september.db
sqlite> ATTACH 'august.db' AS august;
sqlite> SELECT n.url, o.status_code AS was, n.status_code AS now
   ...> FROM urls n JOIN august.urls o ON o.url = n.url
   ...> WHERE n.status_code IS NOT o.status_code;

That is the query that finds what a redesign quietly broke, and there is no version of it that works on a crawl you cannot download.