Events per day
Event counts by day are the first sanity check on any analytics export. The dates need care, because the day in the table name and the UTC day can differ.
How it works
The GA4 export has one table per day, called a shard. A wildcard table reads several shards, and _TABLE_SUFFIX tells you which day each row came from. Grouping by it turns event rows into one row per day.
- Use a wildcard table name that ends in
*, and limit_TABLE_SUFFIXwith literalYYYYMMDDbounds. GROUP BY _TABLE_SUFFIXgives one group per day.COUNT(*)counts the exported event rows.event_timestampis a UTC instant. The shard date follows the property's reporting timezone.
Common mistake
For an America/Los_Angeles property, the UTC date differs from the local reporting date late in the local day. That is 16:00 to 23:59 in winter (UTC-8) and 17:00 to 23:59 in summer (UTC-7).
Worked example
WITH events AS (
SELECT '20240201' AS shard_date, 'view' AS event_name UNION ALL
SELECT '20240201', 'click' UNION ALL SELECT '20240202', 'view'
)
SELECT shard_date, COUNT(*) AS events
FROM events
GROUP BY shard_date
ORDER BY shard_dateThe inline rows stand in for two daily shards and keep their reporting-date labels.
The output shows two events on 20240201 and one on 20240202.