GA3 Landing Page Report rebuild in Google Big Query

If you moved to Google Analytics 4 from Universal Analytics, you have probably felt the friction of missing or oddly built standard reports. The landing page report is a perfect example. It exists now, but the choices behind it make it hard to trust, and harder to use for the one job a landing page report should do well: telling you how each entry page performs at the session level so you can steer your SEO and acquisition strategy. The good news is that the GA4 BigQuery export gives you every raw event, so you do not have to accept the interface’s version. You can build your own report, the way the old Universal Analytics report worked, with metrics you fully control. This code-along walks through doing exactly that.

Why the built-in report disappoints

The core problem is the headline metric. A landing page is a session concept. Every visit has exactly one entry page, so the natural way to count landing pages is by sessions. The GA4 report has historically leaned on views instead, and views count every page load including repeated views of the same page within a single visit. Even deduplicated to unique page views you would at least have a session-scoped number, but raw views are the wrong unit for this report.

Look to the user metrics for relief and you find new users, defined as people interacting for the first time. That excludes every session from returning visitors, which is most of your traffic. Engagement metrics flip you back to a session basis without making clear whether they cover all sessions or only engaged ones. On top of that, conversions appear without a conversion rate, the first landing page row is often blank with no explanation of whether it is the homepage or a null, and the totals frequently fail to reconcile with anything else. None of these are fatal on their own, but together they make the report awkward to reason about.

The plan

We will rebuild the report from the export with a clear, session-first set of metrics, all transparently calculated, grouped by landing page: users, new users, total sessions, the percentage of new sessions, engaged sessions, average engagement time per session, engagement rate, conversions, and conversion rate. Every rate will be based on total sessions, so the denominators are consistent and explainable.

The build has three stages. First we flatten the nested event parameters into flat columns. Then we collapse events into one row per session, which is where we decide each session’s landing page. Finally we aggregate sessions up to the landing page and compute the metrics.

Step one: flatten the event parameters

GA4 export stores most of what we need inside the repeated event_params field, so the first job is to pull the values we care about into ordinary columns. We extract the session id and session number to define and classify sessions, the page location to find the entry page, the entrances flag that marks the landing event, the session engagement flag, and the engagement time in milliseconds.

WITH event_base AS (
  SELECT
    user_pseudo_id,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')        AS session_id,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_number')    AS session_number,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location')        AS page_location,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'entrances')            AS entrances,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'session_engaged')      AS session_engaged,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'engagement_time_msec') AS engagement_msec,
    event_name
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

Each subquery reaches into the parameter array and returns a single scalar, so the nested data becomes a tidy flat table with one row per event. The combination of user_pseudo_id and session_id is what uniquely identifies a session, and we will lean on it throughout. The entrances value equals one on the event that began the session, which is how we will later pick the landing page. The wildcard table with the _TABLE_SUFFIX filter keeps the scan to the date range you want, which matters both for accuracy and for cost.

Step two: collapse events into sessions

Now we reduce all those events down to one row per session. The key decision here is the landing page, which we take from the page location of the event flagged as the entrance. We also carry the session number so we can identify new sessions, flag whether the session was ever engaged, sum the engagement time, and count any conversion events.

, sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST(session_id AS STRING)) AS session_key,
    user_pseudo_id,
    MAX(session_number) AS session_number,
    MAX(IF(entrances = 1, REGEXP_REPLACE(page_location, r'\?.*$', ''), NULL)) AS landing_page,
    MAX(IF(session_engaged = '1', 1, 0)) AS is_engaged,
    SUM(engagement_msec) AS engagement_msec,
    COUNTIF(event_name IN ('purchase', 'generate_lead')) AS conversions
  FROM event_base
  GROUP BY session_key, user_pseudo_id
)

Grouping by the session key gives one row per visit. The landing page comes from the entrance event, and the regular expression strips the query string off the URL so that the same page with different campaign parameters is not split into many rows, which is one of the things that makes the native report noisy. The session number is constant within a session, so taking its maximum simply carries it through. The engagement flag becomes a one if any event in the session was engaged, the engagement time is summed across the visit, and conversions are counted by checking each event name against your list of key events, which you should edit to match the conversions you have configured.

Step three: build the report

The final stage aggregates sessions up to the landing page and calculates every metric on a consistent, session-based footing.

SELECT
  landing_page,
  COUNT(DISTINCT user_pseudo_id)                                          AS users,
  COUNT(DISTINCT IF(session_number = 1, user_pseudo_id, NULL))            AS new_users,
  COUNT(*)                                                                AS sessions,
  ROUND(COUNTIF(session_number = 1) / COUNT(*), 4)                        AS pct_new_sessions,
  COUNTIF(is_engaged = 1)                                                 AS engaged_sessions,
  ROUND(SUM(engagement_msec) / 1000 / COUNT(*), 2)                        AS avg_engagement_seconds_per_session,
  ROUND(COUNTIF(is_engaged = 1) / COUNT(*), 4)                            AS engagement_rate,
  SUM(conversions)                                                        AS conversions,
  ROUND(SUM(conversions) / COUNT(*), 4)                                   AS conversion_rate
FROM sessions
WHERE landing_page IS NOT NULL
GROUP BY landing_page
ORDER BY sessions DESC

Because the sessions table already holds one row per visit, counting rows gives total sessions directly, and that single number is the denominator for every rate, which is exactly the transparency the native report lacks. Users are distinct visitors who landed on the page, and new users are the distinct visitors whose session number was one, which correctly captures first-time visitors without throwing away returning ones. The percentage of new sessions divides first sessions by all sessions. Average engagement time converts the summed milliseconds to seconds and spreads it across all sessions rather than only engaged ones, so the figure is comparable across pages. Engagement rate and conversion rate follow the same logic. Filtering out null landing pages removes the sessions with no recorded entrance event, which is the real cause of that mysterious blank row in the interface.

Notes worth knowing before you ship it

A few details will save you confusion. The session engagement flag is stored as a string in the export, which is why we compare it to the text one rather than a number, and you should sanity-check that in your own dataset since schema quirks do appear. Your conversion list must match the events you actually treat as key events, so edit that IN clause rather than trusting the two placeholders. The date range lives in the table suffix filter, and widening it increases both completeness and the bytes scanned, so set it deliberately. Finally, the export uses UTC timestamps, so if your reporting runs on a local time zone, expect small day-boundary differences from the interface, which is a feature of working with the raw data rather than a bug in this query.

The payoff is a landing page report you can actually defend. Every metric is session-scoped, every rate shares the same denominator, the URLs are cleaned, and the blank row is explained and handled. Better still, because you own the SQL, you can extend it freely, adding channel or device breakdowns, splitting by date, or joining in cost data to turn it into a genuine acquisition dashboard. The export gives you what you need.

See you soon.