1. Store the experimental conditions
A GEO measurement starts with a fixed question manifest. Give each question an ID and an intent, such as discovery, shortlist or direct brand enquiry. Store the exact model identifier, language and retrieval setting for every run. Do not merge a chat application answer and an API answer merely because they share a provider name. The example below uses recorded fixture answers; it does not call any provider or claim to reproduce a particular chat product. In a collector, persist the raw answer privately before extracting observations.
2. Separate the outcome columns
Use separate columns for mention, explicit recommendation and citation. Recommendation extraction needs a documented rule: a negative mention or a comparison example is not an endorsement. A rank is nullable because an unordered list does not necessarily express preference. The database stores each observation once under a run/question/brand key. Parameterized statements keep a brand name from becoming executable SQL. A unique constraint is also the first line of defense against duplicate ingestion when a worker retries after a timeout.
import sqlite3
c = sqlite3.connect(':memory:')
c.executescript('''
CREATE TABLE observations (
run_id TEXT, question_id TEXT, brand TEXT,
model TEXT, retrieval INTEGER, valid INTEGER,
mentioned INTEGER, recommended INTEGER, rank INTEGER,
PRIMARY KEY(run_id, question_id, brand)
);
''')
rows = [
('r1','q1','Acme','model-version-a',0,1,1,1,1),
('r1','q2','Acme','model-version-a',0,1,1,0,None),
('r1','q3','Acme','model-version-a',0,0,0,0,None),
]
c.executemany('INSERT OR IGNORE INTO observations VALUES (?,?,?,?,?,?,?,?,?)', rows)
valid, mentions, recommendations = c.execute('''
SELECT SUM(valid), SUM(CASE WHEN valid=1 THEN mentioned ELSE 0 END),
SUM(CASE WHEN valid=1 THEN recommended ELSE 0 END) FROM observations
''').fetchone()
assert (valid, mentions, recommendations) == (2, 2, 1)
print({'recommendation_rate': recommendations / valid, 'unavailable': 1})
3. Keep the denominator inspectable
The fixture produces two valid observations and one unavailable attempt. Its recommendation rate is one divided by two, not one divided by three. Keep unavailable attempts alongside the rate so a provider outage cannot appear to be a fall in recommendation quality. Before aggregating several runs, require matching manifest and configuration versions. Store extractor version separately: improving a classification rule can change a rate without changing the original model answer. Reprocessing stored answers should create an explicitly versioned measurement, rather than silently rewriting an old chart.
4. Review the collector before scaling
Test duplicate insertion, a negative mention, an unavailable response and a list without explicit ordering. Then manually review a small sample of classifications. If an answer is ambiguous, retain an unknown state instead of forcing it into recommended or not recommended. Add a bounded collection budget and a finite retry policy before scheduling repeated runs. This is a design for a future measurement service, not a statement that our current GEO questionnaire already performs recurring brand monitoring. The questionnaire gathers requirements; measurements need their own validated collector.