ОБСУДИТЬ АНАЛИТИКУ
Перезвоните мне
+7 (499) 350 90 93

Последовательность просмотров страниц

Следующий запрос помогает узнать, в какой последовательности пользователи просматривали страницы за один сеанс:
SELECT
  user_pseudo_id,
--- Превращаем timestamp в понятное отображение
  TIMESTAMP_MICROS(event_timestamp) AS e_tms,
--- Достаем из параметра события ид сессии, url страницы и название страницы
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
--- Выбираем промежуток времени и для примера выберем конкретного пользователя
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202' and user_pseudo_id = "1019680.9514861838"
ORDER BY
  user_pseudo_id,
  ga_session_id,
  event_timestamp ASC
В результате мы получили список по времени перехода пользователя на конкретные страницы.
Первый заход на сайт был на домашнюю страницу, после которой он видимо закрыл сайт (видим по session_id) и только на следующий день продолжил взаимодействие с сайтом.

Но, такой запрос подойдет если вы анализируете одного или нескольких пользователей непосредственно в BigQuery, но если вам нужно проанализировать большой объем данных, то вы скорее всего будете их выгружать в сервисы по визуализации данных такие как Google Data Studio или Power BI. Следующий запрос поможет вам сэкономить время на выгрузку данных и визуализацию их в сторонних сервисах.
SELECT
  DISTINCT user_pseudo_id,
--- Объединяем названия страниц в одно поле по каждому уникальному пользователю
  FORMAT('%T', ARRAY_AGG(page_title_n) OVER (PARTITION BY user_pseudo_id)) AS array_agg
FROM (
  SELECT *,
--- Соединяем название каждой страницы, присваиваем ей порядковый номер отсортированный по таймштампу и объединенный по пользователю
    CONCAT( ROW_NUMBER() OVER(PARTITION BY t.user_pseudo_id ORDER BY t.event_timestamp ASC),' - ', t.page_title) AS page_title_n
  FROM (
    SELECT
      DISTINCT user_pseudo_id,event_timestamp,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE event_name = 'page_view' AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    ORDER BY
      user_pseudo_id,
      event_timestamp ASC) t )
ORDER BY
  user_pseudo_id
В результате получаем список пользователей и упорядоченный список страниц которые он посетил.
В дальнейшем мы можем применять сортировки к конкретному запросу, фильтровать по названию страниц и т.д.
Made on
Tilda