# Content SERP Competitors — Design (Slice 1: SERP only)

- **Date**: 2026-06-08
- **Status**: approved (design)
- **Section**: contenu / planning (content-card sheet)
- **Depends on**: content planning (`content_items` + content-card sheet) + keyword overlays (`briefs`, `gsc`) on `dev`.

## Goal

For a content item's **primary keyword (per locale)**, surface a **SERP / competitors panel** inside the Planning content-card sheet: the **top 10 organic competitors** with per-URL metrics (backlinks, ref. domains, authority, traffic), the **SERP features** present (PAA, Video…), and a **toggle to the YTG brief** when filled. Real data only, ingested from a single SERP export. On-page enrichment (meta/images/alt/JSON-LD) is **slice 2** (crawl) — its columns exist in the model now but stay null.

## Source of truth (verified against real exports in `~/Downloads/export-kw`)

The **SERP URLs export** (SE Ranking style) is the only source for slice 1:
```
Position,Type,Domain,URL,Page AS,Ref.Domains,Backlinks,Search Traffic,URL Keywords,SERP Features
1,Organic,service-public.gouv.fr,https://…,55,141,370,91265,2706,
…
,People also ask,…
,Video,…
```
- `Type=Organic` rows → competitors, ranked by `Position`, keep **top 10**.
- Non-organic rows (`People also ask`, `Video`, …) → distinct `Type` values collected into `features`.
- `Backlinks` is **per URL** (confirmed decision: backlinks vers l'URL, pas le domaine).

**Explicitly NOT used in slice 1:**
- Mangools `serpchecker_*.csv` — redundant (only adds KD + SERP-title + Moz metrics).
- `aciebenergie.fr-backlinks*.csv` — Semrush Backlink Analytics of **our own domain** (`Target url` = our pages). A separate future "backlink profile" feature, not competitor data.

## Data model

`src/lib/keywords/serp-types.ts`:
```ts
export interface SerpCompetitor {
  position: number;             // organic rank
  domain: string;
  url: string;
  backlinks: number | null;     // "Backlinks" — per URL (decision #1)
  refDomains: number | null;    // "Ref.Domains"
  pageAuthority: number | null; // "Page AS"
  searchTraffic: number | null; // "Search Traffic"
  urlKeywords: number | null;   // "URL Keywords"
  // slice 2 (crawl) — present but null in slice 1:
  title: string | null;
  metaDescription: string | null;
  images: number | null;
  imagesWithoutAlt: number | null;
  imageAlts: string[] | null;   // alt text per image (decision #2)
  hasJsonLd: boolean | null;    // JSON-LD / rich snippet
}

export interface SerpSnapshot {
  source: "se-ranking";
  importedAt: string;           // ISO date
  features: string[];           // distinct non-organic SERP feature types
  competitors: SerpCompetitor[];// top 10 organic, sorted by position asc
}
```

Schema: add to `keywords` (`src/lib/db/domain-schema.ts`):
```ts
serp: jsonb("serp")
  .$type<Record<string, import("@/lib/keywords/serp-types").SerpSnapshot>>()
  .notNull()
  .default({}),
```
Keyed by **locale = country code** (consistent with `briefs` keying and the sheet's country tabs). The keyword's own `lang`/`country` derive the key at import time. Migration via `bun run db:generate` + `db:migrate` + `db:migrate:test` (never `db:push`).

## Parsing (rust, wasm)

Add `parse_serp_urls_csv(csv: &str) -> JsValue` to the **`import-core`** crate (`crates/import-core`), consistent with the existing `parse_keywords_csv`. Returns `{ features: string[], competitors: SerpCompetitor[] }` (on-page fields omitted/null). Pure rust CSV parse (the `csv` crate already in the workspace), tolerant of:
- the header row,
- non-organic rows (no position) → contribute only to `features`,
- empty numeric cells → `null`,
- depth > 10 organic → truncate to 10.

Caveat (known, see memory `import-core-wasm-rebuild`): changing the crate needs `build:wasm` + `bun install` + d3k restart.

A thin TS wrapper `src/lib/keywords/serp-import.ts` calls the wasm export and stamps `source`/`importedAt`.

## Flow

- **Action** `importSerpForKeywordAction(keywordId, csvText)` (`src/lib/keywords/serp-actions.ts`, `"use server"`): authorize (owner of the keyword's site), parse via wasm wrapper, derive locale key from the keyword's `country`, merge into `keywords.serp[locale]`, `revalidatePath`.
- **Action** `getSerpForKeywordAction(keywordId)` → `Record<string, SerpSnapshot>` (or the snapshot for the item's locale).
- **Sheet** (`content-card-sheet.tsx`): on open, load SERP in **parallel** with the existing brief fetch.

## UI

In the content-card sheet (Planning board), a **« SERP / Concurrents »** section:
- **Importer export SERP** button → hidden `<input type=file>` → read text → `importSerpForKeywordAction` → refresh.
- **SERP features** badges (PAA, Video…) above the table; empty → nothing.
- **Competitors table** (CSS table, consistent with overview tables), top 10:
  `# · Domaine · URL (lien externe) · Backlinks · Ref.dom · Page AS · Trafic`
  plus on-page columns `Meta · Img · Alt · JSON-LD` rendered as **« — »** / "à crawler" (wired in slice 2).
- **Toggle « Brief YTG »** beside the panel (reuses the existing `BriefPanel`), shown when the keyword has a brief for that locale.

## Error / empty states

- No keyword on the content item → panel shows "Lie un mot-clé pour voir la SERP".
- No SERP imported yet → empty-state with the import button.
- Import parse failure → non-blocking inline error toast; existing snapshot untouched.
- URL/numeric cells empty → "—".

## Testing

- **Unit (rust)** `crates/import-core` tests: `parse_serp_urls_csv` — organic filtering, top-10 truncation, feature collection from PAA/Video rows, empty-cell → null, header skip.
- **Unit (TS)** `serp-import.test.ts`: wrapper stamps `source="se-ranking"` + `importedAt`; shape passes through.
- **Action test** `serp-actions.test.ts`: merge into `keywords.serp[locale]`; `mock.module("next/cache")` for `revalidatePath` (matches existing action tests).
- **Manual (d3k)**: open a content card whose keyword has a locale → import the real `comparateur-energie-pro_serp_urls…csv` → top 10 competitors render with backlinks/ref.dom/authority, SERP features badges show "Video"/"People also ask", on-page columns show "—", brief toggle works; `d3k errors` clean.

## Non-goals (YAGNI / deferred to slice 2+)

- On-page crawl (meta description, images, alt-per-image, JSON-LD) — slice 2 via Firecrawl.
- Ingesting Mangools serpchecker or the own-domain backlink files.
- Per-page SERP history / change tracking; editing competitor rows by hand.
- Surfacing the panel outside the Planning sheet.

## Backlog / améliorations futures (slice 1.x)

- **Filtre par URL à l'import** : à l'ingestion de l'export SERP, permettre de filtrer/dédupliquer par URL — ex. exclure les non-concurrents (youtube.com, facebook.com, réseaux sociaux, l'annuaire .gouv) ou ne garder que les vrais concurrents éditoriaux avant de stocker le top 10.
- **Splitter l'import (chunking)** : les gros exports dépassent la limite **1 Mo** des Server Actions (un mauvais fichier 3.9 Mo renvoyait un 413 — désormais rejeté proprement côté client). Pour accepter de gros fichiers légitimes, découper le CSV en chunks côté client (ou passer par un upload route/streaming) au lieu de tout envoyer en un seul payload d'action.