GA4 BigQuery Export

The GA4 BigQuery export sends every raw event from a Google Analytics 4 property into Google BigQuery, Google's cloud data warehouse, where you can query it with SQL. Instead of the summaries in GA4 reports, you get one row per event, with no sampling, and you can join it with ad costs, orders and other business data.

• Raw events: Every page_view, add_to_cart and purchase, with all its parameters.
• Daily tables: One table per day, named events_ followed by the date.
• Your own rules: Build sessions, funnels and channel rules the way your business defines them.
• No backfill: Data starts from the day you link, so link early.
• Costs: Storage and queries are billed by BigQuery, with a monthly allowance.

This tutorial follows one example: a D2C namkeen and snacks brand from Indore that sells on its own site and ships across India. Its GA4 reports answer basic questions, but the founder wants to join website events with courier data to see which products lead to repeat orders. That needs raw data, so the team sets up the GA4 BigQuery export.

GA4 reports are built for common questions, and they hide some detail to keep reports fast and to protect privacy. When the founder asks something GA4 was not designed for, such as how many first orders included the garlic sev pack and were followed by a second order within 60 days, the only way to answer is to work with the event-level data directly.

Prerequisites for the GA4 BigQuery Export

• A GA4 property with clean events: The purchase event and its items must be correct, as in GA4 events and conversions.
• Editor access to GA4: And owner access to the Google Cloud project.
• A Google Cloud project: Created at console.cloud.google.com with the BigQuery API enabled.
• A billing decision: Start in the BigQuery sandbox, which needs no billing account but has limits, or add billing for full features.
• Basic SQL: SELECT, WHERE and GROUP BY, covered in SQL for marketers.

Setup: Link GA4 to BigQuery

1. In GA4, go to Admin > Product links > BigQuery links and select Link.
2. Choose the project: Pick the Google Cloud project you created.
3. Choose the data location: Pick a region close to your team, such as Mumbai, and keep it the same as your other datasets. It cannot be changed later.
4. Choose the data streams: Select the web stream, and app streams if you have them.
5. Choose the frequency: Daily exports once a day. Streaming sends events within minutes but costs more and is not required for most small brands.
6. Submit: The first daily table usually appears within a day or two.

Step-by-Step: Query GA4 Data in BigQuery

Step 1: Find the Dataset and Tables

Open the BigQuery console. Under your project you will see a dataset called analytics_ followed by the property ID. Inside it are tables named events_ followed by the date, one per day, and events_intraday_ tables if streaming is on.

Step 2: Understand the Schema

Each row is one event. The columns you will use most:

Each value inside event_params has four possible fields: string_value, int_value, float_value and double_value. Use the one that matches the parameter.

Step 3: Count Purchases and Revenue by Day

This query reads the last 30 days of daily tables and totals purchases and revenue:

SELECT
  event_date,
  COUNT(*) AS purchases,
  ROUND(SUM(ecommerce.purchase_revenue), 2) AS revenue_inr
FROM `indore-snacks.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date;

• events_*: A wildcard that reads many daily tables at once.
• _TABLE_SUFFIX: The date part of each table name. Filtering it limits the scan to 30 days, which saves cost.
• purchase_revenue: Revenue in the currency sent with the event, INR for this brand.

Step 4: Read a Parameter with UNNEST

To see which pages get the most views, pull page_location out of event_params:

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page,
  COUNT(*) AS page_views,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `indore-snacks.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND event_name = 'page_view'
GROUP BY page
ORDER BY page_views DESC
LIMIT 20;

The small SELECT inside brackets opens the event's parameter list, finds the row whose key is page_location, and returns its string_value. The same pattern reads any parameter, such as ga_session_id with int_value.

This pattern is the one idea that makes the GA4 export feel different from an ordinary table. Once you are comfortable with it, most questions become a matter of choosing the right event, the right parameter and the right date range, and the rest is the same GROUP BY and ORDER BY logic used in any other SQL query.

Step 5: Preview the Cost Before Running

Before you run a query, the BigQuery editor shows how much data it will process. Keep that number small by selecting only the columns you need and always filtering _TABLE_SUFFIX. Set a budget alert in Google Cloud billing so a mistake cannot run up a large bill.

Step 6: Save and Share Results

Save useful queries in the editor. For a team dashboard, save the result as a small table and connect it to the Looker Studio dashboard, which is faster and cheaper than reading raw events on every page load.

Common Mistakes with the GA4 BigQuery Export

• Linking late: No backfill means every week without a link is data you will never have.
• Scanning everything: A query on events_* with no suffix filter reads every day ever exported.
• Selecting all columns: SELECT * reads nested fields you do not need and raises cost.
• Expecting report totals: Raw counts differ from GA4 reports because of consent modelling and thresholds. Compare trends.
• Wrong value field: Reading string_value for a number parameter returns null. Check which field holds it.
• Personal data: The export is only as clean as your events. Never send names, phones or emails to GA4 in the first place.

Next Steps

• Learn more SQL: Joins and CASE statements are covered in SQL for marketers.
• Join business data: Load courier and order data into the same project and join on transaction ID.
• Model spend: Raw data feeds advanced methods such as marketing mix modeling.

How AI Changes GA4 BigQuery Analysis

What AI Automates Now

AI assistants can write SQL for the GA4 export schema from a plain question, explain an error message, and turn results into a summary. BigQuery also offers Gemini features that suggest and explain SQL inside the console.

What Still Needs a Human

A person must define the business logic (what counts as a repeat order, which channel rules to use), check the query reads the right tables and fields, and confirm results against GA4 trends.

Risk to Watch

AI-written SQL often looks right but reads the wrong value field, forgets the suffix filter or counts events instead of users. Run new queries on a single day first, and read the bytes estimate before running them on months of data.

Do It with AI

Use this prompt to get a first draft of a query on the GA4 export. It works in ChatGPT, Claude or Gemini.

1. Fill in the dataset, question and your real event names.
2. Paste the query into BigQuery and read the bytes estimate.
3. Run it on one day first and check the result against GA4.
4. Widen the date range only when the one-day result makes sense.

Check Before You Use It

• Facts: Check field names against Google's current export schema before trusting a result.
• Brand fit: Make sure the business definitions in the query match how the team counts orders and customers.
• Compliance: Share only the schema and question with AI tools, never exported rows that could identify a customer.