Осваиваем SQL на примере данных интернет-магазина Google. Ч.2

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

Давайте вспомним, что хотел от нас бизнес в первой части, а хотел он следующее:

«Хочу увидеть отчет с воронкой продаж, начиная от посещения сайта и заканчивая получением товара в офисе».

Чаще всего под воронкой продаж подразумевается путь пользователя по сайту перед оплатой заказа, сама оплата, а также статусы по оплаченному заказу (например из CRM).

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

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

• посмотрели каталог товаров,
• перешли в корзину,
• далее на страницу оплаты,
• и как итог страницу «Спасибо за покупку».

Я взял только эти страницы, как наиболее очевидные этапы воронки. На самом деле этапов может быть гораздо больше (регистрация, авторизация, сервисные страницы и прочее).

Для подсчета общего количества пользователей (поле fullVisitorId) достаточно воспользоваться функцией COUNT, которая возвращает количество значений. Если добавить к функции аргумент DISTINCT, то будет посчитано количество уникальных значений.

SELECT COUNT (DISTINCT fullVisitorId) AS countUsers
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`

Посчитали:

Отлично! Теперь нам нужно посчитать сколько пользователей побывало на каждом этапе воронки. А для этого нам потребуется комбинация из типов страниц (параметров) и количества пользователей (показателя). Поможет нам в этом функция CASE, которая в зависимости от указанных условий возвращает одно из множества возможных значений.

Синтаксис CASE:

CASE WHEN условия поиска THEN результат ELSE результат, если условия не найдены END

Чтобы посчитать страницы, нужно запросить поля hits.page.pagePath, однако вместо подсчета страниц вы можете использовать события расширенной электронной торговли hits.eCommerceAction.action_type (более подробно в схеме данных).

SELECT 
CASE  
   WHEN h.page.pagePath LIKE '/google+redesign%' THEN 'Catalog'
   WHEN h.page.pagePath LIKE '/basket%' THEN 'Basket' 
   WHEN h.page.pagePath LIKE '/payment%' THEN 'Payment'
   WHEN h.page.pagePath LIKE '/ordercompleted%' THEN 'OrderCompleted'
   ELSE 'Other' 
END AS funnelStep
, COUNT (DISTINCT fullVisitorId) AS countUsers
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`, UNNEST(hits) AS h
WHERE h.type="PAGE"
GROUP BY funnelStep
ORDER BY countUsers DESC

Уже похоже на воронку:

К шагу воронки «Other» относятся все остальные страницы, не попавшие в заданные условия (главная, сервисные, авторизация, регистрация и прочее), избавимся от него позже.

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

Загружаем данные в Google BigQuery

Представим, что у нас есть CRM, данные из которой мы хотим соединить с данными в BigQuery. Чтобы их загрузить, будем использовать Google Sheets.

1. Заходим в BigQuery.
2. Создаем новый датасет.

   

3. Придумываем название и нажимаем «Ок».

   

4. Теперь добавим в датасет таблицу с данными из CRM (ссылка на файл с тестовыми данными).

   

5. Теперь нужно описать структуру таблицы, то есть ввести название и типы данных для каждого поля.

   

6. Готово! Теперь данные из нашей «CRM» можно использовать для JOIN.

Соединяем данные нескольких таблиц

Чтобы достроить нашу воронку до конца, необходимо соединить данные из таблиц ga_sessions и delivery_data. А для этого нужно использовать оператор JOIN, который соединяет данные из разных таблиц по какому-либо единому для этих таблиц идентификатору.

Чтобы связать две таблицы в предложении FROM нужно написать:

table_1 LEFT JOIN table_2 ON table_1.id = table_2.id

Виды оператора JOIN:

1. INNER JOIN — при помощи этого соединения, вы получите записи присутствующие в обеих таблицах.
2. OUTER JOIN — соединение,  в результат которого входят все записи либо одной, либо обеих таблиц:
   • LEFT OUTER JOIN — возвращает все записи из таблицы слева и соединяет их со связанными записями из правой таблицы (именно такое соединение мы используем).
   • RIGHT OUTER JOIN — тоже самое, что и предыдущее соединение, только все записи возвращаются из присоединяемой таблицы.
   • FULL OUTER JOIN — возвращает все записи обеих таблиц, там где нет пересечений возвращается NULL.
3. CROSS JOIN — перекрестное соединение при котором каждая запись одной таблицы соединяется с каждой записью второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц.

Визуально это выглядит так:

Подробнее читайте в документации.

Теперь можем смело добавить в нашу воронку статусы заказов.

SELECT 
CASE  
   WHEN h.page.pagePath LIKE '/google+redesign%' THEN 'Catalog'
   WHEN h.page.pagePath LIKE '/basket%' THEN 'Basket' 
   WHEN h.page.pagePath LIKE '/payment%' THEN 'Payment'
   WHEN h.page.pagePath LIKE '/ordercompleted%' THEN 'OrderCompleted'
   WHEN deliveryStatus = 'inTransit' THEN 'InTransit'
   WHEN deliveryStatus = 'delivered' THEN 'Delivered'
   ELSE 'Other' 
END AS funnelStep
, COUNT (DISTINCT `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`.fullVisitorId) AS countUsers
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`, UNNEST(hits) AS h
LEFT JOIN `alert-snowfall-167320.crm_data.delivery_data` ON `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`.fullVisitorId = `alert-snowfall-167320.crm_data.delivery_data`.fullVisitorId
WHERE h.type="PAGE"
GROUP BY funnelStep
ORDER BY countUsers DESC

Получилось:

Осталось только немного оптимизировать запрос, так как в текущем виде он работает достаточно медленно. Для этого разобьем наш сложный запрос на более простые подзапросы, а также воспользуемся оператором WITH, который позволяет именовать подзапросы (подробнее у Севы и в доке).

WITH table_1 AS (
SELECT 
  h.page.pagePath AS pageType
, fullVisitorId AS id
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170101`, UNNEST(hits) AS h
WHERE h.type="PAGE"
),
table_2 AS (
SELECT 
  deliveryStatus
, fullVisitorId as id
FROM `alert-snowfall-167320.crm_data.delivery_data`
),
table_join AS (
SELECT 
CASE  
   WHEN pageType LIKE '/google+redesign%' THEN 'Catalog'
   WHEN pageType LIKE '/basket%' THEN 'Basket' 
   WHEN pageType LIKE '/payment%' THEN 'Payment'
   WHEN pageType LIKE '/ordercompleted%' THEN 'OrderCompleted'
   WHEN deliveryStatus = 'inTransit' THEN 'InTransit'
   WHEN deliveryStatus = 'delivered' THEN 'Delivered'
   ELSE 'Other' 
END AS funnelStep
, COUNT (DISTINCT table_1.id) AS countUsers
FROM table_1
LEFT JOIN table_2 ON table_1.id = table_2.id
GROUP BY funnelStep
ORDER BY countUsers DESC
)
SELECT *
FROM table_join
WHERE funnelStep <> 'Other'

Окончательная воронка:

Отлично, а что дальше?

Моей задачей было познакомить вас с основными операторами языка SQL и показать как работает BigQuery (надеюсь справился).

Дальше вы можете попробовать строить свои воронки и отчеты, детализировать пути пользователей по сайту, делать аналитику по конкретному пользователю или визуализировать все это в Data Studio. А самое главное попытаться применить эти знания на практике. Успехов!

Полезные ссылки:

• Осваиваем SQL на примере данных интернет-магазина Google. Ч.1
• Как визуализировать воронку продаж в Google Data Studio?
• 0x0b приемов работы с BigQuery на Standard SQL
• Синтаксис запросов для Standard SQL
• Интерактивный учебник по SQL
• Понимание SQL
• Разговорный SQL. Практический курс

• Об авторе
• Свежие записи

Роман Романчук

Эксперт по маркетинговой и продуктовой аналитике, ex-директор по аналитике Сравни.

• Как правильно организовать работу с гипотезами? - 21.11.2023
• Кейс: как построить отдел аналитики в большой компании? - 06.05.2022
• Учимся применять оконные функции - 29.09.2020