Real-Life SQL Queries for BigQuery in GA4

Once you understand the building blocks of SQL, the next step is putting them to work on real questions.

GA4 data in BigQuery opens up analysis that the standard reporting interface simply can't match, but the nested structure of the export tables means even simple questions often require combining several techniques at once. In this post, we'll walk through four practical queries you can run against your own GA4 export data: auditing every event and parameter flowing into your property, calculating conversions and revenue for a specific week, counting active users over a rolling time window, and identifying your top-selling products. Each query is ready to use as-is or to serve as a foundation you can adapt to your own reporting needs.

Outputting All Events Collected with Their Parameters

The parameters of an event are all collected in a nested field, both system parameters and custom parameters created by you. With larger or unknown setups, keeping track of which event names and parameters are actually being used can become quite difficult.

The script shown below reads the event parameters and lists them for each event name. The script also specifies the field type in which the parameter stores its data. This script thus provides a complete overview of which events, with which parameters, have entered a GA4 property and have therefore been used in the tracking code. This table is quite helpful as documentation and for checking inbound data.

SELECT

    event_name,

    params.key AS event_parameter_key,

    CASE WHEN params.value.string_value IS NOT NULL THEN 'string'

       WHEN params.value.int_value IS NOT NULL THEN 'int'

       WHEN params.value.double_value IS NOT NULL THEN 'double'

       WHEN params.value.float_value IS NOT NULL THEN 'float'

       ELSE NULL END AS event_parameter_value

FROM

    `analytics_151687601.events_202409*` AS t,

    UNNEST(event_params) AS params

GROUP BY

    1, 2, 3

ORDER BY

    1, 2

If you’re only looking for the parameters for a specific event, you can focus using a WHERE statement. The size of the generated table naturally depends on the property being analyzed.

If you add a COUNT command as an additional column, as in the following example, you’ll also receive the frequency of the parameters:

...

    ELSE NULL END AS event_parameter_value,

    COUNT(*)

FROM

...

Determining Conversions and Revenue for One Week

The script shown below queries events for the purchase and in_app_purchase e-commerce conversions for the period of one week from 9/19 to 9/25. The number of conversions and revenue are to be shown per day. Revenue values are rounded to two decimal places using ROUND.

To select a week as the period, the entire month of September is selected in the FROM statement and then the start and end dates are referred fed into the WHERE statement. For longer periods, you would adjust these two dates accordingly. You can also query the entire events table and specify a date, which will increase the data volume and runtime accordingly.

SELECT

    event_date AS Date,

    COUNT(DISTINCT user_pseudo_id) AS Conversions,

    ROUND(SUM(event_value_in_usd),2) AS Revenue

FROM

    `analytics_151687601.events_202409*`

WHERE

    event_name IN ('in_app_purchase', 'purchase')

    AND event_date BETWEEN '20240919' AND '20240925'

GROUP BY

ORDER BY Date

The filter on the event name uses a list query: The event_name of the cell must correspond to one of the values in the list specified with IN.

The BETWEEN ... AND statement specifies a number range “from ... to” and is particularly useful with dates.

The result, shown in the table below, is a table that contains the daily values.

Determining Active Users in the Preceding 20 Days

Sometimes, you want to view users who have visited your website in a specific period of time. BigQuery can query absolute time periods, but you can also calculate a time period in the query. The following statement searches for users who have visited your offering in the blog area in the preceding 20 days.

SELECT

    COUNT(DISTINCT user_pseudo_id) AS Blog_User

FROM `analytics_151687601.events_202409*` AS t

    CROSS JOIN UNNEST (t.event_params) AS event_params

WHERE

    event_name = 'page_view'

    AND event_params.key = 'page_location'

    AND event_params.value.string_value LIKE '%/blog/%'

    AND event_timestamp >

       UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))

The last line of the script compares the timestamp of each row (i.e., the exact time and date when this event took place). The comparison value is calculated based on the time of the query (CURRENT_TIMESTAMP) and looks 20 days into the past (INTERVAL 20 DAY).

Top Products in the Period

The products of e-commerce orders are stored in the nested items column. To gain access to the products, you must first resolve the column via UNNEST.

The SQL script shown returns a table of the best-selling products, including the following information:

• The number of orders in which this product was sold
• The revenue generated as a result
• The quantity (i.e., how often a product was sold in total)

The HAVING statement means that a product must have been included in at least 50 orders to be displayed in the result.

SELECT

    items.item_name AS Product,

    COUNT(DISTINCT ecommerce.transaction_id) AS Orders,

    SUM(items.item_revenue) AS Revenue,

    SUM(items.quantity) AS Quantity

FROM

    `analytics_151687601.events_202409*` AS t,

    UNNEST(items) AS items

WHERE

    event_name = 'purchase'

GROUP BY

    1

HAVING Orders >= 50

ORDER BY Orders DESC

This table shows the result of the query.

Conclusion

These four queries cover some of the most common questions analysts bring to GA4 data, and each one demonstrates techniques you'll reuse constantly: unnesting event parameters and items, filtering with IN and BETWEEN, calculating rolling date ranges with timestamp functions, and refining aggregated results with HAVING.

Rather than treating them as finished products, think of them as templates. Swap in your own property ID, adjust the date ranges, add columns like counts or averages, and layer on WHERE conditions to narrow the focus. The more you customize these patterns to your own tracking setup, the faster you'll be able to answer new questions the moment they come up, without waiting on the limits of the standard GA4 interface.

Editor’s note: This post has been adapted from a section of the book Google Analytics 4: The Practical Guide by Markus Vollmert. Markus is the founder and managing director of the Cologne-based digital marketing agency luna-park.

This post was originally published 7/2026.