GA4 BigQuery starter pack: unnest event_params, count users, rank pages

The GA4 interface answers the questions Google decided you would ask. BigQuery answers the ones you actually have, but the raw export is not a friendly table: every event is a row, and the useful values are buried in a repeated event_params record you have to unnest before you can read them. Once that pattern clicks, most reporting is a variation on it.

This entry is three queries you will reuse constantly: the unnesting pattern itself with a worked example on page_view, daily users and sessions, and top pages by view. Nothing here writes or changes anything; these are read-only SELECT statements against your export tables.

Before you start

• The export lands in a dataset named analytics_XXXXX, where XXXXX is your GA4 property's numeric ID. Find it in BigQuery under your project, or in GA4 under Admin, then BigQuery links.
• Daily tables are named events_YYYYMMDD, and you query across them with the events_* wildcard and a _TABLE_SUFFIX filter.
• In every query below, replace your_project.analytics_XXXXX with your own project and dataset. That is the only edit needed to run them.
• Each query has a _TABLE_SUFFIX date filter near the top. It decides how many daily tables BigQuery reads, and therefore how much you are billed for the scan. Widen the range only when you mean to.

The queries

Unnesting event_params is the pattern under almost everything. Each parameter is a key with a value stored across four typed columns (string_value, int_value, float_value, double_value), so you pull one out with a scalar subquery. Here it is pulling page_location and page_title off page_view:

-- 1. The unnesting pattern, worked on page_view.
-- Replace your_project.analytics_XXXXX with your project and dataset.
SELECT
  event_date,
  user_pseudo_id,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_title') AS page_title,
  (SELECT value.int_value
   FROM UNNEST(event_params)
   WHERE key = 'ga_session_id') AS ga_session_id
FROM `your_project.analytics_XXXXX.events_*`
-- _TABLE_SUFFIX controls how many daily tables are scanned, and the cost.
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
  AND event_name = 'page_view'
LIMIT 1000;

Daily users and sessions. Users are distinct user_pseudo_id; a session is the distinct pairing of a user and their ga_session_id, so counting sessions means counting that pair, not the event:

-- 2. Daily users and sessions.
-- Replace your_project.analytics_XXXXX with your project and dataset.
SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    '-',
    CAST((SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions
FROM `your_project.analytics_XXXXX.events_*`
-- _TABLE_SUFFIX controls how many daily tables are scanned, and the cost.
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
GROUP BY event_date
ORDER BY event_date;

Top pages by view. Same unnesting pattern, aggregated by page path and ordered by count:

-- 3. Top pages by view over the window.
-- Replace your_project.analytics_XXXXX with your project and dataset.
SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  COUNT(*) AS views,
  COUNT(DISTINCT user_pseudo_id) AS unique_viewers
FROM `your_project.analytics_XXXXX.events_*`
-- _TABLE_SUFFIX controls how many daily tables are scanned, and the cost.
WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
  AND event_name = 'page_view'
GROUP BY page_location
ORDER BY views DESC
LIMIT 50;

Set it up

1. Open the BigQuery console and select the project that holds your export dataset.
2. Paste a query into the editor and replace your_project.analytics_XXXXX with your project and dataset name.
3. Set the two dates in the _TABLE_SUFFIX filter to the window you want, keeping it tight while you are testing.
4. Use the validator (the tick or the "This query will process N" note) to see the estimated scan size before you run, then run.
5. Once a query returns what you expect, save it as a view or a saved query so you are not rewriting the unnesting pattern each time.

How to read the output

The unnesting query is a template, not a report: swap page_view for any event and change the parameter keys to pull whatever that event carries. The users and sessions query is the honest daily baseline, though it will not match the GA4 UI number to the decimal, because GA4 applies its own session logic, active-user definitions and modelling on top of the raw events. Treat BigQuery as the source of truth for "what was logged" and the UI as "what Google reported", and expect a small, stable gap between them.

One limitation worth stating: user_pseudo_id is a device and cookie identifier, not a person, so a user clearing cookies or switching devices counts more than once, and consent-denied traffic may be absent or modelled. These queries count what the export contains, which is the right foundation, but it is not a headcount of humans.