Визуализация путей пользователей на данных Google Analytics 4 в BigQuery
В этой статье я покажу вам, как создать диаграмму Санкей (Sankey diagram), используя данные Google Analytics 4, Google BigQuery, SQL-запросы и язык программирования Python.
Введение
Материал частично основан на решении Джошуа Кима (Joshua Kim), но дополнен моими комментариями, скриншотами и новыми приёмами.
Диаграмма Санкей (Sankey diagram)
Диаграмма Санкей, Санки, Сэнки и другие вариации (Sankey diagram) - это тип визуализации, в котором потоки (связи) между категориями отображаются с помощью направленных линий (лент), толщина которых соответствует количественному значению потока. Узлы (категории) располагаются по уровням слева направо, а ленты показывают, как значения распределяются между ними.
Диаграмма Sankey особенно полезна для:
- анализа путей движения ресурсов (денег, товаров, энергии и пр.);
- визуализации воронок (например, пользовательских или продаж);
- отображения распределения значений между различными стадиями процессов.
Если вы успели поработать с Universal Analytics (Google Analytics 3), то наверняка хотя бы раз открывали карту поведения:
Карта поведения в Google Analytics (устар. Universal Analytics)
Она визуализировала пути пользователей между страницами сайта и показывала, как пользователи перемещаются по сайту, какие страницы наиболее популярны и какие приводят к отказам, помогая анализировать контент, улучшать навигацию и определять, что интересует или не интересует аудиторию.
Или отчет Карта событий, который показывал, в каком порядке посетители вашего сайта запускали события на сайте:
Карта событий в Google Analytics (устар. Universal Analytics)
С его помощью вы могли понять, какой контент больше всего привлекает пользователей и как они переходят от одного события к другому.
А еще в Universal Analytics был отчет по путям пользователей, который позволял визуализировать пути перемещения посетителей по вашему сайту, начиная от страницы входа и заканчивая страницей выхода. Он был схож с картой поведения, но был ориентирован на страницы, которые посещали пользователи, и не включал события и группы контента.
Пути пользователей в Google Analytics (устар. Universal Analytics)
В Google Analytics 4 нет элемента интерфейса, который официально называется Sankey diagram, однако концепция Санки используется внутри некоторых визуализаций. Наиболее близкая концепция - это Исследование пути (Path Exploration).
Исследование пути в Google Analytics 4
Данная методика исследования позволяет визуализировать пути перемещения пользователей по вашему сайту или мобильному приложения с целью:
- определения последовательности выполнения тех или иных событий (какие из них происходят первыми, какие вторыми и т.д.);
- выявления наиболее популярных маршрутов;
- нахождения наиболее проблемных мест (где происходят ошибки, отклонения от привычного маршрута посетителей, зацикливания и т.д.);
- влияния конкретных событий на последующие действия пользователей.
Используя диаграмму Сэнки, мы можем реализовывать свои стратегии следующим образом (пример):
- большинство покупателей на сайте обычно проходят путь, начиная со страницы A, затем переходят на страницу Б, далее на страницу В и в итоге совершают покупку. Поэтому мы можем добавить кнопку с призывом к действию, направляющую других посетителей по этому пути, что потенциально позволит нам повысить доход;
- страницы A и Б - это места, откуда наши посетители чаще всего уходят. Скорее всего, у этих страниц есть проблемы с точки зрения удобства использования (юзабилити) или технической реализации. Стоит перейти к проведению интервью и исследованиям пользователей, чтобы глубже понять причины высокого показателя отказов и ухода с этих страниц.
Хотя в GA4 есть поддержка построения воронок и анализа пути пользователей для понимания их перемещений по сайту, на практике этот процесс очень сложен и неудобен, если вы хотите получить какие-либо полезные данные из Исследований.
Поэтому в качестве альтернативного решения рассмотрим пример построения диаграммы Санкей, используя данные Google Analytics 4, Google BigQuery, SQL-запросы и язык программирования Python.
Однако перед тем, как это сделать, вы должны установить связь Google Analytics 4 с BigQuery.
Ниже будут использоваться подзапросы WITH (Common Table Expressions, CTE) - это конструкция SQL, которая позволяет создавать временные именованные результирующие наборы (подзапросы), которые можно использовать внутри основного запроса. Она делает код более читаемым и структурированным, особенно при сложных запросах.
1. Преобразование данных
Хотя интеграция GA4 с BigQuery очень удобна, существует непростая задача - некоторые поля в схеме данных могут иметь тип STRUCT. Это такой составной тип данных, который может содержать произвольное количество полей с различными типами данных.
Тип поля RECORD (STRUCT)
Другими словами, это контейнер упорядоченных полей (группа полей, следующая в определенном порядке), каждое из которых имеет тип и имя поля. Имена полей являются необязательными, то есть структуру можно определить как STRUCT <INT64, STRING> или STRUCT <id INT64, name STRING>
, где:
- INT64 и STRING – тип поля (обязательно);
- id и name – имена полей (необязательно).
В схеме данных, где присутствует вложенность, там всегда тип RECORD. Внутри вложенности могут быть разные типы данных. Например, для параметров событий event_params установлены несколько разных типов:
Разные типы данных внутри "RECORD"
- string_value - STRING
- int_value - INTEGER
- float_value - FLOAT
- double_value - FLOAT
Это и есть группа полей, следующих в определенном порядке, у которых есть названия и типы (STRUCT / RECORD).
Для "расплющивания" (flatten) такой сложной структуры данных Google Analytics 4 в BigQuery используется функция UNNEST. Она позволяет вам взять массив, и развернуть его элементы в отдельные строки.
Первый SQL-запрос, который вы должны выполнить, выглядит так:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
WITH CTE_flatten1 AS ( SELECT event_date, event_timestamp, user_pseudo_id, ga_session_id.value.int_value AS session_id, ga_session_number.value.int_value AS session_number, event_name, page_location.value.string_value AS page_location, ecommerce.purchase_revenue_in_usd AS revenue_usd, geo.country, device.category AS device, utm_campaign.value.string_value AS utm_campaign, utm_medium.value.string_value AS utm_medium, utm_source.value.string_value AS utm_source, page_referrer.value.string_value AS page_referrer FROM `your_table.events_*` LEFT JOIN UNNEST (event_params) AS ga_session_id ON ga_session_id.key = 'ga_session_id' LEFT JOIN UNNEST (event_params) AS ga_session_number ON ga_session_number.key = 'ga_session_number' LEFT JOIN UNNEST (event_params) AS page_location ON page_location.key = 'page_location' LEFT JOIN UNNEST (event_params) AS utm_campaign ON utm_campaign.key = 'campaign' LEFT JOIN UNNEST (event_params) AS utm_medium ON utm_medium.key = 'medium' LEFT JOIN UNNEST (event_params) AS utm_source ON utm_source.key = 'source' LEFT JOIN UNNEST (event_params) AS page_referrer ON page_referrer.key = 'page_referrer' WHERE _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AND ga_session_number.value.int_value = 1 AND user_pseudo_id IS NOT NULL AND ga_session_id IS NOT NULL ), |
, где вместо your_table.events_* задайте свой путь к таблице с данными Google Analytics 4 в BigQuery.
Он:
- преобразует (flatten) события GA4 в более удобный «плоский» формат;
- извлекает ключевые параметры события (session_id, utm, page_location и др.);
- фильтрует события за последние 90 дней;
- выбирает только первую сессию пользователя (session_number = 1);
- убирает записи без user_pseudo_id и session_id.
Подробная расшифровка:
LEFT JOIN UNNEST(event_params) AS ga_session_id ON ga_session_id.key = 'ga_session_id'
Из массива параметров события извлекается параметр ga_session_id. Он нужен, чтобы связать все события в одну сессию и отслеживать путь пользователя внутри нее.
LEFT JOIN UNNEST(event_params) AS ga_session_number ON ga_session_number.key = 'ga_session_number'
Извлекает номер сессии пользователя. Это позволяет отличить первый визит от повторного и анализировать поведение новых пользователей.
LEFT JOIN UNNEST(event_params) AS page_location ON page_location.key = 'page_location'
Извлекает URL текущей страницы. Это нужно, чтобы строить путь пользователя по страницам сайта.
LEFT JOIN UNNEST(event_params) AS utm_campaign ON utm_campaign.key = 'campaign'
Извлекает рекламную кампанию из UTM_метки. Она позволяет понять, из какой кампании (campaign) пришел пользователь.
LEFT JOIN UNNEST(event_params) AS utm_medium ON utm_medium.key = 'medium'
Извлекает канал трафика из UTM_метки (например, cpc, organic, referral). Помогает классифицировать канал посещения (medium).
LEFT JOIN UNNEST(event_params) AS utm_source ON utm_source.key = 'source'
Извлекает источник трафика (source) из UTM_метки.
LEFT JOIN UNNEST(event_params) AS page_referrer ON page_referrer.key = 'page_referrer'
Извлекает реферер (предыдущий URL). Это позволяет понять, с какой страницы пользователь пришел на текущую.
Фильтры в WHERE:
AND ga_session_number.value.int_value = 1
AND user_pseudo_id IS NOT NULL
AND ga_session_id IS NOT NULL
1. ga_session_number = 1. Оставляются только события из первой сессии пользователя, то есть анализируются только новые пользователи.
2. user_pseudo_id IS NOT NULL. Остаются только события, у которых есть пользователь. Без user_pseudo_id нельзя связать события между собой.
3. ga_session_id IS NOT NULL. Остаются только события, у которых есть идентификатор сессии. Без него невозможно отслеживать путь пользователя внутри сеанса.
Пример выполнения SQL:
Результат выполнения SQL
2. Убедитесь, что каждый пользователь и сеанс имеют одинаковые свойства (или параметры).
Иногда, даже если события привязаны к одному и тому же идентификатору сеанса, некоторые события имеют все параметры, а другие - нет. Поэтому при выполнении SQL-запроса создаются дубликаты параметров для одного и того же идентификатора пользователя и идентификатора сеанса.
Только одно событие в таблице имеет значение в medium
В таком случае можно воспользоваться функциями FIRST_VALUE и LAST_VALUE для корректного анализа входных источников и путей пользователей.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CTE_flatten2 AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, session_number, event_name, REGEXP_REPLACE(REGEXP_REPLACE(LOWER(page_location), r'(\?.*)$', ''), r'/$', '') AS page_location, revenue_usd, FIRST_VALUE(country) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS country, FIRST_VALUE(device) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS device, LAST_VALUE(utm_campaign) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS utm_campaign, LAST_VALUE(utm_medium) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS utm_medium, LAST_VALUE(utm_source) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS utm_source, LAST_VALUE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(page_referrer), r'(\?.*)$', ''), r'/$', '')) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS page_referrer FROM CTE_flatten1 ), |
Полная расшифровка запроса:
REGEXP_REPLACE(REGEXP_REPLACE(LOWER(page_location), r'(\?.*)$', ''), r'/$', '')
Это:
- переводит URL в нижний регистр
- убирает GET-параметры (?utm=...)
- убирает слэш на конце (/page/ → /page)
Чтобы одинаковые страницы не считались разными из-за параметров. Зачастую URL-адрес страницы имеет разную форму, хотя перенаправляет посетителей на одну и ту же страницу.
Четыре URL-адреса ниже имеют разные значения, но в любом случае направляют вас на одну и ту же страницу Google.)
- https://google.com
- https://google.com/
- https://google.com?utm_source=medium
- https://google.com/?utm_source=medium
Вот почему используется функция REGEXP_REPLACE (чтобы отбросить ненужные буквы и символы).
FIRST_VALUE(country) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp)
FIRST_VALUE(device) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp)
Берет страну и устройство из самого первого события сессии. Из-за технических проблем у одного пользователя и сеанса может быть несколько стран. В связи с этим целесообразно получать значение самой первой страны, чтобы знать, где пользователь и сеанс начали работу.
Предположим, посетитель находится на борту самолета, и на момент начала сеанса самолет находится в США. Но при посещении следующей страницы он уже был в Канаде. В этом случае мы бы предпочли считать, что посетитель вылетает из США.
LAST_VALUE(utm_campaign) ...
LAST_VALUE(utm_medium) ...
LAST_VALUE(utm_source) ...
Берет последнюю не-NULL UTM-метку внутри сессии.
LAST_VALUE(REGEXP_REPLACE(...page_referrer...)) OVER(...)
То же, что и с URL:
- строчные буквы;
- убраны параметры;
- убран слэш на конце;
- выбираем последнее ненулевое значение в сессии.
Нужно для корректного анализа входных источников и путей пользователей.
Результат выполнения второго SQL-запроса:
Результат выполнения SQL
3. Удалите последовательные дубликаты URL-адресов одной и той же страницы в одном и том же идентификаторе сеанса
В большинстве случаев, когда посетитель попадает на страницу А, могут происходить различные события, такие как session_start, page_view, user_engagement, scroll и другие. Нам нужно оставить только уникальные URL-адреса страниц в пределах одного сеанса, удаляя повторяющиеся последовательные посещения одного и того же URL.
Для этого выполните следующий запрос:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
CTE_flatten3 AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, session_number, event_name, page_location, LAG(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS previous_page_location, LEAD(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS next_page_location, revenue_usd, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM ( SELECT *, CASE WHEN -- Previous URL IS NULL previous_page_location IS NULL THEN 'remain' WHEN -- Previous URL <> Current URL previous_page_location <> page_location THEN 'remain' WHEN -- Previous URL = Current URL previous_page_location = page_location AND page_location <> next_page_location THEN 'del' END AS remain_or_del FROM ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, session_number, event_name, page_location, LAG(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS previous_page_location, LEAD(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS next_page_location, revenue_usd, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_flatten2 ) ) WHERE remain_or_del = 'remain' ORDER BY user_pseudo_id, session_id, event_timestamp ), |
Этот SQL-запрос делает фильтрацию последовательных повторов одного и того же URL в рамках одного сеанса, оставляя только уникальные последовательные страницы для каждого пользователя.
В запросе используются функции смещения LAG и LEAD.
- LAG(page_location) - берет предыдущий URL в рамках того же пользователя и сеанса;
- LEAD(page_location) - берет следующий URL.
Средний подзапрос с CASE WHEN END не менее важен:
CASE
WHEN previous_page_location IS NULL THEN 'remain'
WHEN previous_page_location <> page_location THEN 'remain'
WHEN previous_page_location = page_location AND page_location <> next_page_location THEN 'del'
END AS remain_or_del
Условия:
- если предыдущего URL нет (IS NULL) - оставляем (remain);
- если предыдущий URL отличается от текущего - оставляем (remain);
- если текущий URL такой же, как предыдущий, но следующий отличается - помечаем как удаляемый (del).
По сути, это удаляет неуникальные последовательные дубликаты одного и того же URL в сеансе, но оставляет первый и последний в блоке повторов.
WHERE remain_or_del = 'remain'
ORDER BY user_pseudo_id, session_id, event_timestamp
Фильтрует только те события, которые помечены как remain. В результате мы получаем цепочку событий, где одинаковые последовательные страницы сокращены до одной записи.
Результат выполнения SQL, включая предыдущие запросы:
Результат выполнения SQL
4. Отметьте порядковый номер посещения каждой страницы для каждого сеанса
Мы можем пометить visit_order для каждого URL-адреса страницы на основе event_timestamp в порядке возрастания. Для этого можно использовать функцию ROW_NUMBER.
|
1 2 3 4 5 6 7 8 9 10 |
CTE_user_session_visit_pages AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, page_location, ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS visit_order, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_flatten3 ORDER BY user_pseudo_id, session_id, visit_order ), |
- для каждого пользователя и каждой сессии создается последовательный список посещенных страниц;
- каждое посещение имеет уникальный порядковый номер (visit_order) и дополнительные параметры сессии;
ROW_NUMBER() присваивает порядковый номер каждой записи внутри группы. PARTITION BY user_pseudo_id, session_id группирует данные по пользователю и сессии. ORDER BY event_timestamp - нумерация происходит по времени события, то есть по последовательности посещения страниц.
Результат: visit_order показывает, какая страница была посещена первой, второй и т.д. в рамках одной сессии.
Результат выполнения SQL, включая предыдущие запросы:
Результат выполнения SQL
5. Категоризируйте все страницы
Сам URL страницы выглядит неудобочитаемым, поэтому на этом этапе можно классифицировать все страницы, чтобы сделать их понятными и легко читаемыми.
Автор в своем решении предлагает следующую команду:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CTE_user_session_visit_pagegroups AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, page_location, CASE WHEN page_location = 'https://google.com' THEN 'Google Home' WHEN page_location = 'https://google.com/joshua' THEN 'Joshua Intro' WHEN page_location = 'https://google.com/andrew' THEN 'Andrew Intro' WHEN page_location = 'https://google.com/amber' THEN 'Amber Intro' WHEN CONTAINS_SUBSTR(page_location, 'https://google.com/policy') = True THEN 'Policy Pages' WHEN CONTAINS_SUBSTR(page_location, 'https://google.com/events') = True THEN 'Event Pages' ELSE 'ETC' END AS page_group, visit_order, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_user_session_visit_pages ORDER BY user_pseudo_id, session_id, visit_order ), |
Этот SQL создает новую CTE, которая классифицирует посещенные страницы в группы, присваивая каждой странице удобочитаемое название:
- точные совпадения (=) - конкретные страницы (Google Home, Joshua Intro и т.д.).
- частичные совпадения (CONTAINS_SUBSTR) - категории страниц (Policy Pages, Event Pages).
- все остальное - ETC (прочие страницы).
У каждого проекта будет своя категоризация. В качестве примера я буду использовать такой вариант SQL, где категория выбраны исходя из URL-адресов страниц:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
WITH CTE_user_session_visit_pagegroups AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, page_location, CASE WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'ga4') OR CONTAINS_SUBSTR(page_location, 'google-analytics-4') OR CONTAINS_SUBSTR(page_location, 'google-analytics4')) THEN 'Google Analytics 4' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'gtm') OR CONTAINS_SUBSTR(page_location, 'google-tag-manager')) THEN 'Google Tag Manager' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'universal-analytics') OR CONTAINS_SUBSTR(page_location, 'ga3')) THEN 'Universal Analytics' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'yandex-metrika') THEN 'Яндекс Метрика' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'yandex-tag-manager') OR CONTAINS_SUBSTR(page_location, 'ytm')) THEN 'Яндекс Тег Менеджер' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'gds') OR CONTAINS_SUBSTR(page_location, 'data-studio') OR CONTAINS_SUBSTR(page_location, 'google-data-studio') OR CONTAINS_SUBSTR(page_location, 'looker-studio')) THEN 'Looker Studio' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'yandex-direct') THEN 'Яндекс Директ' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'vk') THEN 'VK Реклама' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'fb') OR CONTAINS_SUBSTR(page_location, 'facebook')) THEN 'Facebook' ELSE 'Все остальное' END AS page_group, visit_order, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_user_session_visit_pages ORDER BY user_pseudo_id, session_id, visit_order ) SELECT * FROM CTE_user_session_visit_pagegroups; |
Если кратко, то здесь идет перечисление разделов моего блога (Google Tag Manager, Google Analytics 4, Яндекс Метрика, Яндекс Тег Менеджер, VK Реклама, Facebook *, Looker Studio и др.) на основе ссылки.
* Деятельность американской компании Meta (бывшая Facebook) запрещена в России, организация признана экстремистской.
Результат выполнения SQL, включая предыдущие запросы:
Результат выполнения SQL
6. Возвращайте каждую сессию к шагу 10 (step 10)
Скопируйте нижеприведенный SQL и вставьте его в BigQuery вместе с предыдущими частями:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CTE_sankey AS ( SELECT user_pseudo_id, session_id, country, device, utm_campaign, utm_medium, utm_source, page_referrer, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 1)) AS step01, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 2)) AS step02, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 3)) AS step03, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 4)) AS step04, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 5)) AS step05, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 6)) AS step06, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 7)) AS step07, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 8)) AS step08, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 9)) AS step09, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 10)) AS step10 FROM CTE_user_session_visit_pagegroups MAIN GROUP BY user_pseudo_id, session_id, country, device, utm_campaign, utm_medium, utm_source, page_referrer ORDER BY step01, step02, step03, step04, step05, step06, step07, step08, step09, step10 ) |
Этот SQL создает таблицу для построения диаграммы Санкей по шагам сессии.
Подробная расшифровка:
MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB
WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id
AND MAIN.session_id = SUB.session_id
AND visit_order = 1)) AS step01
Для каждого пользователя и сессии берется page_group для конкретного visit_order (первый визит - step01). Используется функция MAX, чтобы агрегировать подзапрос в один результат на сессию. Аналогично для step02, step03 и так далее.
GROUP BY user_pseudo_id, session_id, country, device, utm_campaign, utm_medium, utm_source, page_referrer
Группировка по пользователю, сессии и всем атрибутам сессии. Благодаря этому каждая строка = одна сессия пользователя со всеми параметрами и упорядоченными шагами страниц.
ORDER BY step01, step02, ...
Сортирует сессии по последовательности посещtнных страниц (step01 → step10).
В запросе создаются step01 … step10 - это максимально отслеживаемые шаги сеанса.
Причины:
- обычно пользователи просматривают не более 10 страниц за сессию для анализа;
- если сессия длиннее 10 шагов - дальнейшие шаги игнорируются, чтобы не перегружать таблицу;
- это стандартный прием при построении Sankey-диаграмм (фиксированное число шагов удобно для визуализации);
- такое количество шагов используется в Google Analytics.
Итоговый SQL-запрос в BigQuery на данных Google Analytics 4 будет выглядеть так:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 |
WITH CTE_flatten1 AS ( SELECT event_date, event_timestamp, user_pseudo_id, ga_session_id.value.int_value AS session_id, ga_session_number.value.int_value AS session_number, event_name, page_location.value.string_value AS page_location, ecommerce.purchase_revenue_in_usd AS revenue_usd, geo.country, device.category AS device, utm_campaign.value.string_value AS utm_campaign, utm_medium.value.string_value AS utm_medium, utm_source.value.string_value AS utm_source, page_referrer.value.string_value AS page_referrer FROM `osipenkovru-373609.analytics_206854065.events_*` LEFT JOIN UNNEST(event_params) AS ga_session_id ON ga_session_id.key = 'ga_session_id' LEFT JOIN UNNEST(event_params) AS ga_session_number ON ga_session_number.key = 'ga_session_number' LEFT JOIN UNNEST(event_params) AS page_location ON page_location.key = 'page_location' LEFT JOIN UNNEST(event_params) AS utm_campaign ON utm_campaign.key = 'campaign' LEFT JOIN UNNEST(event_params) AS utm_medium ON utm_medium.key = 'medium' LEFT JOIN UNNEST(event_params) AS utm_source ON utm_source.key = 'source' LEFT JOIN UNNEST(event_params) AS page_referrer ON page_referrer.key = 'page_referrer' WHERE _table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AND ga_session_number.value.int_value = 1 AND user_pseudo_id IS NOT NULL AND ga_session_id IS NOT NULL ), CTE_flatten2 AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, session_number, event_name, REGEXP_REPLACE(REGEXP_REPLACE(LOWER(page_location), r'(\?.*)$', ''), r'/$', '') AS page_location, revenue_usd, FIRST_VALUE(country) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS country, FIRST_VALUE(device) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS device, LAST_VALUE(utm_campaign) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS utm_campaign, LAST_VALUE(utm_medium) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS utm_medium, LAST_VALUE(utm_source) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS utm_source, LAST_VALUE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(page_referrer), r'(\?.*)$', ''), r'/$', '')) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS page_referrer FROM CTE_flatten1 ), CTE_flatten3 AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, session_number, event_name, page_location, revenue_usd, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM ( SELECT *, CASE WHEN previous_page_location IS NULL THEN 'remain' WHEN previous_page_location <> page_location THEN 'remain' WHEN previous_page_location = page_location AND page_location <> next_page_location THEN 'del' END AS remain_or_del FROM ( SELECT *, LAG(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS previous_page_location, LEAD(page_location) OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS next_page_location FROM CTE_flatten2 ) ) WHERE remain_or_del = 'remain' ORDER BY user_pseudo_id, session_id, event_timestamp ), CTE_user_session_visit_pages AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, page_location, ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, session_id ORDER BY event_timestamp) AS visit_order, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_flatten3 ), CTE_user_session_visit_pagegroups AS ( SELECT event_date, event_timestamp, user_pseudo_id, session_id, page_location, CASE WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'ga4') OR CONTAINS_SUBSTR(page_location, 'google-analytics-4') OR CONTAINS_SUBSTR(page_location, 'google-analytics4')) THEN 'Google Analytics 4' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'gtm') OR CONTAINS_SUBSTR(page_location, 'google-tag-manager')) THEN 'Google Tag Manager' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'universal-analytics') OR CONTAINS_SUBSTR(page_location, 'ga3')) THEN 'Universal Analytics' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'yandex-metrika') THEN 'Яндекс Метрика' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'yandex-tag-manager') OR CONTAINS_SUBSTR(page_location, 'ytm')) THEN 'Яндекс Тег Менеджер' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'gds') OR CONTAINS_SUBSTR(page_location, 'data-studio') OR CONTAINS_SUBSTR(page_location, 'google-data-studio') OR CONTAINS_SUBSTR(page_location, 'looker-studio')) THEN 'Looker Studio' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'yandex-direct') THEN 'Яндекс Директ' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND CONTAINS_SUBSTR(page_location, 'vk') THEN 'VK Реклама' WHEN CONTAINS_SUBSTR(page_location, 'https://osipenkov.ru/') AND (CONTAINS_SUBSTR(page_location, 'fb') OR CONTAINS_SUBSTR(page_location, 'facebook')) THEN 'Facebook' ELSE 'Все остальное' END AS page_group, visit_order, country, device, utm_campaign, utm_medium, utm_source, page_referrer FROM CTE_user_session_visit_pages ), CTE_sankey AS ( SELECT user_pseudo_id, session_id, country, device, utm_campaign, utm_medium, utm_source, page_referrer, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 1)) AS step01, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 2)) AS step02, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 3)) AS step03, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 4)) AS step04, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 5)) AS step05, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 6)) AS step06, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 7)) AS step07, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 8)) AS step08, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 9)) AS step09, MAX((SELECT page_group FROM CTE_user_session_visit_pagegroups SUB WHERE MAIN.user_pseudo_id = SUB.user_pseudo_id AND MAIN.session_id = SUB.session_id AND visit_order = 10)) AS step10 FROM CTE_user_session_visit_pagegroups MAIN GROUP BY user_pseudo_id, session_id, country, device, utm_campaign, utm_medium, utm_source, page_referrer ) SELECT * FROM CTE_sankey; |
И результат его выполнения:
Результат выполнения всего SQL-запроса
Визуализация данных
В оригинальной статье автор приводит пример, где он использует для построения диаграммы Санкей сервис Redash. Это платформа для визуализации данных и бизнес-аналитики, которая предоставляет простой интерфейс для создания дашбордов, запросов и отчетов.
Конечный результат у Джошуа получился таким:
Конечный результат
Мы же будем использовать другой подход. На основе нашей итоговой таблицы из BigQuery можно построить диаграмму Sankey в Python с помощью библиотеки Plotly, которая отлично поддерживает Sankey-диаграммы.
В правом углу результатов запроса нажмите на Open in и выберите Notebook:
Open in - Notebook
В открывшемся окне в меню программы нажмите Выполнить все, чтобы запустить выполнение кода:
Выполнить все ячейки программы
Вас могу попросить дать доступ к приложению Colab Enterprise. Сделайте это и дождитесь завершения работы программы.
Как только это произойдет, вы увидите, как последняя ячейка pandas_df = bq_df.to_pandas() преобразует табличные данные BigQuery в DataFrame.
Преобразование данных в DataFrame
В конце программы вам необходимо создать еще ячейки и вставить туда фрагменты кода для создания диаграммы Санкей. Я для своего примера попросил ChatGPT сгенерировать мне код, учитывая все шаги последовательности (от step01 до step10). Пример моего варианта - у вас может быть другой:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
import pandas as pd import plotly.graph_objects as go df = pandas_df.copy() # ваша таблица # --- 0. Берём только нужные шаги --- steps = [f"step0{i}" for i in range(1, 10)] + ["step10"] steps = [s for s in steps if s in df.columns] # на случай если чего-то нет # --- 1. Формируем уникальные узлы step::value --- nodes = [] for step in steps: unique_values = df[step].dropna().unique() nodes.extend([f"{step}::{v}" for v in unique_values]) nodes = list(dict.fromkeys(nodes)) # убираем дубли, порядок сохраняем node_index = {node: i for i, node in enumerate(nodes)} # --- 2. Строим переходы между stepX → stepX+1 --- links = {"source": [], "target": [], "value": []} for i in range(len(steps) - 1): step_from = steps[i] step_to = steps[i+1] transitions = ( df[[step_from, step_to]] .dropna() .value_counts() .reset_index(name="count") ) for _, row in transitions.iterrows(): src = f"{step_from}::{row[step_from]}" tgt = f"{step_to}::{row[step_to]}" links["source"].append(node_index[src]) links["target"].append(node_index[tgt]) links["value"].append(int(row["count"])) # --- 3. Подписи (без указания step) --- node_labels = [n.split("::")[1] for n in nodes] # --- 4. Цвета узлов по шагам --- palette = [ "rgba(31,119,180,0.8)", "rgba(255,127,14,0.8)", "rgba(44,160,44,0.8)", "rgba(214,39,40,0.8)", "rgba(148,103,189,0.8)", "rgba(140,86,75,0.8)", "rgba(227,119,194,0.8)", "rgba(127,127,127,0.8)", "rgba(188,189,34,0.8)", "rgba(23,190,207,0.8)" ] # цвет каждому step step_color = {steps[i]: palette[i % len(palette)] for i in range(len(steps))} node_colors = [] for node in nodes: step_name = node.split("::")[0] # step01, step02... node_colors.append(step_color[step_name]) # --- 5. Рисуем диаграмму --- fig = go.Figure(data=[go.Sankey( node=dict( label=node_labels, pad=25, thickness=18, color=node_colors ), link=dict( source=links["source"], target=links["target"], value=links["value"], color="rgba(150,150,150,0.25)" ) )]) fig.update_layout( title_text="Пути пользователей (Sankey)", font_size=13, width=1900, height=1000 ) fig.show() |
Подробное пояснение:
1. Берем только нужные шаги
|
1 2 |
steps = [f"step0{i}" for i in range(1, 10)] + ["step10"] steps = [s for s in steps if s in df.columns] |
Это создает список ["step01", "step02", ..., "step09", "step10"] и исключает шаги, которых нет в таблице.
2. Создаем список узлов (nodes)
|
1 2 3 4 |
nodes = [] for step in steps: unique_values = df[step].dropna().unique() nodes.extend([f"{step}::{v}" for v in unique_values]) |
Каждое возможное значение на каждом шаге - это узел.
3. Убираем дубли и создаем индекс узлов
|
1 2 |
nodes = list(dict.fromkeys(nodes)) node_index = {node: i for i, node in enumerate(nodes)} |
Plotly требует, чтобы каждый узел был просто номером: 0, 1, 2….
Эта таблица делает:
- "step01::Visit" → 0
- "step02::View" → 1
- "step02::Cart" → 2
4. Строим переходы между шагами
|
1 2 3 |
for i in range(len(steps) - 1): step_from = steps[i] step_to = steps[i+1] |
Проходим:
- step01 → step02
- step02 → step03
- ...
- step09 → step10
И считаем сколько пользователей так переходили:
|
1 2 3 4 5 6 |
transitions = ( df[[step_from, step_to]] .dropna() .value_counts() .reset_index(name="count") ) |
Дальше мы превращаем это в номера узлов:
|
1 2 3 |
links["source"].append(node_index[src]) links["target"].append(node_index[tgt]) links["value"].append(int(row["count"])) |
5. Чистые подписи узлов
|
1 |
node_labels = [n.split("::")[1] for n in nodes] |
Убираем stepXX::.
6. Цвета узлов - по шагам
Каждый шаг получает свой цвет:
|
1 |
step_color = {steps[i]: palette[i % len(palette)] for i in range(len(steps))} |
То есть все узлы на step01 - одного цвета, все на step02 - другого, и т.д.
7. Рисуем диаграмму Sankey
|
1 2 3 4 |
fig = go.Figure(data=[go.Sankey( node=dict(...), link=dict(...) )]) |
Узлы:
- label - подписи
- color - назначенные цвета
- pad - отступы
- thickness - толщина блоков
Связи:
- source - узлы «откуда»
- target - узлы «куда»
- value - сколько переходов
- color - цвет линий
8. Настройки вида
Размер, шрифт, заголовок - чисто визуальные настройки:
|
1 |
fig.update_layout(...) |
Вот так выглядит итоговый результат визуализации (диаграмма Санкей / Sankey diagram):
Визуализация путей пользователей на данных Google Analytics 4 в BigQuery
ChatGPT предложил мне улучшить мою диаграмму:
- перерисовать диаграмму под твой стиль;
- сгруппировать редкие значения в «Other»;
- уменьшить количество узлов;
- сделать горизонтальную/вертикальную версию;
- вынести код в функцию или класс.
Я согласился и получил вот такую обновленную диаграмму:
Обновленная диаграмма ("улучшения" от ChatGPT)
Далее начинается творческая работа с настройками диаграммы: вы можете группировать узлы, менять стили и цвета, скрывать лишние элементы, изменять количество шагов последовательности и в целом адаптировать визуализацию так, чтобы она стала максимально понятной и удобной для анализа, а также была похожа на отчеты и Исследования Google Analytics 4!
