В данной статье будет рассмотрен процесс создания сегментов пользователей на основе данных в SQL-таблицах. За основу взята CMS 1С-Битрикс. Нижеприведенные запросы к БД позволяют получить всю необходимую информацию о том или ином пользователе, полученные данные можно использовать, например, при создании сегментов email-рассылок. Собственно, идея написания данной статьи и возникла после потребности автора в сегментации покупательской базы для рассылки. Статья поделена на два блока: Базовая сегментация (По сумме заказов) и расширенная (В рамках которой пользователь относится к той или иной группе на основе содержимого заказа).
Что нужно для старта?
Для того, чтобы начать потребуется 2 вещи:
- Доступ к Базе Данных, чтобы можно было писать SQL-запросы на выборку (Достаточно админских прав в Битриксе)
- Понимание того, по какому принципу будем сегментировать и какие данные хотим получить.
Блок 1. Базовая сегментация.
В данном блоке предполагается разделение покупателей на 4 группы, на основе сделанных заказов. То есть будет следующий список групп:
- Экономные покупатели (Сумма покупок до 10 000 рублей)
- «Средний класс» (Сумма покупок от 10 до 150 тысяч)
- Крупные покупатели (Сумма покупок от 150 до 500 тысяч)
- «Бояре» (сумма покупок от 500 тысяч и выше)
Итого потребуется четыре сходных запроса. Каждый запрос будет выводить на экран: ID покупателя (Опционально), сумму его заказов, Имя, Фамилию, email. Ограничения для каждого запроса это: Исключаются отмененные заказы и ограничение по сумме (для выборки только тех покупателей, которые попадают в одну из вышеперечисленных групп).
Запрос к БД можно отправить через консоль битрикса по адресу: Настройки > Инструменты > SQL запрос.
Запрос на выборку будет выглядеть следующим образом:
SELECT USER_ID, SUM(PRICE), NAME, LAST_NAME, EMAIL FROM b_sale_order, b_user WHERE USER_ID = b_user.ID AND PRICE < 10000 AND CANCELED = 'N' GROUP BY USER_ID ORDER BY PRICE ;
И, само собой, различаться для каждой группы запросы будут только ограничением по цене:
PRICE < 10000 //Первая группа PRICE BETWEEN 10000 AND 149999 //Вторая группа PRICE BETWEEN 150000 AND 500000 //Третья группа PRICE > 500000 //Четвертая группа
Также стоит обратить внимание на то, что в запросе стоит ограничение на выборку из заказов (CANCELED = ‘N’), то есть в итоговые результаты не включаются заказы, которые были отменены.
Из SQL-консоли битрикса результаты можно скопировать только одним образом: Выделить результаты мышкой и через ctrl+c скопировать в excel. Если требуется сразу генерировать файл с результатами запроса, то выходом может стать написание небольшого php-скрипта на основе данного запроса к БД.
В целом, работы по созданию базовых сегментов можно считать завершенными. Можно переходить ко второму блоку.
Блок 2. Создание расширенных сегментов
В данном блоке мы хотим получить контактные данные покупателей посудомоечных машин, которые:
- Не отменили заказ
- Оформили заказ в 2017 году (В качестве примера)
Подобная сегментация может понадобится при организации «тематической» рассылки пользователям, которые покупали товар определенной категории. В данном случае, предполагается организовать рассылку с целью предложения покупателю таблеток для посудомойки по промо-цене.
Итак, приступим. Понятно, что предыдущий запрос для выборки подобных данных не подходит. В данном случае запрос будет выглядеть следующим образом (В данном случае рассматриваем самый простой случай, когда в таблице с содержимым заказа хранится url товара и на сайте настроены ЧПУ, то есть можно понять по url к какой категории относится тот или иной товар из заказа покупателя. В противном случае, потребуется включать в запрос товарные таблицы, в которых хранится привязка товара к категории.):
SELECT s2.NAME, s2.LAST_NAME, s2.EMAIL FROM b_sale_order AS s1, b_user AS s2, b_sale_basket AS s3 WHERE s1.USER_ID = s2.ID AND s3.ORDER_ID = s1.ID AND s1.CANCELED = 'N' AND s3.DETAIL_PAGE_URL LIKE '%dishwashers%' AND s3.DATE_UPDATE LIKE '%2017%' ;
Также сохраняется ограничение на отмененные заказы (они не попадают в выборку). Таким образом можно получить данные покупателей по любой интересующей группе.
Какое заключение можно сделать из рассмотренной выше информации? Во-первых, не существует никакой проблемы с построением сегментов на основе извлечения информации из БД сайта. В зависимости от CMS сам процесс будет различаться только запросом. Во-вторых, понятно, что вышеописанный процесс может показаться очень сложным и непонятным человеку, не обладающим минимальными познаниями в программировании или теории баз данных. Поэтому остается открытым вопрос о разработке некого модуля (если речь идет о конкретной CMS) или сервиса, который проводил бы подобную сегментацию в полуавтоматическом режиме, используя веб-формы. И, напоследок, по мнению автора, выше был описан инструмент, с помощью которого можно значительно оптимизировать стратегию email-маркетинга для магазинов. Да, нельзя отрицать, что механизм описан в очень сыром виде и не учитывает ряд параметров (Например, в запросах не указано, было ли получено согласие пользователя на получение новостей с сайта.).