-- з даних екпорту GA4

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

-- візьми Primary Channel Group
session_traffic_source_last_click.cross_channel_campaign.primary_channel_group,

-- ID сесії

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

-- дані про те, чи була сесія із взаємодією формату цілого числа

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

-- та текстового,

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

-- час взаємодії

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

-- назва івенту
event_name,

-- та дохід
ecommerce.purchase_revenue

-- додай колонку з визначенням сесії із взаємодією на основі обох параметрів

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

-- визнач чи була сесія із взаємодією

MAX(session_engaged) AS session_engaged,

-- порахуй час взаємодії в секундах,

SUM(engagement_time_msec) / 1000 AS engagement_time_sec,

-- кількість івентів,

COUNT(*) AS events,

-- кількість конверсій,

COUNTIF(event_name = 'purchase') AS conversions,

-- та загальний дохід

SUM(purchase_revenue) AS total_revenue

-- на рівні кожної Primary Channel Group та окремої сесії

GROUP BY
primary_channel_group,
session_id

-- тепер розрахуй
|>AGGREGATE

-- кількість сесій
COUNT(*) AS sessions,

-- кількість сесій із взаємодією

SUM(session_engaged) AS engaged_sessions,

-- долю сесій із взаємодією,

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

-- середній час взаємодії,

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

-- середню кількість подій на сесію,

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

-- загальну кількість подій,

SUM(events) AS event_count,

-- кількість конверсій,

SUM(conversions) AS conversions,

-- коєфіціент конверсії сесії

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

-- та суму доходу

ROUND(SUM(total_revenue), 2) AS total_revenue

-- для кожної Primary Channel Group

GROUP BY
primary_channel_group

-- та відсортуй результати по кількості сесій в спадному порядку

|>ORDER BY sessions desc

Що таке Pipe Query Syntax в BigQuery