Selection of pages visited by paying customers

It happens that for some tasks you need to get a list of pages visited by customers making payments. In my case, for the subsequent check of page loading speed.

Let’s make a query to the data collected by GA4 and stored in BigQuery:

SELECT
  DISTINCT path,
  COUNT(event_timestamp) AS count
FROM (
  SELECT
    event_timestamp,
    REGEXP_EXTRACT(params.value.string_value, r'(?:[a-zA-Z]+://)?(?:[a-zA-Z0-9-.]+)/{1}([a-zA-Z0-9-./]+)') AS path
  FROM
    `project_name.dataset_name.events_2022*`,
    UNNEST(event_params) AS params
  WHERE
    user_id IN (
    SELECT
      user_id
    FROM
      `project_name.dataset_name.events_2022*`
    WHERE
      event_name = 'payment') ## payment event
    AND event_name = 'page_view'
    AND params.key = 'page_location')
WHERE
  path IS NOT NULL
GROUP BY
  path
ORDER BY
  count DESC

The output is a table: