GA4 BigQuery Export: Setup and First Queries for SEO

If GA4 reports keep hitting limits for you, such as “(other)” rows, thresholds or slow explorations, the GA4 BigQuery export is the way out. It sends your raw event data to Google BigQuery, a cloud database you query with SQL. The export is free to turn on for standard GA4 properties, and you can run your first SEO queries the same week.

In this guide I cover what the export gives you, what it costs, how to set it up step by step, and the first queries I run for organic search. You don’t need to be a data engineer. You need about 30 minutes and a Google Cloud account.

What the GA4 BigQuery Export Actually Gives You

GA4 reports show summaries. The BigQuery export gives you every single event behind those summaries: each page view, scroll, click and form submit, with its parameters, device, location and traffic source.

For SEO work, that matters in a few ways:

• No sampling or “(other)” rows. Big sites with many URLs often see long-tail landing pages lumped into “(other)” in GA4 reports. In BigQuery, every URL is there.
• Your own definitions. You decide what counts as a session, a landing page or a conversion, instead of living with the report defaults.
• History you control. GA4 keeps event-level data for explorations for 2 or 14 months. Data in BigQuery stays as long as you keep it.
• Joins with other data. You can combine GA4 data with Search Console, CRM or sales data in one place.

In my experience, the biggest win for most sites is the last point on history. Even if you don’t write a single query this year, turning on the export now means the raw data is there when you need it.

One Thing It Won’t Do: Backfill

The export only starts from the day you link it. Google does not send your older GA4 data to BigQuery. That is the main reason I tell people to set it up early, even on small sites. It is the same lesson many sites learned when Universal Analytics stopped processing data: history you don’t save early is hard to get back.

What It Costs and the Limits to Know

Linking GA4 to BigQuery costs nothing on the GA4 side. The costs, if any, come from BigQuery itself, which charges for two things: storage and query processing.

You have two ways to start:

• BigQuery sandbox. A free mode that needs no credit card. It gives you 10 GB of storage and 1 TB of query processing per month. Tables expire after 60 days by default, and the streaming export is not available.
• A project with billing turned on. You still get a free monthly allowance (the first 10 GB of storage and 1 TB of queries), then pay for what you use above that.

For a small or medium site, I usually see costs stay at or near zero for a long time. A site with a few thousand visits a day creates a small dataset by BigQuery standards.

The sandbox is fine for testing, but I’d move to billing quickly. The 60-day expiry means your data quietly disappears, which defeats the main reason for exporting.

The Daily Export Limit

Standard GA4 properties have a limit of 1 million events per day for the daily export. Google says that if a property keeps going over this limit, the daily export is paused, and days that were missed are not re-processed later.

There is no event limit on the streaming export. If you are close to 1 million events a day, you have two options: exclude events you don’t need during setup, or use streaming. GA4 360 properties have higher limits.

How to Set Up the GA4 BigQuery Export

Before you start, check your access. You need the Editor role (or higher) on the GA4 property, and the email you use must have Owner access on the Google Cloud project. If you are not sure what your GA4 role is, my guide to GA4 user roles and access explains each level.

Step 1: Create a Google Cloud Project

1. Go to the Google Cloud console and sign in with the same Google account you use for GA4.
2. Click the project picker at the top and choose New project.
3. Give it a clear name, such as “yoursite-analytics”, and click Create.

I keep one project per business, not per website feature. It makes billing and access much easier to manage later.

Step 2: Turn On the BigQuery API

1. In the Cloud console, open APIs & Services and then Library.
2. Search for “BigQuery API” and open it.
3. Click Enable and accept the terms if asked.

Step 3: Choose Sandbox or Billing

If you want to test first, you can use the sandbox and skip billing. If you plan to keep the data, add a billing account to the project now. Google says a valid payment method must be on file for exports to keep running, and data missed because of a payment problem is not re-exported.

Step 4: Link GA4 to BigQuery

1. In GA4, click Admin, then under Product links click BigQuery links.
2. Click Link, then Choose a BigQuery project, select your project and click Confirm.
3. Pick a data location, such as a US or EU region.
4. Choose which data streams to include, and add any events to exclude.
5. Choose the export frequency: Daily, Streaming, or both.
6. Review the settings and click Submit.

Take care with the data location. Once the dataset exists, you can’t simply switch regions. Google’s process is to delete the link, recreate the dataset elsewhere and relink, which leaves a gap in your data. I pick the region where the business and its other data live, and I don’t change it.

The full list of options is in Google’s own help page on how to set up BigQuery Export.

Step 5: Check the Data Arrived

Google says data should start arriving within 24 hours. In BigQuery, you should then see a dataset called analytics_ followed by your property ID, with tables inside it.

Daily vs Streaming: Which to Pick

The two export types create different tables:

• Daily creates one table per day, named events_YYYYMMDD. It contains the previous day’s data and usually lands in the early afternoon in your reporting time zone. Events that arrive late are added for up to three days.
• Streaming fills a table named events_intraday_YYYYMMDD through the day. When the daily table for that date is complete, the intraday table is removed.

For SEO, I almost always pick Daily only. Organic traffic analysis rarely needs data from the last hour. Streaming adds cost, and some fields, such as the first-user traffic_source, are not filled in the intraday table anyway.

I’d choose streaming only when you go over the 1 million events limit, or when someone needs same-day numbers for a launch or sale.

How the Export Data Is Structured

This is where most people get stuck, so it helps to know the shape before writing queries. Each row in an events table is one event. Some fields are simple columns, and some are nested lists.

The fields I use most for SEO work:

• event_date and event_name: the day and the type of event, such as page_view or session_start.
• user_pseudo_id: an anonymous ID for each browser or device.
• event_params: a nested list of key and value pairs. Page URL, page title and session ID all live here.
• device and geo: device category, browser, country and city.
• traffic_source: the source that first brought the user to your site, not the current session.
• collected_traffic_source: the source data collected on each event.
• session_traffic_source_last_click: the session’s source and medium using last non-direct click. Google added this in July 2024, so older tables don’t have it.

The mistake I usually see is using traffic_source to report organic sessions. It describes how a user first arrived, so a user who first came from an ad and returns through Google will still show as paid. For session-level SEO numbers, use session_traffic_source_last_click.

How to Read Event Parameters

Because event_params is a list, you pull a value out with a small subquery and UNNEST (a SQL function that opens a list into rows). This pattern appears in nearly every GA4 query:

(SELECT value.string_value
 FROM UNNEST(event_params)
 WHERE key = 'page_location') AS page_location

Text values sit in string_value, and whole numbers sit in int_value. The session ID, ga_session_id, is a number, so you read it from int_value.

First GA4 BigQuery Queries for SEO

Open BigQuery, click the + to start a new query and paste each example. Replace your-project.analytics_123456789 with your own project and dataset name. Change the dates to a range you have data for.

Query 1: Check What Events You Are Collecting

I run this first on every new export. It confirms the data is flowing and shows whether your key events are there.

SELECT
  event_name,
  COUNT(*) AS events
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250501' AND '20250528'
GROUP BY event_name
ORDER BY events DESC;

The _TABLE_SUFFIX line limits the query to the dates you name. Always include it. Without it, BigQuery scans every daily table, which costs more and runs slower.

Query 2: Organic Landing Pages by Sessions

This gives you a clean organic landing page report with no “(other)” row. It counts sessions that came from organic search, grouped by the first page viewed.

WITH landings AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS session_id,
    SPLIT((SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location'), '?')[OFFSET(0)] AS landing_page
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250501' AND '20250528'
    AND event_name = 'page_view'
    AND (SELECT value.int_value FROM UNNEST(event_params)
         WHERE key = 'entrances') = 1
    AND session_traffic_source_last_click.manual_campaign.medium = 'organic'
)
SELECT
  landing_page,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(session_id AS STRING))) AS organic_sessions
FROM landings
GROUP BY landing_page
ORDER BY organic_sessions DESC
LIMIT 100;

A few notes on how it works:

• The entrances parameter equals 1 on the first page view of a session, which is the landing page.
• A session is counted as the user ID plus the session ID, because session IDs alone are not unique across users.
• The SPLIT part removes query strings, so /pricing/?ref=x and /pricing/ are counted as one page.
• To see only Google, add AND session_traffic_source_last_click.manual_campaign.source = 'google'.

Query 3: Organic Sessions That Led to a Key Event

Traffic is only half the story. This query shows which organic landing pages start sessions that end in a key event (what GA4 used to call a conversion). Change generate_lead to your own key event name.

WITH events AS (
  SELECT
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params)
      WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    event_name,
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'entrances') AS entrances,
    SPLIT((SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location'), '?')[OFFSET(0)] AS page,
    session_traffic_source_last_click.manual_campaign.medium AS medium
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250501' AND '20250528'
),
sessions AS (
  SELECT
    session_key,
    MAX(IF(event_name = 'page_view' AND entrances = 1, page, NULL)) AS landing_page,
    MAX(IF(event_name = 'generate_lead', 1, 0)) AS converted
  FROM events
  WHERE medium = 'organic'
  GROUP BY session_key
)
SELECT
  landing_page,
  COUNT(*) AS organic_sessions,
  SUM(converted) AS sessions_with_lead,
  ROUND(SAFE_DIVIDE(SUM(converted), COUNT(*)) * 100, 2) AS lead_rate_pct
FROM sessions
WHERE landing_page IS NOT NULL
GROUP BY landing_page
ORDER BY sessions_with_lead DESC
LIMIT 50;

When I look at this output, I sort two ways. By leads, to see which pages earn their keep. By sessions with a low lead rate, to find pages with good rankings but a weak offer or call to action.

Query 4: Referrals From AI Assistants

AI tools such as ChatGPT, Perplexity, Gemini and Copilot now send some visits. When they pass a referrer or tag their links, the source shows up in the export. This query groups those sessions by source:

SELECT
  session_traffic_source_last_click.manual_campaign.source AS source,
  COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value
    FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS sessions
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250501' AND '20250528'
  AND REGEXP_CONTAINS(session_traffic_source_last_click.manual_campaign.source,
      r'chatgpt|openai|perplexity|gemini|copilot')
GROUP BY source
ORDER BY sessions DESC;

Treat these numbers as a floor, not the full picture. Some AI visits arrive with no referrer and land in direct traffic.

Keep BigQuery Costs Low

BigQuery charges for queries by the amount of data each one scans, not by how long it runs. A few habits keep that number small:

• Always filter dates with _TABLE_SUFFIX. This is the single biggest saver.
• Avoid SELECT *. BigQuery stores data by column, so naming only the columns you need scans less.
• Check the estimate first. The query editor shows how much data a query will process before you run it. I glance at it every time.
• Use the table preview to explore. Previewing a table is free, while a query that returns the same rows is not.
• Save results you reuse. If you run the same landing page report every week, save it as a smaller table or a scheduled query.

You can also set a budget alert on the billing account in Google Cloud. It won’t stop spending, but it emails you before a surprise bill.

Why BigQuery Numbers Won’t Match GA4 Reports

Expect small differences between your queries and the GA4 interface. This is normal, and it’s the question I get most after people set up the export.

The usual reasons:

• Counting method. GA4 reports estimate counts of users and sessions with an approximation method. BigQuery gives you exact counts.
• Modelled data. If you use consent mode, GA4 reports can include modelled data for users who declined cookies. The raw export does not include that modelling.
• Different definitions. Your session and landing page logic may not match GA4’s exactly, such as how you treat sessions with no page view.
• Time zones and late data. Event timestamps are in UTC, while event_date uses your property’s time zone. Late events are also added to daily tables for a few days.

My rule of thumb: if trends match and totals are close, trust both. If they are far apart, check your date range, your filters and whether you are reading the right traffic source field.

What to Do After Your First Queries

Once the basics work, these are the next steps I’d take:

1. Connect Looker Studio. Turn your saved queries into charts the team can use without writing SQL. My guide on sharing a Looker Studio report covers who should get viewer or editor access.
2. Add Search Console data. Search Console has its own bulk data export to BigQuery. With both in one project, you can line up clicks and queries with what visitors did on the page.
3. Write down your definitions. Keep a short note of how you count sessions, landing pages and organic traffic, so reports stay consistent.

If you are still deciding which Google tools your site needs, my overview of free Google tools for SEO shows where BigQuery fits beside GA4, Search Console and Looker Studio.

Conclusion

The GA4 BigQuery export is one of the most useful free settings in Google Analytics, and it only collects data from the day you switch it on. Link it, choose Daily, filter by date in every query, and start with organic landing pages and key events.

If your GA4 property itself still needs work, such as missing key events or a messy data stream, fix that first with my GA4 setup guide. Clean tracking in GA4 means clean data in BigQuery.