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

На заре своей веб-аналитической молодости, работая над составлением отчетов, я использовал только Excel.

То есть, например, бизнес говорит:

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

Это сейчас я знаю Data Studio, практикую Power BI, SQL и еще много чего. А раньше, что я делал в таком случае? Открывал Google Analytics, создавал кастомный отчет с необходимыми параметрами и показателями и выгружал его в Excel. Далее шел к аналитику (не веб, а обычному, в банках такие есть) и просил выгрузить из базы все в тот же Excel, данные по клиентам посетившим офис, а после сводил две таблицы в единый отчет.

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

А как надо, спросите вы? «Изучайте SQL» — отвечу я!

Google BigQuery

Осваивать SQL мы будем на примере реальных данных электронной торговли магазина Google Merchandise Store, который продает товары под торговой маркой Google. Публичный датасет совсем недавно был выложен в BigQuery — облачную базу данных, которая позволяет обрабатывать терабайты данных за считанные секунды.

Чтобы получить доступ к набору данных:

1. Перейдите на страницу http://bigquery.cloud.google.com.
2. Если вы новичок в BigQuery или у вас еще нет проекта, вам нужно будет создать проект.
3. Включить биллинг для проекта (нужно будет создать платежный аккаунт и привязать его к проекту, а также указать данные кредитной карты). Но не беспокойтесь, во-первых, Google предоставляет достаточно большой бесплатный пробный период, во-вторых, деньги без вашего ведома он снимать не будет.
4. После того как проект создан, можно переходить непосредственно к датасету Google Merchandise Store.

Набор данных содержит информацию о трафике, взаимодействии с контентом и транзакциях за период с 1 августа 2016 года по 1 августа 2017 года. Для каждого дня в наборе данных создается по одной таблице с названием в формате «ga_sessions_ГГГГММДД», а каждая строка таблицы содержит данные об одном сеансе (схема данных в помощь).

Пишем SQL-запросы

SQL или structured query language — это язык структурированных запросов применяемый для создания, модификации и управления данными в реляционной базе данных.

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

Оператор SELECT состоит из нескольких предложений:

1. SELECT — определяет список возвращаемых столбцов (как существующих, так и вычисляемых).
2. FROM — указывает откуда (из какой таблицы и какого датасета) брать данные.

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

Давайте потренируемся и попробуем получить общее количество просмотров страниц за 2017-01-01. Смотрим в схему данных, находим поле totals.pageviews и пишем запрос:

SELECT totals.pageviews
FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101]

И получаем вот такой результат:

Но что это? Вы ведь ожидали увидеть одну цифру с общим количеством просмотров страниц, а не 1528 строк. А все дело в том, что любая БД работает как КЭП (капитан очевидность) — что попросили, то и получили 🙂

В  запросе вы сказали: «Выведи мне поле всего просмотров страниц из таблицы за 2017-01-01». На что в ответ и получили все строки таблицы, содержащие данные о количестве просмотров страниц.

А нужно было сформулировать запрос так: «Выведи мне СУММУ поля всего просмотров страниц из таблицы за 2017-01-01».

Пробуем:

SELECT SUM(totals.pageviews) AS TotalPageviews
FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101]

То что нужно:

Также в запросе, помимо агрегируещей функции SUM, вы могли заметить параметр AS, который отвечает за пользовательское название столбца (если его не указать, то у столбца не будет имени, точнее будет примерно такое «f0_»).

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

Параметры оператора SELECT

1. WHERE — фильтрует данные по заданным вами условиям.
2. GROUP BY — группирует строки по результатам агрегатных функций (MAX, SUM, AVG, …).
3. ORDER BY — сортирует значения по одному или более столбцам. Сортировка может производиться как по возрастанию, так и по убыванию значений. Параметр ASC (по умолчанию) устанавливает порядок сортировки по возрастанию, DESC по убыванию.

Дополняем наш запрос:

SELECT 
  date
, SUM(totals.pageviews) AS TotalPageviews
FROM [bigquery-public-data:google_analytics_sample.ga_sessions_20170101],[bigquery-public-data:google_analytics_sample.ga_sessions_20170102],[bigquery-public-data:google_analytics_sample.ga_sessions_20170103]
GROUP BY date
ORDER BY date ASC

И получаем просмотры страниц по дням, отсортированные по возрастанию:

Лирическое отступление

BigQuery поддерживает два SQL-диалекта — стандартный (standard SQL) и устаревший (legacy SQL). Оба работают, но между ними есть некоторые отличия. Поясню на примере выбора диапазона дат.

Legacy SQL

Как вы могли заметить, чтобы получить данные за период с 2017-01-01 по 2017-01-03, мне пришлось перечислить в предложении FROM три таблицы. Неужели, если потребуется отчет за неделю или месяц, придется перечислять все таблицы с датами? Совсем нет, для таких случаев в BigQuery существует подстановочная функция TABLE_DATE_RANGE, которая запрашивает несколько ежедневных таблиц, которые охватывают диапазон дат.

Функция TABLE_DATE_RANGE имеет следующий синтаксис:

TABLE_DATE_RANGE(prefix, timestamp1, timestamp1)

Где:

prefix — префикс (имя) таблиц без даты

timestamp1 — начальная дата

timestamp1 — конечная дата.

То есть имена таблиц должны иметь следующий формат: <prefix><day> где <day>находится формате YYYYMMDD.

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

SELECT 
  date
, SUM(totals.pageviews) AS TotalPageviews
FROM (TABLE_DATE_RANGE([bigquery-public-data:google_analytics_sample.ga_sessions_], TIMESTAMP('20170101'), TIMESTAMP('20170107')))
GROUP BY date
ORDER BY date ASC

Просмотры страниц за неделю:

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

Например, следующее предложение вернет вам дату которая была неделю назад от текущей даты: DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY').

Standard SQL

Давайте включим стандартный диалект. Для этого нужно нажать на кнопку «Show Options» и убрать галочку «Use Legacy SQL».

И попробуем выбрать тот же диапазон дат.

SELECT 
  date
, SUM(totals.pageviews) AS TotalPageviews
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170107'
GROUP BY date
ORDER BY date ASC

В данном случае, вместо функции TABLE_DATE_RANGE в FROM используется шаблон названия таблицы, а в WHERE фильтр _TABLE_SUFFIX, который выбирает данные находящиеся в заданном диапазоне дат (BETWEEN).

Плюс обратите внимание на немного изменившуюся пунктуацию в предложении FROM.

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

Результат тот же:

Подробнее о различиях между диалектами читайте в справке Google (на вражеском языке).

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

Собираем отчет

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

SELECT 
 date
, SUM(totals.pageviews) AS Pageviews
, ROUND((SUM(totals.transactions)/SUM(totals.pageviews))*100,2) AS CR
, SUM(totals.transactions) AS Transactions
, ROUND(AVG(totals.totalTransactionRevenue)/1000000,2) AS AverageCheck
, ROUND(SUM(totals.totalTransactionRevenue)/1000000,2) AS Revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170107'
GROUP BY date
ORDER BY date ASC

Из нового:

ROUND — округляет число до стольких знаков после запятой, сколько указано во втором аргументе функции (в нашем случае до 2).

AVG — выводит среднее значение диапазона чисел.

Уже похоже на отчет:

Что дальше с этим делать? Пробуйте построить более сложные отчеты, скачивайте в CSV или экспортируйте в Google Sheets и Data Studio для анализа.

А я пока буду готовить следующую статью, в которой расскажу о более продвинутых возможностях SQL.

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

• Осваиваем SQL на примере данных интернет-магазина Google. Ч.2
• Справочник по BigQuery
• Устаревшие (но еще используемые) функции и операторы SQL
• Стандартные функции и операторы SQL
• 0x0b приемов работы с BigQuery на Standard SQL

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

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

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

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