-- from GA4 export data

FROM `bigqueryfortests.analytics_11111111.events_20*`
|>SELECT

-- take Primary Channel Group
session_traffic_source_last_click.cross_channel_campaign.primary_channel_group,

-- session ID

CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')) AS session_id,

-- engagement data as an integer

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'session_engaged') AS session_engaged_int,

-- engagement data as a string

(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'session_engaged') AS session_engaged_string,

-- engagement time

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'engagement_time_msec') AS engagement_time_msec,

-- event name
event_name,

-- revenue
ecommerce.purchase_revenue

-- add a column to determine session engagement based on both parameters

|>EXTEND
COALESCE(session_engaged_int, CAST(session_engaged_string AS int64)) AS session_engaged
|>AGGREGATE

-- determine whether the session was engaged

MAX(session_engaged) AS session_engaged,

-- calculate total engagement time in seconds

SUM(engagement_time_msec) / 1000 AS engagement_time_sec,

-- count events

COUNT(*) AS events,

-- count conversions

COUNTIF(event_name = 'purchase') AS conversions,

-- sum total revenue

SUM(purchase_revenue) AS total_revenue

-- group by Primary Channel Group and Session ID

GROUP BY
primary_channel_group,
session_id

-- now, calculate
|>AGGREGATE

-- sessions
COUNT(*) AS sessions,

-- engaged sessions

SUM(session_engaged) AS engaged_sessions,

-- engagement rate

ROUND(SUM(session_engaged) / COUNT(*) * 100, 2) || '%' AS engagement_rate,

-- average engagement time

ROUND(SUM(engagement_time_sec) / COUNT(*), 2) AS avg_engagement_time_per_session_in_sec,

-- average events number per session

ROUND(SUM(events) / COUNT(*), 2) AS events_per_session,

-- total events

SUM(events) AS event_count,

-- total conversions

SUM(conversions) AS conversions,

-- session conversion rate

ROUND(COUNTIF(conversions >= 1) / COUNT(*) * 100, 2) || '%' AS session_conversion_rate,

-- sum total revenue

ROUND(SUM(total_revenue), 2) AS total_revenue

-- for each Primary Channel Group

GROUP BY
primary_channel_group

-- sort results by the number of sessions in descending order

|>ORDER BY sessions desc

What is Pipe Query Syntax in BigQuery