Товарная воронка в BigQuery на данных Google Analytics 4

13 февраля, 2024

В этом руководстве я покажу, как можно с помощью простых SQL-команд создать товарную воронку в BigQuery на данных Google Analytics 4 и визуализировать все это в Looker Studio (Google Data Studio).

Примечание: данный материал посвящен проектам, для которых настроена электронная торговля Google Analytics 4 (рекомендуемые события). Однако вы можете использовать нижеприведенные примеры SQL-команд и для своих собственных воронок, даже если ваш сайт не является интернет-магазином и имеет другие названия событий.

Товарная воронка (Funnel product или Ecommerce purchases) - это маршрут пользователя от первого взгляда на товар до окончательной покупки на вашем сайте. С ее помощью вы сможете посмотреть статистику в разрезе каждого товара и узнать, в какое количество событий данный конкретный товар попадал. Может быть такое, что товар часто добавляют в корзину, но не покупают. Или его часто просматривают, но даже не добавляют в корзину. И так далее.

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

Товарная воронка в Looker Studio

Как сделать подобное? Давайте разбираться!

Содержание

Отчеты электронной торговли

Если на вашем сайте уже настроена электронная торговля, то данные по отслеживаемым событиям можно посмотреть в самом интерфейсе 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, начинаем писать запрос. Для начала необходимо создать подзапрос, который будет содержать название события, название товара и общее количество товаров:

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

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-запрос:

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

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.

SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END) AS step1_view_item

1	SUM(CASE WHEN event_name = 'view_item' THEN items ELSE 0 END) AS step1_view_item

Таким образом, когда событие равно view_item, тогда результат BigQuery заносит в поле items и выполняет общее суммирование. В противном случае если событие не равно view_item, то результат будет 0, и он не будет участвовать в расчете.

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

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

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 отобразит нам правильные цифры:

Результат выполнения запроса (шаг1 - step1)

Для каждого товара за выбранный диапазон дат событие view_item было зарегистрировано n-ое количество.

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

SUM(CASE WHEN event_name = 'add_to_cart' THEN items ELSE 0 END) AS step2_add_to_cart

1	SUM(CASE WHEN event_name = 'add_to_cart' THEN items ELSE 0 END) AS step2_add_to_cart

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

Результат выполнения запроса (шаг2 - step2)

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

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

SUM(CASE WHEN event_name = 'view_cart' THEN items ELSE 0 END) AS step3_view_cart

1	SUM(CASE WHEN event_name = 'view_cart' THEN items ELSE 0 END) AS step3_view_cart

Query results:

Результат выполнения запроса (шаг3 - step3)

Здесь мы видим, что некоторые товары на шаге просмотра корзины имеют бОльшие значения, чем на шаге добавления товара в корзину. Это можно объяснить тем, что пользователь, просматривая корзину, еще не до конца уверен в приобретении того или иного товара, и бОльшее количество событий здесь может означать переход нескольких раз на эту страницу после ознакомления с другими материалами/страницами на сайте, но уже без добавления товара в корзину, поскольку он и так уже его отложил. Либо же это может быть связано из-за повторного обновления страницы. Пользователь обновил вкладку браузера – событие зарегистрировалось, еще раз – еще раз зарегистрировалось и т.д. Также этот шаг на моем сайте является необязательным и пользователь после добавления товара в корзину может сразу же перейти на шаг оформления заказа.

Следующий шаг воронки – это начало оформления заказа. Добавьте соответствующее событие begin_checkout четвертым этапом.

SUM(CASE WHEN event_name = 'begin_checkout' THEN items ELSE 0 END) AS step4_begin_checkout

1	SUM(CASE WHEN event_name = 'begin_checkout' THEN items ELSE 0 END) AS step4_begin_checkout

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

Результат выполнения запроса (шаг4 - step4)

Как видите, на этом этапе шаги воронки уже выравниваются и все становится так, как и должно быть в классической воронке. При желании, вы можете убрать промежуточный шаг view_cart и оставить только add_to_cart и begin_checkout.

События add_shipping_info (добавление способа доставки) у меня на сайте нет, но я все равно решил включить его, чтобы показать вам такой сценарий:

SUM(CASE WHEN event_name = 'add_shipping_info' THEN items ELSE 0 END) AS step5_add_shipping_info

1	SUM(CASE WHEN event_name = 'add_shipping_info' THEN items ELSE 0 END) AS step5_add_shipping_info

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

Результат выполнения запроса (шаг5 - step5)

Столбец пустой, так как в Google Analytics 4 я не собираю информацию по этому событию.

Следующий этап воронки - add_payment_info (добавление способа оплаты):

SUM(CASE WHEN event_name = 'add_payment_info' THEN items ELSE 0 END) AS step6_add_payment_info

1	SUM(CASE WHEN event_name = 'add_payment_info' THEN items ELSE 0 END) AS step6_add_payment_info

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

Результат выполнения запроса (шаг6 - step6)

И в завершение событие purchase (покупка).

SUM(CASE WHEN event_name = 'purchase' THEN items ELSE 0 END) AS step7_purchase

1	SUM(CASE WHEN event_name = 'purchase' THEN items ELSE 0 END) AS step7_purchase

Данные в BigQuery:

Результат выполнения запроса (шаг7 - step7)

В идеале, при построении воронки пользователь обязательно должен переходить из одного шага в другой шаг. Иначе получится картина, аналогичная моей, когда некоторые шаги пользователи осознанно или механически пропускали. Чтобы улучшить свою воронку в рамках текущего руководства, я предлагаю просто удалить три шага (view_cart, add_shipping_info и add_payment_info), чтобы получить более актуальные цифры.

Итоговый SQL-запрос:

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

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 предотвращает ошибки деления на ноль, которые могут возникнуть при использовании оператора деления /.

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

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):

Визуализация данных в BigQuery

Здесь вы можете выбрать тип чарта и отображение данных по осям X и Y. Но поскольку у вас может быть много товаров, такая визуализация сложно читаема. Поэтому лучше воспользоваться инструментом Looker Studio и выполнить все построения там. Для этого нажмите на меню EXPLORE DATA и выберите Explore with Looker Studio:

EXPLORE DATE - Explore with Looker Studio

Перейдя в Looker Studio, отредактируйте диаграмму, созданную автоматически на вашем листе. Добавьте в качестве показателей шаги воронки:

Добавление шагов воронки на диаграмму

Если в качестве параметра выбрано поле item_name, тогда на диаграмме будет разбивка по шагам воронки для каждого отдельного товара. Точно такой же результат вы могли бы получить в самом BigQuery, если бы перешли на вкладку CHART (PREVIEW). Поэтому чтобы иметь возможность более гибко работать с визуализацией в Looker Studio, создайте новое вычисляемое поле, добавив в него произвольное слово в формате строка. Например, 'воронка':

Новый параметр "Воронка"

А затем сохраните этот параметр и добавьте его в качестве параметра на вашу диаграмму:

Новый параметр "Воронка" на диаграмме

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

Товарная воронка в Looker Studio

А добавив дополнительный фильтр по полю item_name, вы можете анализировать данные по воронке для каждого товара:

Воронка по каждому товару

И итоговая таблица с данными по шагам и конверсии в Looker Studio:

Итоговая таблица с данными

Метки: Google Analytics 4, Google BigQuery, Looker Studio

Товарная воронка в BigQuery на данных Google Analytics 4

Отчеты электронной торговли

Создание товарной воронки в Google BigQuery

Расчет коэффициента конверсии

Визуализация данных в Looker Studio

Получайте бесплатные уроки и фишки

Еще статьи