BigQuery query examples: six sample queries you can run on GA4 data

Before you run them

Always filter on _TABLE_SUFFIX. The export writes one table per day, and events_* means all of them. The filter is what limits the days BigQuery reads and bills. More in wildcard tables.

Parameters are nested. Values such as the page title and the session ID sit in the event_params array, so they are read with a small subquery over UNNEST. Queries 2 and 3 show the pattern; the guide to unnesting GA4 event parameters explains it.

1. Which events are recorded, and how often

The first query to run on any export: what is in it.

SELECT
  event_name,
  COUNT(*) AS events,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
GROUP BY event_name
ORDER BY events DESC
LIMIT 5;

2. Users and sessions per day

GA4 has no session table. A session is a user ID plus the ga_session_id parameter.

SELECT
  event_date AS day,
  COUNT(DISTINCT user_pseudo_id) AS users,
  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 `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
GROUP BY day
ORDER BY day;

Daily users do not add up to weekly users: one person on three days is counted three times here and once in a weekly COUNT(DISTINCT).

3. Most viewed pages

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_title') AS page,
  COUNT(*) AS views
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'page_view'
GROUP BY page
ORDER BY views DESC
LIMIT 5;

Swap page_title for page_location to group by URL.

4. Purchases and revenue by traffic source

SELECT
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  COUNT(*) AS purchases,
  ROUND(SUM(ecommerce.purchase_revenue_in_usd), 2) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'purchase'
GROUP BY source, medium
ORDER BY revenue DESC
LIMIT 5;

Read this one carefully. traffic_source is the source that first brought the user, not the source of the session in which they bought. It will not match the session-based reports in the GA4 interface. That mismatch is the most common reason people think their export is wrong; see GA4 BigQuery Export mistakes. The “(data deleted)” rows are part of the obfuscation of this sample.

5. Best-selling items

Items are a second nested array. Here it is joined to its event with a comma, which makes one row per item.

SELECT
  i.item_name,
  SUM(i.quantity) AS quantity,
  ROUND(SUM(i.item_revenue_in_usd), 2) AS revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items) AS i
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'purchase'
GROUP BY i.item_name
ORDER BY revenue DESC
LIMIT 5;

Do not sum ecommerce.purchase_revenue_in_usd in this query. After the unnest, an order with three items has three rows, and the order total would be counted three times. The JOIN and UNION guide calls this fan-out.

6. A purchase funnel

SELECT
  COUNT(DISTINCT IF(event_name = 'view_item',
    user_pseudo_id, NULL)) AS viewed,
  COUNT(DISTINCT IF(event_name = 'add_to_cart',
    user_pseudo_id, NULL)) AS added,
  COUNT(DISTINCT IF(event_name = 'begin_checkout',
    user_pseudo_id, NULL)) AS checkout,
  COUNT(DISTINCT IF(event_name = 'purchase',
    user_pseudo_id, NULL)) AS purchased
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107';

Of the users who viewed an item, 20.7% added one to the cart and 3.8% bought. This is an open funnel: it counts users who did each step in the week, in any order. A closed funnel, where each step must follow the one before, needs window functions; see the window functions guide.

Adapting the queries

• Your own export: change the table name and the dates. Use FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) in place of a fixed suffix for a rolling week.
• Today’s data: if streaming export is on, today’s events are in events_intraday_*.
• Cost: all six together read about 91 MiB of this small sample. On a large property, a week of events_* can be gigabytes; check the estimate the console shows before you run. See BigQuery pricing.

The link itself is covered in the GA4 to BigQuery guide, and the functions used above in the BigQuery functions map.

From sample queries to a report people use

A query that works is the start. The next person to need “sessions” will write their own, and by the third copy the definitions have drifted: one counts sessions with the session ID, one counts session_start events, and the totals differ by a few percent that nobody can explain.

In OWOX Data Marts you publish the query once as a Data Mart, describe each column, and deliver it on a schedule to Google Sheets or a dashboard. Everyone reads the same sessions. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.

Google BigQueryBigQuery aggregate functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and moreOctober 16, 2026→Google BigQueryBigQuery Array Functions: Syntax and Tested ExamplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: Convert Data Types Without ErrorsOctober 6, 2026→

See all articles →