Map out AI Mode Follow-up Queries using BigQuery

Add me as Preferred Source on Google

Conversational queries have always been buried in the Search Performance report, and yet many still underestimate their first-party search data

The search for AI Mode queries is an odyssey, except you never truly come home.

Google Search Console applies anonymisation and aggregation mechanisms to its query-level data, so not every query appears in the interface. This is particularly relevant for conversational queries, which tend to be longer, less frequent and more likely to disappear from the report.

As shown in my zero-click and AI search tracking framework, moving this data into a data warehouse lets you investigate first-party search data at a much deeper level.

But there is another problem: AI Mode queries are still aggregated alongside other search activity in the Search Console Performance dataset.

The challenge is not to find AI Mode queries, but to build a deterministic preprocessing pipeline that can identify query patterns associated with conversational search, clean the data, and reduce the noise introduced by prompt trackers.

In this article, I’ll walk you through how I built this pipeline using Google Search Console and BigQuery to uncover unanswered questions and comparison searches where the site has no dedicated content.

⚠️ Disclosure – I will use a replica of the framework I successfully applied with TUI UK to gather actionable insights across several product segments.

Establish an ELT Pipeline

You could use a regex in Search Console or through the API, but you are unlikely to capture the full population of query data without working with a data warehouse.

If you haven’t already, go to the Search Console settings and connect the data flow directly to BigQuery.

I am not a fan of MCP-based solutions for this particular type of research. An LLM could automate the preprocessing, query classification and subsequent analysis, but doing so introduces an additional layer of interpretation between the raw search data and the research output.

For this use case (and not only), automation is useful only when the rules are under your control.

This is why the workflow deliberately keeps the intelligence of the process in the SQL, regex and classification logic, while using Python or Excel for further refinements before plotting the outputs.

Undoubtedly, an LLM can help interpret the findings down the line, but it should not interpose between the raw data and the chosen pre-processing logic.

How to Transform Query Data

Once you have your raw Search Console data in BigQuery, you can finally start elaborating your queries and dissect them into a repeatable transformation pipeline.

The transformation architecture of this framework is composed of two main stages of data pre-processing:

• Data Cleaning. A UDF routine to clean up the dataset from noise
• Query Pre-Processing. RegEx to capture follow-up queries

A UDF can normalise messy data (accents, junk characters, phone numbers) before text matching.

However, inline RegEx may skip that and match raw text directly, which is what we need to screen our conversational queries first.

For this reason, query pre-processing is regulated by a RegEx borrowed from Jean-Christophe Chouinard that I slightly tweaked to suit my use case.

⚠️ Mind you, a RegEx is a useful starting point but not a definitive AI Mode classifier.

It tells us a query looks conversational and its job is only to shrink a large query set down to a manageable population. Actual query classification can occur further down the line.

UDF Routines for Query Pre-Processing

More details on the composition of the UDF routine.

For starters, a User-Defined Function (UDF) routine is a custom program that extends standard database capabilities to perform specific data transformations.

BigQuery labels it as a “persistent function” that takes in a combination of SQL and JavaScript to standardise query strings before text matching.

Again, the preprocessing is deliberately deterministic: no LLM decides how to normalise a query.

The routine applies to search queries to generate a brand new clean Search Console dataset based on the following requirements:

• map accented characters to base equivalents
• lowercase everything
• strip non-ASCII characters
• remove phone numbers and similar noise
• collapse multiple spaces
• run the conversational regex on the matching text to capture conversation fragments (e.g; yes please, more, show me more, can you compare).

The function generates a new custom dataset with the pre-processed output, which will be stored asnew-all-378911.searchconsole.ai_mode_query

Data Transformation: Query AI Mode Dataset

Once the queries have been preprocessed inside thenew-all-378911.searchconsole.ai_mode_query dataset via UDF, you need to further manipulate the dataset to increase the likelihood of approaching the bespoke follow-up queries.

To transform that dataset, Dataform in BigQuery comes into place.

Upon the UDF-cleaned dataset, you can create a data mart – a focused subset of a dataset in your data warehouse (BigQuery) – that allows you to organise the original Search Console dataset and dissect queries based on the following key requirements:

1. Separate brand from non-brand terms
2. Classify the pages into labelled segments
3. Approaching conversational AI mode queries by filtering out:

• word count < 12 characters
• impressions < 10

Here is the resulting SQLX query in Dataform to produce the new data mart.

config {
  type: "incremental",
  schema: "seo_marts",
  name: "ai_mode_query",
  uniqueKey: ["data_date", "query", "url"],
  bigquery: {
    partitionBy: "data_date",
    clusterBy: ["query"]
  }
}
SELECT
  data_date,
  url,
  word_count,
  query,
  CASE
    WHEN REGEXP_CONTAINS(query, r'(?i)(simone\s*de\s*palma|simone\s*depalma|s[iy]mone\s*de\s*palma|seo\s*-?\s*depths|seodepths?)') THEN 'Brand'
    ELSE 'Non-Brand'
  END AS query_type,
  CASE
    WHEN req_path LIKE '/python-for-seo%' THEN 'Python SEO'
    WHEN req_path LIKE '/seo-research%' THEN 'SEO research'
    WHEN req_path LIKE '/tools-for-seo%' THEN 'Tools for SEO'
    WHEN req_path LIKE '/about%' THEN 'About'
    WHEN req_path = '/' THEN 'homepage'
    ELSE 'Other'
  END AS segment,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
  
FROM (
  SELECT
    data_date,
    url,
    query,
    ARRAY_LENGTH(SPLIT(TRIM(query), ' ')) AS word_count,
    REGEXP_EXTRACT(url, r'^https?://[^/]+(/.*)?$') AS req_path,
    clicks,
    impressions,
    sum_position,
    `new-all-378911.searchconsole.ai_mode_query`(query) AS match_flag
  FROM ${ref("searchconsole", "searchdata_url_impression")} 
  --TABLESAMPLE SYSTEM (2 PERCENT)
)
WHERE match_flag = 'true'
  AND word_count < 12
GROUP BY
  url,
  query,
  data_date,
  word_count,
  req_path
HAVING impressions < 10

⚠️ Uncomment TABLESAMPLE SYSTEM (2 PERCENT) while testing your query to avoid draining your Google Cloud budget. This samples 2% of the original dataset, allowing you to trial the query against a smaller dataset before running it in full.

⚠️ Before this, make sure to create a YAML file that details the specs and origin of your dataset

How far can you dissect the dataset?

If you stopped reading after the data transformation paragraph, I won’t blame you.

At that stage, it is clearly not possible to infer the presence of AI mode queries showing up for a URL in any time window that is probably longer than 5 minutes.

The bad news is that your dataset will only have queries associated with a single URL affected by the following caveats:

1. Aggregated impressions based on the selected time period, country and device
2. Showing up in either AI Mode, AI Overviews or Organic for a short but indefinite time span
3. Lower chances of being retrieved by web scrapers and prompt trackers

For this reason, the best possible workaround is to query up to the last 2 days.

So is that it?

Let’s play devil’s advocate: what would happen if you dissected the clean UDF Search Console dataset further?

Prompt trackers usually hit the desktop endpoint, while web scrapers tend to follow the mobile endpoint.

Further classifying queries by device type can be useful, as it may make it easier to associate query patterns with potential prompt tracker or web scraper activity.

However, this introduces the over-refinement bias or a cognitive trap where overthinking during data preprocessing can do more harm than good to the final output.

And I believe this is relevant because AI Mode is available on both desktop and mobile devices.

Extract Query Insights from AI Mode Data Mart

Now you have an “AI Mode-like” data mart where the table is partitioned by date and aggregated by URL and query. It’s expectedly sampled based on your requirements, and you can query it almost for free due to its lineage.

For instance, you can aggregate clicks and impressions by URL and query, while retaining the segment and query-type dimensions needed for later analysis.

SELECT
  url,
  query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
  FROM `new-all-378911.seo_marts.ai_mode_query`
WHERE segment = 'Python SEO'
AND query_type = 'Non-Brand'
AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
GROUP BY url, query
ORDER BY url

The result is a cheap and useful table. One row represents a URL associated to a query with clicks and impressions aggregated over the selected period.

From there, the dataset can be exported to Excel or Python for further refinement and visualisation. But a lot of useful analysis can already happen in BigQuery.

For example, you can ask which site segments contain the greatest concentration of conversational queries.

SELECT
  segment,
  COUNT(DISTINCT query) AS ai_mode_query_count,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `new-all-378911.seo_marts.ai_mode_query` 
GROUP BY segment
ORDER BY ai_mode_query_count DESC

⚠️ Disclosure – for what it’s worth, a larger website with more categories would return many more queries and a more telling bar chart than this.

Query Classification with ML

At this point we have a candidate dataset with queries that either match conversational patterns or warrant investigation.

However, we still need to define which queries are genuinely conversational, which are ordinary search queries, and which represent something else.

This is where Suganthan’s AI Mode and AI Overview Query Classifier become useful. The tool classifies queries into different buckets and exposes the classification confidence, allowing the results to be exported for analysis.

How does it feed into your SEO Strategy?

This framework doesn’t classify every potential candidate as an AI Mode query. Instead, it identifies recurring patterns in conversational search, increasing the likelihood that these queries are associated with those appearing in the AI Mode dataset.

However, this framework is instrumental in revealing unanswered questions and comparison queries that existing content may not address.

These insights can inform concrete actions such as:

1. Expanding FAQ generation on PLP or category hubs
2. Strengthening commercial pages through enhanced internal linking
3. Creating new comparison content that helps build consensus for LLMs and direct PageRank

Final Words

We started with a messy dataset of Search Console queries. We then normalised the data, isolated conversational candidates, classified them and finally mapped those queries back to the URLs and page segments generating them.

The main goal was never to label every long query as AI Mode because the industry still lacks the tools, but to identify behavioural patterns consistent with conversational search.

And this requires applying deterministic preprocessing and classification rules rather than allowing an LLM to perform the entire task for you.

Simone De Palma

Technical SEO Manager

Simone De Palma is an SEO Manager at TUI and the founder of SEO Depths.

He graduated in Marketing and Management from Università IULM before completing a degree in Digital Marketing and Data Science at Leeds Beckett University.
Simone has worked as an SEO Specialist in digital agencies in Italy and the United Kingdom and he’s a contributor for the Search Engine Land and Majestic SEO podcast

When he’s away from his double screens, he enjoys cooling down with a refreshing swim at the pool. You could find him exploring art museums or enjoying the company of a classic romance

Add your content…

• Linkedin