Forensics on defective SEO exports

Inputs

• A raw export: chunked CSV files per source and a state file that claims the chunk and row counts.

• A manifest with the domain, the database and the snapshot date.

• The tool's documented meanings: what empty, zero and a timestamp mean in its columns.

Procedure

1. Compare the state file with the bytes

   For every source the claimed rows, the rows on disk and the distinct rows are counted. The diagnosis needs no catalog and no prior load, so it runs the moment an export lands.

2. Classify the defects before ingest

   The classes, each with a confidence level: a repeated chunk (the same page again, pagination never advanced), a missing chunk, a repeated header, a truncated file, a byte-order mark, the delimiter, the null token and the decimal separator. The exit code says whether anything is defective.

3. Profile with bounded sampling

   The profile records schema facts and aggregates, never rows. Sampling is bounded so a large export runs in minutes.

4. Take the dates from the manifest, not the file

   Domain, database and snapshot date come from the manifest. A per-keyword timestamp is when the tool last updated that keyword, not the snapshot date.

5. Read the values in the tool's terms

   A previous position of zero means the site did not rank, not rank zero. Empty and zero are different things, and a value is recorded as the tool defines it.

6. Judge repairability honestly

   A repeated-page export cannot be repaired locally: deduplication would shrink it to a fraction while looking successful. The only fix is a corrected re-export, and the validation failure stays until it arrives.

7. Never touch the raw export

   The diagnosis reads and never writes the raw export, and a test enforces it. The evidence is written to a directory of its own.

Outputs and how to read them

• A diagnosis per source: chunks, claimed rows, usable rows, never-retrieved rows, class and confidence, as JSON and CSV.

• A requirements list for the exporter: what an export must satisfy for the load to accept it.

• A record that a truncated competitor discovery travels to every row, so the list is never presented as complete.

Failure modes

• The export is loaded first and the defect noticed in a report.

• Deduplication is presented as a repair.

• The file's timestamps are read as snapshot dates.

• Repeated header rows are counted as rows.

• A competitor list is presented as complete although discovery was cut off.

Where it is used

In the load pipeline for competitive-research exports in the search work of a large healthcare website and in the market-intelligence pipeline: the diagnosis runs the moment an export lands, and the exporter requirements were written from its findings.