🐸 FrogPipe — Autonomous Screaming Frog SEO Pipeline

The Production Headless Crawler, Zero-Loss Dataset Vault & Executive Google Sheets Engine

📑 Table of Contents

⚡ Full Setup & Installation Guide
🏗️ Architectural Pipeline & Mechanics
📊 The 11 Core SEO Datasets + Competitors Matrix
🎨 Color Circle Theory Design System
💻 Command Line Interface (CLI) Reference
📁 Vault Directory Architecture
🛡️ Pre-Flight Diagnostics & Disk Space Guard
🔒 License & Intellectual Property

⚡ Full Setup & Installation Guide

1. System Prerequisites

Before running FrogPipe, ensure your workstation meets the following requirements:

Operating System: Windows 10/11, macOS, or Linux (hardened for Windows PowerShell / Terminal UTF-8).
Python: Version 3.10 or higher (python.org).
Screaming Frog SEO Spider: Installed on your system (screamingfrog.co.uk). Free version supports up to 500 URLs; Licensed version supports unlimited crawls.
Disk Space: At least 10 GB of free space on your system drive (C:\). Screaming Frog has an internal safety stop that aborts crawls if free space drops below 5.0 GB.
Google Account: Required for hosting the live executive Google Sheets via Google Apps Script.

2. Step 1: Clone Repository

Clone the FrogPipe source code into your desired directory:

git clone https://github.com/expgg/frogpipe-source.git
cd frogpipe-source

3. Step 2: Install Python Dependencies

FrogPipe utilizes rich for cyberpunk terminal visuals, pandas and openpyxl for high-fidelity Excel generation, and requests for Google Apps Script webhook streaming.

Install all required packages:

pip install -r requirements.txt

(Recommended: Use a virtual environment via python -m venv venv and activate it before installing).

4. Step 3: Link Screaming Frog CLI

FrogPipe requires direct access to ScreamingFrogSEOSpiderCli.exe.

The engine automatically scans the following standard installation paths:

X:\APPS\Screaming Frog SEO Spider\ScreamingFrogSEOSpiderCli.exe
C:\Program Files\Screaming Frog SEO Spider\ScreamingFrogSEOSpiderCli.exe
C:\Program Files (x86)\Screaming Frog SEO Spider\ScreamingFrogSEOSpiderCli.exe
D:\APPS\Screaming Frog SEO Spider\ScreamingFrogSEOSpiderCli.exe

If your installation is in a custom folder, you can either:

Let FrogPipe prompt you on first run and auto-save the path to config.json.
Or manually set it in config.json as shown in Step 5.

5. Step 4: Deploy Google Apps Script Webhook

FrogPipe can automatically generate a formatted, color-coded Google Sheet for every crawl and provide you with a live shareable URL for clients.

Follow these steps to deploy the backend engine:

Open Google Apps Script Dashboard.
Click New Project (top-left button).
Rename the project to FrogPipe Sheets Engine.
In the editor, open the default Code.gs file and delete any placeholder code.
Open templates/code.gs in this repository, copy its entire contents, and paste them into the Google Apps Script editor.
Click the Save icon (💾) or press Ctrl + S.
Click the blue Deploy button (top-right) and select New deployment.
Click the gear icon (⚙️) next to Select type and choose Web app.
Configure the deployment settings:
- Description: FrogPipe Live Sync v4.5
- Execute as: Me (your_email@gmail.com) (CRITICAL: Must be "Me")
- Who has access: Anyone (CRITICAL: Allows FrogPipe to stream datasets securely)
Click Deploy.
Grant Google permissions if prompted (Click Advanced ➔ Go to FrogPipe Sheets Engine (unsafe) ➔ Allow).
Copy the generated Web App URL (it ends with /exec).

6. Step 5: Configure Persistent Settings

Create or open config.json in the root folder of the project. Paste your Screaming Frog CLI path and the Google Apps Script Webhook URL:

{
  "screaming_frog_cli": "C:\\Program Files\\Screaming Frog SEO Spider\\ScreamingFrogSEOSpiderCli.exe",
  "sheet_webhook_url": "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec"
}

(Note: If you don't configure sheet_webhook_url, FrogPipe will ask you interactively in the terminal or allow you to generate offline Excel files only).

7. Step 6: Execute Your First Audit

A. Interactive Mode

Simply run the main command:

python frogpipe.py

You will be greeted with the terminal banner:

╔════════════════════════════════════ ⚡ SEO SPIDER AUTOMATION TOOL ⚡ ════════════════════════════════════╗
║    ______ _____   ____   _____ _____ _____ _____  ______                                            ║
║   |  ____|  __ \ / __ \ / ____|  __ \_   _|  __ \|  ____|                                           ║
║   | |__  | |__) | |  | | |  __| |__) || | | |__) | |__                                              ║
║   |  __| |  _  /| |  | | | |_ |  ___/ | | |  ___/|  __|                                             ║
║   | |    | | \ \| |__| | |__| | |    _| |_| |    | |____                                            ║
║   |_|    |_|  \_|\____/ \_____|_|   |_____|_|    |______|                                           ║
║                                                                                                     ║
║  ● ENGINE: Headless Spider   ● DATASETS: 11 Tabs + Matrix   ● FIDELITY: 100% Unpruned   ● LIVE SYNC ║
╚════════════════════════════════════════════════════════════════════════════════ v2.5.0 Production Engine ═╝

Enter your target website URL (e.g. https://r1fat.pages.dev or http://drstangale.com) and hit Enter.

B. Direct Command Line Mode

Run fully headless crawls by passing flags directly:

# Basic Audit with live Google Sheets Sync
python frogpipe.py --url https://r1fat.pages.dev

# Competitive Benchmark Audit (Compares client with rivals + Moz DA/PA metrics)
python frogpipe.py --url https://r1fat.pages.dev -c rival1.com,rival2.com

# Batch / Lead Gen Mode (Audits an entire list of prospect websites sequentially)
python frogpipe.py --file leads.txt

# Offline Mode (Generates CSVs + Excel workbook, skips Google Sheets)
python frogpipe.py --url https://r1fat.pages.dev --no-sheet

# Custom Output Directory
python frogpipe.py --url https://r1fat.pages.dev --output ./client_reports/r1fat

# Verbose Mode (Displays raw Screaming Frog CLI logs)
python frogpipe.py --url https://r1fat.pages.dev --verbose

🏗️ Architectural Pipeline & Mechanics

FrogPipe executes an autonomous 4-phase sequential workflow:

[TARGET DOMAIN]
       │
       ▼
 ⚡ PHASE 1: Headless Spider Engine
    - Direct Screaming Frog CLI execution with background trace monitoring
    - Real-time progress bar (Crawled URLs, Queue, Speed, Memory, Disk)
    - Pre-flight disk space health guard & automated instance cleanup
       │
       ▼
 ⚡ PHASE 2: Dataset Normalizer & Vault
    - Normalizes raw crawl exports into the 11 standard SEO audit artifacts
    - 100% Data Fidelity: Zero columns pruned, zero rows dropped
    - Packages all files inside audited_sites/SEO_<clean_domain>/
    - Generates audit_manifest.json with integrity hashes & metrics
       │
       ▼
 ⚡ PHASE 3: Offline Master Excel Workbook (.xlsx)
    - Compiles all 11 tabs + Competitors Analysis Matrix into an offline workbook
    - Color Circle Theory palette with chromatic headers & harmonic lane tinting
    - Cell triage formatting (Status codes, Priorities, Length alerts)
       │
       ▼
 ⚡ PHASE 4: Google Sheets Live Streaming Sync
    - Connects to Google Apps Script Webhook (/exec)
    - Initializes spreadsheet and streams tabs sequentially with live yield logs
    - Applies obsidian master bars, sticky headers, and 2D matrix styling
       │
       ▼
 [DELIVERABLES COMPLETE]
  📁 Local Audit Vault: audited_sites/SEO_<domain>/
  📊 Master Excel Workbook: SEO_Audit_Report_<domain>.xlsx
  🚀 Live Executive Google Sheet: https://docs.google.com/spreadsheets/d/...

📊 The 11 Core SEO Datasets + Competitors Matrix

Every audit produces 11 individual CSV artifacts, an Offline Excel Workbook, and a Google Spreadsheet containing all tabs:

#	Artifact CSV File	Sheet Tab Name	Raw Spider Source	Description & Scope
00	Synthesized Dynamic	Executive Summary	Diagnostic Engine	Master Executive Cover Tab: Overall SEO Health Score (0–100, Grade A+/A/B/C), clean page ratios, broken link counts, metadata health, and status triage.
01	Synthesized Matrix	Competitors Analysis	Moz API + Recon	Executive Benchmark Matrix: Domain Authority (DA), Page Authority (PA), and Spam Score comparison between Client and up to 5 rivals.
02	`1_Issues.csv`	Issues	`issues_overview_report.csv`	Complete executive breakdown of all SEO errors, warnings, and opportunities with severity triage.
03	`2_H1.csv`	H1	`h1_all.csv`	All primary H1 heading tags, multiple H1 flags, character lengths, and missing alerts.
04	`3_H2.csv`	H2	`h2_all.csv`	Secondary H2 heading structure, subheading frequency, and content hierarchy.
05	`4_Meta_Description.csv`	Meta Description	`meta_description_all.csv`	Page meta descriptions, character lengths, pixel widths, and SERP snippet previews.
06	`5_Meta_Tags.csv`	Meta Tags	`directives_all.csv`	Meta robots directives, noindex/nofollow flags, canonical tags, and keywords.
07	`6_Content.csv`	Content	`content_all.csv`	Word counts, text-to-HTML ratios, content depth, readability, and thin content warnings.
08	`7_Page_Titles.csv`	ALL page Titles	`page_titles_all.csv`	Complete page title tags, character length optimization (30-60 chars), and pixel widths.
09	`8_URLs.csv`	URLs	`internal_all.csv`	Full index of all crawlable internal URLs, status codes, response times, and hash values.
10	`9_Images.csv`	Images	`images_all.csv`	Comprehensive image audit, missing alt text tags, image dimensions, and payload sizes.
11	`10_Search_Console_Status.csv`	Search Console Status	`search_console_all.csv` / `response_codes_all.csv`	HTTP response codes verification, crawl errors, redirects, and indexability signals.
12	`11_Canonicals.csv`	Canonicals	`canonicals_all.csv`	Canonical link tags, self-canonicalization verification, and canonical mismatch issues.

🎨 Color Circle Theory Design System

Both the Google Sheets Engine (templates/code.gs) and the Excel Master Builder (modules/google_sync.py) implement the Color Circle Theory (Harmonic Sextant Palette). Rather than plain tables or unstyled monochrome text, every column is assigned to a specific chromatic family based on its semantic identity.

The 8 Chromatic Color Families

Addressing & URLs (Navy Sapphire)
- Header: Deep Sapphire Navy (#1E3A8A) | Lane Tint: Cool Slate Ice (#F0F4F8)
- Applied to: Address, URL, Link, Destination, Canonical, Source.
Health & Protocols (Forest Emerald)
- Header: Deep Forest Emerald (#064E3B) | Lane Tint: Soft Mint (#F0FDF4)
- Applied to: Status Code, Response Code, Protocol, Redirect URI.
Editorial & Headings (Ocean Teal)
- Header: Deep Ocean Teal (#0F766E) | Lane Tint: Soft Oceanic (#F0FDFA)
- Applied to: Title, H1, H2, Heading, Snippet.
Meta Descriptions & SERP (Royal Violet)
- Header: Deep Royal Violet (#581C87) | Lane Tint: Soft Violet (#FAF5FF)
- Applied to: Meta Description, Description Length, Snippet Preview.
Directives & Indexing (Twilight Indigo)
- Header: Deep Twilight Indigo (#312E81) | Lane Tint: Soft Indigo (#EEF2FF)
- Applied to: Indexability, Meta Robots, Directives, Canonical Tag.
Quantitative Metrics & Depth (Warm Bronze Amber)
- Header: Deep Warm Bronze (#78350F) | Lane Tint: Soft Parchment (#FFFBEB)
- Applied to: Length, Word Count, Width, Ratio, Size, Occurrences.
Diagnostic Issues & Severity (Ruby Crimson)
- Header: Deep Ruby Crimson (#881337) | Lane Tint: Soft Rose (#FFF1F2)
- Applied to: Issue Name, Priority, Severity, Error, How to Fix.
Media & Assets (Raspberry Plum)
- Header: Deep Raspberry Plum (#701A75) | Lane Tint: Soft Blush Plum (#FDF2F8)
- Applied to: Image, Alt Text, Picture, Media Dimensions.

Dynamic 2D Badge & Triage Flags

Cells are conditionally styled based on industry-standard SEO benchmarks:

HTTP Status Codes:
- 200 / 201: Mint Green badge (#D1FAE5 text: #065F46).
- 3xx (Redirects): Sky Blue badge (#DBEAFE text: #1E40AF).
- 4xx / 5xx (Errors): Coral Red badge (#FEE2E2 text: #991B1B).
Issues Priority:
- High / Critical: Bold Crimson alert badge.
- Medium / Warning: Amber alert badge.
- Low / Opportunity: Sky Blue badge.
Title Length Optimization:
- 30 - 60 chars: Optimal green badge.
- > 60 chars: Amber badge (Warning: Truncated on Google SERP).
- < 30 chars: Amber badge (Warning: Sub-optimal title depth).
Content Word Count:
- < 300 words: Red badge (Thin content risk).
- > 800 words: Green badge (Comprehensive in-depth content).
Missing Elements:
- Missing H1, H2, or Image Alt Text: Bold alert rose (#FFF1F2 text: #BE123C).

Competitor Intelligence Benchmark Matrix

The Competitors Analysis tab is formatted as an executive dashboard:

Row 1: Dark Warm Obsidian Master Banner (#1C1917 with Gold #F59E0B text).
Row 2: Chromatic Jewel-Tone Column Headers covering:
- Website, DA (Domain Authority), PA (Page Authority), SS (Spam Score), Ranking Keywords (Moz), DR (Domain Rating), Backlinks, Linking Websites, Website Traffic, Traffic Values.
Row 3 (Client Domain): Highlighted in radiant Mint Green VIP Glow (#D1FAE5) with a 2px solid emerald border.
Rows 4 to 8 (Rivals): Highlighted in soft Crimson Benchmark Red (#FEE2E2).

💻 Command Line Interface (CLI) Reference

usage: frogpipe.py [-h] [-u URL] [-o OUTPUT] [-w WEBHOOK] [--no-sheet] [-v] [--keep-temp] [-l LIMIT]

FrogPipe — Autonomous Screaming Frog SEO Audit Engine

options:
  -h, --help            Show this help message and exit.
  -u, --url URL         Target website URL or domain (e.g. r1fat.pages.dev or drstangale.com).
  -o, --output OUTPUT   Custom output directory path (defaults to audited_sites/SEO_<domain>).
  -w, --webhook WEBHOOK Google Apps Script Webhook URL for live Google Sheets publishing.
  --no-sheet            Skip Google Sheets sync (only package CSVs and offline Excel workbook).
  -v, --verbose         Display raw verbose console output from Screaming Frog CLI.
  --keep-temp           Retain raw temporary crawl dumps instead of deleting them.
  -l, --limit LIMIT     Optional limit parameter (note: CLI limits are governed by spider profile).

📁 Vault Directory Architecture

All audit outputs are isolated inside the project root under audited_sites/:

FrogPipe/
├── audited_sites/
│   └── SEO_pranfoods_net/
│       ├── 1_Issues.csv
│       ├── 2_H1.csv
│       ├── 3_H2.csv
│       ├── 4_Meta_Description.csv
│       ├── 5_Meta_Tags.csv
│       ├── 6_Content.csv
│       ├── 7_Page_Titles.csv
│       ├── 8_URLs.csv
│       ├── 9_Images.csv
│       ├── 10_Search_Console_Status.csv
│       ├── 11_Canonicals.csv
│       ├── SEO_Audit_Report_pranfoods_net.xlsx   <-- Multi-Tab Styled Excel
│       └── audit_manifest.json                  <-- Audit Metadata & Metrics
├── modules/
│   ├── config.py                                <-- Path & setting resolution
│   ├── crawler.py                               <-- Headless CLI driver & trace watcher
│   ├── google_sync.py                           <-- Excel builder & Webhook streamer
│   ├── organizer.py                             <-- Dataset normalizer & packager
│   └── ui.py                                    <-- Cyberpunk Rich terminal visualizer
├── templates/
│   └── code.gs                                  <-- Google Apps Script Live Engine v4.5
├── frogpipe.py                                  <-- Main CLI entry point
├── config.json                                  <-- Saved CLI paths & Webhook URLs
└── requirements.txt                             <-- Python dependencies

🛡️ Pre-Flight Diagnostics & Disk Space Guard

Screaming Frog SEO Spider stores its database instances on the system drive (~/.ScreamingFrogSEOSpider/ProjectInstanceData/). If available disk space falls below 5.0 GB, Screaming Frog aborts immediately.

To prevent crawl failures:

Automated Disk Check: Before initiating any crawl, FrogPipe inspects the system drive.
Orphaned Cache Sweep: If free disk space is under 6.0 GB, FrogPipe automatically detects and purges abandoned Derby database folders from interrupted crawls.
Trace Log Error Extraction: If Screaming Frog ever exits unexpectedly, FrogPipe reads the tail of trace.txt and displays the exact root cause in plain English directly in the console.

🔒 License & Intellectual Property

This software is strictly proprietary and confidential. No part of this codebase may be copied, modified, redistributed, forked, sublicensed, or used for commercial purposes without explicit, written permission executed directly by the author.

For enterprise licensing, architectural inquiries, or custom tool development:

🌐 Portfolio & Contact: r1fat.pages.dev
💼 Author: Al Hossain Rifat
⚡ Version: FrogPipe Production Engine v2.5.0

Name		Name	Last commit message	Last commit date
assets		assets
configs		configs
modules		modules
templates		templates
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
SEO Audit & Competitors Analysis .docx		SEO Audit & Competitors Analysis .docx
config.example.json		config.example.json
frogpipe.py		frogpipe.py
leads.example.txt		leads.example.txt
requirements.txt		requirements.txt

Navigation Menu

Repository navigation

About

Repository files navigation

🐸 FrogPipe — Autonomous Screaming Frog SEO Pipeline

The Production Headless Crawler, Zero-Loss Dataset Vault & Executive Google Sheets Engine

📑 Table of Contents

⚡ Full Setup & Installation Guide

1. System Prerequisites

2. Step 1: Clone Repository

3. Step 2: Install Python Dependencies

4. Step 3: Link Screaming Frog CLI

5. Step 4: Deploy Google Apps Script Webhook

6. Step 5: Configure Persistent Settings

7. Step 6: Execute Your First Audit

A. Interactive Mode

B. Direct Command Line Mode

🏗️ Architectural Pipeline & Mechanics

📊 The 11 Core SEO Datasets + Competitors Matrix

🎨 Color Circle Theory Design System

The 8 Chromatic Color Families

Dynamic 2D Badge & Triage Flags

Competitor Intelligence Benchmark Matrix

💻 Command Line Interface (CLI) Reference

📁 Vault Directory Architecture

🛡️ Pre-Flight Diagnostics & Disk Space Guard

🔒 License & Intellectual Property

About

Releases

Packages

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

🐸 FrogPipe — Autonomous Screaming Frog SEO Pipeline

The Production Headless Crawler, Zero-Loss Dataset Vault & Executive Google Sheets Engine

📑 Table of Contents

⚡ Full Setup & Installation Guide

1. System Prerequisites

2. Step 1: Clone Repository

3. Step 2: Install Python Dependencies

4. Step 3: Link Screaming Frog CLI

5. Step 4: Deploy Google Apps Script Webhook

6. Step 5: Configure Persistent Settings

7. Step 6: Execute Your First Audit

A. Interactive Mode

B. Direct Command Line Mode

🏗️ Architectural Pipeline & Mechanics

📊 The 11 Core SEO Datasets + Competitors Matrix

🎨 Color Circle Theory Design System

The 8 Chromatic Color Families

Dynamic 2D Badge & Triage Flags

Competitor Intelligence Benchmark Matrix

💻 Command Line Interface (CLI) Reference

📁 Vault Directory Architecture

🛡️ Pre-Flight Diagnostics & Disk Space Guard

🔒 License & Intellectual Property

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages