Six Questions GA4 Wouldn't Answer, So I Asked BigQuery

Every number below comes from the GA4 BigQuery export for chromiumdata.com, 28 May to 26 July 2026. 1,478 events, 133 device IDs, 196 sessions. Nothing here is illustrative.

This case study uses my own site as the subject, because it is the one dataset I can publish in full. The method is the same one I run for clients: connect the GA4 export to BigQuery, filter out the traffic that was never human, and then ask the questions the standard reports cannot answer.

I picked six. The answers were worse than I expected, which is the point of doing it.

The Problem

A GA4 report can only answer the questions someone at Google decided to build a screen for. That is fine until you want to know something specific, at which point three limits show up at once.

The interface quietly edits your data before you see it. Reports get sampled above certain thresholds. High-cardinality dimensions collapse into an (other) bucket. Low-volume rows are withheld for privacy, which on a small site is most of them. The raw export has none of that, because every event row sits there exactly as it was collected.

The second limit is that no interface will join your session data to anything else. Ad spend, CRM records, invoices. On its own, GA4 tells you what happened. Joined to revenue, it starts telling you what was worth happening.

The third limit is the one that matters most on a small site, and it is the reason the first query below exists. The interface has no idea which of your visitors were real.

Is the SQL harder than the interface?

Yes, genuinely, and I would rather say so than pretend otherwise. GA4 stores one row per event with the parameters buried in a nested array of key/value pairs, so pulling out something as basic as a page path costs you an UNNEST and a scalar subquery instead of a column name. That is the tax. You pay it once, wrap the ugly part in a view, and after that you can ask almost anything.

The Solution

1. How much of the traffic is actually human?

Everything else depends on this answer. Three things pollute a small site's analytics: your own tag debugging, staging environments sharing the measurement ID, and datacenter crawlers that run JavaScript well enough to look like Chrome.

WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params)
                                 WHERE key = 'ga_session_id')) AS sid,
    LOGICAL_OR(STARTS_WITH((SELECT value.string_value FROM UNNEST(event_params)
                            WHERE key = 'page_location'), 'https://chromiumdata.com')) AS on_prod,
    LOGICAL_OR(COALESCE(collected_traffic_source.manual_source,
                        traffic_source.source) = 'tagassistant.google.com')       AS debug,
    ANY_VALUE(geo.country) AS country
  FROM `analytics_523752319.events_*`
  GROUP BY sid
)
SELECT
  CASE
    WHEN NOT on_prod         THEN 'localhost / staging'
    WHEN debug               THEN 'Tag Assistant debug'
    WHEN country='Singapore' THEN 'Singapore datacenter (suspected bot)'
    ELSE 'Genuine visitor'
  END AS bucket,
  COUNT(*) AS sessions
FROM sessions GROUP BY bucket ORDER BY sessions DESC;

Seventy-nine out of 196. Which means every engagement rate and channel comparison available in the GA4 interface was diluted by a factor of two and a half, with no way of knowing from inside the interface. That filter became a view, and every query below sits on top of it.

2. Which page earns a brand-new visitor?

GA4 fires a first_visit event exactly once per device. Filter to it and the page on that event is, by definition, the door someone walked through.

SELECT
  REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params)
                  WHERE key = 'page_location'),
                 r'^https://chromiumdata\.com|[?#].*$', '') AS landing_page,
  COUNT(DISTINCT user_pseudo_id) AS new_visitors
FROM `analytics_523752319.events_*`
WHERE event_name = 'first_visit'
GROUP BY landing_page ORDER BY new_visitors DESC LIMIT 6;

Two blog posts brought in 23 new people between them, against 22 for the homepage. The posts written in an afternoon out-recruited the page that took a week to design.

3. Do they read, or do they leave?

GA4's built-in engaged-session flag counts anything past ten seconds, which is generous to the point of uselessness. Scroll depth is the honest measure, fired here at 25, 50, 75 and 90 percent.

SELECT
  COUNT(DISTINCT IF(event_name = 'page_view', sid, NULL)) AS landed,
  COUNT(DISTINCT IF(pct >= 25, sid, NULL)) AS reached_25,
  COUNT(DISTINCT IF(pct >= 50, sid, NULL)) AS reached_50,
  COUNT(DISTINCT IF(pct >= 75, sid, NULL)) AS reached_75,
  COUNT(DISTINCT IF(pct >= 90, sid, NULL)) AS reached_90
FROM genuine_events;

The shape was the surprise. Fifty-three percent never move the page at all, but of the 35 who scroll even once, 25 make it to the bottom. There is almost nobody drifting off in the middle, which is what I had assumed I would find. People seem to decide above the fold and then commit, and that makes the first screen the only part of the site really worth optimizing.

4. Which pages hold attention, and which just collect traffic?

Traffic and attention are different metrics, and ranking pages by pageviews hides that completely. Summing engagement_time_msec per session shows it immediately.

SELECT path,
       COUNT(DISTINCT IF(event_name='page_view', sid, NULL))     AS sessions,
       ROUND(SUM(COALESCE(ms,0))/1000
             / COUNT(DISTINCT IF(event_name='page_view',sid,NULL)), 1) AS sec_per_session
FROM genuine_events GROUP BY path ORDER BY sessions DESC;

The homepage takes the most traffic and holds it for under five seconds. One blog post, with a third of the visitors, holds them for nearly two minutes. Two service pages average under two seconds, which is not a bounce so much as a rejection.

Some of the paths above no longer exist. The service pages were reorganised partway through the measured period, and the export keeps the URLs as they were requested at the time. That is a feature rather than a nuisance: the raw event log is a historical record, so it can still answer questions about a version of the site that has since been replaced.

5. Which channel sends people who care?

Session counts flatter cheap channels. Weighting by engagement re-orders the table entirely.

SELECT channel,
       COUNT(*) AS sessions,
       ROUND(AVG(COALESCE(engage_sec,0)),1) AS avg_engaged_sec,
       ROUND(AVG(COALESCE(max_scroll,0)))   AS avg_max_scroll
FROM genuine_sessions GROUP BY channel ORDER BY sessions DESC;

Organic search sends the least volume and by far the most attention, about fifteen times the direct average. Direct is the mirror image: 56 sessions, more than all other channels combined, holding people for a little over six seconds each. On a site with an email list and an Upwork profile, most of that direct bucket is untagged campaign traffic rather than people typing the URL, which is its own thing to fix.

Paid search sent two sessions that produced zero engaged seconds and zero scroll, meaning both people left before the page had finished settling. Two sessions is far too small a sample to judge paid search as a channel. It is quite enough to know that neither of those two clicks read a word.

One detail from building this chart is worth keeping, because it is the article's own argument turning on itself. My first version of this query filtered debug traffic using the session's first recorded source, while the traffic-authenticity query filtered on any event in the session. One session had a clean first source and a Tag Assistant hit later on. That single session survived into the direct bucket and pulled its average from 6.3 seconds up to 14.3, more than double. The channel totals also came to 80 against a known 79. Filters that are nearly the same are not the same, and the reconciliation check is what catches it.

6. What actually happened before someone tried to book?

This is the query that changed my mind about the whole exercise. It pulls every conversion and outbound click with its full context.

SELECT event_date, event_name,
       (SELECT value.int_value    FROM UNNEST(event_params) WHERE key='ga_session_number') AS visit_no,
       (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_location')     AS page,
       (SELECT value.string_value FROM UNNEST(event_params) WHERE key='link_domain')       AS clicked_to,
       COALESCE(collected_traffic_source.manual_source, traffic_source.source)             AS source
FROM `analytics_523752319.events_*`
WHERE event_name IN ('book_call','click') ORDER BY event_timestamp;

The GA4 interface reports two conversions for this period. Both are mine, on visits 49 and 50 from my own machine, testing that the tag fired. The genuine conversion count is zero.

What the export gave instead is more useful than a vanity number. Three real people showed buying intent. All three were on a portfolio page when they did it, and two of the three had arrived from ChatGPT. Nobody clicked anything from a services page at all. So the portfolio is doing the selling, and an AI assistant that was never optimized for is already sending warmer traffic than the ads.

Tools and Deliverables

• GA4 BigQuery export: daily event tables streamed from the GA4 property, queried with wildcard table scans across the full date range.
• BigQuery SQL: UNNEST on nested event parameters, session reconstruction from user_pseudo_id plus ga_session_id, and a reusable genuine-traffic view.
• Traffic authenticity filter: separates debug, staging, and datacenter traffic from real visitors before any metric is calculated.
• Charts: inline SVG with no external charting library, on a palette validated for contrast and colour-vision-deficiency separation.

The Result

Six queries in an afternoon produced a to-do list the standard reports could not have suggested. Internal traffic needs filtering at the tag itself rather than being patched in SQL afterwards, so the numbers are honest for anyone who only opens the interface. The homepage first screen needs rebuilding, since that is where the whole decision happens. The portfolio should move up the navigation, because it is the only page type that has produced a single sign of intent. ChatGPT belongs on the measurement plan rather than being noticed by accident.

One caveat worth stating plainly, because a consultant who hides it is not worth hiring: 79 genuine sessions and three intent clicks is a small sample. These findings are directionally useful, not statistically strong. The traffic-authenticity result is the exception, since that one is a count rather than an inference, and it does not get less true with more data.

If your dashboard has never given you bad news, it is worth checking whether it is reading your data or just a tidy summary someone else picked out for you.

This is the work in miniature. Connect the export, build the filtered views so the numbers stop lying quietly, then go after the questions the interface was never built to answer. If you have a GA4 property and a nagging suspicion the reports are flattering you, that suspicion is usually right, and it is usually a week of work to fix.