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