Проектируем сессионную таблицу при помощи SQL в BigQuery
У многих компаний для более глубокой аналитики есть потребность собирать данные об использовании своих продуктов в сыром виде. В сети есть решения как собирать хиты и складывать их в BigQuery. Работать с данными об использовании продуктов можно на разных уровнях: пользователя, сессии или хита.
Чтобы работать с данными на уровне сессии, таблицу хитов нужно преобразовать в сессионную таблицу. Попробуем спроектировать сессионную таблицу с учетом 30 минут бездействия.
Возьмем таблицу с идентификатором пользователей и временем их хитов:
Добавим дополнительную колонку timestamp_lag, в которой для каждого хита запишем время предыдущего хита юзера. Добавим колонку session_start, в которой запишем 1, если предыдущего хита нет (первые хиты пользователей) и если время между текущим и предыдущим хитом 30 и более минут, в остальных случаях запишем 0. Для этого воспользуемся оконной функцией lag() и операторами TIMESTAMP_DIFF и IF:
Добавим колонку session_number, в которой кумулятивно и последовательно просуммируем значение session_start с помощью оконной функцией sum():
В результате у нас выйдет таблица, по дополнительным колонкам которой хиты можно группировать по сессиям:
timestamp_lag - время предыдущего хита пользователя,
session_start - факт начала сессии,
session_number - номер сессии пользователя.
Чтобы каждую сессию записать одной строкой, добавим дополнительные запросы, в которых сгенерируем session_id путем соединения user_id и session_number:
В результате получим таблицу с временем начала и конца сессии:
По правилам Google Analytics сессия пользователя определяется не только 30-ю минутами бездействия, но и началом нового дня и сменой источника трафика. Если планируете делать анализ с учетом источников трафика, для разрыва сессий нужно учитывать и эти условия. После чего нужно будет определить источник трафика для каждой сессии.
Если интересно увидеть полное решение, с учетом всех условий разрыва сессий, дайте знать, “похлопайте” ниже под публикацией и я расскажу об этом в другой раз.
Подписывайтесь на мой Телеграм-канал BigQuery Insights, в котором делюсь интересными решениями аналитики в Google BigQuery.
