How To Measure AI Visibility In A Spreadsheet Before You Buy Anything

Budget approval usually requires a number, and the number usually requires budget. If you are stuck in that loop on AI visibility, a spreadsheet and a few hours of disciplined work will get you a defensible baseline without a purchase order.

State the conflict up front, because it shapes how you should read this. RankRift does not sell an AI-visibility tracker, and this post explains how to do the job in a spreadsheet. The method is published as a complete option, not as a limited demo for an owned product. Our position on this and every other conflict is on the disclosure page. There is no product pitch below.

What you can genuinely learn without a tool

A spreadsheet baseline answers a narrow set of questions well:

• Do assistants name us at all when buyers ask category questions?
• Which competitors show up more often than we do, and on which kinds of question?
• Which sources do the assistants cite when they answer?
• Is our absence broad, or concentrated in one stage of the buying journey?

It answers a different set of questions badly or not at all: small changes over time, statistical confidence at fine resolution, anything requiring daily cadence, and anything requiring more than a handful of markets. It cannot detect a three-point movement. It can detect being invisible in shortlist questions, which is usually the finding that matters at the start.

Manual measurement also has one real advantage over automation, and it disappears the moment you scale: you read every answer. People who read the raw output learn things about their category that no aggregate column will surface.

What you need first

• A frozen prompt set. Do this before anything else. Build a prompt set first covers the construction method, and it is not optional; unversioned prompts make the rest of the exercise unrepeatable.
• Access to the assistants you care about, through official provider APIs where available, or by running prompts by hand in the official interface.
• A spreadsheet. Any of them.
• Two to four uninterrupted hours for the first cycle.

Use official APIs or genuinely manual runs, and nothing else. Automating consumer web interfaces, driving assistant accounts with scripts, or working around rate limits breaks provider terms of service and puts your accounts at risk. It also produces data you cannot publish or defend. If a method feels like it needs to be hidden from the provider, it is not a measurement method.

Setting up the sheet

Use two tabs. One holds raw observations, one row per single run of one prompt against one model. The other holds the calculations.

Tab 1: Runs. Columns, in order:

Tab 2: Metrics. Formulas only, reading from Tab 1. Never type an observation into Tab 2.

Normalise brand names as you record them. "Acme", "Acme Inc", and "acme.com" must become one string, or every count will be wrong. Keep the mapping in a third tab if your category has messy naming.

Running and recording without contaminating your results

Manual measurement is easy to corrupt, usually by accident. The rules:

• Start each run clean. New conversation, no prior turns. Assistants condition on conversation history, so a follow-up question is a different measurement from a fresh one.

• Turn off personalisation features and memory where the provider offers a control, and record which controls were set. An assistant that remembers you work at Acme is not measuring public visibility.

• Do not edit the prompt mid-run. If you notice a typo, finish the set, fix it in v1.1, and note the change.

• Paste the answer whole. Not a summary. Not the part you found interesting.

• Record non-answers. Refusals, empty results, and errors are data. Deleting them biases everything.

• Do not stop when you see a good result. Deciding you have "enough" after a favourable run is the most common self-inflicted bias in this exercise.

• Repeat each prompt several times. These systems are non-deterministic; one run per prompt tells you almost nothing. How many runs you need works through choosing a repetition count you can afford.

• Log the date on every row. If a cycle spans three days, the sheet must know.

Calculating the three core metrics

With Tab 1 populated, three metrics carry most of the load. Define each one in writing next to the formula, because the same word means different things in different tools.

1. Mention rate. The share of runs in which your brand appears at all.

mention_rate = SUM(our_brand_present) / COUNT(run_id)

Slice it by stage and by model. A single blended figure hides the difference between "invisible at discovery" and "invisible everywhere."

2. Share of voice. Your share of all brand mentions across the sample.

share_of_voice = (your brand mention count) / (total brand mentions, all brands)

Count each brand once per run, even if the answer names it three times, or verbose answers will distort the denominator. Record your counting rule.

3. Citation share. Your share of the sources the assistants actually cited.

citation_share = (runs citing your domain) / (total runs with any citation)

Citation and mention move independently, and conflating them is a reporting failure rather than a rounding error. What counts as a citation separates the terms. Which metric answers which question covers when to lead with each.

Also compute a fourth column that costs nothing: the top ten cited domains across your whole sample. In practice this is often the most actionable output of the first cycle, because it tells you which publications, comparison pages, and communities are feeding answers in your category.

Reading the result honestly

The honest reading has a confidence caveat attached, and it belongs in the summary rather than a footnote.

Small samples produce unstable percentages. A mention rate of 30 percent from 40 runs and a mention rate of 30 percent from 400 runs are not the same claim, and the first one should not be compared to last month's figure as if a five-point move meant anything. Report the count alongside every percentage: "12 of 40 runs" is more honest than "30 percent" and harder to over-read.

Three phrasings that keep a spreadsheet baseline defensible:

• "Across 40 runs of prompt set v1.0 on [models], run [dates], we appeared in 12 answers."
• "We cannot detect changes smaller than roughly a third of this sample from data this size."
• "This is a baseline, not a trend. The second cycle is the first point at which a comparison exists, and it will still be noisy."

Resist single-number scoring. Composite "AI visibility scores" feel executive-friendly and hide exactly the detail that makes the number actionable. If a stakeholder insists on one figure, give them mention rate with the run count attached, and put the stage breakdown on the next slide.

Be equally careful in the other direction. A weak result is not proof of a crisis; it may be a prompt-set artifact or a phrasing sensitivity. The diagnostic sequence in why ChatGPT recommends your competitor is the right next read before anyone reorganises a roadmap around the finding.

What a cycle actually costs you

Do this arithmetic before you decide the spreadsheet is free. An illustrative cycle, using a modest set:

Those ranges are illustrative estimates from doing the work by hand, not measured benchmarks. Multiply by your own set size. For a 40-prompt set with five repetitions across three models, you are at 600 runs, which is a full working week per cycle at manual pace. Using official APIs for retrieval collapses the running time and leaves the coding time roughly intact.

The number that matters is cost per cycle multiplied by cycles per year, valued at the loaded hourly rate of whoever does it. Compare that honestly to the alternatives before concluding that free is cheap. Build, buy, or spreadsheet walks that comparison through, including the case where building your own harness on official APIs beats both.

The four signs you have outgrown the spreadsheet

1. You need cadence, not snapshots. Once someone asks "is it moving?", you need runs frequent enough to separate signal from noise, and manual cycles cannot supply that frequency.
2. The coding step is where your judgement varies. When two people label the same answer differently, and nobody has time to write a coding guide, your data has an unmeasured reliability problem.
3. Scope has multiplied. More markets, more languages, more brands, or more models turn a one-week cycle into an impossible one. The workload grows with the product of every dimension.
4. Someone is making budget decisions on it. The moment the number drives spend, it needs an audit trail, version control, and reproducibility that a shared spreadsheet does not reliably provide.

Hitting one of these means tighten the process. Hitting three means the spreadsheet has become the bottleneck, and the right move is either a purpose-built internal harness on official APIs or a vendor. Which of those fits depends on whether you have engineering capacity, how many brands you report on, and how much you care about controlling metric definitions yourself. Agencies reporting on many clients usually reach the vendor threshold first; a single in-house team with a data engineer often does better building their own.

The broader measurement approach, including how the metrics fit together, is on how to measure AI visibility.

In Plain English

You can produce a genuine AI visibility baseline with a frozen prompt set, a two-tab spreadsheet, official APIs or manual runs, and a few hours. Record every run with its model version, date, and full answer. Calculate mention rate, share of voice, and citation share, and report the raw counts next to the percentages. Note the top cited domains, because that list usually tells you where to work next.

This suits anyone who needs evidence before they can request budget, and anyone who wants to understand their category rather than watch a dashboard. Do it now, do it twice, and then decide. Do not automate consumer accounts or scrape assistant interfaces to make it faster; use provider APIs or accept the manual pace. And do not run it once, see a bad number, and treat it as a trend.

Next step: run one full cycle, then read it against the four outgrown signs above. Download the AI visibility scorecard to record the cycle in a structured format rather than inventing columns as you go.

Primary-source verification

Checked July 28, 2026. The operational and factual guidance above was reviewed against the primary references below. Provider features, prices, policies, interfaces, jurisdictions, and account-specific results can change; recheck the controlling first-party document and your own account or measured data before acting.

• Google Search: AI features and your website - How Google describes eligibility, preview controls, and appearance in its AI search features.
• Google: common crawlers and special-case user agents - Current Googlebot and Google-Extended identities and controls.
• OpenAI: overview of OpenAI crawlers - Current GPTBot, OAI-SearchBot, and ChatGPT-User descriptions and publisher controls.
• Anthropic: web-crawler controls - Current ClaudeBot, Claude-SearchBot, and Claude-User descriptions and controls.
• Perplexity: crawler and user-agent documentation - Current PerplexityBot and Perplexity-User descriptions and controls.