The SQL Behind an AI Visibility Dashboard: Five Metrics and Their Traps

Once you're collecting LLM answers daily and scoring them for brand mentions, someone will ask for a dashboard. That's when a pile of good data turns into misleading charts.

The traps are specific to this kind of data. Answers are non-deterministic, sample sizes per prompt are small, engines differ wildly, and the competitor set changes. This post writes the five metrics worth putting on a dashboard as SQL (SQLite dialect, easy to port), with the guards that keep each one honest.

The schema is the one from earlier in this series: prompt, brand, run (one API call) and score (one row per run × brand).

Every metric starts from the same join. Put it in a view so the dashboards can't drift apart:

CREATE VIEW v_scored AS SELECT r. run_date, r. engine, r. prompt_id, p. intent_stage, p. topic, b. name AS brand, b. is_ours, s. mentioned, s. position, s. cited, s. sentiment FROM run r JOIN prompt p ON p. id = r. prompt_id AND p. active = 1 JOIN score s ON s. run_id = r. id AND s. scorer_version = 'v2' JOIN brand b ON b. id = s. brand_id; Enter fullscreen mode Exit fullscreen mode Pinning scorer_version in one place means a scoring upgrade is one line, not a hunt through twenty chart queries.

Share of answers that mention you. Daily values jump around because each prompt only gets a handful of samples per day, so chart a 7-day rolling rate and show the daily points faintly behind it.

WITH daily AS ( SELECT run_date, engine, SUM ( mentioned ) AS k, COUNT ( * ) AS n FROM v_scored WHERE is_ours = 1 GROUP BY run_date, engine ) SELECT run_date, engine, n, 1. 0 * SUM ( k ) OVER w / SUM ( n ) OVER w AS visibility_7d FROM daily WINDOW w AS ( PARTITION BY engine ORDER BY run_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) ORDER BY engine, run_date; Enter fullscreen mode Exit fullscreen mode Trap: averaging daily percentages ( AVG(k*1.0/n) ) weights a 4-sample day the same as a 40-sample day. Sum the numerators and denominators across the window, as above.

Trap: ROWS BETWEEN 6 PRECEDING assumes one row per day. If the collector skipped a day, the window silently stretches to 8 days. If gaps matter, join against a calendar table first.

Your share of all brand mentions, per engine. The denominator is mentions of tracked brands, so it only means something with a fixed competitor set:

SELECT engine, brand, SUM ( mentioned ) AS mentions, ROUND ( 1. 0 * SUM ( mentioned ) / SUM ( SUM ( mentioned )) OVER ( PARTITION BY engine ), 3 ) AS sov FROM v_scored WHERE run_date >= date ( 'now', '-28 days' ) GROUP BY engine, brand ORDER BY engine, sov DESC; Enter fullscreen mode Exit fullscreen mode SUM(SUM) OVER is a window over the grouped result: total mentions per engine, computed after the GROUP BY.

Trap: adding a competitor mid-period drops everyone's SOV. Annotate the chart when the brand set changes, or recompute history with the new set.

Position only exists when the answer is a list and you're in it. Report it alongside the count it's based on, never alone:

SELECT engine, COUNT ( position ) AS listed_n, ROUND ( AVG ( position ), 2 ) AS avg_position, ROUND ( 1. 0 * SUM ( position = 1 ) / COUNT ( position ), 3 ) AS top1_rate FROM v_scored WHERE is_ours = 1 AND run_date >= date ( 'now', '-28 days' ) GROUP BY engine HAVING COUNT ( position ) >= 20; Enter fullscreen mode Exit fullscreen mode Trap: average position can improve while visibility falls. If you drop out of the answers where you were listed fifth, the remaining average goes up. That's why this metric never goes on a dashboard without metric 1 next to it.

HAVING COUNT(position) >= 20 hides engines with too few listed answers to say anything. Pick your own threshold, but have one.

Two separate lines, same chart. The gap between them is the useful part:

SELECT run_date, ROUND ( AVG ( mentioned ), 3 ) AS mention_rate, ROUND ( AVG ( cited ), 3 ) AS citation_rate, ROUND ( 1. 0 * SUM ( mentioned AND NOT cited ) / NULLIF ( SUM ( mentioned ), 0 ), 3 ) AS mentioned_not_cited FROM v_scored WHERE is_ours = 1 GROUP BY run_date ORDER BY run_date; Enter fullscreen mode Exit fullscreen mode A high mentioned_not_cited share means the engines know your brand but source their claims about you elsewhere, usually review sites, forums or competitor comparison pages. That's worth knowing, because those third-party pages are then what shapes the description of you.

If your scorer classifies how each mention is framed (positive recommendation, neutral listing, critical), show the mix as a stacked share, not a single "sentiment score":

SELECT strftime ( '%Y-%W', run_date ) AS week, ROUND ( AVG ( sentiment = 'positive' ), 3 ) AS positive, ROUND ( AVG ( sentiment = 'neutral' ), 3 ) AS neutral, ROUND ( AVG ( sentiment = 'critical' ), 3 ) AS critical, COUNT ( * ) AS n FROM v_scored WHERE is_ours = 1 AND mentioned = 1 GROUP BY week ORDER BY week; Enter fullscreen mode Exit fullscreen mode (In SQLite, a comparison returns 0 or 1, so AVG(x = 'y') is a proportion. In Postgres, write AVG((x = 'y')::int).)

Trap: a net score (positive minus critical) hides a shift from neutral to critical when positive stays flat. The stacked view shows it. Also, audit the classifier: sample 50 labelled mentions a month and check them by hand. LLM-based sentiment labels drift when the labelling model changes.

Five metrics, laid out so that each one is read next to the one that keeps it honest:

That last one is the most-used feature of any dashboard like this, and it's only possible because the collector stores raw answers (see the schema post).

The SQL above is the easy part. A production dashboard also needs regional breakdowns (answers vary by country), multi-brand separation for agencies, scheduled email reports, alerting on sudden drops and an interface non-technical stakeholders will actually open.

Vista AI's Brand Visibility Dashboard packages those: visibility overall and by engine, share of voice against competitors, sentiment split into positive, neutral and critical, per-query rankings, 30-day, 90-day and yearly trends, regional breakdowns, weekly email reports, and a raw data stream that keeps the full AI responses behind each number.

Build or buy, keep the rule that runs through every query above: every rate ships with its denominator.