Standard SQL for the GA4 Export, with Plain-English Notes on What Each Query Returns
Type your project and dataset and this free GA4 BigQuery SQL generator writes GA4 BigQuery queries you can paste straight into the BigQuery console. Each one reads the events_ export tables, filters the dates on _TABLE_SUFFIX to keep the cost down, and unpacks event_params and items for you. Every template says what it returns, which columns you get, and where the numbers will differ from the GA4 interface.
GA4 + BigQueryFree SQL Generator
OfficialStape PartnerFill in your export details, pick a template and copy the SQL.
Rolling ranges end yesterday, because today’s table is not complete. Use the same time zone as your GA4 property.
Nineteen templates in four groups, each adjusted to your export.
Users, new users, sessions, events, conversions and revenue for each day.
Sessions and purchases by session source and medium, plus first-touch user acquisition.
Sessions, engagement and conversions by landing page, and top pages by views.
The funnel from view_item to purchase, revenue and average order value, and top items.
New and returning users, engagement time per session, event counts and weekly retention.
Pull one parameter out of event_params for the event you name and count its values.
Repeated transaction IDs, the share of events by consent state, and sessions with no source.
Every query filters the tables by date and selects only the columns it needs.
Three steps. The tool only writes text.
Project ID, dataset, date range and the time zone of your GA4 property.
Search or filter by group. Read what it returns and the caveats.
Paste the SQL into the BigQuery console, check the bytes it will process, then run it.
When you link GA4 to BigQuery, Google creates a dataset named analytics_ followed by your property ID. Inside it there is one table per day, named events_YYYYMMDD. If you switch on streaming export you also get events_intraday_YYYYMMDD for the current day.
Each row is one event. Details that belong to the event sit in nested lists: event_params holds parameters as a key with a value in one of four slots (string_value, int_value, float_value, double_value), and items holds the products. That is why a GA4 BigQuery query needs UNNEST, and why writing one from scratch is slow.
BigQuery charges on-demand queries by the amount of data they read. A query on events_* with no date filter reads every day you have ever exported. Filtering on _TABLE_SUFFIX tells BigQuery which daily tables to open, so it reads only those. Every template here does that. Before you run a query, look at the estimate of bytes it will process in the BigQuery console.
traffic_source is the source that first acquired the user. It does not change on later visits.collected_traffic_source holds the campaign values and click IDs collected with that event.session_traffic_source_last_click holds the last-click source of the session, including Google Ads details where available.The generator has a template for each and says which one it uses. The two newer records were added to the export after it launched. If your export is older, days from before they were added show empty values.
Google explains several reasons. The GA4 interface estimates counts such as users and sessions, while BigQuery counts them exactly. The interface can add modelled data for visitors who declined consent and can use Google Signals; neither is in the export. Reports can hide small rows through thresholding. Attribution in the interface cannot be fully rebuilt from the export. Daily tables can also keep changing for up to 72 hours. Expect close, not identical.
The queries are generated text. The tool cannot run them, cannot see your data and cannot confirm that your project ID, dataset or event names exist. It has not been able to test the SQL against your export. If your export started before a field was added, queries that use that field return empty values for the older days: this affects the session source template (session_traffic_source_last_click), the collected source and missing source templates (collected_traffic_source) and the active users column (is_active_user). Always check the bytes-processed estimate before you run anything, and compare a first result with a GA4 report you trust.
From Google’s export schema documentation.
| Field | What it holds | Note |
|---|---|---|
| event_timestamp | When the event was logged | Microseconds, in UTC |
| event_date | The day of the event | Text as YYYYMMDD, in the property’s time zone |
| user_pseudo_id | The anonymous ID for the browser or app install | Empty for events collected without analytics consent |
| ga_session_id | The session ID | Inside event_params as an int_value. Unique only together with user_pseudo_id |
| event_params | Parameters sent with the event | A repeated key and value record. Needs UNNEST |
| items | Products on ecommerce events | A repeated record. Needs UNNEST |
| ecommerce.purchase_revenue | Revenue of a purchase event | In the local currency. Purchase events only |
| traffic_source | Source that first acquired the user | Not filled in intraday tables |
| collected_traffic_source | Campaign values collected with the event | Added to the export later than the original schema |
| session_traffic_source_last_click | Last-click source of the session | Added to the export later than the original schema |
| privacy_info | Consent state for analytics and ads storage | Values Yes, No or Unset |
| is_active_user | Whether the user was active that day | Filled in daily tables only |
Start from a working query instead of a blank editor.
Get numbers out of BigQuery without learning UNNEST first.
Reuse the same checked templates across client exports.
Find duplicate purchases, consent gaps and missing sources quickly.
33 free tools for tracking, analytics, advertising and SEO. No login needed.
Build and test regular expressions for GTM triggers and GA4 filters, with ready patterns.
Open the regex tester →Get a tracking plan for your business type: events, parameters and platform mapping in Excel.
Open the plan generator →Scan a landing page and keywords for Quality Score, ad copy, CTR and tracking issues.
Open the Google Ads audit →Create clean UTM links with platform presets, GA4 channel preview and bulk export.
Open the UTM builder →Hear from our clients. Loved by 600+ businesses worldwide.
“I had an excellent experience working with MD on my Google Ads account. Everything was set up perfectly and worked smoothly without any issues or troubleshooting needed. His expertise and attention to detail made the whole process effortless.”
“I worked with Niamul on multiple Google Analytics projects and was impressed by his expertise and precision. He has a strong grasp of tracking, reporting, and optimization, always ensuring accurate insights. He is proactive, reliable, and easy to collaborate with.”
“MD Niamul is extremely skilled. He fixed my Shopify, Google Analytics, and Google Ads conversion tracking perfectly. Everything works exactly as it should now, and he even added an extra data layer, which was very helpful. Outstanding service, fast delivery, and highly recommended.”
“Reliable, knowledgeable, and truly trustworthy. I’ve been working with Niamul for over a year, and he consistently delivers exceptional results across all analytics tasks. His expertise, communication, and commitment make him my go-to specialist for tracking, measurement, and data accuracy.”
“MD N delivered flawless Meta Pixel, CAPI, and server-side tracking. He understood our goals quickly, explained everything clearly, and ensured full transparency. Communication was smooth, updates were consistent, and the results improved our data accuracy and ad performance.”
Five-star reviews from businesses and agencies in the USA, Canada, UK and Australia.
Read All ReviewsYes. It runs in your browser with no login and no email form.
No. It only writes SQL text using the project and dataset names you type. It cannot run the queries or see your data, and nothing you type is sent anywhere.
In the BigQuery console, the left panel lists your project. Under it is the dataset, named analytics_ followed by your GA4 property ID. The daily tables inside are named events_ with the date.
BigQuery bills on-demand queries by the data they read, and has a free monthly allowance. Each template limits the dates with _TABLE_SUFFIX and selects only the columns it needs. Check the bytes-processed estimate in the console before you run a query.
Google lists several reasons: the interface estimates some counts, adds modelled data and Google Signals, applies thresholding and uses attribution that cannot be fully rebuilt from the export. Recent days can also still be updating.
events_ tables are the finished daily export. events_intraday_ tables are filled during the day when streaming export is on, and some fields such as traffic_source and is_active_user are not filled there.
Parameters are stored in the repeated event_params record. You select the value with a small sub-query that uses UNNEST and the parameter key. The “Extract an event parameter” template writes it for you.
The main template uses session_traffic_source_last_click, the session’s last-click source. A second template uses collected_traffic_source for older exports. Both are explained next to the SQL, with their limits.
They are written against Google’s published export schema and checked for syntax, but they have not been run on your data. Treat the first result as a draft and compare it with a GA4 report.
I am MD Niamul, a conversion tracking and web analytics specialist and an official Stape partner. I set up GA4 with BigQuery export, fix the tracking behind it, and build reporting in Looker Studio that your team can rely on.