What needs to be in place
Your GA4 property is linked to a Google Cloud project via BigQuery links in Admin, and the export has been running for at least a day. Note: the export starts from the moment you link, and past data won’t appear retroactively.
How the data is structured
- The dataset is named
analytics_ plus your property number, e.g. analytics_123456789.
- The daily export creates
events_YYYYMMDD tables, one per day. If streaming export is on, the current day is in events_intraday_YYYYMMDD.
- One row is one event:
page_view, first_visit, generate_lead and so on.
- Event parameters — page URL, title and so on — aren’t in separate columns but in a nested
event_params list of key–value pairs. To get them out you need UNNEST.
Query 1. Events and users by day
SELECT
event_date,
COUNT(*) AS events,
COUNT(DISTINCT user_pseudo_id) AS users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
GROUP BY event_date
ORDER BY event_date;
events_* takes all the daily tables at once, and the _TABLE_SUFFIX condition keeps only the dates you need. Without it, the query reads your entire history — and you pay for that. Note that user_pseudo_id is a browser or device, not a person.
Query 2. Most viewed pages
SELECT
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'page_location') AS page,
COUNT(*) AS views
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
AND event_name = 'page_view'
GROUP BY page
ORDER BY views DESC
LIMIT 20;
The subquery with UNNEST pulls the value with the page_location key out of the parameter list. Other parameters work the same way: page_title, page_referrer. Numeric values are in value.int_value, not string_value.
Query 3. Where new users came from
SELECT
traffic_source.source AS source,
traffic_source.medium AS medium,
COUNT(DISTINCT user_pseudo_id) AS new_users
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
AND event_name = 'first_visit'
GROUP BY source, medium
ORDER BY new_users DESC;
The traffic_source field stores the source of the user’s first visit, not the current session. It suits new users; for session analysis, other fields are used, such as collected_traffic_source.
Why the numbers differ from the GA4 interface
- The Analytics interface uses modelling and data thresholds, while BigQuery holds only the events actually collected.
- Users in BigQuery are counted by device and browser, while GA4 reports may combine them differently.
event_date is in the property’s time zone, while event_timestamp is in UTC.
How to avoid overpaying
- Always limit dates with
_TABLE_SUFFIX.
- Select only the columns you need. BigQuery charges for the amount of data read, not for the number of rows returned, so
LIMIT doesn’t save money and SELECT * is the most expensive query.
- Check the estimate in the editor before running: BigQuery shows how much data a query will process.
- BigQuery’s free tier includes 1 TB of query processing per month and 10 GB of storage. That’s usually enough for a small site.
- In the BigQuery sandbox without billing, tables are deleted after 60 days. For long-term storage you need a project with a billing account.
- Standard GA4 properties have a daily export limit of one million events per day; if it’s consistently exceeded, the export is paused.
In short
- One row is one event; parameters are in the nested
event_params.
- Query tables via
events_* and always filter on _TABLE_SUFFIX.
- Parameters come out with an
UNNEST subquery.
traffic_source is the first-visit source, not the session source.- You pay for data read: select only the columns you need.
If you still need to set up the export or want ready-made reports on raw data, we can set up the Google Analytics 4 export to BigQuery: create the project, link it to GA4, check the data and set up cost controls. Query results are easy to present in a Data Studio report.
