Build one SQL query on the GA4 BigQuery export with SELECT, WHERE and DISTINCT, check its scope and cost with a dry run, then test the answer before sharing.
6 min readBranislav Mateas
Copy post as Markdown
On this page
Your first useful SQL query does not need to be clever. It needs to answer one question, read as little data as possible, and survive a check before anyone acts on it. SELECT, WHERE and DISTINCT are enough for that.
This article builds one query on the GA4 BigQuery export, step by step. All SQL here is an illustration written against Google's documented schema. It has not been run on real data. The project, dataset and dates are hypothetical, so replace them with your own.
Turn the business question into columns
The question is simple: how many users triggered generate_lead last week? Three parts of it need a definition before you write any SQL.
The event. Google lists generate_lead as a recommended event for when someone "submits a form or a request for information". Check that your setup sends this exact name. If it sends a custom one, use yours.
Last week. In this example, Monday 26 October to Sunday 1 November 2026. The daily export creates one table per day, named events_YYYYMMDD.
Users. Use user_pseudo_id. Google's basic queries say to count user_id values, and to use user_pseudo_id if you are unsure.
That last definition needs care. user_pseudo_id is an identifier, not a person. Google notes that separate browsers can produce separate IDs, and that without consent "each session will have a different user_pseudo_id". So your answer counts identifiers. Say that when you report it.
SELECT names the one column you need
SELECT says which columns to return. FROM says which tables to read. Start with the smallest version:
-- Illustration only. Incomplete: do not run it yet.
SELECT user_pseudo_id
FROM `your-project.your_dataset.events_*`
The * matches every daily table. Because the name contains it, you must wrap the name in backticks. Quotation marks produce a syntax error.
Notice what is missing: SELECT *. Google's advice is to query only the columns you need, because extra columns mean wasted reads. Adding LIMIT does not rescue it. The same page says a LIMIT on a SELECT * query "does not affect the amount of data read".
If you want to see what the table looks like, use the table preview instead of a query. It shows the data at no charge.
WHERE limits the days and the event
WHERE is where this query gets its scope. Add two conditions:
-- Illustration only, not run on real data.
SELECT user_pseudo_id
FROM `your-project.your_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261026' AND '20261101'
AND event_name = 'generate_lead'
The two conditions do different jobs.
_TABLE_SUFFIX decides which tables BigQuery reads. It holds the part of the table name that the wildcard matched, and filtering on it can greatly reduce the bytes scanned. The condition must be a constant expression. A subquery that works out the date does not limit the tables scanned, so write the dates as plain strings.
If streaming export is on, events_* also matches the events_intraday_YYYYMMDD tables. Their suffix starts with the word intraday_, so a range of numeric dates leaves them out.
event_name keeps only the lead rows in the result. It does not limit which tables are read.
DISTINCT turns events into users
The query so far returns one row per event. Someone who submits two forms appears twice, and that overstates your number. SELECT DISTINCTdiscards duplicate rows, which is exactly what you need:
-- Illustration only, not run on real data.
SELECT DISTINCT user_pseudo_id
FROM `your-project.your_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261026' AND '20261101'
AND event_name = 'generate_lead'
Each row is now one ID, so the number of rows is your answer. To get that number as a single cell, change only the first line. This is the same form Google uses in its sample queries:
SELECT COUNT(DISTINCT user_pseudo_id) AS users_with_lead
The dry run tells you the cost of your scope before you pay for it. In the BigQuery editor, a valid query shows a check mark and the amount of data it will process. Dry runs are not charged. From the command line, it looks like this:
# Illustration only. Paste your full query between the quotes.
bq query --use_legacy_sql=false --dry_run 'SELECT DISTINCT user_pseudo_id FROM ...'
Read that number as a test of your scope. You asked for one column across seven daily tables, so the estimate should look like that. To see the filter working, delete the _TABLE_SUFFIX line and look at the estimate again, without running anything. Then put the line back.
The estimate is an upper bound, and the bytes billed can be lower. A number far above what you expected is a reason to stop and look at the query. For a hard limit, set maximum bytes billed. A query estimated above that limit fails without a charge.
A query that runs without an error can still be wrong. Run three checks.
Are all the days there? Keep the FROM and WHERE, and change the first line to SELECT DISTINCT event_date. You expect seven rows. Fewer rows mean a day had no leads or the table is missing. The daily export typically lands mid-afternoon in your property's timezone and can slip to the next day, so a very recent range may be incomplete.
Did the event fire in the right place? This is the one point where you need event_params. Each event row holds a repeated list of parameters, each with a key and a typed value. A URL sits in value.string_value. Web events collect page_location by default, so you can pull it out like this:
-- Illustration only, not run on real data.
SELECT DISTINCT
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location') AS page_location
FROM `your-project.your_dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261026' AND '20261101'
AND event_name = 'generate_lead'
UNNEST turns the list into a small table with one row per parameter. The inner query picks the one row you want. A scalar subquery like this returns NULL for zero rows and fails if it finds more than one. Now read the list. A lead on a page with no form points to a tracking problem, and the count inherits it. This query reads an extra column, so do the dry run again first.
Does it roughly agree with GA4? Compare it with a GA4 report for the same event and dates, but do not expect a match. Google explains why: unique counts in the interface are approximations, modeled data is not in the export, and Google Signals can lower the user count in reports. Also, event_date follows the reporting time zone, while event_timestamp is UTC. A large gap means you should recheck the event name and the dates.
When all three checks pass, report the number together with its definition: distinct user_pseudo_id values with generate_lead, 26 October to 1 November 2026, from the daily export tables. Anyone reading it then knows exactly what was counted.
