DEV Community

Cover image for Building an AI Search Visibility & Brand Analyzer with Gemini, BigQuery, and Google Search Grounding
Hastimal Jangid
Hastimal Jangid

Posted on Edited on

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

#ai #gemini #bigquery #googlecloud

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
Enter fullscreen mode Exit fullscreen mode

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

Architecture

Building an AI Search Visibility & Brand Analyzer with Gemini

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",
    ],
)
Enter fullscreen mode Exit fullscreen mode

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.

AI Search Visibility

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."
Enter fullscreen mode Exit fullscreen mode

That is a mention.

But now compare:

1. RankRabbit — recommended first
2. Competitor A
3. Competitor B
Enter fullscreen mode Exit fullscreen mode

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"
  ]
}
Enter fullscreen mode Exit fullscreen mode

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

visibility_score = 74
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

Bigquery dataset

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.

Big Query visibility scans

BigQuery dataset showing the persisted visibility-analysis tables.

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
Enter fullscreen mode Exit fullscreen mode

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?
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

Inspecting the V3 BigQuery schema directly from the CLI

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
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

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

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
Enter fullscreen mode Exit fullscreen mode

and

retrieved = true
mentioned = true
recommended = true
cited = true
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

BigQuery AI visibility

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?
Enter fullscreen mode Exit fullscreen mode

Those questions are much easier to reason about than:

visibility_score = 74
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

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

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
Enter fullscreen mode Exit fullscreen mode

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
  '
Enter fullscreen mode Exit fullscreen mode

Aggregating brand mentions, retrievals, recommendations, and citations across repeated scans
This lets me separate several ideas that often get grouped together as AI visibility:

Presence
Position
Retrieval
Recommendation
Citation
Enter fullscreen mode Exit fullscreen mode

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:

Top comments (0)