GA4 URL Conversion Rate Analysis with BigQuery

To form this table from your GA4 dataset:

CREATE TABLE AS
SELECT *
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_2021**`

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  param.key AS kkey,
  param.value.string_value AS string_value
FROM `future-abacus-432804-b6.ecom_sample.small_table`
CROSS JOIN UNNEST(event_params) AS param
WHERE param.key IN ('page_location')
  AND event_name IN ("page_view", "purchase");

WITH non_converted_visits AS (
  SELECT DISTINCT
    user_pseudo_id,
    param.key AS kkey,
    param.value.string_value AS string_value
  FROM `future-abacus-432804-b6.ecom_sample.small_table`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE param.key IN ('page_location') 
    AND event_name = "page_view"
    AND user_pseudo_id NOT IN (
      SELECT user_pseudo_id 
      FROM `future-abacus-432804-b6.ecom_sample.small_table` 
      WHERE event_name = "purchase"
    )
)
SELECT 
  string_value AS visited_page, 
  COUNT(string_value) AS non_converted_visits  
FROM non_converted_visits 
GROUP BY string_value 
ORDER BY non_converted_visits DESC

WITH unnest_table AS (
  SELECT
    event_name,
    event_timestamp,
    user_pseudo_id,
    param.key AS kkey,
    param.value.string_value AS string_value
  FROM `future-abacus-432804-b6.ecom_sample.small_table`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE param.key IN ('page_location') 
    AND event_name IN ("page_view","purchase")
)
SELECT
  lt.event_name,
  lt.event_timestamp,
  lt.user_pseudo_id,
  lt.kkey,
  lt.string_value,
  rt.event_timestamp AS first_conversion
FROM unnest_table lt
JOIN (
  SELECT user_pseudo_id, MIN(event_timestamp) AS event_timestamp 
  FROM unnest_table 
  WHERE event_name = 'purchase' 
  GROUP BY user_pseudo_id
) rt 
ON rt.user_pseudo_id = lt.user_pseudo_id
WHERE lt.event_name = "page_view" 
  AND lt.event_timestamp <= rt.event_timestamp

WITH final_table AS (
  WITH unnest_table AS (
    SELECT
      event_name,
      event_timestamp,
      user_pseudo_id,
      param.key AS kkey,
      param.value.string_value AS string_value
    FROM `future-abacus-432804-b6.ecom_sample.small_table`
    CROSS JOIN UNNEST(event_params) AS param
    WHERE param.key IN ('page_location') 
      AND event_name IN ("page_view","purchase")
  )
  SELECT
    lt.event_name,
    lt.event_timestamp,
    lt.user_pseudo_id,
    lt.kkey,
    lt.string_value,
    rt.event_timestamp AS first_conversion
  FROM unnest_table lt
  JOIN (
    SELECT user_pseudo_id, MIN(event_timestamp) AS event_timestamp 
    FROM unnest_table 
    WHERE event_name = 'purchase' 
    GROUP BY user_pseudo_id
  ) rt 
  ON rt.user_pseudo_id = lt.user_pseudo_id
  WHERE lt.event_name = "page_view" 
    AND lt.event_timestamp <= rt.event_timestamp
)
SELECT DISTINCT 
  user_pseudo_id, 
  string_value AS visited_page 
FROM final_table;

WITH final_table AS (
  WITH unnest_table AS (
    SELECT
      event_name,
      event_timestamp,
      user_pseudo_id,
      param.key AS kkey,
      param.value.string_value AS string_value
    FROM `future-abacus-432804-b6.ecom_sample.small_table`
    CROSS JOIN UNNEST(event_params) AS param
    WHERE param.key IN ('page_location') 
      AND event_name IN ("page_view","purchase")
  )
  SELECT
    lt.event_name,
    lt.event_timestamp,
    lt.user_pseudo_id,
    lt.kkey,
    lt.string_value,
    rt.event_timestamp AS first_conversion
  FROM unnest_table lt
  JOIN (
    SELECT user_pseudo_id, MIN(event_timestamp) AS event_timestamp 
    FROM unnest_table 
    WHERE event_name = 'purchase' 
    GROUP BY user_pseudo_id
  ) rt 
  ON rt.user_pseudo_id = lt.user_pseudo_id
  WHERE lt.event_name = "page_view" 
    AND lt.event_timestamp <= rt.event_timestamp
)
SELECT 
  visited_page, 
  COUNT(visited_page) AS converted_visits 
FROM (
  SELECT DISTINCT user_pseudo_id, string_value AS visited_page 
  FROM final_table
) a
GROUP BY visited_page
ORDER BY converted_visits DESC;

WITH non_converted_visits AS (
  SELECT
    string_value AS visited_page,
    COUNT(*) AS non_converted_visits
  FROM (
    SELECT DISTINCT
      user_pseudo_id,
      param.value.string_value
    FROM `future-abacus-432804-b6.ecom_sample.small_table`
    CROSS JOIN UNNEST(event_params) AS param
    WHERE param.key = 'page_location'
      AND event_name = "page_view"
      AND user_pseudo_id NOT IN (
        SELECT user_pseudo_id
        FROM `future-abacus-432804-b6.ecom_sample.small_table`
        WHERE event_name = "purchase"
      )
  )
  GROUP BY visited_page
),

converted_visits AS (
  WITH unnest_table AS (
    SELECT
      event_name,
      event_timestamp,
      user_pseudo_id,
      param.value.string_value AS visited_page
    FROM `future-abacus-432804-b6.ecom_sample.small_table`
    CROSS JOIN UNNEST(event_params) AS param
    WHERE param.key = 'page_location'
      AND event_name IN ("page_view","purchase")
  ),
  final_table AS (
    SELECT
      lt.user_pseudo_id,
      lt.visited_page
    FROM unnest_table lt
    JOIN (
      SELECT
        user_pseudo_id,
        MIN(event_timestamp) AS first_conversion
      FROM unnest_table
      WHERE event_name = 'purchase'
      GROUP BY user_pseudo_id
    ) conv
    ON lt.user_pseudo_id = conv.user_pseudo_id
    WHERE lt.event_name = "page_view"
      AND lt.event_timestamp <= conv.first_conversion
  )
  SELECT
    visited_page,
    COUNT(*) AS converted_visits
  FROM (
    SELECT DISTINCT user_pseudo_id, visited_page FROM final_table
  )
  GROUP BY visited_page
)

SELECT
  COALESCE(n.visited_page, c.visited_page) AS visited_page,
  COALESCE(n.non_converted_visits, 0) AS non_converted_visits,
  COALESCE(c.converted_visits, 0) AS converted_visits,
  ROUND(
    COALESCE(c.converted_visits, 0) * 100.0 / 
    NULLIF(COALESCE(c.converted_visits, 0) + COALESCE(n.non_converted_visits, 0), 0),
    2
  ) AS conversion_rate_percent
FROM non_converted_visits n
FULL OUTER JOIN converted_visits c
  ON n.visited_page = c.visited_page
ORDER BY (COALESCE(n.non_converted_visits, 0) + COALESCE(c.converted_visits, 0)) DESC;