---
title: "Build a 40-Prompt AI Visibility Tracker in Google Sheets"
description: "Mine 40 buyer prompts, check ChatGPT, Gemini, and AI Overviews five times each, and use a Google Sheet to find the gaps worth fixing."
url: "https://groas.com/post/build-a-40-prompt-ai-visibility-tracker"
image: "https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/77e6b2af-070e-4948-a9f8-09599b353f3d.png"
published: "2026-10-18T05:27:56.827Z"
modified: "2026-10-18T05:27:56.884Z"
---

October 18, 2026 · 11 min read

# Build a 40-Prompt AI Visibility Tracker in Google Sheets

[Alexander PerelmanHead Of Product @ groas](https://groas.com/author/alexander-perelman)[LinkedIn](https://www.linkedin.com/in/alexander-433793253/)

![A tiny model woman tallies five gumballs from a giant machine, some orange, some blue: the same question gives different answers, so you track the rate.](https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/77e6b2af-070e-4948-a9f8-09599b353f3d.png)

In this article

1. [Before you start: gather buyer language and a blank sheet](#before-you-start-gather-buyer-language-and-a-blank-sheet)
2. [Step 1: Mine 40 prompts from buyer questions](#step-1-mine-40-prompts-from-buyer-questions)
3. [Step 2: Make the ledger strict enough to audit](#step-2-make-the-ledger-strict-enough-to-audit)
4. [Step 3: Give every prompt five fresh runs per engine](#step-3-give-every-prompt-five-fresh-runs-per-engine)
5. [Step 4: Calculate rates from successful answers only](#step-4-calculate-rates-from-successful-answers-only)
6. [Step 5: Turn the summary into a work queue](#step-5-turn-the-summary-into-a-work-queue)
7. [Step 6: Automate only after the manual test makes sense](#step-6-automate-only-after-the-manual-test-makes-sense)
8. [Read the rates like a search terms report](#read-the-rates-like-a-search-terms-report)
9. [Verify the tracker, then change one page](#verify-the-tracker-then-change-one-page)

Run a buyer prompt through ChatGPT once and your brand may appear. Run it again and a competitor may take its place. **One answer is a screenshot, not a baseline.**

I treat AI visibility like a search terms report: collect the language buyers use, run each prompt repeatedly, and act on the rate at which my brand appears. This tutorial builds that system in Google Sheets. You will track **40 prompts across ChatGPT, Gemini, and Google AI Overviews**, with five runs planned for every prompt-engine pair. That is 600 checks per audit cycle, provided each engine returns an answer you can evaluate.

## Before you start: gather buyer language and a blank sheet

Prepare:

- A Google Sheet with tabs named `Prompt Ledger` and `Weekly Summary`.
- Access to ChatGPT with Search enabled, Gemini, and Google Search. Start each test in a fresh, unprimed session; do not carry a conversation between runs.
- Customer questions from CRM notes, sales objections, support tickets, or unbranded Search Console queries. Give yourself about 30 minutes to collect the raw language before you choose prompts.

The sheet itself is straightforward. **The manual sampling is the work.** Do not expect to complete 600 careful checks in the time it takes to set up the tabs.

At the end, you will have a mention rate, a citation rate, and a view of the commercial prompts where competitors appear and you do not. Those are observations from your runs, not a census of everything an engine might say.

## Step 1: Mine 40 prompts from buyer questions

**Action:** In Google Search Console, open `Performance > Search results`, set the date range to `Last 3 months`, select `Export`, and download the query data. Look for questions and comparison language such as `how`, `what`, `best`, `vs`, and `alternative`. Cross-check those themes against ten raw objections from lost-deal notes or sales conversations. Keep the buyer’s wording where you have it; do not tidy every question into the same keyword template. Separate unbranded discovery from branded validation, as the [brand visibility auditing framework](https://ranking-atlas.com/blog/measure-brand-visibility-ai-search) suggests.

![A printed prompt audit sheet with coloured tags marking buyer-question categories.](https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/1f424d2b-7c59-49fa-ad25-208ba78b9dda.png)

Put your chosen prompts in four buckets:

1. **Category — 12 prompts:** Unbranded purchase questions, such as `best autonomous google ads software for ecommerce brands spending 20k`.
2. **Problem — 12 prompts:** Operational questions, such as `how to fix rising cpc in pmax campaigns without cutting budget`.
3. **Comparison — 10 prompts:** Head-to-head questions, such as `competitor a vs competitor b for b2b lead tracking`.
4. **Branded — 6 prompts:** Questions about your own company, such as `what is groas pricing model and does it replace agency retainers`.

**Expected result:** A fixed set of 40 prompts you can run again next week without quietly changing the test. Keep a scratch copy of their category assignments; you will enter them in the ledger.

**Mistake:** Asking a model to `Write 40 prompts people use to find my product` and calling the output buyer research. It may give you polished versions of questions nobody asks. Operators in a [digital marketing discussion](https://www.reddit.com/r/digital_marketing/comments/1x5mrck/what_is_the_best_tool_to_track_and_improve/) make the same objection to synthetic prompt lists. Real buyer language can be long and awkward; [this buyer-prompt example](https://groas.com/post/somebody-pasted-a-1-000-character-buyer) is a useful reminder not to sand off the constraints.

## Step 2: Make the ledger strict enough to audit

**Action:** Open `Prompt Ledger`. Paste these tab-separated headers into cell `A1`; Google Sheets will place them across columns A through K.

```text
Date	Category	Prompt_Text	Engine	Run_Number	Run_Status	Brand_Mentioned	Brand_Cited	Owned_URL_Cited	Competitors_Named	Top_External_Source
```

Select column B, then use `Data > Data validation > Add rule` to create a drop-down containing `Category`, `Problem`, `Comparison`, and `Branded`. Repeat for column D with `ChatGPT`, `Gemini`, and `AI Overviews`; column F with `Successful` and `Failed`; and columns G and H with `Yes` and `No`. In column E, use run numbers `1` through `5`.

Use `Brand_Mentioned` when the answer names your company. Use `Brand_Cited` when it provides a clickable citation to your owned domain, and put that URL in `Owned_URL_Cited`. Record competitor names in column J exactly as they appear, separated by commas if an answer names more than one. Keep a URL or source name in column K when an external source matters to your later diagnosis.

**Expected result:** One row can describe one prompt, on one engine, on one run, without forcing you to infer later whether `Y`, `yes`, and `true` meant different things.

**Mistake:** Treating a mention and a citation as one event. An answer can name your brand without linking your site. It can also cite your site while directing the buyer toward somebody else. Keep those observations separate.

## Step 3: Give every prompt five fresh runs per engine

**Action:** Run each of the 40 prompts five times in ChatGPT with Search enabled, five times in Gemini, and five times in Google Search when an AI Overview appears. Start each run in a new, unprimed session rather than continuing the previous chat. Enter its date, category, exact prompt, engine, and run number in the ledger before you score the answer.

If you get an answer you can assess, set `Run_Status` to `Successful` and complete the mention, citation, and competitor fields. If the session fails or Google Search does not show an AI Overview, set it to `Failed`; do not score missing output as `No`. That distinction matters when you calculate the denominator.

**Expected result:** Five independently recorded attempts per prompt-engine pair: 600 planned rows in all. Five successes give you a rate you can read as `0/5` through `5/5`. Fewer successes tell you the sample is incomplete, not that your visibility fell.

**Mistake:** Running all five tests in one chat. After the first answer, the conversation itself becomes context. If your brand appears in run one, later answers may reflect that context rather than the original prompt. Repeated sampling is useful only if you avoid priming the next run. The [multi-engine tracking workflow](https://open.substack.com/pub/buildtheworkflow/p/build-your-own-ai-visibility-tracker?utm_source=post\&comments=true\&utm_medium=web) makes the case for repeated checks; a single-probe score hides the variation those checks reveal.

## Step 4: Calculate rates from successful answers only

**Action:** Open `Weekly Summary` and paste these headers into `A1`:

```text
Prompt_Text	Category	ChatGPT_Mention_%	ChatGPT_Cite_%	Gemini_Mention_%	Gemini_Cite_%	AIO_Mention_%	AIO_Cite_%	Top_Competitor	Top_Competitor_Rate	Action_Required
```

Put one of your 40 exact prompts in each row of column A, starting at `A2`, and its bucket in column B. In `C2`, enter this ChatGPT mention-rate formula:

```text
=IFERROR(COUNTIFS('Prompt Ledger'!$C:$C,$A2,'Prompt Ledger'!$D:$D,"ChatGPT",'Prompt Ledger'!$G:$G,"Yes",'Prompt Ledger'!$F:$F,"Successful")/COUNTIFS('Prompt Ledger'!$C:$C,$A2,'Prompt Ledger'!$D:$D,"ChatGPT",'Prompt Ledger'!$F:$F,"Successful"),"")
```

Copy it into `D2` and change `$G:$G` to `$H:$H` to calculate citation rate. For `E2:F2`, use those same two formulas but replace `"ChatGPT"` with `"Gemini"`. For `G2:H2`, replace it with `"AI Overviews"`. Fill all six columns down through your 40 prompts, then format `C:H` as percentages. This follows the [successful-run denominator](https://snezzi.com/resources/ai-visibility-tracker-template-2026/) rather than counting failed checks as negative answers.

![Diagram of successful prompt runs flowing into separate mention and citation rates.](https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/6b413428-2645-4ed8-8ac9-a6f3656d1b27.png)

For competitor rate, inspect the successful ledger rows for each prompt and enter the most frequently named rival in column I. Put its share of successful rows in column J. If a rival appears in 8 of 15 successful answers, enter `8/15` and format the cell as a percentage. Count answers that name the rival, not the number of times its name appears in one answer. Keep the comma-separated names in the ledger as your audit trail; a pivot table would group each complete comma-separated cell, not reliably count each rival.

**Expected result:** Separate mention and citation rates for each engine, plus a competitor rate you can trace back to individual answers. An empty rate means there were no successful answers for that prompt-engine pair; it does not mean `0%` visibility.

**Mistake:** Averaging branded and unbranded prompts into one attractive number. If an engine names you every time you put your name in the question but never names you in category prompts, the branded result has not cancelled out the discovery gap. Read rates by bucket before you compare them.

## Step 5: Turn the summary into a work queue

**Action:** In `Weekly Summary`, apply conditional formatting to `C:H`: mark `0%` red, rates above `0%` through `60%` yellow, and rates above `60%` green. Leave blank cells uncoloured. Filter to `Category`, `Problem`, or `Comparison` prompts where your mention rate is `0%` on a relevant engine, then sort column J from highest to lowest. In `Action_Required`, write the next check for that prompt: for example, `inspect cited competitor URL` or `review owned page for direct answer`.

**Expected result:** A short list of commercial questions where a rival appears repeatedly and your brand does not, with the underlying answers still available in the ledger. **That is the useful view**, not a single account-wide visibility score.

**Mistake:** Sorting the sheet alphabetically and treating it as a filing exercise. I want to know which buyer questions repeatedly send attention elsewhere. The colour helps me find them; the ledger tells me what to inspect.

## Step 6: Automate only after the manual test makes sense

**Action:** If you want scheduled collection, start with `Extensions > Apps Script` in your Sheet and plan a trigger for the checks an API can actually perform. For ChatGPT or Gemini probes intended to reflect live information, use the available web-search or search-grounding capability rather than assuming a plain model call behaves like the browser experience. Keep the manual Google AI Overviews checks separate; do not relabel a model API response as an AI Overview.

At this cadence, 40 prompts multiplied by five runs means **200 probes per API engine per cycle**. Across two API engines, that is 400 probes per cycle, before you account for the manual AI Overviews checks. Price the setup you intend to use, including maintenance, before calling it cheaper than your time.

**Expected result:** A clear decision about which checks you can repeat automatically and which still need a browser and a human reviewer. The Sheet remains the place where you can inspect what was actually measured.

**Mistake:** Buying an alert dashboard before deciding what action its alerts will trigger. Commercial tools have different prompt limits and prices, as this [monitoring-software comparison](https://humanswith.ai/blog/ai-visibility-tools-2026-10-platforms-compared-with-pricing/) shows. First decide [whether an AEO tool will help you get cited or merely report that you are missing](https://groas.com/post/will-this-tool-actually-get-us-cited-or). A scheduled check is not an improvement to the page it checks.

## Read the rates like a search terms report

Open your highest-priority rows and read the answers, not just the percentages. Three patterns tell you where to look next:

- **No mention, frequent rival mention — _the content void_.** On a category prompt, your brand is absent while a rival appears in most successful runs. Inspect what the answers say and cite. Look for a buyer question your existing pages fail to answer directly. Do not diagnose an indexing fault from a mention rate alone.
- **Brand mentioned, owned site not cited — _the citation gap_.** The answer knows your name but links somewhere else. Check whether your owned page gives the engine a specific, usable answer: pricing information, technical detail, a comparison, or evidence it can cite. Repeating your product name is not a substitute for that substance.
- **Two mentions in five runs — _the threshold zone_.** A `40%` mention rate says the result is inconsistent in this small sample. Inspect the two answers that include you and the three that do not before changing a page. Do not promise yourself that one edit will turn `2/5` into `4/5`.

![Illustration of a scale comparing keyword repetition with citable detail.](https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/1a4e8fc3-0e37-4d62-b7c7-5049900a9035.png)

I used to want one clean score for this kind of report. It makes a slide easier to build and a decision harder to make. **The useful unit is the prompt, its successful-run count, and the answer behind the rate.**

## Verify the tracker, then change one page

Run two control prompts through the same process: an ultra-specific branded question, such as your company name plus `pricing model`, and a generic industry question on which you expect a familiar rival to appear. Check the ledger against the summary by hand. If your brand appears in four of five successful branded answers, the displayed rate should be `80%`, not `100%`; a failed sixth attempt, if you log one, should not change it. Confirm that cited URLs sit in column I, competitor names are spelled consistently, and blank engine results remain blank in the summary rather than displaying `#DIV/0!`.

Do not require either control to produce a predetermined answer. **You are verifying the arithmetic and the logging, not proving that an engine agrees with your expectation.** If a rate looks wrong, check the exact prompt text, engine label, and `Successful` spelling before you draw a conclusion from it.

Once the sheet checks out, pick one unbranded category prompt where a competitor appears in at least four of five successful runs and your brand appears in none. Open the URL the answer cites. Compare the question it answers with your closest existing page, then revise that page to answer the buyer’s question directly with the pricing, specifications, use-case boundaries, or comparison details you can substantiate. Run the same five-prompt check again in fresh sessions. Keep the original rows so you can see whether the rate changed, rather than replacing an inconvenient baseline.

The Sheet tells you where to work; it does not do the work. If you are deciding who should own the next step, compare [DIY checks, visibility trackers, and done-for-you AEO](https://groas.com/post/diy-script-vs-visibility-tracker-vs-done). Traditional setups can leave one team reading the report and another waiting to make the change. [groas](https://groas.com/) takes the opposite approach: autonomous execution across paid and organic search, with specialized AI models working continuously under business guardrails and a named human strategist accountable for direction. Build the tracker to find the gap. Then close one gap and run the prompt again.

## Frequently Asked Questions

### Why do I need to run the same prompt multiple times when checking AI search engines?

One answer is a screenshot, not a baseline. ChatGPT may name your brand on one run and replace it with a competitor on the next, so the useful signal is the rate at which your brand appears across repeated runs, not a single result.

### Where should the 40 prompts for an AI visibility tracker come from?

Mine them from real buyer language: unbranded Search Console queries, lost-deal notes, sales objections, and support tickets. Sort the final 40 into four buckets: 12 category prompts, 12 problem prompts, 10 comparison prompts, and 6 branded prompts.

### Can I just ask ChatGPT to write 40 prompts people use to find my product?

No. Model-generated prompts are polished versions of questions nobody actually asks. Real buyer language can be long and awkward, so keep the buyer's original wording from Search Console, sales notes, or support tickets instead of tidying everything into a keyword template.

### What is the difference between a brand mention and a brand citation?

A mention is when an answer names your company; a citation is when it provides a clickable link to your owned domain. They are separate events: an answer can name your brand without linking your site, or cite your site while pointing the buyer toward somebody else.

### Should failed runs count as zero visibility when tracking AI search results?

No. Log a failed session or a missing AI Overview as Failed, not as a No answer. Rates are calculated from successful answers only, so the denominator contains only runs that produced an answer you can evaluate. An empty rate means no successful answers, not 0% visibility.

### Should I average all my prompt results into one overall AI visibility score?

No. Averaging branded and unbranded prompts hides the discovery gap: an engine may name you every time you put your name in the question while never naming you in category prompts. Read mention and citation rates by bucket before comparing anything.

### What should I look at after I have mention and citation rates in my tracker?

Read the actual answers behind the rates and check three patterns. A content void means your brand is absent while a rival appears in most runs. A citation gap means the answer knows your name but links elsewhere. A threshold zone, such as 2 in 5 runs, means results are inconsistent and you should inspect both the included and excluded answers before changing a page.

### Can I automate my AI visibility checks with the ChatGPT and Gemini APIs?

Yes, but only for the engines where an API can do the job. Use web-search or search-grounding capability for ChatGPT and Gemini probes rather than a plain model call, and keep Google AI Overviews checks manual, since a model API response is not an AI Overview. Automate only after the manual test makes sense.

## Pay For Results, Not For Hours

Businesses buy the outcome, agencies resell it, and groas answers for it either way.

[See If You Qualify](https://groas.typeform.com/to/xC1bQNUT)

## Structured data

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://graph.groas.com/entity/groas","name":"groas","url":"https://groas.com/","logo":{"@type":"ImageObject","url":"https://groas.com/icon-512.png","width":512,"height":512},"sameAs":["https://www.linkedin.com/company/groas/"]},{"@type":"WebSite","@id":"https://groas.com/#website","name":"groas","url":"https://groas.com/","inLanguage":"en-US","publisher":{"@id":"https://graph.groas.com/entity/groas"}},{"@type":"WebPage","@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#webpage","url":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker","name":"Build a 40-Prompt AI Visibility Tracker in Google Sheets","description":"Mine 40 buyer prompts, check ChatGPT, Gemini, and AI Overviews five times each, and use a Google Sheet to find the gaps worth fixing.","inLanguage":"en-US","isPartOf":{"@id":"https://groas.com/#website"},"about":{"@id":"https://graph.groas.com/entity/groas"},"primaryImageOfPage":{"@type":"ImageObject","url":"https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/77e6b2af-070e-4948-a9f8-09599b353f3d.png"},"breadcrumb":{"@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#breadcrumb"},"dateModified":"2026-10-18T05:27:56.884Z","mainEntity":{"@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#article"}},{"@type":"BreadcrumbList","@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://groas.com/"},{"@type":"ListItem","position":2,"name":"Blog","item":"https://groas.com/blog"},{"@type":"ListItem","position":3,"name":"Build a 40-Prompt AI Visibility Tracker in Google Sheets","item":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker"}]},{"@type":"BlogPosting","@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#article","url":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker","mainEntityOfPage":{"@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#webpage"},"isPartOf":{"@id":"https://groas.com/blog#blog"},"headline":"Build a 40-Prompt AI Visibility Tracker in Google Sheets","description":"Mine 40 buyer prompts, check ChatGPT, Gemini, and AI Overviews five times each, and use a Google Sheet to find the gaps worth fixing.","datePublished":"2026-10-18T05:27:56.827Z","dateModified":"2026-10-18T05:27:56.884Z","image":{"@type":"ImageObject","url":"https://pub-87da24ecbbfc4c3bad6875f3aa013712.r2.dev/generated-images/77e6b2af-070e-4948-a9f8-09599b353f3d.png"},"author":{"@id":"https://groas.com/author/alexander-perelman#person"},"publisher":{"@id":"https://graph.groas.com/entity/groas"},"keywords":"prompts, prompt, google, chatgpt, run, each, visibility, five","wordCount":2290,"inLanguage":"en-US"},{"@type":"Person","@id":"https://groas.com/author/alexander-perelman#person","name":"Alexander Perelman","jobTitle":"Head Of Product @ groas","description":"Ex Goldman Sachs and Ex Stanford Computer Science","image":"https://groas.com/media/blog/4f17cac3a81acc224cc1b2cfab8f94bbc295a3089d13e8421125771ef8d4064f.jpg","worksFor":{"@id":"https://graph.groas.com/entity/groas"},"sameAs":["https://www.linkedin.com/in/alexander-433793253/"]},{"@type":"FAQPage","@id":"https://groas.com/post/build-a-40-prompt-ai-visibility-tracker#faq","mainEntity":[{"@type":"Question","name":"Why do I need to run the same prompt multiple times when checking AI search engines?","acceptedAnswer":{"@type":"Answer","text":"One answer is a screenshot, not a baseline. ChatGPT may name your brand on one run and replace it with a competitor on the next, so the useful signal is the rate at which your brand appears across repeated runs, not a single result."}},{"@type":"Question","name":"Where should the 40 prompts for an AI visibility tracker come from?","acceptedAnswer":{"@type":"Answer","text":"Mine them from real buyer language: unbranded Search Console queries, lost-deal notes, sales objections, and support tickets. Sort the final 40 into four buckets: 12 category prompts, 12 problem prompts, 10 comparison prompts, and 6 branded prompts."}},{"@type":"Question","name":"Can I just ask ChatGPT to write 40 prompts people use to find my product?","acceptedAnswer":{"@type":"Answer","text":"No. Model-generated prompts are polished versions of questions nobody actually asks. Real buyer language can be long and awkward, so keep the buyer's original wording from Search Console, sales notes, or support tickets instead of tidying everything into a keyword template."}},{"@type":"Question","name":"What is the difference between a brand mention and a brand citation?","acceptedAnswer":{"@type":"Answer","text":"A mention is when an answer names your company; a citation is when it provides a clickable link to your owned domain. They are separate events: an answer can name your brand without linking your site, or cite your site while pointing the buyer toward somebody else."}},{"@type":"Question","name":"Should failed runs count as zero visibility when tracking AI search results?","acceptedAnswer":{"@type":"Answer","text":"No. Log a failed session or a missing AI Overview as Failed, not as a No answer. Rates are calculated from successful answers only, so the denominator contains only runs that produced an answer you can evaluate. An empty rate means no successful answers, not 0% visibility."}},{"@type":"Question","name":"Should I average all my prompt results into one overall AI visibility score?","acceptedAnswer":{"@type":"Answer","text":"No. Averaging branded and unbranded prompts hides the discovery gap: an engine may name you every time you put your name in the question while never naming you in category prompts. Read mention and citation rates by bucket before comparing anything."}},{"@type":"Question","name":"What should I look at after I have mention and citation rates in my tracker?","acceptedAnswer":{"@type":"Answer","text":"Read the actual answers behind the rates and check three patterns. A content void means your brand is absent while a rival appears in most runs. A citation gap means the answer knows your name but links elsewhere. A threshold zone, such as 2 in 5 runs, means results are inconsistent and you should inspect both the included and excluded answers before changing a page."}},{"@type":"Question","name":"Can I automate my AI visibility checks with the ChatGPT and Gemini APIs?","acceptedAnswer":{"@type":"Answer","text":"Yes, but only for the engines where an API can do the job. Use web-search or search-grounding capability for ChatGPT and Gemini probes rather than a plain model call, and keep Google AI Overviews checks manual, since a model API response is not an AI Overview. Automate only after the manual test makes sense."}}]}]}
```