GA4 SQL-BigQuery SQL for GA4 Analytics

Example

SELECT event_name, COUNT(*) AS event_count FROM `{table_name}` WHERE _TABLE_SUFFIX BETWEEN '20230801' AND '20230831' GROUP BY event_name ORDER BY event_count DESC;

Scenario

This function allows businesses to track and analyze specific events (e.g., button clicks, form submissions) that are critical to their operations. For instance, an e-commerce company can use this query to identify which product categories are most viewed by users, enabling them to optimize their inventory and marketing strategies.

Example

WITH user_journey AS (SELECT user_pseudo_id, ARRAY_AGG(event_name ORDER BY event_timestamp) AS events FROM `{table_name}` WHERE _TABLE_SUFFIX BETWEEN '20230801' AND '20230831' GROUP BY user_pseudo_id) SELECT * FROM user_journey WHERE ARRAY_TO_STRING(events, ' > ') LIKE '%product_view > add_to_cart > purchase%';

Scenario

Businesses can map out the paths users take on their websites or apps, identifying key steps that lead to conversions or drop-offs. A retail company could use this to analyze the effectiveness of their sales funnel by tracking how many users who viewed a product also added it to their cart and then completed the purchase.

Example

SELECT platform, COUNT(DISTINCT user_pseudo_id) AS active_users FROM `{table_name}` WHERE event_name = 'user_engagement' AND _TABLE_SUFFIX BETWEEN '20230801' AND '20230831' GROUP BY platform;

Scenario

This function helps businesses segment their audience based on various attributes like device, location, or behavior. For instance, a media company could use this query to compare user engagement on different platforms (e.g., mobile vs. desktop), allowing them to tailor their content strategy to the preferences of their most active users.