Работа с сырыми данными Google Analytics 4 в BigQuery

У многих пользователей, перешедших с Universal Analytics на Google Analytics 4, до сих пор возникают трудности с пониманием принципов работы последнего.

В этом материале речь пойдет о работе с сырыми данными GA4. Мы расскажем как настроить бесплатную нативную интеграцию ресурса с базой данных BigQuery и покажем несколько примеров обработки данных на языке SQL.

Если у вас есть собственный BigQuery, то вы можете настроить бесплатную интеграцию данных GA4 с вашей базой данных. Для этого перейдите в раздел “Администратор”, затем в пункт “Связь с BigQuery”. Нажмите на кнопку “Связать”:

В открывшемся окне выберите ваш проект в BigQuery. Укажите данные, которые необходимо передавать — данные о событиях или данные о пользователях. Затем выберите тип передачи данных — один раз в сутки или потоковая передача.

После этого проверьте введенные данные и подтвердите связку. Данные появятся в вашей базе через несколько часов.

Если вы до этого никогда не работали с сырыми данными Google Analytics 4, то их вид может поначалу сбивать вас с толку. Данные представляют собой конструкцию 1 строка — 1 событие, а их структура имеет множественную вложенность.

Ниже мы покажем примеры того, как можно “выпрямить” такие данные в более привычный вид.

Составим код, который выведет таблицу с количеством сессий, пользователей, просмотров товара, добавлений в корзину и покупок в разрезе канала привлечения. Код будет выглядеть следующим образом:

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 сессии в один объект и выводит финальные данные, которые выглядят так:

По желанию можно собрать данные в других срезах, добавить даты, девайсы, регионы и так далее

Построим таблицу, в которой будут названия товаров и количество их просмотров, добавлений в корзину и покупок. Код на 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

Принцип работы этого кода совершенно такой же, как в предыдущем примере. Отличается он только набором извлекаемых полей. Выходные данные будут выглядеть примерно так:

Финальным примером построим статистику по корзинам и покупкам в разрезе дней. Плюсом, вычислим конверсию из сессии в покупку. Код выглядит так:

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 из сессии в покупку. Полученная таблица выглядит так:

Теперь вы имеете базовое представление о том, как работать с сырыми данными Google Analytics 4 в базе данных BigQuery. Конечно, примеры довольно простые, а для реальных запросов клиентов, построения дашбордов или сквозной аналитики, может потребоваться более комплексная и сложная работа с данными.

Обращайтесь к нашей команде, и мы проведем для вас работу с данными любой сложности.