GA4 in BigQuery: App Funnel SQL Queries That Actually Work

The GA4 interface for app analytics is painful. Sampling at large volumes, a cardinality limit that collapses your data into "(other)", and no access to raw events whatsoever. You look at a report and can't tell whether these are real numbers or something the model made up.

The BigQuery export solves this outright. You get every event row by row, no sampling, no limits. From there it's SQL and complete freedom. But there's a barrier to entry, and everyone trips over the same thing.

UNNEST — nothing works without it

In GA4, event parameters sit in a nested event_params array. You can't just write WHERE page_location = '...'. You have to pull the value out with UNNEST.

The basic pattern looks like this: you unnest the parameter array and extract the key you need.

sql

SELECT
event_name,
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location') AS page_location
FROM `project.dataset.events_*`
WHERE event_name = 'screen_view'

Once you've grasped that, everything else opens up. Until you have, no query gets written at all.

The signup funnel in a single query

Let's count the classic path: opened the app → started signup → completed it. At every step, how many unique users and how big the drop-off.

sql

SELECT
COUNTIF(event_name = 'app_open') AS opens,
COUNTIF(event_name = 'sign_up_start') AS starts,
COUNTIF(event_name = 'sign_up_complete') AS completes
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'

In a minute you can see exactly where the funnel leaks. In the GA4 interface the same thing takes longer to assemble and comes with sampling caveats.

Retention cohorts with DATE_DIFF

Retention is where the interface lies most often. In BigQuery you count it honestly: take the date of the first open as the cohort, then look at who came back after 1, 7 and 30 days.

The logic is simple: for each user find the first day, then compute the difference in days to every subsequent visit with DATE_DIFF, and group. Day 1 / Day 7 / Day 30 retention on real cohorts, no models.

First-touch attribution

You tie the conversion to the campaign straight from traffic_source in the raw data, bypassing GA4's attribution models. You decide for yourself what counts as first touch instead of trusting a black box.

Why this is for you and not your data engineer

A take data teams won't like: a marketer needs to be able to write basic GA4 SQL themselves. Not wait a week for an analyst to assemble a report. Three or four patterns — UNNEST, COUNTIF, DATE_DIFF — cover 80% of day-to-day questions. The rest you can delegate. But keep the basics in your own hands, or you'll be blind in exact proportion to how busy your analyst is.