Введение
У многих пользователей, перешедших с Universal Analytics на Google Analytics 4, до сих пор возникают трудности с пониманием принципов работы последнего.
В этом материале речь пойдет о работе с сырыми данными GA4. Мы расскажем как настроить бесплатную нативную интеграцию ресурса с базой данных BigQuery и покажем несколько примеров обработки данных на языке SQL.
Настройка интеграции с BigQuery
Если у вас есть собственный BigQuery, то вы можете настроить бесплатную интеграцию данных GA4 с вашей базой данных. Для этого перейдите в раздел “Администратор”, затем в пункт “Связь с BigQuery”. Нажмите на кнопку “Связать”:
![](https://streammydata.ru/wp-content/uploads/2024/07/2024-07-04_17-09-53-1024x431.png)
В открывшемся окне выберите ваш проект в BigQuery. Укажите данные, которые необходимо передавать — данные о событиях или данные о пользователях. Затем выберите тип передачи данных — один раз в сутки или потоковая передача.
После этого проверьте введенные данные и подтвердите связку. Данные появятся в вашей базе через несколько часов.
Работа с сырыми данными. Частые кейсы использования
Если вы до этого никогда не работали с сырыми данными Google Analytics 4, то их вид может поначалу сбивать вас с толку. Данные представляют собой конструкцию 1 строка — 1 событие, а их структура имеет множественную вложенность.
Ниже мы покажем примеры того, как можно “выпрямить” такие данные в более привычный вид.
![](https://streammydata.ru/wp-content/uploads/2024/07/2024-07-04_17-19-25-243x300.png)
Пример 1. Построение отчета по воронке Ecommerce
Составим код, который выведет таблицу с количеством сессий, пользователей, просмотров товара, добавлений в корзину и покупок в разрезе канала привлечения. Код будет выглядеть следующим образом:
WITH prepared_data AS ( SELECT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST (event_params) WHERE key = 'ga_session_id')) AS session_id, ARRAY_AGG( (CASE WHEN collected_traffic_source.gclid IS NOT NULL THEN 'google' ELSE collected_traffic_source.manual_source END) IGNORE NULLS ORDER BY event_timestamp LIMIT 1 ) [SAFE_OFFSET(0)] AS session_source, ARRAY_AGG( (CASE WHEN collected_traffic_source.gclid IS NOT NULL THEN 'cpc' ELSE collected_traffic_source.manual_medium END) IGNORE NULLS ORDER BY event_timestamp LIMIT 1 ) [SAFE_OFFSET(0)] AS session_medium, user_pseudo_id, SUM(IF(event_name = 'view_item', 1, 0)) AS item_view_events, SUM(IF(event_name = 'add_to_cart', 1, 0)) AS add_to_cart, SUM(IF(event_name = 'purchase', 1, 0)) AS purchases, SUM(ecommerce.purchase_revenue) AS revenue FROM `your_dataset` GROUP BY session_id, user_pseudo_id ) SELECT CONCAT( COALESCE(session_source, '(direct)'), ' / ', COALESCE(session_medium, '(none)') ) AS session_source_medium, COUNT(DISTINCT session_id) AS sessions, COUNT(DISTINCT user_pseudo_id) AS total_users, SUM(item_view_events) AS item_view_events, SUM(add_to_cart) AS add_to_cart, SUM(purchases) AS purchases, SUM(revenue) AS revenue FROM prepared_data GROUP BY session_source_medium
Первая часть кода извлекает отдельные столбцы исходных данных и агрегирует их на уровне пользователя + сессии. Вторая часть кода склеивает ID пользователя и ID сессии в один объект и выводит финальные данные, которые выглядят так:
![](https://streammydata.ru/wp-content/uploads/2024/07/2024-07-04_17-54-37-1024x294.png)
По желанию можно собрать данные в других срезах, добавить даты, девайсы, регионы и так далее
Пример 2. Построение отчета в разрезе товаров
Построим таблицу, в которой будут названия товаров и количество их просмотров, добавлений в корзину и покупок. Код на SQL:
WITH prepared_data AS ( SELECT items.item_name AS item_name, SUM(IF(event_name = 'view_item', items.quantity, NULL)) AS items_viewed, SUM(IF(event_name = 'add_to_cart', items.quantity, NULL)) AS items_added_to_cart, SUM(IF(event_name = 'purchase', items.quantity, NULL)) AS items_purchased FROM `your_dataset`, UNNEST (items) AS items GROUP BY item_name ) SELECT item_name, SUM(items_viewed) AS items_viewed, SUM(items_added_to_cart) AS items_added_to_cart, SUM(items_purchased) AS items_purchased FROM prepared_data GROUP BY item_name ORDER BY items_purchased DESC
Принцип работы этого кода совершенно такой же, как в предыдущем примере. Отличается он только набором извлекаемых полей. Выходные данные будут выглядеть примерно так:
![](https://streammydata.ru/wp-content/uploads/2024/07/2024-07-04_20-01-47-1024x223.png)
Пример 3. Построение отчета по дням
Финальным примером построим статистику по корзинам и покупкам в разрезе дней. Плюсом, вычислим конверсию из сессии в покупку. Код выглядит так:
WITH prepared_data AS ( SELECT event_date, SUM(IF(event_name = 'add_to_cart', 1, 0)) AS add_to_cart, SUM(IF(event_name = 'purchase', 1, 0)) AS purchases, CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST (event_params) WHERE key = 'ga_session_id')) AS session_id FROM `your_dataset` GROUP BY event_date, session_id ) SELECT PARSE_DATE("%Y%m%d", event_date) AS event_date, COUNT(DISTINCT session_id) AS sessions, SUM(add_to_cart) AS add_to_cart, SUM(purchases) AS purchases, CONCAT(ROUND(SUM(purchases) / COUNT(DISTINCT session_id) * 100, 2), '%') AS purchase_CR FROM prepared_data GROUP BY event_date ORDER BY event_date DESC
Принципиальных отличий построения кода от предыдущих примеров нет. Можно увидеть, что в финальной части было добавлено вычисляемое поле CR из сессии в покупку. Полученная таблица выглядит так:
![](https://streammydata.ru/wp-content/uploads/2024/07/2024-07-04_20-15-58-1024x484.png)
Заключение
Теперь вы имеете базовое представление о том, как работать с сырыми данными Google Analytics 4 в базе данных BigQuery. Конечно, примеры довольно простые, а для реальных запросов клиентов, построения дашбордов или сквозной аналитики, может потребоваться более комплексная и сложная работа с данными.
Обращайтесь к нашей команде, и мы проведем для вас работу с данными любой сложности.