Популярные продукты

WITH ecommerce_products AS(
SELECT 
item_name,
--- Количество просмотренных страниц
COUNT(CASE WHEN event_name = 'view_item' THEN CONCAT(event_timestamp, CAST(user_pseudo_id AS STRING)) ELSE NULL END) AS item_views,
--- Количество добавлений в корзину
COUNT(CASE WHEN event_name = 'add_to_cart' THEN CONCAT(event_timestamp, CAST(user_pseudo_id AS STRING)) ELSE NULL END) AS add_to_carts,
--- Количество покупок
COUNT(CASE WHEN event_name = 'purchase' THEN ecommerce.transaction_id ELSE NULL END) AS ecommerce_purchases, 
--- Суммарная выручка по товару
SUM(item_revenue) AS item_revenue
FROM 
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(items) AS items
WHERE _table_suffix BETWEEN '20210101' AND '20210131'
GROUP BY item_name) 
--- Результирующий запрос
SELECT item_name, item_views, add_to_carts,
 ecommerce_purchases, item_revenue
FROM ecommerce_products
WHERE item_views > 0 OR item_revenue > 0
ORDER BY item_views DESC