Python Webpage Auditor – Fetch, Store, Analyze & Export

Page Analysis (SQLite): Practical SEO Checks You Can Run From Your Database

• Store your crawl in pages (raw HTML) and summaries in page_summary.
• Keep your Python script the same—only swap the query line to answer a specific SEO question.
• Workflow: fetch → summarize → analyze → export. Fix issues, recrawl, re-run.

1) Missing or Too-Short <title> (active example)

• Why: Titles <~20 chars or missing underperform on CTR and clarity.
• Query (on pages):

SELECT url
FROM pages
WHERE html NOT LIKE '%<title%'
   OR LENGTH(substr(html, instr(html, '<title>')+7,
                    instr(html, '</title>')-instr(html, '<title>')-7)) < 20;

• Fix: Write unique, descriptive titles (~50–60 chars) aligned to intent/keyword.

2) Canonical Mismatch vs. Actual URL

• Why: Wrong canonicals split signals and cause duplicate confusion.
• Query (on page_summary):

SELECT url, canonical_url
FROM page_summary
WHERE canonical_url IS NOT NULL
  AND canonical_url != url;

• Fix: Point rel="canonical" to the preferred, crawlable URL.

3) GA4 / Analytics Not Present

• Why: Missing analytics = lost engagement and conversion data.
• Query (on pages):

SELECT url
FROM pages
WHERE html NOT LIKE '%G-%'      -- quick GA4 id presence check
   OR html NOT LIKE '%gtag(';

• Fix: Add GA4 snippet in the base template; validate on key templates.

4) Extremely Short Pages (Possible Thin Content)

• Why: Very small HTML often indicates placeholders or thin pages.
• Query (on pages):

SELECT url, LENGTH(html) AS bytes
FROM pages
ORDER BY LENGTH(html)
LIMIT 20;

• Fix: Enrich or consolidate; align with search intent and internal links.

5) Missing Meta Description

• Why: Not a ranking factor, but boosts CTR and snippet quality.
• Query (on pages):

SELECT url
FROM pages
WHERE html NOT LIKE '%<meta name="description"%';

• Fix: Add unique, compelling 120–160 char descriptions summarizing value.

6) Duplicate Titles Across Pages

• Why: Duplicate signals dilute relevance and reduce discoverability.
• Query (on page_summary):

SELECT title, COUNT(*) AS cnt, GROUP_CONCAT(url, ' | ') AS urls
FROM page_summary
WHERE title IS NOT NULL AND title != ''
GROUP BY title
HAVING COUNT(*) > 1;

• Fix: Differentiate titles to reflect each page’s unique topic/intent.

7) Empty or Duplicate H1s

• Why: H1 conveys primary on-page focus; keep one clear H1 per page.
• Queries (on page_summary):

-- Empty H1s
SELECT url
FROM page_summary
WHERE h1 IS NULL OR TRIM(h1) = '';

-- Duplicate H1s
SELECT h1, COUNT(*) AS cnt, GROUP_CONCAT(url, ' | ') AS urls
FROM page_summary
WHERE h1 IS NOT NULL AND TRIM(h1) != ''
GROUP BY h1
HAVING COUNT(*) > 1;

• Fix: Ensure one descriptive H1 aligned with title and query intent.

8) Broken Internal Links (If Captured)

• Why: Broken links waste crawl budget and harm UX.
• Example shape (on page_summary):

SELECT url, broken_link
FROM page_summary
WHERE broken_link IS NOT NULL;

• Fix: Update or remove; add redirects where appropriate.

How to Use This Section

• Swap the query in your existing analysis script for any check above.
• Inspect the output URLs and prioritize high-impact templates and money pages.
• Fix issues, recrawl, and re-run the same query to ensure counts drop to zero.
• Export from page_summary to Excel/CSV for progress tracking.