В анализе продаж и CRM-системах часто возникает задача сегментации клиентской базы. Нам необходимо классифицировать покупателей по их активности за определенный период (например, за год) по следующим критериям:
В этой статье мы подробно разберем, как правильно составить такой запрос в системе «1С:Предприятие», исправим типичные логические ошибки, с которыми сталкиваются разработчики, и оптимизируем код под стандарты производительности 1С.
Проанализируем сначала архитектурные ошибки, которые часто допускаются при проектировании подобных отчетов, а затем пошагово разработаем надежное решение.
Прежде чем перейти к написанию кода, определим несколько важных правил оптимизации СУБД в 1С:
Документ.РасходнаяНакладная заставляет систему сканировать тяжелые таблицы со множеством реквизитов и табличных частей. Для анализа продаж всегда следует использовать виртуальные таблицы оборотов регистров накопления. В типовых конфигурациях это может быть РегистрНакопления.Продажи.Обороты или аналогичный регистр выручки. Данные в них уже агрегированы и проиндексированы.РасходнаяНакладная.Покупатель.Телефон) порождает скрытые соединения таблиц на уровне СУБД. Это критически снижает скорость работы запроса. Мы должны группировать данные по ссылке на контрагента, а дополнительные реквизиты (телефон, адрес) получать только на самом финальном этапе выборки.ПОМЕСТИТЬ) с обязательным созданием индексов по полям связи — это стандарт разработки «1С», обеспечивающий стабильную скорость работы на больших базах данных.Перейдем к реализации каждого условия.
Для начала соберем данные о продажах за анализируемый период (например, текущий год) и поместим их во временную таблицу. Сгруппируем продажи по покупателям и датам (с точностью до дня или месяца, в зависимости от бизнес-логики). Для наших целей достаточно точности до дня.
За основу возьмем виртуальную таблицу оборотов регистра Продажи.Обороты. Сформируем первую временную таблицу:
ВЫБРАТЬ
ПродажиОбороты.Контрагент КАК Контрагент,
ПродажиОбороты.Период КАК ДатаПокупки,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ) КАК МесяцПокупки
ПОМЕСТИТЬ ВТ_ПродажиЗаПериод
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ИНДЕКСИРОВАТЬ ПО
Контрагент,
ДатаПокупки
;
Мы получили исходный массив данных. Обратите внимание, что мы использовали индексацию по полям Контрагент и ДатаПокупки. Это ускорит все последующие соединения.
Рассмотрим логическую ловушку: если мы просто выберем клиентов, у которых первая покупка в таблице ВТ_ПродажиЗаПериод лежит в пределах последнего месяца, мы совершим ошибку. Этот клиент мог активно покупать товары в прошлые годы. Чтобы клиент считался действительно «новым», у него вообще не должно быть покупок за всю предыдущую историю учета.
Для реализации этого условия мы выполним левое соединение наших покупателей с таблицей продаж за весь предшествующий период:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПродажиТекущие.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_НовыеКлиенты
ИЗ
ВТ_ПродажиЗаПериод КАК ПродажиТекущие
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(, &НачалоПериода, День, ) КАК ПродажиПрошлое
ПО ПродажиТекущие.Контрагент = ПродажиПрошлое.Контрагент
ГДЕ
ПродажиТекущие.ДатаПокупки МЕЖДУ &НачалоНовогоПериода И &КонецПериода
И ПродажиПрошлое.Контрагент ЕСТЬ NULL
;
Параметр &НачалоНовогоПериода определяет границу, начиная с которой клиент считается новым (например, начало текущего месяца или квартала). Если запись в прошлых периодах отсутствует (ПродажиПрошлое.Контрагент ЕСТЬ NULL), клиент признается новым.
По условию задачи, «ушедшие» — это клиенты, последняя покупка которых была совершена более полугода назад (относительно конечной даты анализа). Здесь нам нужно найти максимальную дату покупки для каждого клиента и проверить, превышает ли разница между ней и концом периода 6 месяцев.
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент,
МАКСИМУМ(Продажи.ДатаПокупки) КАК ДатаПоследнейПокупки
ПОМЕСТИТЬ ВТ_ПоследниеПокупки
ИЗ
ВТ_ПродажиЗаПериод КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПоследниеПокупки.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_УшедшиеКлиенты
ИЗ
ВТ_ПоследниеПокупки КАК ПоследниеПокупки
ГДЕ
РАЗНОСТЬДАТ(ПоследниеПокупки.ДатаПоследнейПокупки, &КонецПериода, МЕСЯЦ) >= 6
;
Использование функции РАЗНОСТЬДАТ позволяет четко определить временной интервал в месяцах между последним визитом покупателя и текущей точкой отсчета.
Это наиболее сложная алгоритмическая часть. Нам нужно зафиксировать факт того, что между любыми двумя последовательными покупками клиента прошел интервал более 6 месяцев. Если мы просто соединим таблицу продаж саму с собой по условию разности дат, мы сопоставим, например, первую покупку в январе и покупку в сентябре у абсолютно лояльного клиента, который покупал товары каждый месяц. Наш запрос ошибочно посчитает его «вернувшимся».
Чтобы решить эту задачу правильно без оконных функций SQL, применим классический паттерн поиска следующей даты покупки:
ПродажиСлед.ДатаПокупки > ПродажиТек.ДатаПокупки.Реализуем этот алгоритм в коде:
ВЫБРАТЬ
Т1.Контрагент КАК Контрагент,
Т1.ДатаПокупки КАК ДатаТекущая,
МИНИМУМ(Т2.ДатаПокупки) КАК ДатаСледующая
ПОМЕСТИТЬ ВТ_ПоследовательныеПродажи
ИЗ
ВТ_ПродажиЗаПериод КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПродажиЗаПериод КАК Т2
ПО Т1.Контрагент = Т2.Контрагент
И Т2.ДатаПокупки > Т1.ДатаПокупки
СГРУППИРОВАТЬ ПО
Т1.Контрагент,
Т1.ДатаПокупки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПоследовательныеПродажи.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_ВернувшиесяКлиенты
ИЗ
ВТ_ПоследовательныеПродажи КАК ПоследовательныеПродажи
ГДЕ
РАЗНОСТЬДАТ(ПоследовательныеПродажи.ДатаТекущая, ПоследовательныеПродажи.ДатаСледующая, МЕСЯЦ) >= 6
;
Этот изящный метод гарантирует, что мы анализируем только реальные «разрывы» в покупках конкретного контрагента, исключая ложные срабатывания.
Постоянным клиентом считается тот, у которого есть хотя бы одна продажа в каждом месяце анализируемого периода. Если период может изменяться (например, отчет формируется не за календарный год, а за 9 месяцев или за полтора года), жестко зашивать число «12» в запрос нельзя.
Мы передадим количество месяцев в периоде как параметр &КоличествоМесяцевПериода, рассчитав его предварительно на встроенном языке:
// В модуле отчета перед выполнением запроса:
КоличествоМесяцев = РазностьДат(НачалоПериода, КонецПериода, "МЕСЯЦ") + 1;
Запрос.УстановитьПараметр("КоличествоМесяцевПериода", КоличествоМесяцев);
Теперь в самом запросе мы можем выполнить группировку по контрагентам и наложить условие в секции ИМЕЮЩИЕ:
ВЫБРАТЬ
Продажи.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_ПостоянныеКлиенты
ИЗ
ВТ_ПродажиЗаПериод КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Контрагент
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Продажи.МесяцПокупки) = &КоличествоМесяцевПериода
;
Теперь у нас есть четыре временные таблицы, содержащие списки контрагентов по каждой категории. Объединим эти данные в одну итоговую выборку с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, сопоставляя каждому клиенту его текстовый статус. На этом же этапе мы безопасно обратимся к справочнику контрагентов, чтобы получить контактные данные:
ВЫБРАТЬ
"Новый" КАК Статус,
Новые.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_ИтоговыеКлассы
ИЗ
ВТ_НовыеКлиенты КАК Новые
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Ушедший",
Ушедшие.Контрагент
ИЗ
ВТ_УшедшиеКлиенты КАК Ушедшие
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Вернувшийся",
Вернувшиеся.Контрагент
ИЗ
ВТ_ВернувшиесяКлиенты КАК Вернувшиеся
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Постоянный",
Постоянные.Контрагент
ИЗ
ВТ_ПостоянныеКлиенты КАК Постоянные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ИтоговыеКлассы.Статус КАК СтатусПокупателя,
ИтоговыеКлассы.Контрагент КАК Покупатель,
ИтоговыеКлассы.Контрагент.Телефон КАК ТелефонПокупателя,
ИтоговыеКлассы.Контрагент.Код КАК КодПокупателя
ИЗ
ВТ_ИтоговыеКлассы КАК ИтоговыеКлассы
УПОРЯДОЧИТЬ ПО
СтатусПокупателя,
Покупатель
;
Мы спроектировали гибкий, масштабируемый и высокопроизводительный запрос для сегментации клиентов. Мы применили технологию временных таблиц, ушли от ресурсоемких обращений к физическим таблицам документов в пользу виртуальных таблиц оборотов регистров, корректно решили задачу вычисления интервалов между последовательными датами и обеспечили динамический расчет стабильности покупок.
Данный подход может быть легко внедрен в любой отчет на СКД (Системе компоновки данных) или использован при разработке аналитических панелей (дашбордов) в конфигурациях на платформе «1С:Предприятие 8».