SEO Mediapilote, an SEO analysis and reporting tool

Mediapilote’s SEO consultants juggled several tools to analyse a site, compare competitors and prepare a client report. The agency wanted this data in one internal app, linked to its clients and their Google accounts.

The project started in February 2026 on GitHub and then moved to GitLab. It also supplies data to other agency tools, such as the GEO audit and the Backlinks platform.

The data comes from paid, heterogeneous sources: DataForSEO for rankings, backlinks and SERPs, Search Console and Analytics for real traffic, PageSpeed for performance. Every call costs money, so expensive requests had to be cached and capped.

The move from MySQL to Cloudflare D1 in August 2026 added a constraint: large analyses no longer fit in the database and had to be stored elsewhere without breaking the report history.

I built the domain analysis with a switch between DataForSEO and Google data, multi-domain comparison with a Venn diagram of shared keywords, branded Excel exports, then semantic keyword clustering based on SERP overlap. A technical audit relies on a crawler running in Workflows with Browser Run, and a weekly tracker emails its results.

The local ranking API measures a business’s Google Maps position on a grid of 3, 5 or 7 points per side, with a hard cap on the number of requests. For the migration, analysis blobs moved to R2 and the rest to D1. Recent reports are written by AI and rendered as A4 PDFs or 16:9 decks.