GA4 SQL-AI-powered GA4 SQL queries

Example

SELECT count(distinct concat(user_pseudo_id, (SELECT value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id'))) AS sessions FROM `{table_name}`

Scenario

This function is used to count the number of sessions on a website. For instance, an e-commerce site can use this to understand daily traffic trends.

Example

SELECT count(distinct CASE WHEN (SELECT value.string_value FROM unnest(event_params) WHERE key = 'session_engaged') = '1' THEN concat(user_pseudo_id, (SELECT value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id')) END) AS engaged_sessions FROM `{table_name}`

Scenario

An engaged session is one where the user has interacted significantly with the site. This is crucial for identifying highly engaged users, such as those who add items to their cart or spend more time on key pages.

Example

SELECT CASE WHEN (SELECT value.int_value FROM unnest(event_params) WHERE event_name = 'session_start' AND key = 'ga_session_number') = 1 THEN 'new' ELSE 'returning' END AS user_type, count(distinct user_pseudo_id) AS users FROM `{table_name}` GROUP BY user_type

Scenario

This query helps in segmenting users into new and returning categories, enabling businesses to tailor their marketing strategies for acquisition versus retention.