Товарная воронка в BigQuery на данных Google Analytics 4
В этом руководстве я покажу, как можно с помощью простых SQL-команд создать товарную воронку в BigQuery на данных Google Analytics 4 и визуализировать все это в Looker Studio (Google Data Studio).
Примечание: данный материал посвящен проектам, для которых настроена электронная торговля Google Analytics 4 (рекомендуемые события). Однако вы можете использовать нижеприведенные примеры SQL-команд и для своих собственных воронок, даже если ваш сайт не является интернет-магазином и имеет другие названия событий.
Товарная воронка (Funnel product или Ecommerce purchases) - это маршрут пользователя от первого взгляда на товар до окончательной покупки на вашем сайте. С ее помощью вы сможете посмотреть статистику в разрезе каждого товара и узнать, в какое количество событий данный конкретный товар попадал. Может быть такое, что товар часто добавляют в корзину, но не покупают. Или его часто просматривают, но даже не добавляют в корзину. И так далее.
То, что вы в результате получите после выполнения описанных действий, выглядит примерно так:
Как сделать подобное? Давайте разбираться!
Отчеты электронной торговли
Если на вашем сайте уже настроена электронная торговля, то данные по отслеживаемым событиям можно посмотреть в самом интерфейсе Google Analytics 4:
- в отчете по событиям (Events)
- в отчете Монетизация - Совершенные покупки (Monetization - Ecommerce purchases), который содержит информацию о товарах или услугах, которые вы продаете в интернет-магазине
- в Исследованиях с использованием шаблона отрасли
Еще в GA4 есть два наглядных отчета с визуализациями воронки - Путь к покупке (Purchase journey):
Этот отчет показывает, сколько пользователей уходит на каждом шаге воронки продаж. Он поможет вам определить, какие шаги нужно оптимизировать.
И Путь к оформление покупки (Checkout journey):
В этом отчете можно посмотреть количество и процент пользователей, которые начали оформлять покупку на вашем сайте или в приложении и выполнили определенный шаг воронки оформления покупки.
Оба отчета имеют закрытый тип воронки. Но один отчет отличается от другого тем, что в отчете Путь к покупке (Purchase journey) воронка начинается с события начала сеанса (session_start) и заканчивается покупкой (purchase или in_app_purchase), а в отчете Путь к оформление покупки (Checkout journey) воронка начинается с события начала оформления покупки (begin_checkout) и заканчивается покупкой (purchase или in_app_purchase).
Эти отчеты нельзя настраивать. Поэтому если вы хотите посмотреть другие шаги воронки, вам необходимо создать Исследование воронки:
Но все эти отчеты и Исследования очень сильно ограничивают нас в выборе и возможностях построения гибких воронок с нужными нам данными. Поэтому мы будем учиться строить свои собственные воронки в Google BigQuery.
Создание товарной воронки в Google BigQuery
Перед тем, как создать свою товарную воронку в BigQuery, вам необходимо:
Я также рекомендую при создании связи с BigQuery использовать оба типа экспорта - ежедневный (daily) и потоковый (streaming), чтобы иметь возможность анализировать статистику в реальном времени. Для потоковой передачи данных нужен активный платежный аккаунт и привязанная банковская карта. Это позволит вам полноценно работать в BigQuery и выполнять любые SQL-запросы без каких-либо ограничений.
В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации этого материала вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность пользоваться Google Cloud и оплачивать счета.
После того, как вы это сделаете, вам необходимо продумать саму воронку - какие события вы будете использовать для ее построения? Сделать это можно на обычном листке бумаги, выписав нужные события по порядку. Я буду создавать воронку для своего сайта osipenkov.ru. Он является не только контентным и новостным проектом, но и также ecommerce, поскольку в разделе Магазин можно приобрести различные товары и онлайн-курсы.
В качестве примера я буду использовать следующий список событий:
- view_item (просмотр товара)
- add_to_cart (добавление товара в корзину)
- view_cart (переход на страницу корзины)
- begin_checkout (начало оформления заказа)
- add_shipping_info (добавление способа доставки)
- add_payment_info (добавление способа оплаты)
- purchase (покупка)
Это классические события электронной торговли, они же рекомендуемые события.
Перейдя в BigQuery, начинаем писать запрос. Для начала необходимо создать подзапрос, который будет содержать название события, название товара и общее количество товаров:
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT event_name, item_name, SUM(items.quantity) AS items FROM `osipenkovru-373609.analytics_206854065.events_*`, UNNEST(items) AS items WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY event_name, item_name |
, где вам необходимо будет изменить следующие значения:
- osipenkovru-373609 - ваш проект в Google Cloud;
- analytics_206854065 - ваш набор данных (датасет);
- 20240101 и 20240131 - дата начала и окончания (диапазон дат).
Выполнив запрос, вы получите примерно такой результат:
Этот запрос агрегирует данные из таблицы events_* за заданный диапазон дат с 1 января по 31 января 2024 года с помощью функции UNNEST, разбирая структуру повторяющихся полей и получая доступ к элементам списка items. Затем он суммирует количество элементов в каждой группе, группируя результат по полю event_name (название события) и item_name (название товара).
Примечание: подробнее о схеме данных Google Analytics 4 в Google BigQuery читайте здесь.
Теперь мы готовы к нашему основному запросу. Обернем текущий запрос в подзапрос и начнем писать основной запрос. Для этого можно использовать оператор WITH. Он позволяет вам определять общие таблицы выражений (CTE), которые можно использовать в вашем запросе. Common Table Expressions (CTE) - это виртуальные таблицы, которые создаются на основе существующих таблиц или других CTE. Они могут быть полезны для:
- разделения сложных запросов на более мелкие, более управляемые части;
- повторного использования результатов одного запроса в другом запросе;
- объединения данных из нескольких источников.
Таким образом, мы можем наш вышеприведенный запрос заключить в оператор WITH и круглые скобки, а затем сослаться на него в нашем основном запросе. В результате у вас должен получиться такой SQL-запрос:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH cte1 AS ( SELECT event_name, items.item_name, SUM (items.quantity) AS items FROM `osipenkovru-373609.analytics_206854065.events_*`, UNNEST(items) AS items WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY event_name, item_name) SELECT ... -- здесь будет основной запрос FROM cte1 |
Теперь мы хотим получить список всех товаров item_name. Затем мы можем начать подсчет количества товаров для каждого этапа нашей воронки продаж. Первый шаг – это view_item. Для этого можно использовать простую функцию агрегации SUM и конструкцию CASE WHEN.
1 |
SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END) AS step1_view_item |
Таким образом, когда событие равно view_item, тогда результат BigQuery заносит в поле items и выполняет общее суммирование. В противном случае если событие не равно view_item, то результат будет 0, и он не будет участвовать в расчете.
Чтобы иметь возможно протестировать этот шаг воронки прежде, чем перейти к следующим, добавьте в конце группировку по названию товара:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
WITH cte1 AS ( SELECT event_name, items.item_name, SUM (items.quantity) AS items FROM `osipenkovru-373609.analytics_206854065.events_*`, UNNEST(items) AS items WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY event_name, item_name) SELECT item_name, SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END) AS step1_view_item FROM cte1 GROUP BY item_name |
Результат в Google BigQuery отобразит нам правильные цифры:
Для каждого товара за выбранный диапазон дат событие view_item было зарегистрировано n-ое количество.
Следующий шаг воронки – это событие добавления товара в корзину add_to_cart. Вы можете просто скопировать функцию еще раз и поменять одно событие на другое, а также указав другой псевдоним:
1 |
SUM(CASE WHEN event_name = 'add_to_cart' THEN items ELSE 0 END) AS step2_add_to_cart |
Результат в BigQuery:
Как видите, на втором шаге количество достигнутых событий меньше, чем на предыдущем. Это логично, поскольку добавить товар в корзину можно только после его просмотра на карточке товаров. Но это бывает не всегда, поскольку пользователи могут перескочить с шага на шаг, в том числе благодаря сохранению страницу в закладках, перейти на нее напрямую и т.д.
Теперь добавим следующий шаг – view_cart (переход на страницу корзины). Здесь в SQL-запросе все аналогично, только отличается название события и название псевдонима шага.
1 |
SUM(CASE WHEN event_name = 'view_cart' THEN items ELSE 0 END) AS step3_view_cart |
Query results:
Здесь мы видим, что некоторые товары на шаге просмотра корзины имеют бОльшие значения, чем на шаге добавления товара в корзину. Это можно объяснить тем, что пользователь, просматривая корзину, еще не до конца уверен в приобретении того или иного товара, и бОльшее количество событий здесь может означать переход нескольких раз на эту страницу после ознакомления с другими материалами/страницами на сайте, но уже без добавления товара в корзину, поскольку он и так уже его отложил. Либо же это может быть связано из-за повторного обновления страницы. Пользователь обновил вкладку браузера – событие зарегистрировалось, еще раз – еще раз зарегистрировалось и т.д. Также этот шаг на моем сайте является необязательным и пользователь после добавления товара в корзину может сразу же перейти на шаг оформления заказа.
Следующий шаг воронки – это начало оформления заказа. Добавьте соответствующее событие begin_checkout четвертым этапом.
1 |
SUM(CASE WHEN event_name = 'begin_checkout' THEN items ELSE 0 END) AS step4_begin_checkout |
Результат в BigQuery:
Как видите, на этом этапе шаги воронки уже выравниваются и все становится так, как и должно быть в классической воронке. При желании, вы можете убрать промежуточный шаг view_cart и оставить только add_to_cart и begin_checkout.
События add_shipping_info (добавление способа доставки) у меня на сайте нет, но я все равно решил включить его, чтобы показать вам такой сценарий:
1 |
SUM(CASE WHEN event_name = 'add_shipping_info' THEN items ELSE 0 END) AS step5_add_shipping_info |
Результат в BigQuery:
Столбец пустой, так как в Google Analytics 4 я не собираю информацию по этому событию.
Следующий этап воронки - add_payment_info (добавление способа оплаты):
1 |
SUM(CASE WHEN event_name = 'add_payment_info' THEN items ELSE 0 END) AS step6_add_payment_info |
Результат в BigQuery:
И в завершение событие purchase (покупка).
1 |
SUM(CASE WHEN event_name = 'purchase' THEN items ELSE 0 END) AS step7_purchase |
Данные в BigQuery:
В идеале, при построении воронки пользователь обязательно должен переходить из одного шага в другой шаг. Иначе получится картина, аналогичная моей, когда некоторые шаги пользователи осознанно или механически пропускали. Чтобы улучшить свою воронку в рамках текущего руководства, я предлагаю просто удалить три шага (view_cart, add_shipping_info и add_payment_info), чтобы получить более актуальные цифры.
Итоговый SQL-запрос:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
WITH cte1 AS ( SELECT event_name, items.item_name, SUM (items.quantity) AS items FROM `osipenkovru-373609.analytics_206854065.events_*`, UNNEST(items) AS items WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' GROUP BY event_name, item_name) SELECT item_name, SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END) AS step1_view_item, SUM(CASE WHEN event_name = 'add_to_cart' THEN items ELSE 0 END) AS step2_add_to_cart, SUM(CASE WHEN event_name = 'begin_checkout' THEN items ELSE 0 END) AS step3_begin_checkout, SUM(CASE WHEN event_name = 'purchase' THEN items ELSE 0 END) AS step4_purchase FROM cte1 GROUP BY item_name |
И его результат в Google BigQuery:
Расчет коэффициента конверсии
Теперь рассчитаем коэффициент конверсии, поделив полученное значение на шаге покупки на значение первого шага, то есть step4_purchase / step1_view_item. И поможет нам в этом функция SAFE_DIVIDE. Она выполняет деление двух значений, но возвращает NULL, если делитель равен нулю. Таким образом, что функция SAFE_DIVIDE предотвращает ошибки деления на ноль, которые могут возникнуть при использовании оператора деления /.
1 |
SAFE_DIVIDE (SUM(CASE WHEN event_name = 'purchase' THEN items ELSE 0 END),SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END)) AS view_to_purchase_cr |
Добавив в конце сортировку по первому шагу step1, наш итоговый запрос будет выглядеть так:
Вы можете легко проверить результат деления одного значения на другое с помощью калькулятора. Например, для товара в таблице Онлайн-курс по Google Analytics 4 и BigQuery коэффициент конверсии получился 0.012903225806451613. Если поделить 2 покупки на 155 просмотров товара, то получится как раз 0.0129 (или 1,29%). Таким образом, BigQuery верно посчитал для нас значения коэффициента конверсии для каждого товара.
Визуализация данных в Looker Studio
Но что, если пойти дальше и не просто построить товарную воронку в BigQuery на данных Google Analytics 4, но и выполнить красивую визуализацию? Самый простой способ - это воспользоваться встроенным функционалом BigQuery и перейти на вкладку CHART (PREVIEW):
Здесь вы можете выбрать тип чарта и отображение данных по осям X и Y. Но поскольку у вас может быть много товаров, такая визуализация сложно читаема. Поэтому лучше воспользоваться инструментом Looker Studio и выполнить все построения там. Для этого нажмите на меню EXPLORE DATA и выберите Explore with Looker Studio:
Перейдя в Looker Studio, отредактируйте диаграмму, созданную автоматически на вашем листе. Добавьте в качестве показателей шаги воронки:
Если в качестве параметра выбрано поле item_name, тогда на диаграмме будет разбивка по шагам воронки для каждого отдельного товара. Точно такой же результат вы могли бы получить в самом BigQuery, если бы перешли на вкладку CHART (PREVIEW). Поэтому чтобы иметь возможность более гибко работать с визуализацией в Looker Studio, создайте новое вычисляемое поле, добавив в него произвольное слово в формате строка. Например, 'воронка':
А затем сохраните этот параметр и добавьте его в качестве параметра на вашу диаграмму:
Воронка преобразится и больше не будет сгруппирована по товарам, а будет отображать каждый шаг воронки. Что нам и нужно!
А добавив дополнительный фильтр по полю item_name, вы можете анализировать данные по воронке для каждого товара:
И итоговая таблица с данными по шагам и конверсии в Looker Studio: