Building an AI Search Visibility & Brand Analyzer with Gemini, BigQuery, and Google Search Grounding

AI Search Journey Lab — Part 3 of 7

Measuring how brands appear across AI search journeys using Gemini, Google Search grounding, deterministic visibility scans, and BigQuery history.

Open source: ai-search-journey-lab

Previous: Building Grounded Local Search with Gemini and Google Maps on Google Cloud

---

The first two articles answered a user question. This one measures the system itself.

In Part 1, I focused on the search journey:

How does one natural-language request become a grounded decision?

In Part 2, I went deeper into the actual local-search implementation:

How do Gemini, Google Places, Search grounding, deterministic ranking, and Maps work together?

Once that worked, I started asking a different question.

Suppose I run the same kind of search repeatedly.

What happens if instead of only asking:

Which business should the user choose?

I ask:

Which brands keep appearing?
Which brands are cited?
Which competitors show up more often?
Does a brand appear in the initial answer, the fan-out journey, or only in supporting evidence?
How does that change over time?

That was the starting point for V3 — AI Visibility.

This article is about turning an AI search workflow into something measurable.

---

Why AI visibility needs a different data model

A single search result is transient.

If I want to measure visibility, I need history.

One run is interesting.

Fifty runs are analyzable.

Five hundred runs can start showing patterns.

So V3 introduced a new concern into the project: persistence

That is where BigQuery enters the architecture.

---

What I wanted to measure

For each visibility run, I wanted to capture things such as:

• target brand
• competitors
• original query
• fan-out tasks
• mentions
• citations
• candidate coverage
• source coverage
• ranking position
• execution timestamp
• historical trend

The important shift was this:

V1/V2:
user query → recommendation

V3:
many queries → repeatable measurements

That turns the project from a search demo (which we have planned) into an analytics system. :)

---

Architecture

---

Step 1: I made visibility scans deterministic

One important design choice was that V3 should not rely on a user manually clicking around and interpreting the results.

I wanted repeatable runs.

That means the runner should accept an explicit input scope and execute the same workflow consistently.

Conceptually:

run = VisibilityRun(
    target_brand="Example Brand",
    competitors=[
        "Competitor A",
        "Competitor B",
    ],
    queries=[
        "best local coffee shops in San Antonio",
        "quiet coffee shop for remote work",
    ],
)

Then the runner executes the scan and records the results.

The goal is reproducibility.

If I run the same scenario tomorrow, I want to compare the resulting data rather than rely on screenshots or memory.

---

Step 2: Separate mention detection from visibility scoring

A brand appearing in a response is not the same as a brand being strongly visible.

For example:

Target brand:
RankRabbit

Response:
"Other platforms include RankRabbit, Competitor A, and Competitor B."

That is a mention.

But now compare:

1. RankRabbit — recommended first
2. Competitor A
3. Competitor B

Those two appearances should not necessarily have the same visibility weight.

So I separate several concepts:

• mention presence
• position
• citation presence
• fan-out coverage
• narrative prominence

That lets the application calculate visibility metrics explicitly instead of collapsing everything into one LLM judgment.

---

Step 3: Capture evidence before calculating visibility

The workflow should preserve what caused a brand to be counted.

Conceptually:

{
  "brand": "Example Brand",
  "mentioned": true,
  "rank": 2,
  "citation_present": true,
  "fanout_coverage": 0.67,
  "evidence_sources": [
    "source-a",
    "source-b"
  ]
}

That data is much more valuable later than a single score such as:

visibility_score = 74

because it lets me explain where the score came from.

---

Step 4: Persist visibility history in BigQuery

V3 can run in-memory for a quick demo, but historical analysis needs persistence.

I used a BigQuery dataset for that purpose:

Project:
ai-search-journey-lab

Dataset:
ai_search_journey_v3

Let's explore how bigQuery tables look like.....

bq query \
  --use_legacy_sql=false \
  '
  SELECT
    table_name,
    table_type,
    creation_time
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.INFORMATION_SCHEMA.TABLES`
  ORDER BY
    table_name
  '

---

Step 5: Query historical visibility with SQL

Once I started persisting V3 runs in BigQuery, I no longer had to rely only on what the Streamlit dashboard displayed.

I could query the visibility history directly from the terminal.
That became one of my favorite parts of V3 because it gave me a second way to validate everything the UI was showing.

For all of the examples below, I use the BigQuery CLI:

Once scan results are persisted, SQL becomes one of the most useful tools in the project.

BigQuery dataset showing the persisted visibility-analysis tables.

The four tables I use most for visibility analysis are:

visibility_scans
brand_observations
fanout_observations
citations

Each answers a different question.

visibility_scans
    └── What happened during the scan?

brand_observations
    └── How did each brand appear?

fanout_observations
    └── Where was the brand found during query fan-out?

citations
    └── Which sources supported the grounded result?

That separation became important as V3 grew.

I did not want one oversized table containing execution metadata, brand observations, fan-out evidence, citations, and historical measurements all mixed together.

Inspect the actual schema

Before writing analytical queries, I wanted to verify the exact schema from the terminal.

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    table_name,
    ordinal_position,
    column_name,
    data_type
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.INFORMATION_SCHEMA.COLUMNS`
  WHERE
    table_name IN (
      "visibility_scans",
      "brand_observations",
      "fanout_observations",
      "citations"
    )
  ORDER BY
    table_name,
    ordinal_position
  '

This is useful because the schema itself reflects the measurements I wanted to preserve.

For example, brand_observations contains separate fields for:

mentioned
mention_count
first_mention_position

retrieved
best_retrieval_position

recommended
recommendation_position

cited
citation_urls
citation_domains

That was deliberate.

I wanted to store what actually happened before reducing everything to a single visibility number.

Query recent visibility scans

The first operational query I usually run is simple:

Show me the latest scans

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    batch_id,
    project_id,
    brand_id,
    brand_name_snapshot,
    prompt_id,
    model_name,
    location_snapshot,
    started_at,
    completed_at,
    status,
    duration_seconds
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.visibility_scans`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  ORDER BY
    started_at DESC
  LIMIT 20
  '

Recent AI visibility scans queried directly from BigQuery using the bq CLI.

The time filter is important.

My visibility tables are partitioned by started_at, and I intentionally require partition filtering rather than allowing unrestricted scans over the complete history.

That becomes increasingly useful as the amount of historical data grows.

From this one query I can inspect:

• which scan ran
• which batch it belonged to
• which brand was evaluated
• which prompt triggered it
• which Gemini model was used
• which location context was applied
• when the scan started and completed
• whether it succeeded
• how long it took

The dashboard gives me the visualization.

BigQuery gives me the underlying record.

---

Step 6: Mentioned is not the same as recommended

This became one of the most important distinctions in V3.

A brand can appear in an AI-generated result in several different ways.

It might be:

• retrieved
• mentioned
• recommended
• cited
• ranked before or after another brand

Those are not the same thing.

Let's consider these two observations:

retrieved = true
mentioned = true
recommended = false
cited = true

and

retrieved = true
mentioned = true
recommended = true
cited = true

Both brands were visible.

But they were not visible in the same way.

The first brand was discovered and mentioned.

The second made it into the recommendation.

Instead of asking Gemini to generate a vague visibility score, I first persist these observable states.

I can inspect them directly:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    brand_id,
    role,
    mentioned,
    mention_count,
    first_mention_position,
    retrieved,
    best_retrieval_position,
    recommended,
    recommendation_position,
    cited,
    citation_domains
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.brand_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  ORDER BY
    started_at DESC,
    scan_id,
    first_mention_position
  LIMIT 50
  '

I can ask:

Was the brand retrieved?

Was it mentioned?

Where did the first mention occur?

Was it actually recommended?

At what recommendation position?

Was there citation evidence associated with it?

Those questions are much easier to reason about than:

visibility_score = 74

without knowing why the score is 74.

---

Step 7: Aggregate visibility across repeated scans

Once multiple scans exist, SQL becomes even more useful.

For example, I can ask:

How often does each brand appear, get recommended, or receive citation support?

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    brand_id,
    COUNT(*) AS observations,
    COUNTIF(mentioned) AS mentioned_count,
    COUNTIF(retrieved) AS retrieved_count,
    COUNTIF(recommended) AS recommended_count,
    COUNTIF(cited) AS cited_count,
    ROUND(
      SAFE_DIVIDE(COUNTIF(mentioned), COUNT(*)) * 100,
      2
    ) AS mention_rate_pct,
    ROUND(
      SAFE_DIVIDE(COUNTIF(recommended), COUNT(*)) * 100,
      2
    ) AS recommendation_rate_pct
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.brand_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    brand_id
  ORDER BY
    recommendation_rate_pct DESC,
    mention_rate_pct DESC
  '

Now I am no longer asking only:

What did Gemini answer?

I can ask:

Across repeated grounded search journeys, which brands keep appearing?

That is a very different problem.

And it is where V3 started becoming an analytics system rather than only a search demo.

Aggregating brand mentions, retrievals, recommendations, and citations across repeated scans

Let's be on the same page.

Position matters too
Presence alone is not enough.

A brand appearing first in an answer is different from appearing sixth.

That is why V3 also preserves:

first_mention_position
best_retrieval_position
recommendation_position

I can analyze those separately:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    brand_id,
    COUNTIF(mentioned) AS mentions,
    ROUND(AVG(first_mention_position), 2) AS avg_first_mention_position,
    COUNTIF(recommended) AS recommendations,
    ROUND(AVG(recommendation_position), 2) AS avg_recommendation_position,
    ROUND(AVG(best_retrieval_position), 2) AS avg_best_retrieval_position
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.brand_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    brand_id
  ORDER BY
    recommendations DESC,
    avg_recommendation_position ASC
  '

This lets me separate several ideas that often get grouped together as AI visibility:

Presence
Position
Retrieval
Recommendation
Citation

I find that separation much more useful.

---

Step 8: Look inside query fan-out

One thing I learned while building the earlier versions of this project is that the final answer hides a lot of interesting behavior.

A single user query can create several retrieval tasks.

For example:

User prompt
    │
    ├── search intent A
    ├── search intent B
    ├── search intent C
    └── search intent D

A brand may appear in all four branches.

Or only one.

Those situations should not necessarily be treated as identical.

That is why V3 persists fanout_observations.

The table includes fields such as:

task_id
query_text
tool
brand_id
brand_found
position_in_task

I can inspect those observations directly:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    prompt_id,
    task_id,
    query_text,
    tool,
    brand_id,
    brand_found,
    position_in_task
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.fanout_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  ORDER BY
    started_at DESC,
    scan_id,
    task_id,
    position_in_task
  LIMIT 100
  '

Now the intermediate journey becomes queryable:

Original prompt
      ↓
Fan-out task
      ↓
Generated query
      ↓
Retrieval tool
      ↓
Brand found?
      ↓
Position in task

This gives me a way to investigate why one brand appears repeatedly while another does not.

Measure fan-out coverage

I can also aggregate the fan-out behavior.

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    brand_id,
    COUNT(DISTINCT task_id) AS fanout_tasks,
    COUNTIF(brand_found) AS observations_where_brand_was_found,
    ROUND(
      SAFE_DIVIDE(
        COUNTIF(brand_found),
        COUNT(*)
      ) * 100,
      2
    ) AS found_rate_pct
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.fanout_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    brand_id
  ORDER BY
    found_rate_pct DESC
  '

I think of this as a project-specific fan-out coverage signal.

It is not intended to be a universal AI visibility metric.

It simply tells me how consistently a brand was found across the retrieval observations generated by this implementation.

Being explicit about that distinction matters.

---

Step 9: Make citation visibility queryable

Mentions are useful.

Recommendations are useful.

But I also wanted to understand the evidence behind the grounded answer.

So citations are persisted separately.

The citations table contains:

scan_id
project_id
prompt_id
started_at
url
domain
source_type
matched_brand_ids

I can inspect the underlying evidence directly:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    prompt_id,
    domain,
    source_type,
    url,
    matched_brand_ids
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.citations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  ORDER BY
    started_at DESC
  LIMIT 100
  '

This leads to another interesting question:

Which domains repeatedly appear as supporting sources?

That gives me another dimension of AI visibility.

Instead of only asking:

Was my brand mentioned?

I can also ask:

Which sources repeatedly support grounded answers?

Which domains are connected to particular brands?

Which sources appear across multiple scans?

For me, that makes citation analysis one of the more interesting parts of V3.

Working with repeated fields in BigQuery

matched_brand_ids is stored as an ARRAY<STRING>.

BigQuery makes it straightforward to expand that repeated field using UNNEST.

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    matched_brand_id AS brand_id,
    domain,
    COUNT(*) AS citation_count
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.citations`,
    UNNEST(matched_brand_ids) AS matched_brand_id
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    matched_brand_id,
    domain
  ORDER BY
    citation_count DESC
  LIMIT 50
  '

That connects two useful concepts:

Brand visibility
        +
Citation visibility

A brand may appear frequently but have relatively little citation support.

Another may appear less often but repeatedly connect to a particular set of sources.

Persisting both lets me investigate that difference instead of hiding it inside one aggregate score.

---

Step 10: Turn visibility into a historical signal

The reason I chose BigQuery was not just to persist individual runs.

The real value appears when the same measurements accumulate over time.

For example, I can calculate daily visibility observations:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    DATE(started_at) AS scan_date,
    brand_id,
    COUNT(*) AS observations,
    COUNTIF(mentioned) AS mentions,
    COUNTIF(recommended) AS recommendations,
    COUNTIF(cited) AS citations,
    ROUND(
      SAFE_DIVIDE(COUNTIF(mentioned), COUNT(*)) * 100,
      2
    ) AS mention_rate_pct,
    ROUND(
      SAFE_DIVIDE(COUNTIF(recommended), COUNT(*)) * 100,
      2
    ) AS recommendation_rate_pct
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.brand_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    scan_date,
    brand_id
  ORDER BY
    scan_date DESC,
    brand_id
  '

Now the system evolves from:

Search Journey
      ↓
One Answer

into:

Search Journey
      ↓
Repeated Scans
      ↓
Persisted Observations
      ↓
Historical Visibility

That historical layer is what I was missing in the first versions of the project.

A single answer can tell me what happened now.

A history can start telling me what keeps happening.

---

Step 11: The scan itself is also observable

Another useful part of the schema is that visibility_scans does not only record the result.

It also records operational information such as:

started_at
completed_at
status
duration_seconds
error_code
error_message

That means I can inspect the behavior of the visibility pipeline itself.

For example:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    status,
    COUNT(*) AS scans,
    ROUND(AVG(duration_seconds), 2) AS avg_duration_seconds,
    ROUND(MAX(duration_seconds), 2) AS max_duration_seconds
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.visibility_scans`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY
    status
  ORDER BY
    scans DESC
  '

And I can inspect unsuccessful runs separately:

bq query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    brand_name_snapshot,
    prompt_text_snapshot,
    model_name,
    started_at,
    status,
    error_code,
    error_message
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.visibility_scans`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND status != "completed"
  ORDER BY
    started_at DESC
  LIMIT 25
  '

This distinction became useful later when I added deeper observability to the project.

There are really two different things I want to observe:

What did the AI search journey produce?

and

How did the system behave while producing it?

V3 primarily focuses on the first.

Later versions go much deeper into the second.

---

Step 12: Keep BigQuery as the analytical source of truth

At this point, the V3 architecture became much clearer to me.

                    AI Visibility Scan
                           │
                           ▼
                 Gemini + Search Grounding
                           │
                           ▼
                    Evidence Extraction
                           │
              ┌────────────┼────────────┐
              │            │            │
              ▼            ▼            ▼
        Brand          Fan-out       Citation
      Observation    Observation     Evidence
              │            │            │
              └────────────┼────────────┘
                           │
                           ▼
                        BigQuery
                           │
                 ┌─────────┴─────────┐
                 ▼                   ▼
          Streamlit Dashboard    SQL Analysis
                 │                   │
                 └─────────┬─────────┘
                           ▼
                Historical Visibility

BigQuery gives me an independent analytical layer.

That means I can:

• validate what the dashboard shows
• write new analytical queries without changing the UI
• compare historical runs
• inspect fan-out behavior
• study citation sources
• later expose the same evidence to an agent

That last part becomes important in V4.

---

Step 13: Validate the implementation, not just the demo

I try not to treat a working Streamlit page as proof that the underlying application is healthy.

Before considering a milestone complete, I run the repository checks together.

./.venv/bin/ruff check . && \
./.venv/bin/mypy src && \
./.venv/bin/pytest -q

At the time of this build, the repository validation looked like:

ruff
All checks passed!

mypy
Success: no issues found in 38 source files

pytest
382 passed

The exact number of tests will naturally change as the project continues to grow.

What matters more to me is keeping the same habit:

Build
  ↓
Run
  ↓
Inspect
  ↓
Test
  ↓
Then extend

That has become especially important as this project has expanded from one search workflow into multiple versions with analytics, agents, and observability.

---

From analytics to an agent

Once V3 was working, I found myself doing something repeatedly.

I would open the dashboard.

Then I would run a BigQuery query.

Then another.

Then I would compare brands.

Then citation domains.

Then fan-out behavior.

Eventually the next question became obvious:

What if I could ask an agent questions about this visibility history instead of manually writing every query?

But I did not want an unconstrained agent with arbitrary access to the analytics store.

I wanted something much more controlled:

User question
      ↓
Gemini
      ↓
Google ADK Agent
      ↓
Approved tools
      ↓
MCP
      ↓
BigQuery visibility history
      ↓
Grounded analytical answer

That became V4.

And it introduced a different engineering problem:

How do I give an AI agent useful access to analytics while keeping its capabilities explicit, inspectable, and constrained?

That is where I go next.

---

Next: Building an AI Visibility Agent with Gemini, Google ADK, MCP, and BigQuery

In Part 4, I take the visibility history created in V3 and put an agent in front of it.

The focus will be on:

• Gemini for reasoning
• Google ADK for agent orchestration
• MCP for tool access
• BigQuery as the visibility data source
• explicit read-only analytical boundaries
• tool-call visibility
• handling unsupported questions
• keeping deterministic data access separate from model reasoning

The goal is not to add a chatbot on top of a dashboard.

The goal is to build an agent that can investigate the evidence already produced by the system.