Ask the GA4 BigQuery export how many purchases you had last week, and several queries will return a clean number. One counts events, another transactions, another buyers, another item rows. Each can be correct, and only one answers your question.
Aggregation errors rarely come from syntax. They come from not knowing what one row represents. This guide builds on Your First Useful SQL Query on Analytics Data, so it assumes you know SELECT, WHERE and DISTINCT. SQL also has INSERT, UPDATE and DELETE, which change stored data, but nothing here needs them.
The queries are illustrations. They have not been run on real data, so replace the table name and test them on a short date range.
Name the grain before you write GROUP BY
Grain means what one row represents. When you query the export, treat each top-level row as one event, with parameters and items nested in repeated fields. That is why Google's basic queries use COUNT(*) as an event count.
A GROUP BY query has two grains. Say both before typing: "One input row is an event. I want one output row per day and event name."
SELECT
event_date,
event_name,
COUNT(*) AS event_count
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261026' AND '20261101'
GROUP BY event_date, event_name
ORDER BY event_date, event_count DESC;GROUP BY collapses rows that share the same values into one. Every selected column must be either grouped or aggregated, otherwise the query fails. Adding a grouped column changes the output grain. Add and you get one row per day, event name and device.
