GA4 SQL-Advanced Analytics Querying

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 determine the number of unique sessions on a website or app. It's essential for understanding user engagement and site traffic trends.

Example

SELECT event_name, COUNT(*) AS event_count FROM `{table_name}` GROUP BY event_name

Scenario

This query helps in understanding how users interact with various elements on a website or app, such as button clicks or page views, crucial for optimizing user experience.

Example

SELECT SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchases FROM `{table_name}`

Scenario

This function is vital for e-commerce platforms to track sales performance, understand purchase patterns, and measure the effectiveness of marketing campaigns.

Example

WITH temp AS (...) SELECT session_source_medium, COUNT(DISTINCT session_id) AS sessions FROM temp GROUP BY session_source_medium

Scenario

This query is used to analyze where website traffic is coming from (e.g., organic search, paid ads). It helps in evaluating the effectiveness of different marketing channels.

Example

SELECT geo.country, COUNT(DISTINCT user_pseudo_id) AS users FROM `{table_name}` GROUP BY geo.country

Scenario

This function is used to understand the geographical distribution and demographic profiles of users, aiding in targeted marketing and localization strategies.