Prompt
I'm building a dashboard for my team that shows where we appear across ChatGPT, Claude, Gemini, Perplexity, and AI overviews. I need it to break out branded and category queries, show citations, and compare us against a few competitors. What kind of data model or tool setup should I use?
Latest observation
A good setup is to treat this as an AI visibility / share-of-answer measurement system, not a traditional SEO dashboard. You want a data model that can capture:
- the query
- the engine/model
- the response
- whether you appeared
- how you appeared
- what sources/citations were used
- who else appeared
- how you compare vs competitors over time
Recommended architecture
1) Split the system into 4 layers
A. Query library
A controlled list of prompts/searches you run repeatedly.
Include:
- branded queries
e.g."[Your Brand] pricing","[Your Brand] vs [Competitor]","[Your Brand] reviews" - category queries
e.g."best platform for [use case]","top tools for [category]","how to choose [category] software" - optional:
- informational
- comparison
- transactional
- problem/solution
- local/regional variants
Each query should have metadata:
- query_id
- query_text
- query_type: branded / category / competitor / comparison
- intent
- target_market / locale
- product line / feature theme
- priority
- benchmark competitors
B. Engine run layer
Each time you test a query in a model/engine, create a run record.
Track:
- engine: ChatGPT / Claude / Gemini / Perplexity / Google AI Overviews
- model/version if available
- date/time
- locale
- device type if relevant
- prompt variant
- temperature / deterministic settings if you control them
- session/run ID
- whether citations were requested/available
This lets you compare the same query across engines and over time.
C. Response parsing layer
Store the actual output in a structured way.
You’ll want to extract:
- raw response text
- answer summary
- entities mentioned
- brands mentioned
- ranking/order if it’s a list
- sentiment/tone toward your brand
- citations/sources
- whether your site/domain is cited
- competitor domains cited
- whether you’re mentioned in the main answer, footnotes, or just source list
For citation-heavy tools like Perplexity and AI Overviews, this is critical.
D. Analytics layer
Use this for your dashboard metrics:
- presence rate: % of runs where your brand appears
- citation rate: % where your domain is cited
- rank/position: if the answer ranks vendors
- share of voice / share of answer
- competitor overlap
- category vs branded visibility
- engine-specific visibility
- trend over time
- source diversity
- citation quality (e.g. own site, review site, directory, competitor, forum)
Data model suggestion
A relational model works well, with some JSON fields for flexible response content.
Core tables
queries
query_id
query_text
query_type
intent
locale
market
topic_cluster
priority
notes
engines
engine_id
engine_name
provider
model_version
supports_citations (bool)
runs
run_id
query_id
engine_id
run_timestamp
locale
device
prompt_version
session_context
raw_prompt
raw_response_text
mentions
One row per entity/brand mentioned in a response.
mention_id
run_id
entity_type (brand / competitor / domain / product)
entity_name
mentioned_in_answer (bool)
position_index
sentiment
rank_order
citations
One row per source citation.
citation_id
run_id
source_url
source_domain
source_title
citation_position
used_in_answer (bool)
is_owned_domain (bool)
brands
brand_id
brand_name
canonical_domain
category
competitors
competitor_id
brand_id
competitor_name
canonical_domain
Useful derived metrics
You’ll probably want calculated fields like:
- Visibility score
- e.g. weighted score based on whether mentioned, ranked, cited, and positively framed
- Brand mention rate
- Competitor mention rate
- Citation share
- Owned citation share
- Average rank in answer
- Top cited domains
- Answer inclusion by query type
- Engine coverage
- Prompt sensitivity
e.g. how often results change by phrasing
Example score formula:
- Mentioned in answer = 1
- In top 3 list = +2
- Cited = +1
- Own domain cited = +2
- Positive sentiment = +1
Dashboard views you likely need
1) Executive overview
- visibility score by engine
- branded vs category query performance
- trend line over time
- compare vs competitors
2) Query drilldown
For each query:
- response snapshot
- entities mentioned
- citations
- your position
- competitor positions
3) Engine comparison
For the same query:
- ChatGPT vs Claude vs Gemini vs Perplexity vs AI Overviews
- who mentions you
- which sources are cited
- answer consistency
4) Competitive share
- your share of mentions
- your share of citations
- competitor dominance by query cluster
5) Citation/source analysis
- top source domains
- owned vs earned vs third-party citations
- which content pieces are being used by engines
Tool setup options
Option 1: Warehouse + BI dashboard
Best if you want flexibility and long-term control.
Stack:
- ingestion: Python / Airflow / simple scheduled jobs
- database: Postgres, BigQuery, or Snowflake
- transform: dbt
- dashboard: Looker, Tableau, Metabase, Superset, Power BI
Good for:
- clean data model
- historical tracking
- custom metrics
- competitor comparison
- internal reporting
Option 2: Lightweight operational stack
If you want to move fast:
- Airtable or Notion for query library
- Python scripts to collect responses
- Postgres for storage
- Metabase for dashboard
Good for:
- smaller team
- quick iteration
- not too many queries/runs
Option 3: Purpose-built AI visibility platform
If you don’t want to build everything yourself, use a tool that already tracks AI search visibility and citations.
Look for features like:
- branded and category tracking
- multi-engine coverage
- citation tracking
- competitor benchmarking
- scheduled runs
- historical trends
- export/API access
You may still want your own warehouse if you need custom scoring and deeper analysis.
Important implementation considerations
1) Normalize your brand and competitor entities
AI outputs can vary:
- abbreviated names
- product names
- parent company names
- misspellings
Use an entity mapping table so all variants roll up correctly.
2) Keep raw outputs
Always store the full response and citations exactly as returned.
You’ll need them for audits and for improving parsing later.
3) Separate branded and category query reporting
These behave very differently:
- branded queries should be near-certain visibility
- category queries are more competitive and more sensitive to source quality
Don’t average them together without segmentation.
4) Track citation source type
Classify each citation:
- owned site
- competitor site
- review site
- directory
- forum / community
- news / editorial
- government / academic
This helps explain why the model is surfacing certain brands.
5) Watch for prompt drift
AI answers can change based on:
- phrasing
- geography
- time of day
- tool version
- user context
Use a controlled query framework so your comparisons are meaningful.
Recommended minimum viable schema
If you want to start lean, build these 5 tables:
queriesenginesrunsmentionscitations
That’s enough to power:
- branded vs category views
- competitor comparison
- citation reporting
- trend analysis
Best-practice KPI set
I’d recommend these KPIs:
- Brand visibility rate
- Category visibility rate
- Citation rate
- Owned-domain citation rate
- Average mention rank
- Competitive win rate
- Share of answer
- Top cited pages/domains
- Visibility by engine
- Visibility by query intent
Practical recommendation
If you’re starting from scratch:
- Build a query library with branded/category/competitor clusters
- Store each engine output as a run
- Extract mentions and citations
- Add a competitor mapping layer
- Build dashboards in Metabase/Looker
- Add a custom visibility score later
If you want, I can also give you:
- a sample SQL schema
- a dbt model design
- a Metabase dashboard layout
- or a JSON schema for the run/response data.