Tophoo SEO — Project-Based SEO Workbench

Background

Cross-border independent site teams usually run multiple websites at once. GSC data, keywords, TDK and competitors are scattered everywhere, making centralized management and comparison hard. Tophoo SEO treats one website project = one isolated set of SEO data, bringing daily SEO operations into a single workbench.

Features

Projects & Accounts

Users, workspaces, plans and per-project permission management;
Project management: create, switch current project, export per-project Markdown info;
All business data is bound to site_project_id; switching projects switches GSC, tasks, keywords, competitors and TDK automatically — never mixed;
Plan quotas: 3 projects on Free, 10 on Professional.

GSC Analytics & Management Weekly Report

Manual import of four base datasets: GSC performance, page indexing, internal links, external links;
Six indexing-issue URL details: crawled-not-indexed, discovered-not-indexed, 404, noindex, 403, duplicate canonical;
Generates a management weekly report Excel: indexing status/trends, issue URL details, link counts, traffic, top queries/pages/regions, MoM changes, risks and support needs;
Report named SEO周报_YYYY_MM_DD.xlsx; auto-generates weekly analysis tasks from GSC data with status updates and Excel export.

Keyword Layout

Unlimited-level keyword directory tree: expand/collapse, inline category editing, add sub-categories, move up/down, recursive delete;
Keyword fields: keyword, Chinese translation, monthly search volume; fuzzy title search, category binding, per-project dedupe;
Excel import with three modes: smart update, merge-new, clear-then-reimport; export to Excel or Markdown.

Competitor Analysis

Per-project competitor list: name, URL, description, custom sort; inline editing with add/delete/move;
Export naming: 项目名称_竞品列表_YYYY_MM_DD.

Product TDK & Article TDK

Separate modules for import, browse, fuzzy search and export; strict standard Excel header import, ordered by wp_id;
Same-project same-wp_id auto-overwrites to avoid duplicates;
WordPress XML converter moved into the matching modules;
Export names carry project name and date.

Tech Stack

Backend: Python 3.12, FastAPI, Uvicorn, SQLAlchemy, PyMySQL
Frontend: Jinja2, HTML, CSS, vanilla JavaScript
Data: OpenPyXL, WordPress XML
Database: MySQL
Auth: JWT, PBKDF2-HMAC

Design Highlights

Data isolation: GSC tables use gsc_import_ prefix; core records carry site_project_id with API-level permission checks;
Dedupe strategy: keywords by (site_project_id, normalized_keyword); TDK by (site_project_id, wp_id);
Performance: composite indexes for import batches, daily/dimension metrics, indexing snapshots, tasks, keyword tree and TDK queries, created automatically at startup when missing;
utf8mb4: import and data tables use utf8mb4 to store emoji and 4-byte chars from WordPress content.

Status & Roadmap

Status: core features implemented; payment is a simulated checkout (test activation, no real charges);
Roadmap: real payment (Stripe/Alipay Webhook verification), Alembic migrations, list pagination after 10k TDK rows, MySQL FULLTEXT for 100k-level title search, GA4 analytics module.

A personal tool — happy to discuss SEO tooling ideas: Get in touch.