Google Analytics 4 Landing Page Report in BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Use the following SQL (automatically generated via a text prompt in ChatGPT) to create a Google Analytics 4 landing page report in BigQuery:

WITH base_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
    event_name
  FROM `dbrt-ga4.analytics_207472454.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),

landing_page_sessions AS (
  SELECT
    page_location AS landing_page,
    user_pseudo_id,
    session_id,
    session_engaged,
    engagement_time_msec
  FROM base_events
  WHERE event_name = 'page_view'
),

landing_page_metrics AS (
  SELECT
    landing_page,
    COUNT(*) AS pageviews,
    COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS sessions,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT CASE
      WHEN session_engaged = '1'
      THEN CONCAT(user_pseudo_id, session_id)
    END) AS engaged_sessions,
    SUM(CASE
      WHEN session_engaged = '1' THEN engagement_time_msec
    END) / 1000 AS total_engagement_time_sec
  FROM landing_page_sessions
  GROUP BY landing_page
),

returning_users AS (
  SELECT
    page_location AS landing_page,
    COUNT(DISTINCT user_pseudo_id) AS returning_users
  FROM base_events
  WHERE event_name = 'session_start'
    AND session_number > 1
  GROUP BY landing_page
)

SELECT
  lpm.landing_page,
  lpm.pageviews,
  lpm.sessions,
  lpm.users,
  IFNULL(ru.returning_users, 0) AS returning_users,
  lpm.engaged_sessions,
  ROUND(SAFE_DIVIDE(lpm.engaged_sessions, lpm.sessions) * 100, 2) AS engagement_rate,
  ROUND(
    SAFE_DIVIDE(lpm.total_engagement_time_sec, lpm.engaged_sessions),
    2
  ) AS avg_engagement_time_sec
FROM landing_page_metrics lpm
LEFT JOIN returning_users ru
  ON lpm.landing_page = ru.landing_page
ORDER BY lpm.pageviews DESC
LIMIT 20;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you. All you have to do is supply your table ID to generate SQL that works for you.

If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various dimensions and metrics used in this report.

Once you understand the logic, customizing this report is easy peasy.

I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.

The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.

Related Articles:

Himanshu Sharma