Agentic KPI Workflow

Problem to solve Business owners frequently struggle to identify the most impactful, non-obvious Key Performance Indicators (KPIs) for their specific domain, and traditional static analytics tools lack the autonomy to proactively discover them. Furthermore, mapping high-level business goals directly to complex existing database schemas often reveals a gap in which requested metrics cannot be derived from the available data. Finally, giving LLMs direct access to generate and execute SQL against production databases introduces significant security risks, data governance challenges, and the potential for LLM hallucinations.

Our solution We built the "Governed KPI Discovery & Validation Agent," an autonomous multi-agent system that bridges business strategy with database reality. It operates using a parallel dual-branch architecture:

Branch A (Business): Analyzes user-provided profiles and goals to discover core and "hidden" non-trivial KPIs, ensuring suggestions are realistically computable via standard SQL or BigQuery ML. Branch B (Schema): Connects safely to the analytical database via the Model Context Protocol (MCP) to read and batch-process the table structures. A "Merge Gate" evaluates which discovered KPIs are genuinely computable from the existing schema, producing a derivability matrix. Formulator and Evaluator agents then generate BigQuery-native SQL and BQML statements, executing "dry-runs" to validate syntax, estimate costs, and assign a Confidence Score. The final outputs, including traceable code and non-derivable metric explanations, are presented on a clean React dashboard.

Technologies used Google Agent Development Kit (ADK): Powers the multi-agent orchestration via Sequential and Parallel agents. Gemini 3.5 Flash: Handles high-volume structured extraction and schema classification tasks. Gemini 3.1 Pro: Manages critical correctness tasks, complex KPI elaboration, and evaluation judging. Model Context Protocol (MCP) Toolbox for Databases: Establishes the governed, read-only connection to the analytical database. BigQuery & BigQuery ML (BQML): Serves as the analytical engine and machine learning foundation. Cloud Run: Hosts the MCP Toolbox and the agent application (via FastAPI). Secret Manager: Secures database and application credentials. React, Vite, and Tailwind: Drives the frontend single-page application dashboard. Data sources Google Analytics e-commerce sample dataset (2016/2017): Hosted publicly on BigQuery (bigquery-public-data.google_analytics_sample). Findings and learnings Schema Scale: Processing the entire 366 date-sharded tables of the Google Analytics dataset in one prompt was not feasible. We had to build a deterministic, shard-aware classifier to batch tables into chunks of 100 that respected token limits while preserving parallel execution. Nested Data Complexity: Translating natural language to SQL against complex nested data (RECORD/REPEATED fields) required us to persist the nested structures in state and utilize explicit, dataset-specific few-shot UNNEST prompting to effectively guide the agent. Governed ML Contexts: Allowing an agent to write machine learning models safely required a dual-source MCP configuration. Production source data is strictly read-only, while a quarantined dataset (kpi_ml_scratch) is provided exclusively for model training via a dedicated tool. Cost-Free Validation: Leveraging BigQuery's built-in "dry-run" feature served as a highly effective validation layer. It allowed our Evaluator agent to test query syntax and estimate scanned bytes entirely for free before attempting execution. Third-party integrations (if applicable) ADK Data Science Agent: We vendored and adapted sample modules (db_agent and bqml_agent) to handle the baseline BigQuery NL2SQL and ML workflows. Pydantic: Used for structuring and strictly typing the agent outputs. React, Vite, and Tailwind: Open-source UI libraries utilized for building the frontend panels. Note on authorization: The system relies strictly on publicly provided Google sample datasets and open-source software libraries. Problem to solve Business owners frequently struggle to identify the most impactful, non-obvious Key Performance Indicators (KPIs) for their specific domain, and traditional static analytics tools lack the autonomy to proactively discover them. Furthermore, mapping high-level business goals directly to complex existing database schemas often reveals a gap in which requested metrics cannot be derived from the available data. Finally, giving LLMs direct access to generate and execute SQL against production databases introduces significant security risks, data governance challenges, and the potential for LLM hallucinations.

Our solution We built the "Governed KPI Discovery & Validation Agent," an autonomous multi-agent system that bridges business strategy with database reality. It operates using a parallel dual-branch architecture:

Branch A (Business): Analyzes user-provided profiles and goals to discover core and "hidden" non-trivial KPIs, ensuring suggestions are realistically computable via standard SQL or BigQuery ML. Branch B (Schema): Connects safely to the analytical database via the Model Context Protocol (MCP) to read and batch-process the table structures. A "Merge Gate" evaluates which discovered KPIs are genuinely computable from the existing schema, producing a derivability matrix. Formulator and Evaluator agents then generate BigQuery-native SQL and BQML statements, executing "dry-runs" to validate syntax, estimate costs, and assign a Confidence Score. The final outputs, including traceable code and non-derivable metric explanations, are presented on a clean React dashboard.

Technologies used Google Agent Development Kit (ADK): Powers the multi-agent orchestration via Sequential and Parallel agents. Gemini 3.5 Flash: Handles high-volume structured extraction and schema classification tasks. Gemini 3.1 Pro: Manages critical correctness tasks, complex KPI elaboration, and evaluation judging. Model Context Protocol (MCP) Toolbox for Databases: Establishes the governed, read-only connection to the analytical database. BigQuery & BigQuery ML (BQML): Serves as the analytical engine and machine learning foundation. Cloud Run: Hosts the MCP Toolbox and the agent application (via FastAPI). Secret Manager: Secures database and application credentials. React, Vite, and Tailwind: Drives the frontend single-page application dashboard. Data sources Google Analytics e-commerce sample dataset (2016/2017): Hosted publicly on BigQuery (bigquery-public-data.google_analytics_sample). Findings and learnings Schema Scale: Processing the entire 366 date-sharded tables of the Google Analytics dataset in one prompt was not feasible. We had to build a deterministic, shard-aware classifier to batch tables into chunks of 100 that respected token limits while preserving parallel execution. Nested Data Complexity: Translating natural language to SQL against complex nested data (RECORD/REPEATED fields) required us to persist the nested structures in state and utilize explicit, dataset-specific few-shot UNNEST prompting to effectively guide the agent. Governed ML Contexts: Allowing an agent to write machine learning models safely required a dual-source MCP configuration. Production source data is strictly read-only, while a quarantined dataset (kpi_ml_scratch) is provided exclusively for model training via a dedicated tool. Cost-Free Validation: Leveraging BigQuery's built-in "dry-run" feature served as a highly effective validation layer. It allowed our Evaluator agent to test query syntax and estimate scanned bytes entirely for free before attempting execution. Third-party integrations (if applicable) ADK Data Science Agent: We vendored and adapted sample modules (db_agent and bqml_agent) to handle the baseline BigQuery NL2SQL and ML workflows. Pydantic: Used for structuring and strictly typing the agent outputs. React, Vite, and Tailwind: Open-source UI libraries utilized for building the frontend panels. Note on authorization: The system relies strictly on publicly provided Google sample datasets and open-source software libraries.