GA4 BigQuery Export Analysis: Query Raw Events Without SQL
Analyse the GA4 BigQuery export: sessions, purchases and paths from events_* tables, the key columns, UNNEST patterns, and plain-English queries.
Summary
The GA4 BigQuery export stores every event as a row in daily events_YYYYMMDD tables, which lets you answer questions the GA4 interface can't, such as exact purchase paths or user-level analysis. Connect the export dataset to Better Analyst and ask in plain English; it writes the SQL, including the UNNEST(event_params) steps the schema requires, and shows it to you.
On this page
Who this report is for
Analysts and marketers whose GA4 property is linked to BigQuery but who don't want to hand-write nested SQL for every question, and teams that need more history than GA4 keeps.
What the report includes
| Metric | Source field | Why it matters |
|---|---|---|
| Event date | event_date | Date string (YYYYMMDD) in the property's time zone; the basis for daily trends. |
| Event name | event_name | Filters events such as page_view, add_to_cart and purchase. |
| User | user_pseudo_id | Pseudonymous browser or app-instance ID for counting users and building paths. |
| Session ID | event_params key 'ga_session_id' | An event parameter, not a column; combined with user_pseudo_id it identifies a session. |
| Purchase revenue | ecommerce.purchase_revenue | Revenue on purchase events, for revenue by source, device or path. |
| Transaction ID | ecommerce.transaction_id | De-duplicates purchases that fired twice. |
| Session source / medium | session_traffic_source_last_click.manual_campaign.source / .medium | Last-click session attribution; traffic_source.* holds the first-user source instead. |
| Device and country | device.category, geo.country | Standard breakdowns without GA4's sampling or thresholds. |
How to build it in Better Analyst from Google BigQuery
- In GA4 Admin, link the property to BigQuery (a Google step) so daily events_YYYYMMDD tables start appearing in your project; the export only contains data from the link date onward.
- In Better Analyst, open Connectors, choose Add Connector, then BigQuery.
- Connect with Google sign-in, or upload a service-account JSON key for an account with the BigQuery Data Viewer and BigQuery Job User roles.
- Select the project and dataset, test the connection and save. Queries are read-only, and BigQuery's normal query pricing applies to what you scan.
- Add a Data Context note describing the export (for example 'ga_session_id is in event_params; use session_traffic_source_last_click for session source') so every query starts from the right definitions.
Questions to ask
- “How many sessions and purchases did we have by session source and medium last month, using the GA4 export?”
- “What were the last three pages users viewed before a purchase?”
- “Which purchases have duplicate transaction IDs?”
- “Compare revenue by device category for the last 90 days.”
- “How much data will this query scan before I run it?”
Sample output
Sample data for illustration, not real account data.
| Source / medium | Sessions | Purchases | Revenue | Revenue per session |
|---|---|---|---|---|
| google / organic | 41,280 | 602 | $47,860 | $1.16 |
| google / cpc | 18,930 | 389 | $33,450 | $1.77 |
| newsletter / email | 6,140 | 211 | $15,190 | $2.47 |
| (direct) / (none) | 15,720 | 248 | $19,840 | $1.26 |
Charts to use
- Revenue per session by source (Bar chart): Value of a visit from each source, not just volume.
- Paths to purchase (Sankey or ranked table): The most common page sequences that end in a purchase.
Keep it up to date
Use a Data Flow to write a daily summary table (sessions, purchases and revenue by source) into BigQuery or Google Sheets, so dashboards read a small table instead of scanning raw events each time.
Further reading
Frequently asked questions
Why don't BigQuery export numbers match GA4 reports?
GA4 reports can apply thresholds, modelling and attribution settings, while the export holds raw events. Session definitions and time zones also need to match. Expect small differences and document your definitions.
How do I count sessions in the GA4 export?
Count distinct combinations of user_pseudo_id and the ga_session_id event parameter, read with (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id').
What's the difference between traffic_source and session_traffic_source_last_click?
traffic_source holds the source that first acquired the user; session_traffic_source_last_click holds last-click attribution for the session. For session-level channel reports, use the session record.