froggy-bottom-line

---
name: froggy-bottom-line
description: Turns a Screaming Frog crawl into role-specific, prioritised action plans that people without SEO knowledge can actually execute. Use this whenever someone wants to hand crawl findings to a client, an editor, a working student, an internal IT contact or an external agency — including phrases like "prepare the Screaming Frog export for the client", "turn this crawl into a to-do list", "SF-Export für den Kunden aufbereiten", "Fehlerliste zum Abarbeiten", "wer soll welche Crawl-Fehler beheben", "action plan from crawl", "broken links with the pages they sit on", "prioritise crawl errors by relevance", or when a folder of Screaming Frog exports appears together with the wish for a deliverable. Also use it to work out which exports are needed for a given depth of analysis.
license: MIT
---

# Froggy Bottom Line

A crawl report tells you what is broken. It does not tell anyone what to do about it.

The standard Screaming Frog export lists a dead URL but not the pages that link to it,
so the person fixing it has nowhere to start. It ranks nothing, so a broken link on the
most valuable page of the site sits below an alt text on a page nobody visits. And it
speaks in status codes to people who do not read status codes.

This skill closes that gap: it produces one workbook per recipient, every row a single
piece of work, ordered by what actually pays off, written in plain language.

## What it is not

Not an audit and not a report. It produces **work**, not findings. Anything that needs
a strategic decision is separated out into a decision list for whoever is allowed to
make that call, and never handed to the person implementing.

---

## The intake is not optional

Four things must come from the user before anything is built: **the domain, the
recipients, the level, and how the crawl data arrives.** Ask them one at a time, in that
order, using `AskUserQuestion` so the choices are visible and clickable.

This is a hard rule, not a preference:

- **Never assume an answer, and never fill one in "for a demo".** If the user asks for a
  quick run or an example, they still get asked. A deliverable built on invented
  parameters is worse than no deliverable, because it looks finished.
- **Do not reorder.** The level determines which exports are needed, and the recipients
  determine which of those actually matter. Asking for exports before both are settled
  wastes the user's time on files nobody will read.
- **Do not proceed with partial answers.** `build_actionplan.py` refuses to run without
  `--client`, `--level`, `--lang` and `--roles` for exactly this reason. If you find
  yourself wanting to pass a default, you skipped a question.

The one thing you may do without asking: look up data you already have access to. Check
Search Console and SISTRIX yourself rather than asking the user what they think is
connected.

---

## Workflow

### Step 1 — Which domain, and what data exists for it

Ask for the domain first. Everything else is meaningless without it: it names the file,
it heads the cover sheet, and it decides whether the ordering rests on measured data or
on a proxy.

Then look it up yourself, before asking anything further:

1. **Search Console** — call `list_sites` and check whether the domain is there. Do not
   ask the user whether it is connected; you can see it.
2. **SISTRIX** — check visibility and, if the domain is known there, pull
   `traffic_estimation`.

Then say plainly what you found, and let the user decide how to proceed:

| Situation | What to offer |
|---|---|
| Domain is in Search Console | pull page-level rows for the last 90 days, done |
| Not in GSC, but in SISTRIX | ask for a GSC export, or a SISTRIX URL export, or fall back |
| In neither | ask for any performance export, or accept the crawl-signal fallback |

**Know what SISTRIX over MCP can and cannot give you.** `traffic_estimation` returns
**directories, not URLs** — `/guide/` 19 %, `/blog/` 14 %. That is enough to weight whole
sections, never enough to order individual pages. Say so rather than implying the
ordering is URL-accurate. If the user wants URL-level data from SISTRIX, it has to come
from a manual export out of the interface.

Never invent relevance. A wrong priority sends the work to the wrong pages.

### Step 2 — Ask who receives this

Ask which recipients the consultant needs, and offer the four roles.
Multiple answers are normal; one file is produced per role selected.

| Role | Gets | Typical person |
|---|---|---|
| `editor` | everything changeable in the CMS: links in content, titles, descriptions, headings, alt texts | editor, working student, content manager |
| `tech` | server, status codes, sitemap, redirect rules, hreflang | internal IT, hosting contact |
| `agency` | templates, shared building blocks, system-level issues | external agency, developers |
| `lead` | decisions to be made before implementation starts | client's SEO lead, or the consultant |

Ask in the language the user is writing in.

**If `lead` is not among the answers, say once what that costs.** Broken links whose
target could not be established land in the editor's file as `open`, and nobody is
allowed to decide where they should point. The editor clears the safe rows and stalls on
the rest. State it in one sentence, accept the user's decision, and move on — then make
good on it in step 6 by reporting exactly which findings nobody received.

### Step 3 — Ask for the level

Levels build on each other: 2 contains 1, 3 contains 1 and 2. Offer them with these
short descriptions.

**Level 1 — hard errors.** Broken links (4xx, 5xx, no response) including every page
they sit on, pages without content, missing titles, missing headings, redirect chains
and loops, hreflang if the site is multilingual. Plus a relevance verdict on every
affected page: anything with no optimisation history, no performance data and no index
presence is flagged rather than fixed, with a noindex recommendation where a near
duplicate exists.

**Level 2 — metadata, headings, images.** Everything from level 1 plus titles, meta
descriptions, heading structure and image alternative texts, clustered by topic.
Optionally with ready-made copy (see below).

**Level 3 — internal linking and architecture.** Everything above plus: does internal
linking actually reach the pages that earn money, do links come from body text or only
from navigation, orphan pages, click depth, and where link equity leaks away.

### Step 4 — Ask how the crawl data arrives

**First check whether a Screaming Frog MCP is connected.** Search the available tools
for `screaming frog`, `crawl` or `sf_`. If one is there, offer the short route:

> You have Screaming Frog connected. I can pull the exports myself, or start a fresh
> crawl — no manual exporting needed. Is there a recent crawl I should use, or shall I
> crawl now?

Then follow `references/mcp-mode.md`: verify the licence, have the user close the
Screaming Frog GUI (it locks the crawl database), pull the exports for the chosen level
into one folder, and continue below as normal. Do not invent tool names — read the
schemas of the tools that are actually connected. Prefer an existing crawl over a new
one; re-crawling a large site costs hours and moves the numbers under a client who may
already be looking at them.

If no MCP is connected, ask for exports the normal way and say nothing about it.

Run the detector on whatever folder you end up with, it is faster than asking:

```bash
python3 scripts/detect_exports.py <folder> --level 2 --lang de
```

It identifies every file by its **column signature, not its file name**, so the
Screaming Frog UI language does not matter and renamed files are still recognised. It
prints what is present, what is missing, and the exact menu path for each missing
export in German and English.

### Step 5 — Name the exports you need, cut to this job

When the user exports by hand, give them the list from `references/exports.md` — but
**filtered by the roles they chose, not just by the level.** The level says what could be
analysed; the recipients say what will actually be read. Asking someone to export
redirect chains when no `tech` file is being produced is asking them to work for the bin.

For level 1 with only `editor`, that is two files: the internal HTML export and the 4xx
inlinks export. Say explicitly which ones would be needed *additionally* if they later
want another role, so the decision stays theirs.

Give the menu path in the user's language, and flag two things every time:

- **Link Positions must be enabled** (Configuration → Spider → Extraction). Without that
  column, navigation and footer links cannot be told apart from content links, and a
  single broken footer link turns into hundreds of separate tasks.
- Export as **CSV in UTF-8**.

Always explain why it must be the **inlinks** bulk export and not the tab export: only
the inlinks version carries the source page, the visible link text and the position.
Without it nobody can find the link to fix it, and that is the single most common reason
a handover fails.

The user may also simply drop a folder holding *all* their exports. That works fine: the
detector picks out what it needs and names what is missing.

### Step 6 — Build, then say what is *not* in the box

```bash
python3 scripts/build_actionplan.py <folder> \
  --level 2 --lang de --roles editor,lead \
  --gsc gsc_pages.csv --out ./out --client "Client name"
```

All four of `--client`, `--level`, `--lang` and `--roles` are required; the script exits
if one is missing. Output language follows the recipient, not the consultant.

Then report three things, in this order:

1. **What was produced** per role, with row counts.
2. **What the analysis flagged** in `notes`. A shared broken block belongs in the first
   sentence — one broken module standing behind hundreds of dead links is usually the
   largest single lever in the project, and it is invisible in any raw export.
3. **What nobody received.** The script returns `not_delivered` and `not_delivered_severe`
   for findings whose role was not requested. Name them and say what they are. A severe
   finding that silently belongs to an unrequested role is the one failure mode of this
   whole approach: the consultant hands over a tidy file and believes the site is covered.

---

## Optional: ready-made copy for level 2

Only offer this when level 2 or 3 was chosen, and only when asked. Writing titles and
descriptions is a separate piece of work and it consumes real budget.

Ask for the scope and say plainly what it costs:

> Shall I draft the actual titles and descriptions? Top 50, top 100, or all of them?
> Each one needs a SERP analysis, so this is significantly more token spend than the
> rest — the top 50 usually cover most of the traffic.

For every page in scope, run the **SXO skill**
(<https://github.com/tools-enerix/claude-sxo-skill>) rather than writing from
intuition: it derives the user story from the live SERP and matches it against the
target page. Use performance data through the MCP wherever it is available, so the
draft is aimed at the query the page actually ranks for.

Everything produced this way lands in the workbook flagged `approval needed = yes`. It
is a draft for editorial sign-off, never an instruction.

---

## Rules that hold in every run

- **Never guess a link target.** A proposal is `safe` only when the visible link text
  supports it. Everything else is `check` or `open` and goes to the decision list. A
  wrong confident proposal is worse than an empty cell.
- **Decide once, implement many times.** One broken address sitting in forty links is
  one decision for the lead, then forty mechanical edits. Never make the implementer
  decide.
- **Drop pages with no visibility.** They belong on the decision list as noindex
  candidates, not in anybody's task list.
- **Look for the shared block.** Before listing hundreds of rows, check whether they
  trace back to one template. The scripts do this automatically and report it.
- **Say what not to touch.** Every workbook carries a list of things that look like
  errors but are not, so nobody burns a week on brand suffixes in titles.
- **Never let a finding vanish with the handover.** If a role was not requested, its
  findings still exist. Report them instead of quietly dropping them.
- **Check the data yourself before asking.** Search Console and SISTRIX can be looked up;
  asking the user what they think is connected wastes a turn and invites a wrong answer.
- **Separate approval from execution.** Two clearly marked classes, never mixed.

## Files

| Path | Purpose |
|---|---|
| `scripts/detect_exports.py` | classify a folder, report missing exports with menu paths |
| `scripts/build_actionplan.py` | build one workbook per role |
| `scripts/lib/columns.py` | language-agnostic export detection and column mapping |
| `scripts/lib/relevance.py` | relevance classes from GSC/SISTRIX, noindex candidates |
| `scripts/lib/matching.py` | replacement-URL resolution, shared-block detection |
| `scripts/lib/findings.py` | the finding catalogue with all plain-language wording |
| `scripts/lib/analyze.py` | exports to findings |
| `scripts/lib/workbook.py` | themed Excel output |
| `references/exports.md` | which export for which level, German and English menu paths |
| `references/roles.md` | who gets which finding and why |
| `references/prioritisation.md` | how relevance and priority are derived |
| `references/mcp-mode.md` | pulling the crawl through a Screaming Frog MCP instead of manual exports |
| `config/theme.json` | output colours, Screaming Frog palette by default |
| `examples/make_fixtures.py` | generate sample crawls in both UI languages |

## Theming

The workbooks ship in the Screaming Frog palette. `config/theme.froggy-bottom-line.json`
is a second palette with a red accent, for deliverables that should not look like a tool
export. To brand them for a specific agency, copy either file as a starting point and
pass `--theme yourfile.json`.

## Requirements

Python 3.9+ and `openpyxl`. Nothing else.

---

Built by [Martin Weber](https://www.linkedin.com/in/martinweber-search/) ·
[github.com/machtiiin](https://github.com/machtiiin)

If this saved you an afternoon of spreadsheet work:
[buy me a coffee](https://buymeacoffee.com/machtiiin).