События улучшенной статистики Google Analytics 4 в Google BigQuery

В этом руководстве рассмотрим различные SQL-запросы для экспортированных данных из Google Analytics 4 в BigQuery событий улучшенной статистики, а в конце построим сводную таблицу по всем событиям улучшенной статистики для каждого пользователя по его уникальному идентификатору Client ID (он же user_pseudo_id).

Улучшенная статистика (Enhanced measurement) - это один из четырех типов событий в Google Analytics 4, которые Google собирает автоматически, но после того, как в настройках ресурса вы активируете эту функцию. Они предназначены только для веб-потоков (веб-сайтов).

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

Вы можете нажать на значок шестеренки, чтобы посмотреть какие события в данный момент у вас активны (=отслеживаются), а какие нет.

В моем блоге есть несколько материалов на тему улучшенной статистики, я рекомендую прочитать их все, чтобы предметнее разбираться в данной теме перед тем, как вы начнете выполнять запросы в BigQuery.

• Улучшенная статистика в Google Analytics 4
• Взаимодействия с формами в улучшенной статистике
• События улучшенной статистики (GA4) в Google Tag Manager

А установив связь Google Analytics 4 с BigQuery, вы сможете приступить к извлечению данных.

Отслеживание события "Прокрутка"

В рамках первого запроса мы с вами разберем событие Прокрутка.

Прокрутка регистрируется каждый раз, когда пользователь прокручивает страницу сайта вниз до конца. Другими словами, событие scroll срабатывает один раз для страницы, когда пользователь на сайте прокручивает ее вниз и достигает 90% вертикальной прокрутки. Именно с этим событием передается параметр percent_scrolled со значением 90, который вы можете найти в вашей таблице BigQuery.

Самый простой запрос, который вы можете запустить первым, это вывести все события с названием scroll и всеми параметрами событий, чтобы убедиться, что в вашем наборе данных такие события присутствуют:

, где вместо osipenkovru-373609.analytics_206854065.events_20231215 вам необходимо использовать свой путь к таблице или набору данных, указав в запросе _TABLE_SUFFIX и несколько таблиц, чтобы посмотреть статистику за нужный диапазон дат.

Примечание: здесь и во всех последующих SQL-запросах вам нужно заменять путь к таблице BigQuery на свой собственный. Пожалуйста, не забудьте про это!

Результат будет таким:

Как видите, в событии scroll отображается параметр percent_scrolled, а его значение сохранено в параметре события с типом данных INTEGER. Но событие улучшенной статистики имеет только одно значение percent_scrolled, равно 90. Если вы регистрируете аналогичное событие, но с другими порогами прокрутки (например, на 25, 50, 75% прокрутки), то оно уже не является классическим событием улучшенной статистики. Чуть далее мы применим фильтр, чтобы извлекать данные только тогда, когда percent_scrolled = 90.

Еще нас интересует параметр page_location (страница, на которой запускается событие scroll), потому что мы не можем просто взять событие, которого нет в списке, а затем подсчитать количество событий. Мы должны убедиться в том, что событие scroll совершается на конкретной странице и имеет параметр события percent_scrolled = 90. И чтобы все это происходило из одного и того же события.

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

Если выполнить этот запрос, то увидите такие данные:

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

Затем нужно сгруппировать события по страницам прокрутки и отсортировать их в порядке убывания по количеству событий. Таким образом, ваш SQL-запрос будет выглядеть так:

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

Таким образом, в представленной таблице вы увидите список ваших страниц, на которых было совершено событие улучшенной статистики Прокрутка, отсортированных в порядке убывания по их количеству.

Однако в этой таблице будут присутствовать и данные от других пороговых значений, если они у вас настроены (25, 50, 75, 100% и т.д.). Чтобы отфильтровать данные только по какому-то конкретному значению, вы можете воспользоваться функцией CASE WHEN:

Благодаря ей мы можем выполнить условие проверки percent_scrolled = 90, и если оно выполняется, то вернуть результат с адресами страниц, на которых было совершено событие scroll и достигнуто 90% прокрутки. В противном случае для всех остальных страниц будет возвращено значение null. И поскольку параметр события percent_scrolled хранит информацию в поле INTEGER, то нам необходимо в подзапросе использовать поле value.int_value.

Полученные данные вы можете сравнить с интерфейсными значениями в Google Analytics 4, просто построив Исследование в свободной форме, где нужно выбрать параметр Местонахождение страницы, Название события и Процент прокрутки, а показатель Количество событий, и сделать соответствующий фильтр по событию на scroll и значение 90:

Вышеприведенный SQL-запрос корректен. Но его можно написать по-другому:

Результат будет аналогичным:

Отслеживание события "Исходящие ссылки"

Исходящий клик регистрируется каждый раз, когда пользователь нажимает на ссылку, ведущую за пределы домена (то есть, когда URL-адрес в ссылке отличается от вашего домена). По умолчанию такое событие регистрируется при нажатии на любую подобную ссылку. Ссылки на домены, для которых в настройках тегов настроено междоменное отслеживание, не будут вызывать события исходящих кликов.

Другими словами, событие click срабатывает всякий раз, когда пользователь нажимает на ссылку, которая ведет на другой домен (не ваш!).

Начинаем с простого SQL-запроса:

В BigQuery:

Как видите, каждое событие click содержит дополнительный параметр события outbound, принимающий строковое значение true. Помимо этого параметра в событии click передаются и другие параметры, характерные для данного события:

• link_classes - класс ссылки;
• link_domain - домен перехода (на какой сайт попадет пользователь после клика);
• link_id - идентификатор ссылки;
• link_url - URL-адрес исходящей ссылки (на какую страницу перейдет пользователь после клика).

Иногда в событии присутствуют все параметры, иногда некоторые из них. Например, без link_classes и link_id.

Написание SQL-запроса начинается из излечения текущих URL-страниц, с которых было совершено событие click. Делается это с помощью все того же скалярного подзапроса и функции UNNEST.

Затем мы перечисляем в запросе параметры события, которые передаются вместе с событием click. Для этого можно просто копировать вышеприведенную строку и заменять в ней значения ключа key на нужное.

Для link_domain:

Для link_url:

Единственное, что еще нужно сделать – это подсчитать количество случаев, когда это событие регистрируется. Функция COUNTIF поможет нам в этом:

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

В BigQuery будет такой результат:

Полученные данные вы можете сравнить с интерфейсными значениями в Google Analytics 4, просто построив Исследование в свободной форме, где нужно выбрать параметр Местонахождение страницы, Домен, связанный со ссылкой и URL ссылки, а показатель Количество событий, и сделать соответствующий фильтр по событию на click:

Но как вы понимаете, событие click, хоть и является событием улучшенной статистики, все равно используется интернет-маркетологами для отслеживания своих собственных исходящих кликов. Если вы хотите отфильтровать только исходящие клики, которые имеют параметр события outbound, равный true, то вы можете выполнить вот такой запрос:

Результат в BigQuery не изменится. При желании вы можете вывести значение параметра outbound на экран с помощью дополнительного скалярного подзапроса:

Отслеживание события "Поиск по сайту"

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

Другими словами, событие view_search_results срабатывает каждый раз, когда пользователь выполняет поиск по сайту, при наличии одного из 5 параметров в URL-адресе: q, s, search, query, keyword.

Начинаем с простого запроса:

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

Как видите, вместе с событием view_search_results фиксируется дополнительный параметр search_term (поисковый запрос). Его значение хранится в поле с типом STRING. Поэтому написание SQL-запроса для поиска по сайту сводится к двум функциям, одна из которых извлекает текущий поисковый запрос пользователя:

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

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

Результат в Google BigQuery покажет вам поисковые запросы и их количество за выбранный диапазон дат:

Полученные данные вы можете сравнить с интерфейсными значениями в Google Analytics 4, просто построив Исследование в свободной форме, где нужно выбрать параметр Поисковый запрос, а показатель Количество событий, и сделать соответствующий фильтр по событию на view_search_results:

Отслеживание события "Взаимодействия с видео"

События, связанные с видео (начало просмотра, процент просмотра и завершение просмотра) регистрируются, когда пользователи смотрят встроенные видео на вашем сайте. Другими словами, для встроенных на вашем сайте видео YouTube, для которых включена поддержка JavaScript API  (enablejsapi = 1), срабатывают следующие события:

• video_start - при запуске воспроизведения видео;
• video_progress - при достижении отметок 10%, 25%, 50% и 75% от общей продолжительности видео;
• video_complete - при завершении видео.

Поскольку таких событий несколько, то классическая фильтрация на event_name = 'название_события' здесь уже не подойдет. Нам бы хотелось захватить сразу же несколько разных наименований событий – video_start, video_progress и video_complete.

Поскольку все события взаимодействия с видео начинаются с одного и того же префикса video_ , мы можем вместо = использовать оператор LIKE и такую конструкцию:

Полученный результат в BigQuery отобразит нам список всех событий, содержащих в названии слово video_ :

Как видите, в этих событиях содержатся дополнительные параметры события:

• video_current_time - текущее время видео (в секундах), в которое произошло событие;
• video_duration - общая продолжительность видео (в секундах);
• video_percent - значение воспроизведенного видео (в процентах) на момент, когда сработало событие;
• video_provider - название платформы видео (YouTube);
• video_title - название видео;
• video_url - URL-адрес, ссылка на видео YouTube (https://www.youtube.com/watch?v=...);
• visible (логическое значение) - значение видимости видео в окне браузера. Если видео отображается в области просмотра, результатом будет значение true, если же в другой области (например, в нижней части страницы, на фоновой вкладке) – false.

Параметр video_percent напоминает параметр события percent_scrolled для события прокрутки, поскольку он тоже фиксирует определенное значение в момент, когда было зарегистрировано событие. И тип данных для него INTEGER. Общая продолжительность видео video_duration тоже имеет тип INTEGER, а все остальные параметры используют строковый формат STRING.

Начинаем писать свой SQL-команду с помощью нескольких скалярных подзапросов, каждый из которых возвращает результат для отдельного параметра события video_provider, video_title, video_url и video_duration. Причем последний, как мы уже определили, имеет тип INTEGER, а не STRING.

Затем мы хотим подсчитать количество событий, которое было совершено для события video_start. Здесь тоже нет ничего сложного, поскольку известная вам функция COUNTIF знает свое дело:

Аналогично можно поступить и для события video_complete:

А вот для video_progress, давайте, добавим еще одно дополнительное условие, которое будет возвращать результат только тогда, когда параметр события video_percent = 50.

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

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

Выполнив запрос, Google BigQuery отобразит итоговую таблицу результатов за выбранный диапазон дат:

Как видите, в таблице есть видео, которые пользователи начали смотреть, но они не дошли до конца и даже до отметки 50%. А есть видео, как например Сохранение исторических данных Universal Analytics (GA3), которое было начато, пройдено на 50%, но не завершено. Опять же, данная статистика представлена всего лишь за один день. Вы можете расширить в запросе период анализа, чтобы увидеть больше данных.

Полученные сведения о роликах на YouTube, размещенных на вашем сайте, вы можете сравнить с интерфейсными значениями в Google Analytics 4, просто построив Исследование в свободной форме, где нужно выбрать параметр Поставщик видео, Название видео и URL видео, а показатель Количество событий, и сделать соответствующие фильтры по событиям на video_start, video_progress и video_complete, а сам параметр Название события добавить в Исследования в столбцы:

Отслеживание события "Скачивание файлов"

Скачивание файла регистрируется каждый раз, когда пользователь нажимает на ссылку для скачивания обычного документа, сжатого файла, приложения, аудио- или видеоматериала. Тип файла определяется по его расширению. Другими словами, событие file_download срабатывает, когда пользователь нажимает на ссылку скачивания файла на вашем сайте одного из следующих типов:

• документ;
• текст;
• исполняемый файл;
• презентация;
• сжатый файл;
• видео;
• аудио.

Расширения файлов, которые отслеживаются по умолчанию, соответствуют такому регулярному выражению:

Простой SQL-запрос:

Полученный результат в BigQuery отобразит список всех событий file_download:

Как видите, в этих событиях содержатся дополнительные параметры события:

• file_extension - расширение файла;
• file_name - имя файла;
• link_classes - класс ссылки;
• link_id - идентификатор ссылки;
• link_text - текст ссылки;
• link_url - полный URL-адрес скачиваемого файла.

Все эти параметры в структуре данных BigQuery имеют строковый тип данных (STRING).

Начинаем писать свой SQL-команду с помощью нескольких скалярных подзапросов, каждый из которых возвращает результат для отдельного параметра события file_name, file_extension, link_url и link_text.

Затем мы хотим подсчитать количество событий, которое было совершено для события file_download. Здесь тоже нет ничего сложного, поскольку известная вам функция COUNTIF знает свое дело:

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

Нажав на кнопку RUN, BigQuery отобразит вам перечень файлов, которые скачивали пользователи на вашем сайте за выбранный диапазон дат:

Как видите, третьей строкой отобразилась информация с null. Мы можем отфильтровать ее с помощью оператора HAVING:

Полученные данные вы можете сравнить с интерфейсными значениями в Google Analytics 4, просто построив Исследование в свободной форме, где нужно выбрать параметр Название файла, Расширение файла, URL ссылки, Текст ссылки, Классы ссылок, Идентификатор ссылки (по желанию), а показатель Количество событий, и сделать соответствующий фильтр по событию на file_download:

Отслеживание события "Взаимодействия с формами"

Взаимодействие с формой регистрируется каждый раз, когда посетитель выполняет на вашем сайте действия с ней. Другими словами, Google Analytics 4 будет регистрировать два события:

• form_start - срабатывает, когда пользователь взаимодействует с формой впервые за сеанс;
• form_submit - запускается при отправке формы.

Чтобы события для формы регистрировались правильно, она должна иметь HTML-тег <form>. Проверить его наличие можно с помощью инспектора элементов в консоли разработчика. Например, в браузере Google Chrome:

Если ваша форма находится внутри тега <form>, тогда улучшенная статистика GA4 сможет регистрировать взаимодействия пользователей. В противном случае, новое событие не будет работать корректно.

Поскольку таких событий несколько (form_start и form_submit), то классическая фильтрация на event_name = 'название_события' здесь уже не подойдет. Нам бы хотелось захватить сразу же несколько разных наименований событий.

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

Полученный результат в BigQuery отобразит нам список всех событий, содержащих в названии слово form_ :

Как видите, в этих событиях содержатся дополнительные параметры события:

• form_id - идентификатор формы тега <form> (если есть);
• form_name - имя формы тега <form> (если есть);
• form_destination - обработчик, к которому обращаются данные формы при их отправке на сервер (атрибут action тега <form>);
• form_length - возвращает числовое значение, которое представляет количество полей ввода или элементов в форме;
• first_field_id - идентификатор конкретного поля, с которым было первое взаимодействие;
• first_field_name - имя элемента формы, с которым было первое взаимодействие;
• first_field_type - тип элемента формы, с которым было первое взаимодействие;
• first_field_position - позиция элемента формы, с которым было первое взаимодействие.

Приступаем к написанию SQL-команды с помощью нескольких скалярных подзапросов, каждый из которых возвращает результат для отдельного параметра события. В нашем примере это будут form_name и form_destination.

Затем мы хотим подсчитать количество событий, которое было совершено для события form_start. Здесь тоже нет ничего сложного, поскольку известная вам функция COUNTIF уже использовалась множество раз в этом руководстве:

Аналогично можно поступить и для события form_submit:

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

Выполнив запрос, Google BigQuery отобразит итоговую таблицу результатов за выбранный диапазон дат:

Как видите, в полученных результатах у некоторых форм нет названия. Такое бывает, поскольку не всегда программисты размечают их вручную или же в CMS-движке сайта изначально заложен функционал прометки формы по ID.

Но если вы хотите, вы можете отфильтровать пустые строки с помощью оператора HAVING:

К сожалению, Google по умолчанию не всегда корректно регистрирует те или иные события без дополнительных настроек. Если вы хотите, чтобы данные в вашем проекте были более точными, выполняйте самостоятельно настройку с использованием Google Tag Manager.

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

Сводная таблица для всех событий улучшенной статистики с группировкой по пользователям

Хотели бы получить сводную таблицу по всем событиям улучшенной статистики? Похожую на то, что вы можете построить в Исследовании?

Выше приведен пример с привязкой параметру Дата. А что, если сделать привязку к user_pseudo_id? Тем более, что Google BigQuery позволяет нам выполнять запросы и группировать данные по конкретным пользователям. В Google Analytics 4 выбрать Client ID в качестве отдельного параметра нельзя.

Если вы заинтригованы, то самое время начинать писать SQL-запрос! И поможет нам в таком виде результатов оператор PIVOT, который появился в Google BigQuery в 2021 году. Он позволяет преобразовывать данные из строк в столбцы и может использоваться для создания сводных таблиц, которые позволяют быстро и легко анализировать данные.

PIVOT является частью блока FROM и имеет следующий синтаксис:

, где:

• aggregation_function – агрегатная функция, которая используется для вычисления значений в столбцах сводной таблицы. Например, можно использовать функцию SUM, AVG, COUNT или MAX;
• value_expression - выражение, значение которого должно быть агрегировано.
• column_name - имя столбца сводной таблицы;
• value1, value2, ... - значения, для которых будет вычисляться агрегированная функция.

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

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

COUNT(*) - это функция, которая подсчитывает количество строк в наборе данных. Она может использоваться для подсчета общего количества строк в таблице или для подсчета количества строк, удовлетворяющих определенному условию.

Далее идет оператор FOR.

Он определяет столбцы выходной сводной таблицы и принимает список значений, которые будут использоваться для создания столбцов. Поскольку мы хотим получить сводную таблицу, где каждым столбцом будет являться отдельное событие, то нам необходимо параметр event_name как раз «повернуть» и написать в операторе FOR. Таким образом, уникальные идентификаторы пользователей user_pseudo_id будет отображаться по строкам, по названия событий event_name по столбцам.

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

• page_view - просмотры страниц
• scroll - прокрутка
• click - исходящие ссылки
• view_search_results - поиск по сайту
• video_start, video_progress, video_complete - взаимодействие с видео
• file_download - скачивание файлов
• form_start, form_submit - взаимодействие с формами

Все это будет определено в операторе IN:

Все вместе выглядит так:

Теперь нам нужно «обернуть» полученный запрос в другой запрос. Получим такой SQL:

А оператор ORDER BY в самом конце позволит вам отсортировать статистику по нужному значению в таблице. Например, по событию просмотров страницы в порядке убывания:

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

Такое не получится сделать в Google Analytics 4!