Расчет сеансов Google Analytics 4 в Google BigQuery

Запрос №3. Количество сеансов за динамический диапазон дат

В предыдущем запросе мы с вами использовали диапазон дат, но заданный статически, то есть неизменными датами (датой начала и датой окончания) с помощью конструкции _TABLE_SUFFIX, операторов BETWEEN, AND и подстановочного знака *.

Но что, если вы хотите изменить диапазон дат на динамический, то есть указать не конкретную дату (например, 20231123), а «за вчера»? Да, это можно сделать, используя функции даты и времени.

В Google BigQuery есть много различных функций, заранее предопределенных. Одна из таких – это функция CURRENT_DATE, которая возвращает текущую дату. Если ввести ее в редакторе запросов Google BigQuery, то в результатах вы увидите сегодняшнюю дату.

Причем эта функция не расходует лимиты запросов, поскольку она является встроенной и ей не нужно сканировать данные в ваших таблицах Google Analytics 4.

Результат функции CURRENT_DATE возвращается в виде формата DATE, а не STRING, который используется в схеме данных Google Analytics 4 для поля event_date. Поэтому для того, чтобы использовать ее в динамическом диапазоне дат, ее сначала необходимо преобразовать в строку.

И поскольку мы хотим построить запрос, который извлекал бы для нас результат за вчерашний день, а не за сегодняшний как в CURRENT_DATE, нам необходимо использовать функцию DATE_SUB. Эта функция вычитает указанный интервал времени из DATE.

Таким образом, для вычитания одной даты из другой, вы можете функцию CURRENT_DATE заключить в другую функцию DATE_SUB, и задав еще один аргумент для разницы, посчитать итоговое значение.

Для извлечения вчерашней даты получается такая конструкция:

, где:

• CURRENT_DATE – текущая дата в формате DATE;
• DATE_SUB – функция разницы дат;
• INTERVAL 1 DAY - интервал времени, равный одному дню.

Выполнив запрос, вы получите вчерашнюю дату. У меня это 2024-01-21 (21 января 2024 года) на момент написания этого материала:

А поскольку формат возвращенного результата даты отличается от того, который мы задавали в предыдущем запросе, нам необходимо преобразовать тип данных DATE в формат STRING.

Делается это с помощью еще одной функции FORMAT_DATE. Она позволяет преобразовать дату в строку в нужном нам формате. Этот формат задается еще одним аргументом и имеет различные варианты написания. Для данных Google Analytics 4, как правило, используется шаблон %Y%m%d, где:

• %Y - обозначает год в виде четырехзначного числа;
• %m – обозначает месяц в виде двухзначного числа;
• %d – обозначает день в виде в виде двухзначного числа.

Используя такую конструкцию в SQL-запросе:

Вы получите вчерашнюю дату в нужном формате:

Именно эту строку вы можете скопировать и вставить в свой третий запрос для подсчета количества сеансов с динамическим диапазоном дат для даты окончания:

После выполнения этого запроса BigQuery отобразит вам результат с данными, запрошенный с 1 января 2024 года и заканчивая вчерашним днем на момент написания этого материала, то есть 21 января 2024 года:

Если вы хотите выполнить запрос, который будет извлекать данные за последние 30 дней, то вы можете скопировать вышеприведенную строку и для даты начала, заменив дату начала, идущую после BETWEEN, на INTERVAL 30 DAY:

Запрос №4. Количество сеансов с разбивкой по дате

А что, если вы хотите увидеть не общее количество сеансов за выбранный диапазон дат, а еще и с разбивкой по дате? Сделать это очень легко! Нужно просто в операторе SELECT добавить дополнительное поле event_date, а в конце сделать группировку по дате события с использованием оператора GROUP BY:

Результат запроса будет выглядеть примерно так:

Данные, извлеченные с помощью данного SQL-запроса, могут идти не по порядку. Такой результат не очень удобно анализировать. Вы можете отсортировать полученный результат по дате события или по количеству сеансов с помощью оператора ORDER BY с указанием сортировки – по убыванию (DESC) или по возрастанию (ASC).

Если не указать способ сортировки, то по умолчанию BigQuery будет сортировать по возрастанию (от низких значений к высоким, ASC). В случае строковых значений они будут упорядочены от А/A до Я/Z. При работе с типами данных даты с указанием сортировки ASC самая ранняя дата отображается вверху списка, а при добавлении DESC самая поздняя дата отображается вверху списка.

Например, указав ORDER BY по event_date без использования порядки сортировки:

Переключившись на вкладку CHART, вы увидите визуализацию ваших данных:

А если указать ORDER BY по event_date с DESC, то результат запроса будет представлен с порядком сортировки по убыванию, от 20240124 до 20231223. Изменение сортировки по псевдониму sessions для нашего запроса то же возможно (DESC – по убыванию):

Запрос №5. Подсчет количества сеансов по дням недели

Даты – это здорово. Но что, если вы хотите произвести расчет количества сеансов не за выбранный диапазон дат, даже если он является статическим или динамическим, а по дням недели – понедельник, вторник, среда и т.д.?

Что ж, это тоже можно сделать. Но для начала нам необходимо поле event_date, которое в схеме данных является строкой (STRING), преобразовать в тип данных DATE. Для этого в запросе на уровне SELECT вы можете задать два раза поле event_date, только в одном случае оставить его без изменений и добавить псевдоним (например, date), а в другом использовать функцию PARSE_DATE, которая преобразует отформатированную строку YYYYMMDD значения в объект DATE.

В качестве аргумента функции необходимо указать желаемый формат. В нашем примере – это тот же шаблон %Y%m%d. SQL-запрос будет выглядеть так:

Результат в Google BigQuery:

Как видите, функция PARSE_DATE преобразовала строковые значения поля event_date в формат даты нужного нам вида ГГГГ-ММ-ДД.

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

• DAYOFWEEK - возвращает значения в диапазоне [1,7], где воскресенье является первым днем недели;
• DAY – возвращает день;
• DAYOFYEAR – возвращает день в году;
• WEEK - возвращает номер недели даты в диапазоне [0, 53]. Недели начинаются с воскресенья, а даты, предшествующие первому воскресенью года, относятся к неделе 0;
• MONTH – возвращает месяц года;
• QUARTER - возвращает значения в диапазоне [1,4];
• YEAR – возвращает год;
• и другие.

В качестве примера давайте вернем результат в формате дня недели (DAYOFWEEK). Для этого добавьте функцию EXTRACT для поля с преобразованным в формат даты значением, а также в качестве аргумента укажите DAYOFWEEK:

Результат запроса – отдельный столбец с днем недели, где 1 – воскресенье, а 7 – суббота:

Получается, что день недели с 2 до 6 – это будни, а 1 и 7 – это выходные. Можно ли в BigQuery построить запрос, который отдавал бы результат в таком срезе? Выходные/Будни?

Да, это возможно сделать с помощью оператора CASEWHEN. Создав нижеприведенную цепочку из нескольких условий, вы сможете получить заветный результат:

В данном примере функция EXTRACT осталась прежней, только перед ней мы добавили еще одну функцию CASE WHEN, которая позволяет возвращать разное значение в зависимости от заданного вами условия. Она может использоваться для преобразования данных, вычисления метрик или  создания сложных запросов.

В этом запросе используется функция IN с диапазоном (1,7), которая проверяет, входит ли значение в набор значений. И наша задача проверить день недели только на эти два значения. Если день недели 1 или 7, то результатом должно быть Выходные, в противном случае день неделя является Будни.

Результат выполнения SQL-запроса:

Запрос №6. Подсчет количества сеансов по части дня

Давайте спустимся еще на один уровень ниже и попробуем рассчитать количество сеансов за выбранные дни и в разрезе его части. Например, разбить день на утро, день, вечер и ночь.

Здесь мы предполагаем, что каждый день можно разделить на четыре равные части, каждая из которых состоит из 6 часов:

• утро (от восхода, примерно с 6 до 12 часов);
• день (с 13 до 18 часов);
• вечер (с 19 до 23 часов);
• ночь (с 00 часов до 5 утра).

К сожалению, поле event_date мы использовать не можем, потому что в этом столбце нет конкретных данных по нужным нам срезам. А вот другой столбец event_timestamp с типом данных INTEGER вполне подойдет для этой задачи.

Добавив его в свой SQL-запрос, мы увидим его в результатах:

Вот так это будет выглядеть:

Как видите, напротив каждой временной метки отображается только один сеанс, поскольку event_timestamp очень сильно детализирует статистику. event_timestamp – это время, когда было зарегистрировано событие. Оно измеряется в микросекундах. Таким образом, вероятность того, что два посетителя окажутся на вашем сайте в одно и то же время при такой детализации крайне мала (но она есть).

А с помощью функции TIMESTAMP_MICROS мы можем преобразовать временную метку event_timestamp. Она преобразует количество микросекунд с 1 января 1970 г. 00:00:00 в формате UTC и возвращает метку времени.

Добавив ее в запрос:

Результат отображения уже будет другим:

Как видите, в таком формате уже присутствует понятная дата и время совершения события. Теперь с помощью все той же функции EXTRACT мы можем извлечь не день недели, а час.

Тогда вышеприведенный запрос будет преобразован в этот:

И результат в BigQuery:

Теперь это значение мы можем использовать в функции CASE WHEN, изначально зная интервалы дня: утро (с 6 часов до 12 часов), день (с 13 до 18 часов), вечер (с 19 до 23 часов) и ночь (с 00 часов до 5 утра).

Ваш SQL-запрос будет выглядеть так:

А его результат:

Как видите, в нашей таблице есть столбец f0_ с часом дня, а есть новый столбец с частью дня – утро, день, вечер и ночь. Вы можете проверить корректность составления запроса, поочередно сопоставляя часть и итоговое переопределение с помощью функции CASE WHEN. Данные должны отображаться правильно.

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

Чтобы привести ее в формат, возможный для анализа, необходимо данные сгруппировать иным способом. Для этого используйте группировку по полям day_of_week и part_of_day, а сортировку по sessions в порядке убывания (DESC). Тогда самая популярная часть дня и часть недели будут наверху таблицы результатов, а наименее посещаемые части дня и части недели будут внизу таблицы.

SQL-запрос:

И результат запроса:

Исходя из полученных данных, наиболее посещаемым временем моего сайта за выбранный диапазон дат является утреннее время (с 6 до 12 часов) в будни. Наименее посещаемое – ночь (с 0 до 5 утра) в выходные.

Запрос №7. Расчет количества сеансов на основе параметра события ga_session_id

Каждому сеансу в Google Analytics 4 присваивается уникальный идентификатор сеанса (ga_session_id) - это временная метка начала сеанса. Таким образом, теоретически можно иметь несколько сеансов от разных пользователей с одинаковым идентификатором сеанса, потому что с точки зрения GA они могут начинаться в одно и то же время. Номер сеанса определяет количество сеансов, которые пользователь провел до текущего сеанса (например, третий или пятый сеанс пользователя на вашем сайте).

В схеме данных GA4 параметр события ga_session_id является повторяющимся и вложенным полем:

Поэтому для его извлечения необходимо использовать более сложную конструкцию с использованием функции UNNEST:

Вот так выглядит результат после выполнения запроса:

BigQuery возвращает идентификатор сеанса для каждого события, то есть каждая строка – это событие. Именно поэтому на некоторых строчках присутствуют одни и те же значения ga_session_id.

Подсчет количества уникальных идентификаторов сеанса даст нам количество сеансов, так как ga_session_id привязывается именно к сессии. Чтобы их подсчитать, используйте уже известную функцию COUNT (DISTINCT):

Это даст нам количество уникальных идентификаторов сеансов за выбранный диапазон дат:

Запрос №8. Расчет количества сеансов на основе ga_session_id и user_pseudo_id

Вышеприведенный SQL-запрос позволяет нам ответить на вопрос «Сколько было сеансов с 1 января по 15 января 2024 года»? Нет, потому что идентификатор сеанса ga_session_id может быть одинаковым для двух разных пользователей. А такое возможно? Да, так как несколько пользователей могут зайти на ваш веб-сайт в одно и то же время. В результате у них будет одинаковый идентификатор сеанса ga_session_id, но разные значения user_pseudo_id.

Чтобы избежать таких сценариев, нам необходимо использовать функцию CONCAT, объединяющую несколько строк. Она принимает в качестве аргументов одну или несколько строк и возвращает одну строку, содержащую все исходные строки, соединенные вместе. Таким образом, наш SQL-запрос преобразится и станет выглядеть так:

Результат в Google BigQuery:

Вы также можете сравнить полученные значения в BigQuery с интерфейсной статистикой Google Analytics 4:

Как видите, значения практически сошлись (14349 в BigQuery vs 14358 в GA4).

Запрос №9. Запрос с двумя комбинациями: по ga_session_id, ga_session_id и user_pseudo_id

Объединив запросы №7 и №8, вы можете получить новый запрос, в котором будет наглядно видно различие между двумя способами расчета сеансов:

• только по уникальным идентификаторам сеанса ga_session_id за выбранный диапазон дат;
• по уникальным идентификаторам сеанса ga_session_id и уникальным идентификаторам пользователей user_pseudo_id за выбранный диапазон дат.

Пример такого SQL:

Здесь мы добавили псевдонимы для двух полей, где only_ga_session_id – подсчет уникального количества значений по полю ga_session_id, а ga_session_id_and_users – подзапрос, в котором используется функция объединения строк CONCAT, где в качестве одного аргумента принимается поле user_pseudo_id, а в качестве другого – все тот же подзапрос с подсчетом уникального количества значений по полю ga_session_id.

Результатом выполнения этого SQL-запроса будет такая таблица:

• Подсчет уникального количества значений по полю ga_session_id – 14195;
• Подсчет уникального количества значений по полю ga_session_id и по user_pseudo_id – 14349.

Полученные цифры свидетельствуют о том, что при подсчете уникального количества значений по полю ga_session_id получилось меньше уникальных записей, чем при подсчете комбинации ga_session_id и user_pseudo_id. А это подтверждает наше предположение о том, что несколько пользователей могут зайти на веб-сайт в одно и то же время. В результате у них будет одинаковый идентификатор сеанса ga_session_id, но разные значения user_pseudo_id.

Запрос №10. Расчет количества сеансов с учетом ga_session_id, user_pseudo_id и session_start

Однако расчет количества сеансов с использованием уникального идентификатора сеанса не будет точно соответствовать данным в интерфейсе GA4. Это связано с тем, что Google Analytics 4 для расчета сеанса использует событие session_start.

И еще в GA4 есть свои особенности, которые сложно/невозможно объяснить. Во-первых, он может фактически записывать более одного события session_start для одного события. Это случается не очень часто, но может вызвать некоторые различия при детальном анализе, основанном на предположении, что в сеансе всегда есть одно событие session_start. Во-вторых, один и тот же идентификатор сеанса может быть назначен двум сеансам разных пользователей. Ну и, конечно же, в-третьих - это то, что сеансы могут вообще быть без события session_start.

Как это возможно? Все дело в том, что в Google Analytics 4 сеансы могут длиться несколько дней и при этом считаться одним и тем же сеансом, они не будут перезапускаться в полночь, как это было в Universal Analytics. Поэтому у вас может возникнуть такая ситуация, когда событие session_start должно произойти в пятницу вечером, но некоторые просмотры страниц и события user_engagement происходят рано утром в субботу. Если вы создаете отчет только по субботе, вы увидите взаимодействия пользователей в сеансе, в котором нет события session_start.

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

Вышеприведенный SQL-запрос используется для расчета количества сеансов на основе события session_start для каждого уникального пользователя и его идентификатора сеанса. Это делается путем объединения user_pseudo_id и ga_session_id с помощью функции CONCAT для создания комбинации уникального идентификатора сеанса и уникального идентификатора пользователя. Затем он суммирует количество событий, где event_name равно session_start, а затем упорядочивает результаты по количеству событий начала сеанса в порядке убывания.

Результат выполнения команды в BigQuery:

Если выгрузить эти данные в Microsoft Excel/Google Таблицы или тот же Looker Studio и просуммировать все значения по полю session_start_events, то они будут несколько отличаться от всех вышеприведенных запросов.

Запрос №11. Расчет количества сеансов с учетом user_pseudo_id, event_timestamp и session_start

Альтернативный вариант - SQL-запрос с объединением полей user_pseudo_id и event_timestamp и использованием все того же события session_start:

Результат в BigQuery:

Запрос №12. Определение сеанса с взаимодействием с помощью session_engaged

Помимо сеансов, немаловажной метрикой в Google Analytics 4 являются Сеансы с взаимодействием (Engaged sessions). Чтобы сеанс считался как сеанс с взаимодействием, пользователь должен выполнить хотя бы одно из следующих действий:

• активно взаимодействовать с вашим сайтом или приложением не менее 10 секунд (такая настройка задана по умолчанию);
• выполнить событие-конверсию;
• просмотреть 2 или более страниц.

Таким образом, если сеанс длится дольше определенного времени, он становится сеансом с взаимодействием.

Условие взаимодействовать не менее 10 секунд вы можете переопределить в интерфейсе Google Analytics 4, настроив время ожидания сеанса. Подробнее об этом читайте в этой статье.

И чтобы соответствовать этому определению, вы можете использовать параметр события session_engaged в Google BigQuery. Например, его можно найти в событии user_engagement или в first_visit:

Он принимает для события строковое значение (string_value) 1 в случае, когда сеанс является сеансом с взаимодействием и 0, когда не является. Это выглядит немного странным, поскольку 1 и 0 – это целые числа и должны храниться в поле с типом INTEGER. Но самое интересное, что session_engaged также может быть и числовым значением (int_value). Я встречал в своих данных вот такие события и значения session_engaged:

В одном событии значение session_engaged - string, а в другом - integer. У меня есть предположение, но оно не является подтвержденным. Google добавляет сохраняет числовое значение int_value только для событий session_start, чтобы по ним рассчитывать сеансы с взаимодействием. Для всех остальных событий значение 1 и 0 записывается в string_value.

Почему я так думаю? Попробуйте добавить показатель Сеансы с взаимодействием в отчет по событиям. Вы увидите очень интересную картину - общее количество сеансов в строке Итого рассчитывается именно по событию session_start, а все остальные игнорируются:

Таким образом, можно сделать вывод, что расчет сеансов с взаимодействием основан на событии session_start. И поэтому в таблицах с событиями в BigQuery напротив события session_start параметр session_engaged хранит свое значение именно в int_value, а не как у всех других событий в string_value.

Помимо параметра session_engaged вы можете встречать очень схожий по написанию параметр события engaged_session_event, являющийся целочисленным значением:

Он включает в себя количество сеансов, в которых участвует пользователь и может использоваться для отслеживания уровня вовлеченности/взаимодействия. Однако нас сейчас интересует именно session_engaged.

Выполним вот такой SQL-запрос, чтобы учесть оба варианта значений:

Сначала нам нужно составить список всех идентификаторов сеанса и выяснить, являются ли они сеансы с взаимодействиями или нет. Затем мы должны подсчитать количество сеансов с взаимодействием. Для идентификатора сеанса ga_session_id используется тип данных INTEGER, поэтому в запросе мы извлекаем это значение из поля value.int_value, в то время как параметр session_engaged имеет два типа данных STRING и INTEGER, поэтому  здесь мы используем функцию COALESCE, которая вычисляет по порядку каждый из своих аргументов и на выходе возвращает значение первого аргумента, который был не NULL. А еще используется функция CAST, которая преобразует заданное выражение в заданный тип. В нашем случае - в string.

Добавив к каждому подзапросу псевдоним, выполним сам запрос:

Результат запроса вернет все строки таблицы с выбранными полями, потому что мы работаем с записями событий. В таблице присутствует множество повторяющихся строк с одним и тем же идентификатором сеанса ga_session_id. Например, даже у одного пользователя может быть такое, что изначально для какого-то конкретного события поле session_engaged в сеансе имеет значение 0, а еще через некоторое время оно принимает значение 1. То есть начиная с определенного события, сеанс пользователя становится сеансом с взаимодействием.

Поскольку наша задача – это подсчитать количество сеансов с взаимодействием, то мы можем прибегнуть к функции MAX, которая вернет в таблице большее значение из представленных для session_engaged, то есть 1. Таким образом, мы сможем подсчитать общее количество сеансов с взаимодействием за выбранный период для уникальных пользователей.

Скорректируем запрос, добавив функцию MAX, а также сгруппируем результаты по выбранным полям:

Вы получите таблицу, в которой каждая строка будет представлять уникальную комбинацию user_pseudo_id, session_id и session_engaged. Там, где 1 – у пользователя сеанс с взаимодействием, где 0 – нет.

Запрос №13. Расчет сеансов с взаимодействием

Несмотря на то, что значение 1 или 0 хранится в параметре session_engaged для int_value и string_value, по моим наблюдениям наиболее близкие результаты с интерфейсными данными Google Analytics 4 вы получите тогда, когда будете использовать именно int_value и событие session_start, а не оба поля и функцию COALESCE.

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

В Google BigQuery результат запроса будет иметь такой вид:

Если сравнить текущее значение с интерфейсными данными, то окажется, что процент расхождения составляет менее 1%. 8631 сеанс с взаимодействием в Google BigQuery и 8595 сеансов с взаимодействием в моем ресурсе Google Analytics 4 за выбранный диапазон дат.