Сегментация покупателей из БД Битрикс (На основе SQL-запросов)

В данной статье будет рассмотрен процесс создания сегментов пользователей на основе данных в SQL-таблицах. За основу взята CMS 1С-Битрикс. Нижеприведенные запросы к БД позволяют получить всю необходимую информацию о том или ином пользователе, полученные данные можно использовать, например, при создании сегментов email-рассылок. Собственно, идея написания данной статьи и возникла после потребности автора в сегментации покупательской базы для рассылки. Статья поделена на два блока: Базовая сегментация (По сумме заказов) и расширенная (В рамках которой пользователь относится к той или иной группе на основе содержимого заказа).

Что нужно для старта?

Для того, чтобы начать потребуется 2 вещи:

  1. Доступ к Базе Данных, чтобы можно было писать SQL-запросы на выборку (Достаточно админских прав в Битриксе)
  2. Понимание того, по какому принципу будем сегментировать и какие данные хотим получить.

Блок 1. Базовая сегментация.

В данном блоке предполагается разделение покупателей на 4 группы, на основе сделанных заказов. То есть будет следующий список групп:

  1. Экономные покупатели (Сумма покупок до 10 000 рублей)
  2. «Средний класс» (Сумма покупок от 10 до 150 тысяч)
  3. Крупные покупатели (Сумма покупок от 150 до 500 тысяч)
  4. «Бояре» (сумма покупок от 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-маркетинга для магазинов. Да, нельзя отрицать, что механизм описан в очень сыром виде и не учитывает ряд параметров (Например, в запросах не указано, было ли получено согласие пользователя на получение новостей с сайта.).