# BigQuery AI.GENERATE tutorial: turn SQL queries into AI-powered insights

> How to call Vertex AI from BigQuery SQL with AI.GENERATE, with worked examples on analytics data.

Source: https://www.measurelab.co.uk/insights/blog/bigquery-ai-generate-tutorial/  
Author: Katie Kaczmarek  
Published: 2025-09-03  
Updated: 2026-10-13

BigQuery just got a major upgrade, you can now plug directly into **Vertex AI** using the new AI.GENERATE function. Translation: your analytics data and generative AI are now best friends, and they’re hanging out right inside SQL.

That opens up a whole world of new analysis options for GA4 data, but it also raises some questions:

- How do you actually set it up?
- What’s it good for (and when should you avoid it)?
- Why would you batch the query?

Let’s walk through it step by step.

### **Step 1: How to Set Up AI.GENERATE in BigQuery (Vertex AI connection)**

Before you can start prompting Gemini from SQL, you’ll need to connect BigQuery ↔ Vertex AI. The process is a little fiddly, but once it’s done you’re good to go.

- **Enable APIs**: BigQuery Connection API + Vertex AI API.
- **Create a Cloud Resource Connection** in the GCP console (look for “Vertex AI” → “BigQuery Federation”).Go to **+ add data**
- Search for **vertex ai**
- Click **BigQuery federation**
- Choose **Vertex AI remote models, remote functions and BigLake**
- **Give it a connection_id** (e.g. vertex_gemini_bigquery_link) and make sure the region matches your dataset.

**Picking the right endpoint in your code:** check which Gemini endpoints are available in your region (e.g. gemini-1.5-pro-002 in europe-west2). [https://cloud.google.com/vertex-ai/generative-ai/docs/learn/locations#europe](https://cloud.google.com/vertex-ai/generative-ai/docs/learn/locations#europe)

---

### **Step 2: Best use cases for AI.GENERATE in BigQuery (and what to avoid)**

Think of it like this: SQL is your calculator, AI.GENERATE is your interpreter.

✅ Great for:

- **Insights**: “What does this referrer + landing page say about intent?”
- **Classification**: e.g. group queries into high vs low purchase intent.
- **Summarisation**: turn 1000 rows into 5 takeaways.
- **Unstructured parameters**: analyse feedback, search queries, chat logs.

❌ Not great for:

- Factual lookups (populations, exchange rates).
- Crunching metrics (conversion, bounce rate).

AI isn’t faster or more accurate than SQL for maths. Use it for context, not for sums.

---

### **Step 3: AI.GENERATE example in BigQuery (referrer + landing page analysis)**

Say you want to quickly understand *why* users are landing on certain pages from different sources. We could ask AI to make its best guesstimate!

```
WITH session_referrers AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') AS ld,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS pt
  FROM `myproject.analytics_123456789.events_*`
  WHERE _table_suffix BETWEEN '20240101' AND '20250101'
    AND event_name = 'session_start'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') IS NOT NULL
)
SELECT
  ld,
  pt,
  AI.GENERATE(
    CONCAT(
      'You are a digital marketing analyst. ',
    'I will give you two pieces of information about a new session on a website: ',
    '1) The referring domain where the visitor came from: ', ld, '. ',
    '2) The landing page the visitor viewed: ', pt, '. ',
    'Based on these two fields, provide a short marketing insight. ',
    'Explain what this might suggest about the visitors intent or acquisition opportunity. ',
    'Keep the answer to 2 sentences, focused on actionable insight.'
    ),
    connection_id => 'projects/myproject/locations/europe-west2/connections/__default_cloudresource_connection__',
    endpoint => 'gemini-1.5-pro-002'
  ).result AS insight
FROM session_referrers;
```

- “Visitors from LinkedIn landing on the Careers page are likely job seekers. Emphasise employer branding.”
- “Users from Google landing on Pricing show strong intent. Ensure CTAs are clear.”

---

### **Step 4: Batch queries in AI.GENERATE for faster performance**

Calling AI row by row = slow and clunky.

Example: 1,500 rows = 1,500 separate model calls. You’ll be waiting a while.

Better way:

- Use STRING_AGG to group ~10 rows per prompt.
- Ask Gemini to return insights as a bulleted list.

Now 1,500 rows = 150 calls. That’s not cheaper (same total data processed), but it’s **much faster** and easier to work with.

So the above code could be written into batches like this:

```
-- 1) Pull referrer + landing page pairs from GA4 session_start events
WITH session_referrers AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') AS ld,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS pt
  FROM `myproject.analytics_123456789.events_*`
  WHERE _table_suffix BETWEEN '20240101' AND '20250901'
    AND event_name = 'session_start'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') IS NOT NULL
),

-- 2) Give each row a number so we can batch
numbered AS (
  SELECT
    ld,
    pt,
    ROW_NUMBER() OVER () AS rn
  FROM session_referrers
  WHERE ld IS NOT NULL
),

-- 3) Group into batches of 10 rows and build a prompt-friendly text block
batched AS (
  SELECT
    DIV(rn, 10) AS batch_id,
    STRING_AGG(CONCAT('- Domain: ', ld, ', Landing page: ', pt), '\n') AS batch_text
  FROM numbered
  GROUP BY batch_id
)

-- 4) Call AI.GENERATE once per batch to reduce total model calls (faster; not cheaper)
SELECT
  batch_id,
  AI.GENERATE(
    CONCAT(
      'You are a digital marketing analyst. ',
      'I will give you several referrer domain + landing page pairs. ',
      'For each pair, provide 2 sentences of actionable insight. ',
      'Format your output as a bulleted list. ',
      '\n\nHere are the pairs:\n',
      batch_text
    ),
    connection_id => 'projects/myproject/locations/europe-west2/connections/__default_cloudresource_connection__',
    endpoint => 'gemini-1.5-pro-002'
  ).result AS insights
FROM batched
ORDER BY batch_id;
```

---

### **Step 5: Advanced AI.GENERATE examples in BigQuery (sentiment, classification, summaries)**

GA4’s event and item parameters are a goldmine for unstructured data. With AI.GENERATE you can:

- **Classify search queries** as navigational / informational / transactional.
- **Run sentiment analysis** on feedback or chat logs.
- **Summarise product descriptions** into categories or value props.

Example: feedback sentiment analysis:

```
WITH feedback_events AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'feedback') AS feedback_text
  FROM `myproject.analytics_123456789.events_*`
  WHERE event_name = 'feedback_submitted'
)
SELECT
  feedback_text,
  AI.GENERATE(
    CONCAT(
      'Analyse the sentiment of this customer feedback: "', feedback_text, '". ',
      'Return only "Positive", "Neutral", or "Negative".'
    ),
    connection_id => 'projects/myproject/locations/europe-west2/connections/__default_cloudresource_connection__',
    endpoint => 'gemini-1.5-pro-002'
  ).result AS sentiment
FROM feedback_events
WHERE feedback_text IS NOT NULL
```

---

### **Final thoughts**

AI.GENERATE isn’t replacing SQL, it’s **layering on top of it**.

- Use SQL for facts.
- Use AI.GENERATE for interpretation.
- Combine them (smartly, with batching) and GA4 data turns into actionable insights.

If you’ve been meaning to try generative AI in a real analytics workflow, BigQuery just made it dead simple. You can write whatever prompt you’d like, the only limitation is your imagination.

For full details, check the BigQuery [AI.GENERATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate) documentation.

Check out our other blog on [AI.FORECAST](https://www.measurelab.co.uk/insights/blog/how-to-start-forecasting-in-bigquery-with-zero-training/)

---

Want this running on your own marketing data? See how we build [BigQuery platforms for marketing data](https://www.measurelab.co.uk/solutions/intelligence-platforms/).

👉 The possibilities are endless, what’s the first BigQuery use case you’d try with AI.GENERATE?