Build a GA4 Page Performance Report in BigQuery

GA4’s pages report covers the basics, but the moment you want it defined your way, on the grain you choose, feeding your own dashboard, you hit the limits of the interface. The raw export in BigQuery removes them. This code-along builds a custom page performance report straight from the export, with every metric calculated transparently so you always know what a number means. As with any export query, this will not match a GA4 screen exactly, because the documentation, the interface, and the raw data each define things slightly differently, and that is precisely the value. You own the definitions, and the result drops cleanly into a spreadsheet, a saved table, or Looker Studio.

What the report contains

The dimensions are the date, the page path, and the page title. The metrics describe how each page performs: total page views, users, views per user, unique page views, entrances, exits, conversions, and purchase revenue. A couple of these deserve a quick definition up front. Unique page views count a page once per session no matter how many times it was viewed, entrances count the sessions that began on the page, and exits count the sessions that ended on it.

The build is three steps. Flatten the events and derive the page path and title, flag which page view was the last in each session so we can count exits, then aggregate up to the dimensions and compute the metrics.

Step one: the base layer

First we read the export once, deriving a clean page path from the full URL, pulling the page title and the entrances flag, and carrying any purchase revenue.

WITH events AS (
  SELECT
    user_pseudo_id,
    (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS session_id,
    PARSE_DATE('%Y%m%d', event_date) AS event_dt,
    event_timestamp,
    event_name,
    COALESCE(NULLIF(
      REGEXP_EXTRACT(
        (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location'),
        r'^https?://[^/]+([^?#]*)'
      ), ''), '/') AS page_path,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_title') AS page_title,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'entrances')  AS entrances,
    ecommerce.purchase_revenue AS purchase_revenue
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

The regular expression takes the page location and keeps only the path portion, stripping the protocol, the host, and anything after a question mark or hash, so a page with different campaign parameters is not split into many rows. The COALESCE and NULLIF turn an empty path, which is what a bare homepage URL produces, into a clean slash. The session id, page title, and entrances flag come out of the parameter array, and purchase revenue comes ready-made from the ecommerce record. The table suffix filter sets the window and keeps the scan contained.

Step two: flag the exit page

An exit is the last page view of a session, and the export does not mark it for us, so we work it out. Ranking the page views within each session from latest to earliest lets the top-ranked one stand for the exit.

, ranked AS (
  SELECT
    e.*,
    CASE WHEN event_name = 'page_view'
      THEN ROW_NUMBER() OVER (
        PARTITION BY user_pseudo_id, session_id, (event_name = 'page_view')
        ORDER BY event_timestamp DESC
      )
    END AS pv_rank_desc
  FROM events AS e
)

The window numbers the page views within each session in descending time order, so the final page view gets rank one. Putting the page-view condition in the partition keeps non-page-view events out of that numbering, and the surrounding CASE leaves the rank null for anything that is not a page view. A row with a rank of one is therefore the exit page of its session, which is all we need for the next step.

Step three: the report

Finally we aggregate to the dimensions and compute each metric in one pass.

SELECT
  event_dt    AS date,
  page_path,
  page_title,
  COUNTIF(event_name = 'page_view')                                    AS total_page_views,
  COUNT(DISTINCT IF(event_name = 'page_view', user_pseudo_id, NULL))   AS users,
  ROUND(
    COUNTIF(event_name = 'page_view')
    / COUNT(DISTINCT IF(event_name = 'page_view', user_pseudo_id, NULL)), 2
  )                                                                    AS views_per_user,
  COUNT(DISTINCT IF(event_name = 'page_view',
        CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING)), NULL)) AS unique_page_views,
  COUNTIF(event_name = 'page_view' AND entrances = 1)                  AS entrances,
  COUNTIF(pv_rank_desc = 1)                                            AS exits,
  COUNTIF(event_name IN ('purchase', 'generate_lead'))                AS conversions,
  SUM(IF(event_name = 'purchase', IFNULL(purchase_revenue, 0), 0))     AS purchase_revenue
FROM ranked
GROUP BY date, page_path, page_title
ORDER BY total_page_views DESC

Total page views count the page-view events. Users are the distinct visitors who viewed the page, and views per user divides one by the other. Unique page views count distinct session-and-page combinations, so a page viewed three times in one session counts once. Entrances count the page views flagged as the session’s first, and exits count the page views our ranking marked as the session’s last. Conversions count your key events that occurred on the page, and purchase revenue sums the revenue from purchase events on it, both attributed by each event’s own page location. The whole thing runs in a single pass over the ranked events.

A few details

A few details keep this honest. The page path is derived from the page location with the query string removed, which is usually what you want, but if you need to keep certain parameters, adjust the regular expression. Conversions and revenue are attributed to the page where the key event or purchase fired, edit the key-event list to match your configured conversions. The entrances flag is set by GA4 on the session’s first page view, and our exit logic mirrors it at the other end of the session. The export timestamps are UTC, so expect small day-boundary differences from a locally timed interface. And as always, the totals will not match the GA4 screen exactly, which is the sign that you now control the definitions.

The payoff is a page performance report you can defend and extend. Every metric is defined in SQL you can read, the path is cleaned, entrances and exits are computed honestly, and the same query feeds a one-off export, a scheduled table, or a live dashboard. From here you can add a device or channel breakdown, fold in scroll or engagement events, or join in content metadata to group pages by section.

See you soon.