loading
loading
Agent-ready analytics: Unlocking insights with BigQuery augmented analytics Jenny Ortiz Senior Software Engineer, Google Haoming Chen Staff Software Engineer, Google BigQuery now features a suite of augmented analytics Table-Valued Functions (TVFs) designed to automate complex data analysis at scale. Augmented analytics combines AI, ML and statistical methods to automate insight discovery and pattern explanation. These functions allow you to diagnose why metrics changed, uncover underlying trends and relationships across the data, and even isolate the true impact of business decisions. These TVFs run directly where your data lives, which helps speed up analysis and reduces the need to export data into external tools. In addition, since these functions are compact and yield structured SQL outputs, they can easily be integrated as skills for AI agents, which easily enables automated, conversational data investigation workflows. We are introducing six new augmented analytics functions in BigQuery, each created to address a specific analytical challenge: As we show in the next section, these functions can be easily chained together. The output of one function, such as a detected time window, can directly parameterize the next analytical step. A step-by-step example of chaining insights Consider a case where there is a shift in a metric, and you need to diagnose the underlying cause and measure the business lift. To diagnose, we can chain ML.DETECT_CHANGE_POINTS, AI.KEY_DRIVERS and AI.CAUSAL_EFFECT using the Austin Bikeshare sample dataset (bigquery-public-data.austin_bikeshare.bikeshare_trips). This dataset contains historical trip volume and demographic data for the city’s bikesharing program. Step 1: Detect change points ML.DETECT_CHANGE_POINTS automatically identifies statistically significant structural shifts or level changes in your time-series data. While this example demonstrates the analysis in a single aggregate metric, this function is highly scalable and is capable of running across millions of individual time series. To find these shifts, we run the following query across the daily baseline: The output identifies the exact time intervals where the baselines have shifted over the company’s history: If we look at the raw daily session counts, this aligns with shifts over time. We highlight the two change points with the longest durations below: The shift in February 2018 aligns with the day the Austin City Council passed the “Dockless Mobility Pilot Program”, to transform the transit ecosystem, integrating shared electric scooters and bikes into the public. Step 2: Key drivers attribution We can input the February 2018 slice found directly to AI.KEY_DRIVERS to determine the particular factors (i.e. bike_type, subscriber_type, etc) driving the surge. AI.KEY_DRIVERS can scan through millions of rows of multi-dimensional data in seconds. We define the interest group as the slice of time after the shift occurs and compare it against the time period before the shift as the reference group. AI.KEY_DRIVERS isolates the top contributing dimension values. Each row contains a segment, which represents a slice of data identified by a specific combination of dimension values (e.g., subscriber_type = 'UT Student' and bike_type = 'classic'). The analysis reveals that the overall trip count increased +374.7% (+40,159 trips) between the reference and interest time windows. The massive growth was overwhelmingly concentrated in U.T. Student Memberships (+7,167.1%) and trips ending at the 21st & Speedway @PCL station (+20,739.1%). This aligns with Austin Bikeshare’s response to the Dockless Mobility Pilot Program. In early February, the bikeshare program launched a large promotional partnership with the University of Texas that offered free annual memberships to all UT students. Step 3: Causal effect While we know what drove the surge and when it started, we need to isolate the true return on investment over organic expectations. AI.CAUSAL_EFFECT can construct an ARIMA_PLUS counterfactual to measure what the volume would have been had the program never launched. If we graph the predicted and actual trips per day, we can see the surge compared to the counterfactual. If we set the output_time_series => FALSE, we can see a summary of the lift AI.CAUSAL_EFFECT reveals that the program caused a +358% volume surge above organic baseline projections, resulting in an estimated 89,775 incremental trips (with 99.9% probability of causal effect). Connecting augmented analytics to Conversational Analytics Conversational Analytics lets you chat with agents about your data using natural language. All new BigQuery augmented analytical functions are now available in Conversational Analytics. Since these TVFs can execute complex analytics at BigQuery-scale in seconds, Conversational Analytics can orchestrate multi-step investigative workflows based on a given prompt. Below we show two examples: Example 1: Chicago taxi trips Here is an example using the Chicago Taxi Trips (`bigquery-public-data.chicago_taxi_trips.taxi_trips`). Prompt: What metric has the strongest correlation with drivers getting tipped? Then run an attribution analysis to tell me which categorical dimensions (like location and payment type) most disproportionately drive that specific metric. The results here used ML.CORRELATION in combination with AI.KEY_DRIVERS. Credit card payments serve as the primary positive driver of trip distance, adding +1.65M due to longer travel routes and automated digital tip tracking. Trips originating from O'Hare International Airport (Community Area 76) represent another major positive factor, contributing an additional +1.10M miles among tipped credit card rides. In contrast, cash transactions act as a significant negative driver (-652.96K miles), reflecting that cash is predominantly used for shorter journeys rather than extended airport travel. Example 2: Iowa liquor dataset Here is an example using the Iowa liquor dataset (`bigquery-public-data.iowa_liquor_sales.sales`) that uses both ML.TREND in combination with ML.SEASONALITY. Prompt: Find the historical trend for bottles sold. Then, describe the yearly seasonality patterns. The results show that liquor sales in Iowa show persistent long-term growth, rising from 1.3–1.5 million bottles in 2012 before stabilizing around 2.6 million in recent years. There are strong seasonal cycles, particularly during October and December as well as May and June. There is a drop in sales around January and February. The skills for these TVFs are now available at the Google Skills Github repository. The BQ AI/ML skills can be found here. Take the next step - Documentation: - BigQuery AI/ML support in Conversational Analytics We would like to extend our sincere thanks to Katelin Amann, Shirley Fu, Chaoyi Shen, Haiyang Qi, Zheng Zhang, Xi Cheng and the wider engineering team for their feedback and contributions of this work.
document fetched 2026-09-15 02:50 · canonical https://cloud.google.com/blog/products/data-analytics/bigquery-augmented-analytics-tvfs