Your Citation Join Multiplied the Answers: A Data Model for AI Visibility Exports

By Daoyu Guan

An AI visibility export can contain every answer and every citation, yet produce the wrong mention rate when the files are combined. One answer may cite three pages; another may contain no visible citation. Joining the answer table to the citation table changes the number of rows. If the analyst then averages a brand flag across those rows, the result weights answers by their citation counts and may discard uncited answers entirely.

The useful buying question is therefore more precise than “Can I download a CSV?” Ask whether the exported records preserve the relationship between a collection attempt, its answer, and its citations. Then reconstruct one reported metric without allowing the relationship to change the denominator. A file format describes how data is packaged. It does not establish what one row represents.

First establish what the exports contain

Public product documentation shows why this distinction matters. OtterlyAI documents separate raw-response and citation exports. Peec AI describes a project chat export and an API report for full URLs by prompt or model. These are documented workflows, not account tests conducted for this article. They do not establish that every field needed for a particular customer's reconciliation appears in the same file.

Before importing anything, write down the grain of each dataset: the event or object that one row represents. A prompt table may have one row per configured question. A run table may have one row per collection attempt. A citation table may have several records for one completed answer. A monthly summary may have one row per domain. Those rows are not interchangeable.

A prompt identifier is especially easy to misuse. The same question can run on three platforms, on several dates, and under different modes or market settings. Joining two exports using only that identifier can attach today's sources to yesterday's answer. Matching column names are not enough; the provider must explain the actual relationship and its scope.

A small fixture with a large reporting error

The following dataset is entirely fictional. It contains eight collection attempts, six completed ordinary answers, one result where an AI Overview did not appear, and one failed collection. The mention labels are supplied teaching inputs. No brand-matching algorithm or real product is being evaluated.

The answer-level mention rate is 2/6, or 33.33%. Collection completeness is a separate statement: six completed answers from eight attempts. The missing AI Overview and the failed collection do not establish negative answers.

There are seven citation records. Two records in A1 point to the same URL, representing separate displayed occurrences in this invented example. Keep those occurrences if their positions matter, but do not describe them as two distinct cited pages. Across the fixture, the seven records refer to five distinct URLs. That count does not establish five independent sources of evidence either.

What an inner join removes

An inner join produces a row for each matching pair. PostgreSQL's table-expression documentation explains this behavior and distinguishes it from an outer join, which can preserve an unmatched row. These are database operations, not AI-platform-specific scoring rules.

Joining completed runs to citations creates three rows for A1, one for A2, two for A4, and one for A8. A3 and A5 disappear because they have no matching citation record. The resulting table has seven rows, but only four distinct completed answers.

Averaging the repeated mention flag gives 3/7, or 42.86%. Counting distinct answer IDs fixes the repetition but not the lost answers: only one of the four remaining answers mentions the brand, yielding 1/4, or 25%. Both figures differ from 2/6. Neither difference is an improvement or decline in AI visibility; the underlying observations have not changed.

The uncited positive answer matters as much as the uncited negative answer. An answer can name a company without displaying a supporting URL. Dropping that answer is a reporting decision, not evidence that the mention never occurred.

A left join preserves answers but still multiplies rows

A left join retains A3 and A5 with empty citation fields. It does not turn the relationship into one-to-one. A1 still appears three times and A4 twice. The completed-answer join now contains nine rows, with four repeated positive flags. Averaging those flags gives 4/9, or 44.44%.

This is why “we used a left join” is not sufficient acceptance evidence. The analyst must also preserve the metric's unit through the calculation. The same issue can affect sentiment averages, position averages, or any answer-level label copied onto citation rows. A citation-rich answer can acquire extra weight without anyone deliberately choosing to weight it.

There is another trap: filtering the joined table by a citation property can remove the uncited rows again. If a report needs answer-level coverage and a breakdown of sources, calculate the answer metric from the eligible answer population and present the source breakdown as a separate measure. Do not assume that one joined table provides the correct denominator for both.

Reproduce the fixture in SQLite

This complete setup uses fictional URLs under the reserved `.test` namespace. They are identifiers in a teaching dataset, not pages to visit. Enable foreign-key enforcement before loading the records.

```sql
PRAGMA foreign_keys = ON;

CREATE TABLE runs (
run_id TEXT PRIMARY KEY NOT NULL,
platform TEXT NOT NULL,
prompt_id TEXT NOT NULL,
status TEXT NOT NULL
CHECK (status IN ('complete', 'not_triggered', 'failed')),
mentioned INTEGER,
CHECK (
(status = 'complete' AND mentioned IS NOT NULL
AND mentioned IN (0, 1))
OR (status <> 'complete' AND mentioned IS NULL)
)
);

CREATE TABLE citations (
citation_id TEXT PRIMARY KEY NOT NULL,
run_id TEXT NOT NULL REFERENCES runs(run_id),
url TEXT NOT NULL
);

INSERT INTO runs VALUES
('A1', 'ChatGPT', 'P1', 'complete', 1),
('A2', 'Gemini', 'P1', 'complete', 0),
('A3', 'Google AIO', 'P1', 'complete', 1),
('A4', 'ChatGPT', 'P2', 'complete', 0),
('A5', 'Gemini', 'P2', 'complete', 0),
('A6', 'Google AIO', 'P2', 'not_triggered', NULL),
('A7', 'ChatGPT', 'P3', 'failed', NULL),
('A8', 'Gemini', 'P3', 'complete', 0);

INSERT INTO citations VALUES
('C1', 'A1', 'https://docs.example.test/a'),
('C2', 'A1', 'https://docs.example.test/b'),
('C3', 'A1', 'https://docs.example.test/a'),
('C4', 'A2', 'https://docs.example.test/c'),
('C5', 'A4', 'https://docs.example.test/a'),
('C6', 'A4', 'https://docs.example.test/d'),
('C7', 'A8', 'https://docs.example.test/e');
```

The table includes attempts without answers so their collection states remain visible. A real evidence archive also needs the full answer, capture time, actual search surface, visible mode, account context where appropriate, geography evidence, and source provenance. Those fields are omitted here to isolate the arithmetic. This is not a proposed complete Semvi export schema.

Calculate the headline directly from the completed run population:

```sql
SELECT
COUNT(*) AS eligible_answers,
SUM(mentioned) AS mentioned_answers,
ROUND(100.0 * SUM(mentioned) / NULLIF(COUNT(*), 0), 2)
AS mention_rate_percent
FROM runs
WHERE status = 'complete';
```

The result is `6 | 2 | 33.33`. `NULLIF` leaves an empty population's rate undefined rather than forcing it to zero. Before generalizing this query, agree whether incomplete reviews belong outside the metric and how the export represents them. The fixture's complete rows already have reviewed binary labels; many real workflows have an additional review state.

Add source information without changing the answer population

Reduce the citation data to one row per run before joining it to the answer-level report. Keep citation occurrences and distinct URLs as separate columns:

```sql
WITH per_run_citations AS (
SELECT
run_id,
COUNT(*) AS citation_records,
COUNT(DISTINCT url) AS distinct_urls
FROM citations
GROUP BY run_id
)
SELECT
r.run_id,
r.mentioned,
COALESCE(c.citation_records, 0) AS citation_records,
COALESCE(c.distinct_urls, 0) AS distinct_urls
FROM runs AS r
LEFT JOIN per_run_citations AS c ON c.run_id = r.run_id
WHERE r.status = 'complete'
ORDER BY r.run_id;
```

This returns six rows. A1 has three citation records and two distinct URLs. A3 and A5 remain present with zero citation records. Their zero means no citation records were supplied in this fixture, not a finding about an AI system's undisclosed retrieval activity.

The SQLite aggregate reference distinguishes counting rows from counting non-null values and supports distinct inputs. In a raw left join, `COUNT(*)` also counts the placeholder rows for unmatched answers; counting a non-null citation ID does not. Check what your query is counting before interpreting its label.

Treat identity checks as acceptance requirements

A primary key identifies a row. A foreign key relates it to another table. The W3C tabular data model provides a vocabulary for these relationships in tabular data. That vocabulary is useful for an export contract even when a customer never loads the files into a database.

Ask the provider to document the identifier's scope. Is a run ID unique across the account, only within a project, or only within an export? Can a repeated export contain the same run? How is a corrected classification distinguished from a fresh collection? A local row number can help preserve a file's provenance, but it does not automatically identify the same observation in another file.

Require an explicit mapping when files use different IDs. Joining by prompt wording, a rounded date, or the nearest timestamp is an inference that needs validation. Two identical answers can be separate observations; two differently formatted exports can describe the same observation. Text similarity cannot settle that distinction by itself.

Database constraints cover only part of the problem. The fixture's foreign key rejects a citation attached to a nonexistent run, but it does not prevent a citation from pointing to an existing failed run. That requires a separate semantic validation or a more specific data model. Keep the rejected input and its reason rather than silently removing it to make a report reconcile.

Use a reconciliation sheet before accepting the integration

These are acceptance targets for the invented fixture, not universal expected ratios. In a real pilot, agree a small record set with the provider, preserve its original files, and explain every difference. An exception log should distinguish collection failure, absent AI feature, unreviewed classification, missing source detail, and a genuinely invalid relationship.

For a Semvi by Xindar evaluation, the product team has confirmed complete-answer and citation-URL export. That confirmation does not establish the exported column names, stable join IDs, relationship checks, or the behavior of a customer's actual download. Those remain demonstration requirements, as explained in the product scope and buyer-verification reference. This article's successful SQL example would not validate a Semvi account export.

The practical acceptance task is to trace one answer with several citations, one answer with no supplied citations, and one failed attempt through the actual files. Reproduce the provider's answer-level number, then change the source breakdown without changing that number's population. That exercise tells a buyer more about the reporting integration than a screenshot of a percentage.

Questions analysts and buyers ask

Does `COUNT(DISTINCT run_id)` solve the problem?

It can remove repeated IDs from a count. It cannot restore answers already removed by an inner join or an incompatible source filter. Establish the eligible parent population first, and test that the reporting path preserves it.

Should two citations to the same page be deleted?

Preserve the original occurrences when they carry useful positions or claim mappings. Derive a separately labeled distinct-URL view for a different question. Neither count establishes independent corroboration or proves what the system read internally.

Is an answer without citations a failed collection?

Not necessarily. A completed answer can have no displayed citations, and a captured answer can have unavailable source detail. Record the observed condition. A login failure or an AI Overview that did not appear does not provide an answer to classify.

Can this method certify a vendor's exports?

No. It verifies the fixture's arithmetic and demonstrates data-model checks. Vendor acceptance requires real authorized files, documented identifiers, a screen-to-file comparison, and a reconciliation of the actual reporting scope.

Sources and scope

The SQL and all eight attempts, seven citation records, brand flags, and example URLs are editorial teaching material. Database behavior was checked against PostgreSQL and SQLite documentation; tabular identity concepts use the W3C model. The two vendor help pages establish their stated export workflows only. They are not evidence of a tested account, identical schemas across vendors, customer results, or a change in AI recommendations. Related reading: trace one answer from screen to export.