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:
path | count |
---|---|
user/dashboard | 1122268 |
user/payments | 495336 |
user/login | 400150 |
user/payments/history | 198285 |
user/payments/in | 197803 |
user/promo | 134159 |