DEV Community

Cover image for Building an AI Visibility Agent with Gemini, Google ADK, MCP, and BigQuery
Hastimal Jangid
Hastimal Jangid Subscriber

Posted on AI-assisted

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

#ai #mcp #gemini #googlecloud

In Part 3, I built the historical layer.

The system could persist AI visibility scans, brand observations, citations, and fan-out behavior in BigQuery. That gave me something I did not have in the earlier versions of this project: history.

I could ask questions such as:

  • Which brands were mentioned?
  • Which brands were recommended?
  • Which citation domains appeared?
  • Which queries found or missed a brand?
  • How did visibility change across repeated scans?

But there was still friction.

Every time I wanted to investigate something new, I had to open the dashboard or write another SQL query.

GitHub Code: ai-search-journey-lab

That led to the next step:

What if I could ask an agent questions about the visibility history instead?

Not a generic chatbot.

Not an agent with unrestricted access.

I wanted a constrained analytical agent that could inspect the visibility data I had already collected, use explicit tools, stay read-only, and return an answer grounded in actual BigQuery history.

That became V4: the AI Visibility Agent.

AI Visibility Agent Workspace

What changed from V3 to V4

V3 gave me this:

Visibility Scans
      ↓
BigQuery
      ↓
Dashboard + SQL
Enter fullscreen mode Exit fullscreen mode

V4 adds an agent between the user and that historical data:

User Question
      ↓
Gemini
      ↓
Google ADK Agent
      ↓
MCP Toolset
      ↓
Local MCP Server
      ↓
Read-Only BigQuery Analytics
      ↓
Grounded Answer
Enter fullscreen mode Exit fullscreen mode

The important part is not simply that an LLM sits in front of BigQuery.

The important part is the boundary.

V4 does not execute new Places searches, does not run new web searches, and does not write new visibility data. It acts as an analytical layer over the history produced by V3.

That separation made the design much easier to reason about.

V3 creates evidence.

V4 investigates evidence.

Step 1: Define the agent boundary first

Before I built the agent, I decided what it should not be allowed to do.

That turned out to be one of the most important design choices in this version.

The V4 agent is intentionally read-only.

It should not:

  • insert new BigQuery rows
  • update visibility records
  • delete historical data
  • trigger new visibility scans
  • call Google Places
  • perform live web searches
  • execute arbitrary user-supplied SQL

Instead, the agent should answer questions using a fixed set of analytical tools.

That gives me a much cleaner boundary:

V1 / V2
Live search journey

V3
Persisted visibility evidence

V4
Read-only analytics over V3
Enter fullscreen mode Exit fullscreen mode

I find this pattern much more useful than giving the model broad database access and trying to control it only through prompting.

The capability boundary is architectural, not only instructional.

Step 2: Use Google ADK as the orchestration layer

The agent itself is built with Google ADK.

Its job is not to contain all of the analytical logic.

Instead, it handles the conversational layer:

User asks a question
        ↓
Gemini interprets the intent
        ↓
ADK selects an appropriate tool
        ↓
Tool returns structured data
        ↓
Gemini explains the result
Enter fullscreen mode Exit fullscreen mode

In the implementation, the ADK agent connects to the MCP toolset over stdio, while the MCP server runs separately and performs the read-only BigQuery analytics.

That gives me two useful separations:

Reasoning
    ≠
Data access
Enter fullscreen mode Exit fullscreen mode

and:

Agent orchestration
    ≠
Analytics implementation
Enter fullscreen mode Exit fullscreen mode

The model decides what information it needs.

The tools decide how that information is retrieved.

Gemini and Google ADK orchestrate read-only MCP tools over the historical visibility data created in V3.

Use a clean architecture diagram showing:

User Question
      ↓
Gemini
      ↓
Google ADK Agent
      ↓
MCP Toolset
      ↓
Local MCP Server
      ↓
Read-Only BigQuery
      ↓
V3 Visibility History
Enter fullscreen mode Exit fullscreen mode

Step 3: Why I used MCP here

I could have connected the agent directly to Python functions.

But I wanted the data-access boundary to be more explicit.
That is where MCP became useful.

The analytical tools live behind a local MCP server that communicates with the ADK agent over JSON-RPC stdio.

Conceptually:

ADK Agent
    │
    │ MCP over stdio
    ▼
MCP Server
    │
    ▼
Approved Analytics Tools
    │
    ▼
BigQuery
Enter fullscreen mode Exit fullscreen mode

That means the agent does not need direct knowledge of the underlying SQL implementation.

It sees a set of capabilities.

For example:

get_available_history
get_visibility_summary
get_brand_trend
compare_brands
analyze_citations
find_fanout_gaps
Enter fullscreen mode Exit fullscreen mode

Those six tools form the analytical surface of V4.

That is a much more controlled interface than:

run_any_sql(query)
Enter fullscreen mode Exit fullscreen mode

Step 4: Build tools around analytical questions

I did not want the tool names to mirror database tables.

I wanted them to mirror the questions I actually ask when analyzing visibility.

That resulted in six read-only tools.

  1. get_available_history

Before asking for a trend or comparison, I first need to know what history actually exists.

This tool discovers things such as:

  • persisted scan counts
  • available date ranges
  • known brand IDs
  • recent scan windows

That makes it especially useful for broad prompts where the user does not specify a brand or date range.

This also supports a pattern I like:

Discover first, analyze second.

Instead of letting the model assume what history exists, the agent can inspect the available data.

  1. get_visibility_summary

This tool provides an aggregated visibility view over a selected brand and time window.

It can work with metrics such as:

mention rate
recommendation rate
citation rate
Enter fullscreen mode Exit fullscreen mode

Rather than making the model calculate those metrics itself, I keep them in the analytical layer.

That makes the agent responsible for explanation rather than arithmetic.

  1. get_brand_trend

This tool turns historical scan records into a time-oriented visibility view.

Conceptually:

Scan 1
Scan 2
Scan 3
Scan 4
   ↓
Historical visibility trajectory
Enter fullscreen mode Exit fullscreen mode

That gives the agent a way to answer questions such as:

Is this brand becoming more or less visible?

The trend logic remains deterministic.

Gemini interprets the result.

  1. compare_brands

This tool gives the agent a controlled way to compare a target brand with competitors.

Instead of letting the model assemble arbitrary joins, the comparison is exposed as an explicit business operation.

The tool can compare signals such as:

mentions
recommendations
citations
Enter fullscreen mode Exit fullscreen mode

between the target and competing brands.

  1. analyze_citations

In V3, citations became independently queryable.

V4 turns that into an agent capability.

The agent can inspect citation-domain behavior across the stored search journeys instead of only answering questions about brand presence.

That lets me ask questions closer to:

Which domains repeatedly support this brand?

rather than only:

Was this brand mentioned?

  1. find_fanout_gaps

This is one of the more interesting tools.

The purpose is to identify fan-out queries where a target brand was absent from observed results.

Conceptually:

User Prompt
   ↓
Fan-Out Query A → Brand found
Fan-Out Query B → Brand found
Fan-Out Query C → Brand missing
Fan-Out Query D → Brand missing
Enter fullscreen mode Exit fullscreen mode

That means the agent can investigate where visibility breaks down, not only whether overall visibility is high or low.

Step 5: Keep the tools deterministic

A design principle I carried over from V3 was this:

Let the model reason about results, but do not make the model invent the measurements.

The MCP tools perform the analytical work.

Gemini receives structured results and explains them.

So instead of:

Gemini:
"I think the brand visibility is probably around 70%."
Enter fullscreen mode Exit fullscreen mode

the flow becomes:

Tool:
mention_rate = 68.2
recommendation_rate = 41.7
citation_rate = 53.6

Gemini:
"Your brand appeared in roughly two-thirds of the observed journeys,
but it was recommended less frequently..."
Enter fullscreen mode Exit fullscreen mode

The exact values come from data.

The language comes from the model.

That separation is important to me.

Step 6: Use discovery-first grounding

One problem with analytical agents is that users often ask incomplete questions.

For example:

Give me a visibility summary.

A model could easily make assumptions:

  • which brand?
  • what date range?
  • which scans?
  • what data actually exists?

Instead, V4 can discover the available history first using get_available_history. For broad questions or questions without an explicit brand/date window, that discovery step gives the agent actual scan coverage before it performs deeper analysis

That means:

Question
   ↓
Discover available history
   ↓
Choose valid scope
   ↓
Run analysis
   ↓
Explain answer
Enter fullscreen mode Exit fullscreen mode

I prefer this to embedding assumed date ranges in the prompt.

Step 7: Run the agent locally

The V4 agent can be used through the Streamlit application or through a standalone CLI demo.

First activate the environment:

source .venv/bin/activate
Enter fullscreen mode Exit fullscreen mode

Then launch the application:

streamlit run src/ai_search_journey/app.py
Enter fullscreen mode Exit fullscreen mode

The V4 AI Visibility Agent provides conversational access to the historical visibility evidence created in V3.

Step 8: Start with a broad-history question

One of the first prompts I use is:

Give me a visibility summary for the available history.
Use the most recent available scan window.
Enter fullscreen mode Exit fullscreen mode

The important phrase is:

available history
Enter fullscreen mode Exit fullscreen mode

I do not want the model to assume that a particular brand or date range exists.

The agent can inspect the historical coverage first and then use the appropriate read-only tools to produce the summary.

This gives me a much more grounded conversation.

The user does not need to know the dataset structure.

The agent does not need to guess the dataset structure.

The tools bridge that gap.

The agent first discovers available historical coverage before summarizing visibility.

Step 9: Ask for a competitor comparison

The next test is more specific.

For example:

Compare "pullman_coffee" against its competitors for the most recent available scan window.

The important detail is that the brand ID needs to exist in the historical data. The project documentation explicitly treats pullman_coffee as an example only when it is present in the available scan history.

In practice, I can first discover the available brand IDs and then choose one.

The comparison tool can then return a structured view of:

mentions
recommendations
citations
Enter fullscreen mode Exit fullscreen mode

between the target brand and competing brands.

The model then explains the comparison in natural language.

A read-only competitive visibility comparison generated from persisted BigQuery scan history.

Step 10: Independently verify the agent with BigQuery

One thing I do not want is an agent that becomes the only interface to the data.

The underlying history should still be independently verifiable.

V4 never writes to BigQuery; the queries below are only for verifying the V3 data that the agent reads.

For example, I can inspect per-brand visibility metrics directly:

bq --project_id=ai-search-journey-lab query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    brand_id,
    COUNT(DISTINCT scan_id) AS total_scans,
    ROUND(100 * AVG(CAST(mentioned AS INT64)), 1) AS mention_rate_pct,
    ROUND(100 * AVG(CAST(recommended AS INT64)), 1) AS recommendation_rate_pct,
    ROUND(100 * AVG(CAST(cited AS INT64)), 1) AS citation_rate_pct,
    MAX(started_at) AS latest_scan_at
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.brand_observations`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
  GROUP BY
    brand_id
  ORDER BY
    citation_rate_pct DESC,
    mention_rate_pct DESC,
    total_scans DESC
  '
Enter fullscreen mode Exit fullscreen mode

That query gives me an independent path to the same underlying evidence used by the agent.

I can also inspect recent persisted scans:

bq --project_id=ai-search-journey-lab query \
  --use_legacy_sql=false \
  --format=pretty \
  '
  SELECT
    scan_id,
    started_at,
    brand_id,
    brand_name_snapshot,
    prompt_text_snapshot
  FROM
    `ai-search-journey-lab.ai_search_journey_v3.visibility_scans`
  WHERE
    started_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
  ORDER BY
    started_at DESC
  LIMIT 10
  '
Enter fullscreen mode Exit fullscreen mode

The partition filter matters here because the historical tables require filtering on started_at.

Verifying the historical visibility records independently from the agent using the BigQuery CLI.

Step 11: Why I did not give the agent raw SQL

A natural question is:

Why not just let Gemini generate SQL?

That would certainly be simpler.

But it would also create a much broader capability surface.

With arbitrary SQL generation:

User
  ↓
LLM
  ↓
Generated SQL
  ↓
Database
Enter fullscreen mode Exit fullscreen mode

the model has to understand:

  • table names
  • schemas
  • joins
  • partition requirements
  • date handling
  • security boundaries
  • allowed operations
  • analytical semantics

And then I have to validate all of that.

With MCP tools:

User
  ↓
Gemini + ADK
  ↓
compare_brands(...)
  ↓
Controlled analytics logic
  ↓
BigQuery
Enter fullscreen mode Exit fullscreen mode

the capability is much narrower.

The model asks for an operation.

The tool owns the implementation.

That is a pattern I increasingly prefer for production-oriented agent systems.

Step 12: Separate reasoning from authority

This is probably the most important lesson from V4.

LLMs are very good at:

  • interpreting a question
  • selecting an analytical path
  • comparing results
  • summarizing findings
  • explaining trade-offs

But that does not mean the model needs unrestricted authority over the underlying system.

I can keep the reasoning flexible while keeping the capabilities constrained.

Gemini
    ↓
Flexible reasoning

Google ADK
    ↓
Agent orchestration

MCP
    ↓
Explicit capability boundary

BigQuery
    ↓
Deterministic historical evidence
Enter fullscreen mode Exit fullscreen mode

That architecture is much easier for me to understand and debug.

Step 13: Preserve unsupported-answer behavior

Another boundary matters just as much:

The agent should be able to say:

I do not have enough historical data to answer that.

If the user asks about:

  • a brand that does not exist in the scan history
  • a time period with no scans
  • a metric the tools do not expose
  • current live-search behavior

the right response is not to manufacture an answer.

The system should stay within the evidence available to V4.

That makes the agent less magical.

It also makes it much more useful.

Step 14: Validate the agent layer

As with the previous versions, I do not consider the feature complete just because the UI responds.

I run the repository checks:

./.venv/bin/ruff check . && \
./.venv/bin/mypy src && \
./.venv/bin/pytest -q
Enter fullscreen mode Exit fullscreen mode

Repository validation after adding the V4 agent and MCP analytics layer

What V4 changed for me

V3 taught me to preserve the evidence before calculating the conclusion.

V4 added another lesson:

Give the model access to capabilities, not unrestricted infrastructure.

The final architecture is not:

Gemini
   ↓
Database
Enter fullscreen mode Exit fullscreen mode

It is:

User Question
      ↓
Gemini
      ↓
Google ADK
      ↓
Approved MCP Tools
      ↓
Read-Only Analytics
      ↓
BigQuery History
      ↓
Structured Evidence
      ↓
Grounded Explanation
Enter fullscreen mode Exit fullscreen mode

That distinction matters.

Gemini provides the reasoning.

Google ADK orchestrates the workflow.

MCP defines the tool boundary.

BigQuery remains the source of historical evidence.

The analytical tools remain deterministic and read-only.

What I learned building the AI Visibility Agent

The first lesson was that an analytical agent becomes more trustworthy when it knows less about the database.

That may sound backward.

But the model does not need to know every table, join, and partition rule.

It needs to know which analytical capability to use.

The second lesson was that discovery is a tool too.

get_available_history is not just convenience.

It helps prevent the model from reasoning over data that does not exist.

The third lesson was that agents become easier to evaluate when the tools reflect real business questions:

What history exists?

How visible is this brand?

How is visibility changing?

How does it compare with competitors?

Which sources are citing it?

Where does query fan-out miss it?
Enter fullscreen mode Exit fullscreen mode

From agentic analytics to observability

Once the agent started working, another problem became much more obvious.

Now I had:

User Question
      ↓
Gemini
      ↓
ADK Agent
      ↓
MCP Tool Selection
      ↓
MCP Server
      ↓
BigQuery
      ↓
Tool Result
      ↓
Gemini Synthesis
Enter fullscreen mode Exit fullscreen mode

But if something became slow or failed, I needed to know:

Which stage caused it?

Was Gemini slow?

Did the MCP process take too long?

Did the BigQuery tool fail?

Which tool did the agent call?

How much time did each stage take?

That moved the project into the next phase: observability.

Next: From Query Fan-Out to Trace — Observing AI Search Journeys with OpenTelemetry

In Part 5, I will instrument the AI Search Journey so I can trace what happens across the workflow.

The focus moves from:

What answer did the system produce?

to:

How did the system produce it?

Source code

The project is open source: GitHub Project

Top comments (1)

Collapse
 
hjangid profile image
Hastimal Jangid •

Working Code - github.com/hastimal/ai-search-jour...