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.

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_date

The 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.

All BigQuery lessons