Часть 1. Сегменты, фильтры и немного высшей математики

Привет! Меня зовут Анна Амирова, я из digital-интегратора БизнесПрофи — мы внедряем и сопровождаем Битрикс24, а еще разработали на базе CRM Б24 полноценную CDP (Customer Data Platform) для работы с большими клиентскими базами, содержащими миллионы записей.

Решение создавалось последовательно, исходя из запросов клиентов на решение различных задач от A/B тестирования до аналитики. Основной целью было избавить пользователей от зоопарка систем, который обычно используется для рассылок — рассылки через почтовые сервисы, через Whatsapp, Телеграм, подключение ботов и т.д. Часто случалось, что только маркетолог держал у себя в голове количество реальных касаний с клиентом, а работа по сегментации базы и управлению рассылками велась без всякой системы. В этом случае есть риск перегреть базу контактов, при том, что ценность ее очень велика.

В первой части статьи расскажем о подходах к сегментированию клиентов и их практической реализации в связке с инструментами Битрикс24.

Модуль CDP является неотъемлемой частью Битрикс24 и позволяет использовать продукт комплексно, как связку для взаимодействия отдела продаж и отдела маркетинга. В некоторых проектах используется именно, как инструмент маркетолога, если компания не использует в операционной деятельности CRM, ввиду организационных особенностей. Мы настраиваем синхронизацию с внешними базами данных, 1С и другими продуктами.

CDP позволяет сегментировать клиентов по всевозможным доступным критериям, например, выделить покупателей, которые в последний раз делали заказ в определенной категории товаров более месяца назад. Также система умеет создавать аналитические сегменты, сегменты по k-средним, проводит RFM-анализ. Система позволяет строить сегменты как по компаниям, так и по контактам.

После настройки можно использовать сегменты как для анализа клиентской базы, так и для полноценных рассылок по самым разным каналам: email, СМС, Whatsapp, Телеграм. В том числе комплексные рассылки по цепочкам касаний, для того, чтобы последовательно «догревать» клиентов с учетом различных условий. Например, на определенном этапе можно разбивать сегмент на две части — те, кто открыл предыдущее сообщение, и те, кто не открыл — и создавать новые цепочки сообщений для них.

Пользовательские сегменты по параметрам сделок, контактов, связанных смарт-процессов

Для того, чтобы добиться максимальной детализации и точно попадать в потребности клиентов, к данным можно применять десятки фильтров — одновременно или последовательно.

Например, нужно выбрать тех, кто приобретал определенный товар в нужный период, а затем к выбранному сегменту применить еще один множественный фильтр — сузить сегмент и определить тех, кто в более поздний период времени не делал похожих заказов. Каждый наверняка получал от интернет-магазинов напоминание о том, что пора бы докупить средство для мытья посуды или шампунь, пополнить запасы кошачьего корма и т.д. Но когда ты только что совершил эту покупку в этом же магазине, сообщение раздражает. А если при рассылке выбирать именно тех, кто еще не купил товары повторно, шансы сохранить лояльных покупателей будут выше.

Настройка фильтра дружелюбная и настраивается под задачи пользователя.

В качестве фильтров могут выступать связи с другими сущностями, например, смарт-процессами, компаниями, товарами, а также все связи, которые относятся к сделке, которая создается через контакт. Это позволяет фильтровать данные по самым разным условиям, вне зависимости от того, как они хранятся в CRM. Например, услуга может храниться как товар или как элемент смарт-процессов, у нее могут быть свои связанные сущности, например, цена или динамические реквизиты, которые невозможно хранить вместе с ней. Но это не помешает создавать сегменты, например, выбирая пользователей, которые дважды за определенный период приобретали эту услугу.

Таким образом можно получать очень сложные комбинации фильтров, выбирая данные по полям смарт-процесса, который связан с контактом или со сделкой, которая связана с контактом. При необходимости фильтры имеют возможность программного расширения, без потери обновлений модуля. Например, если слишком сложная связь в хранении данных, то можно доработать и добавить, не меняя код файлов модуля.

Для формирования сложных запросов с помощью фильтров использовали стандартный ORM Битрикс

# Логика работы фильтров сегментов

Для формирования сложных запросов с помощью фильтров использовали стандартный ORM битрикс.

Так как сегменты могут состоять из контактов или компаний, основой запроса будут датаменеджеры `\Bitrix\Crm\ContactTable` и `\Bitrix\Crm\CompanyTable` соответственно. К начальному датаменеджеру добавляем связи со всеми сущностями 1го уровня (сущности которые имеют связь непосредственно с контактом/компанией), а так же связи второго уровня сделки (сущности которые имеют связь со сделкой, за исключением контактов/компаний).

Связи между сущностями определяем с помощью `Bitrix\Crm\Relation\RelationManager`. Для удобства определили хелпер для получения связей

```php
namespace Bizprofi\Cdp\Helpers;

use Bitrix\Crm\Relation\RelationManager;
use Bitrix\Crm\Service\Container;
use Bitrix\Main\Loader;
use Bitrix\TasksMobile\Exception\ModuleNotFoundException;

class CrmRelationHelper
{
    public const SUPPORTED_ENTITY_IDS = [
        \CCrmOwnerType::Contact,
        \CCrmOwnerType::Company,
        \CCrmOwnerType::Deal,
        \CCrmOwnerType::Lead,
        \CCrmOwnerType::Quote,
    ];

    public readonly RelationManager $manager;

    public function __construct()
    {
        if (!Loader::includeModule('crm')) {
            throw new ModuleNotFoundException('Module "crm" not included');
        }

        $this->manager = Container::getInstance()->getRelationManager();
    }

    public function getRelationEntityIds(int $entityTypeId, bool $onlySupported = true): array
    {
        $entityIds = [];
        foreach ($this->manager->getParentRelations($entityTypeId) as $relation) {
            $parentEntityTypeId = $relation->getParentEntityTypeId();

            if (
                $onlySupported
                && !\CCrmOwnerType::isPossibleDynamicTypeId($parentEntityTypeId)
                && !in_array($parentEntityTypeId, static::SUPPORTED_ENTITY_IDS, true)
            ) {
                continue;
            }

            $entityIds[] = (int) $parentEntityTypeId;
        }

        foreach ($this->manager->getChildRelations($entityTypeId) as $relation) {
            $childEntityTypeId = $relation->getChildEntityTypeId();

            if (
                $onlySupported
                && !\CCrmOwnerType::isPossibleDynamicTypeId($childEntityTypeId)
                && !in_array($childEntityTypeId, static::SUPPORTED_ENTITY_IDS, true)
            ) {
                continue;
            }

            $entityIds[] = (int) $childEntityTypeId;
        }

        return array_filter(
            array_unique(
                $entityIds
            )
        );
    }
}

// Получение сущностей связанных с контактом
var_dump(
    (new \Bizprofi\Cdp\Helpers\CrmRelationHelper())->getRelationEntityIds(\CCrmOwnerType::Contact)
);

// Получение сущностей связанных с сделкой
var_dump(
    (new \Bizprofi\Cdp\Helpers\CrmRelationHelper())->getRelationEntityIds(\CCrmOwnerType::Deal)
);
```

После определения сущностей которые имеют связь с контактом и сделкой, добавляем в `\Bitrix\Main\ORM\Entity` датаменеджера, `\Bitrix\Main\ORM\Fields\Relations\Reference` и `\Bitrix\Main\ORM\Fields\ExpressionField` поля для каждой сущности

```php
namespace Bizprofi\Cdp\Helpers;

use Bitrix\Crm\Binding\DealContactTable;
use Bitrix\Crm\Binding\EntityContactTable;
use Bitrix\Crm\Binding\LeadContactTable;
use Bitrix\Crm\Binding\QuoteContactTable;
use Bitrix\Crm\CompanyTable;
use Bitrix\Crm\ContactTable;
use Bitrix\Crm\DealTable;
use Bitrix\Crm\LeadTable;
use Bitrix\Crm\PhaseSemantics;
use Bitrix\Crm\QuoteTable;
use Bitrix\Crm\Relation\EntityRelationTable;
use Bitrix\Crm\Service\Container;
use Bitrix\Main\ORM\Entity;
use Bitrix\Main\ORM\Fields;
use Bitrix\Main\ORM\Query\Join;
use Bitrix\Main\ORM\Query\Query;

class SegmentEntitiesQueryHelper
{
// ... тут есть другая логика хелпера
    protected function addRelationEntitiesRelationsToEntities(Entity $entity): void
    {
        $crmEntityId = match (ltrim($entity->getDataClass(), '\\')) {
            CompanyTable::class => \CCrmOwnerType::Company,
            ContactTable::class => \CCrmOwnerType::Contact,
            default => null,
        };

        if ($crmEntityId === null) {
            return;
        }

        $this->addDealRelationsToEntity($entity);
        $this->addDealExpressionsToEntity($entity);
        $this->addDealRelationEntitiesRelationsToEntities($entity, $crmEntityId);

        $this->addBaseRelationEntitiesRelationsToEntities($entity, $crmEntityId);
    }

    protected function addDealRelationsToEntity(Entity $entity): void
    {
        if (ltrim($entity->getDataClass(), '\\') === ContactTable::class) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    'DEAL_CONTACTS',
                    DealContactTable::class,
                    Join::on('this.ID', 'ref.CONTACT_ID')
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }

        $entity->addField(
            (new Fields\Relations\Reference(
                'DEALS',
                DealTable::class,
                match (ltrim($entity->getDataClass(), '\\')) {
                    ContactTable::class => Join::on('this.DEAL_CONTACTS.DEAL_ID', 'ref.ID'),
                    default => Join::on('this.ID', 'ref.COMPANY_ID'),
                }
            ))->configureJoinType(Join::TYPE_LEFT)
        );

        $entity->addField(
            (new Fields\Relations\Reference(
                'SUCCESS_DEALS',
                DealTable::class,
                (match (ltrim($entity->getDataClass(), '\\')) {
                    ContactTable::class => Join::on('this.DEAL_CONTACTS.DEAL_ID', 'ref.ID'),
                    default => Join::on('this.ID', 'ref.COMPANY_ID'),
                })->where('ref.STAGE_SEMANTIC_ID', PhaseSemantics::SUCCESS)
            ))->configureJoinType(Join::TYPE_LEFT)
        );
    }

    protected function addDealExpressionsToEntity(Entity $entity): void
    {
        $entity->addField(
            Query::expr()->sum('DEALS.OPPORTUNITY_ACCOUNT'),
            'DEALS_SUM_OPPORTUNITY_ACCOUNT'
        );

        $entity->addField(
            Query::expr()->sum('SUCCESS_DEALS.OPPORTUNITY_ACCOUNT'),
            'SUCCESS_DEALS_SUM_OPPORTUNITY_ACCOUNT'
        );

        $entity->addField(
            Query::expr()->countDistinct('DEAL_CONTACTS.DEAL_ID'),
            'DEALS_COUNT'
        );

        $entity->addField(
            Query::expr()->countDistinct('SUCCESS_DEALS.ID'),
            'SUCCESS_DEALS_COUNT'
        );

        $this->addDealExpressionsLastDateToEntity($entity);
    }

    protected function addDealExpressionsLastDateToEntity(Entity $entity): void
    {
        foreach ($this->getDealsDateFieldNames() as $fieldName) {
            $entity->addField(
                Query::expr()->max("DEALS.{$fieldName}"),
                "DEALS_MAX_{$fieldName}"
            );

            $entity->addField(
                Query::expr()->max("SUCCESS_DEALS.{$fieldName}"),
                "SUCCESS_DEALS_MAX_{$fieldName}"
            );
        }
    }

    protected function getDealsDateFieldNames(): array
    {
        $factory = Container::getInstance()->getFactory(\CCrmOwnerType::Deal);

        $fields = $factory->getFieldsInfoByMap();
        $userFields = $factory->getUserFieldsInfo();

        $fieldNames = [];
        foreach (array_merge($fields, $userFields) as $code => $fieldData) {
            if ($fieldData['TYPE'] === 'date' || $fieldData['TYPE'] === 'datetime') {
                $fieldNames[] = $code;
            }
        }

        return $fieldNames;
    }

    protected function addDealRelationEntitiesRelationsToEntities(Entity $entity, int $crmEntityId): void
    {
        $relationEntityIds = array_diff(
            (new CrmRelationHelper())->getRelationEntityIds(\CCrmOwnerType::Deal),
            [$crmEntityId]
        );

        foreach ($relationEntityIds as $relationEntityId) {
            $crmEntityTypeName = \CCrmOwnerType::ResolveName($relationEntityId);

            switch ($relationEntityId) {
                case \CCrmOwnerType::Contact:
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}_RELATION",
                            DealContactTable::class,
                            Join::on('this.DEALS.ID', 'ref.DEAL_ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );

                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}",
                            ContactTable::class,
                            Join::on("this.DEAL_{$crmEntityTypeName}_RELATION.CONTACT_ID", 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Company:
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}",
                            CompanyTable::class,
                            Join::on('this.DEALS.COMPANY_ID', 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Lead:
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}",
                            LeadTable::class,
                            Join::on('this.DEALS.LEAD_ID', 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Quote:
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}",
                            QuoteTable::class,
                            Join::on('this.DEALS.QUOTE_ID', 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                default:
                    if (
                        !\CCrmOwnerType::isPossibleDynamicTypeId($relationEntityId)
                        || $relationEntityId === \CCrmOwnerType::Undefined
                    ) {
                        break;
                    }

                    if (!$entity->hasField('DEAL_RELATIONS')) {
                        $entity->addField(
                            (new Fields\Relations\Reference(
                                "DEAL_RELATIONS",
                                EntityRelationTable::class,
                                Join::on('this.DEALS.ID', 'ref.SRC_ENTITY_ID')
                                    ->where('ref.SRC_ENTITY_TYPE_ID', \CCrmOwnerType::Deal)
                            ))->configureJoinType(Join::TYPE_LEFT)
                        );
                    }

                    $factory = Container::getInstance()->getFactory($relationEntityId);
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            "DEAL_{$crmEntityTypeName}",
                            $factory->getDataClass(),
                            Join::on('this.DEAL_RELATIONS.DST_ENTITY_ID', 'ref.ID')
                                ->where('this.DEAL_RELATIONS.DST_ENTITY_TYPE_ID', $relationEntityId),
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
            }

            $entity->addField(
                Query::expr()->countDistinct("DEAL_{$crmEntityTypeName}.ID"),
                "DEAL_{$crmEntityTypeName}_COUNT"
            );
        }
    }

    protected function addBaseRelationEntitiesRelationsToEntities(Entity $entity, int $crmEntityId): void
    {
        $relationEntityIds = array_diff(
            (new CrmRelationHelper())->getRelationEntityIds($crmEntityId),
            [\CCrmOwnerType::Deal]
        );

        foreach ($relationEntityIds as $relationEntityId) {
            $crmEntityTypeName = \CCrmOwnerType::ResolveName($relationEntityId);

            switch ($relationEntityId) {
                case \CCrmOwnerType::Company:
                    if (ltrim($entity->getDataClass(), '\\') !== ContactTable::class) {
                        break;
                    }

                    $entity->addField(
                        (new Fields\Relations\Reference(
                            'COMPANIES',
                            CompanyTable::class,
                            Join::on('this.COMPANY_BINDINGS.COMPANY_ID', 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Contact:
                    if (ltrim($entity->getDataClass(), '\\') !== CompanyTable::class) {
                        break;
                    }

                    $entity->addField(
                        (new Fields\Relations\Reference(
                            $crmEntityTypeName,
                            ContactTable::class,
                            Join::on('this.CONTACT_BINDINGS.CONTACT_ID', 'ref.ID')
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Lead:
                    if (ltrim($entity->getDataClass(), '\\') === ContactTable::class) {
                        $entity->addField(
                            (new Fields\Relations\Reference(
                                'LEAD_CONTACTS',
                                LeadContactTable::class,
                                Join::on('this.ID', 'ref.CONTACT_ID')
                            ))->configureJoinType(Join::TYPE_LEFT)
                        );
                    }

                    $entity->addField(
                        (new Fields\Relations\Reference(
                            $crmEntityTypeName,
                            LeadTable::class,
                            match (ltrim($entity->getDataClass(), '\\')) {
                                ContactTable::class => Join::on('this.LEAD_CONTACTS.LEAD_ID', 'ref.ID'),
                                default => Join::on('this.ID', 'ref.COMPANY_ID'),
                            }
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                case \CCrmOwnerType::Quote:
                    if (ltrim($entity->getDataClass(), '\\') === ContactTable::class) {
                        $entity->addField(
                            (new Fields\Relations\Reference(
                                'QUOTE_CONTACTS',
                                QuoteContactTable::class,
                                Join::on('this.ID', 'ref.CONTACT_ID')
                            ))->configureJoinType(Join::TYPE_LEFT)
                        );
                    }

                    $entity->addField(
                        (new Fields\Relations\Reference(
                            $crmEntityTypeName,
                            QuoteTable::class,
                            match (ltrim($entity->getDataClass(), '\\')) {
                                ContactTable::class => Join::on('this.QUOTE_CONTACTS.QUOTE_ID', 'ref.ID'),
                                default => Join::on('this.ID', 'ref.COMPANY_ID'),
                            }
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
                default:
                    if (
                        !\CCrmOwnerType::isPossibleDynamicTypeId($relationEntityId)
                        || $relationEntityId === \CCrmOwnerType::Undefined
                    ) {
                        break;
                    }

                    if (!$entity->hasField('ENTITY_CONTACTS')) {
                        $entity->addField(
                            (new Fields\Relations\Reference(
                                'ENTITY_CONTACTS',
                                EntityContactTable::class,
                                Join::on('this.ID', 'ref.CONTACT_ID')
                            ))->configureJoinType(Join::TYPE_LEFT)
                        );
                    }

                    $factory = Container::getInstance()->getFactory($relationEntityId);
                    $entity->addField(
                        (new Fields\Relations\Reference(
                            $crmEntityTypeName,
                            $factory->getDataClass(),
                            match (ltrim($entity->getDataClass(), '\\')) {
                                CompanyTable::class => Join::on('this.ID', 'ref.COMPANY_ID'),
                                default => Join::on('this.ENTITY_CONTACTS.ENTITY_ID', 'ref.ID')
                                    ->where('this.ENTITY_CONTACTS.ENTITY_TYPE_ID', $relationEntityId),
                            }
                        ))->configureJoinType(Join::TYPE_LEFT)
                    );
                    break;
            }

            $entity->addField(
                Query::expr()->countDistinct(match ($relationEntityId) {
                    \CCrmOwnerType::Company => "COMPANIES.ID",
                    default => "{$crmEntityTypeName}.ID",
                }),
                "{$crmEntityTypeName}_COUNT"
            );
        }
    }
// ... там далее другая логика хелпера
}
```

Отдельно еще добавим поля для поиска по реквизитам, адресу и трекингу

```php
namespace Bizprofi\Cdp\Helpers;

use Bitrix\Crm\AddressTable;
use Bitrix\Crm\CompanyTable;
use Bitrix\Crm\LeadTable;
use Bitrix\Crm\RequisiteTable;
use Bitrix\Crm\Tracking\Internals\TraceChannelTable;
use Bitrix\Crm\Tracking\Internals\TraceEntityTable;
use Bitrix\Crm\Tracking\Internals\TraceTable;
use Bitrix\Main\ORM\Entity;
use Bitrix\Main\ORM\Fields;
use Bitrix\Main\ORM\Query\Join;

class SegmentEntitiesQueryHelper
{
// ... тут есть другая логика хелпера
    protected function addRequisiteRelationsToEntity(Entity $entity): void
    {
        if (! $entity->hasField('RQ')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "RQ",
                    RequisiteTable::class,
                    Join::on("this.ID", 'ref.ENTITY_ID')
                        ->where('ref.ENTITY_TYPE_ID', match (ltrim($entity->getDataClass(), '\\')) {
                            CompanyTable::class => \CCrmOwnerType::Company,
                            default => \CCrmOwnerType::Contact
                        })
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }

        if (! $entity->hasField('RQ_ADDR')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "RQ_ADDR",
                    AddressTable::class,
                    Join::on("this.RQ.ID", 'ref.ENTITY_ID')
                        ->where('ref.ENTITY_TYPE_ID', \CCrmOwnerType::Requisite)
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }

    }

    protected function addTrackingTraceRelationsToEntity(Entity $entity): void
    {
        if (! $entity->hasField('TRACKING_ENTITY')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "TRACKING_ENTITY",
                    TraceEntityTable::class,
                    Join::on("this.ID", 'ref.ENTITY_ID')
                        ->where('ref.ENTITY_TYPE_ID', match (ltrim($entity->getDataClass(), '\\')) {
                            CompanyTable::class => \CCrmOwnerType::Company,
                            default => \CCrmOwnerType::Contact
                        })
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }

        if (! $entity->hasField('TRACKING')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "TRACKING",
                    TraceTable::class,
                    Join::on("this.TRACKING_ENTITY.TRACE_ID", 'ref.ID')
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }

        if (! $entity->hasField('TRACKING_CHANNEL')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "TRACKING_CHANNEL",
                    TraceChannelTable::class,
                    Join::on("this.TRACKING_ENTITY.TRACE_ID", 'ref.ID')
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }
    }

    protected function addAddressToLeadEntity(): void
    {
        $entity = LeadTable::getEntity();
        if (! $entity->hasField('ADDR')) {
            $entity->addField(
                (new Fields\Relations\Reference(
                    "ADDR",
                    AddressTable::class,
                    Join::on("this.ID", 'ref.ENTITY_ID')
                        ->where('ref.ENTITY_TYPE_ID', \CCrmOwnerType::Lead)
                ))->configureJoinType(Join::TYPE_LEFT)
            );
        }
    }
// ... там далее другая логика хелпера
}
```

Так же для возможности расширения функционала фильтра добавили кастомное событие инициализации связей. Через него можно добавить дополнительную связь или виртуальное поле.

```php
/**
 * @var string $entity = contact|company
 */
foreach (GetModuleEvents("bizprofi.cdp", "onGetEntityQuery", true) as $arEvent) {
    try {
        ExecuteModuleEventEx($arEvent, [$entity]);
    } catch (\Exception|\Error $e) {
        //skip event
    }
}
```

Таким образом мы можем значения выбранные в фильтре прокидывать сразу в `\Bitrix\Main\ORM\Query\Query` и ORM дальше сама определит какие JOIN надо будет сделать.

Фильтры в сегментах могут быть множественными и логическими. Каждый отдельный фильтр в итоговом запросе будет выглядеть как подзапрос в основную таблицу `\Bitrix\Crm\ContactTable` или `\Bitrix\Crm\CompanyTable` по полю ID с соответствующими логическоми операторами. Если указан только один фильтр то нет смысла делать его подзапросом, сразу передаем как фильтр в запрос.

```php
namespace Bizprofi\Cdp\Helpers;

use Bitrix\Crm\CompanyTable;
use Bitrix\Crm\ContactTable;
use Bitrix\Main\Loader;
use Bitrix\Main\ORM\Query\Filter\Condition;
use Bitrix\Main\ORM\Query\Filter\ConditionTree;
use Bitrix\Main\ORM\Query\Query;
use Bizprofi\Cdp\DataManagers\EO_Segments;
use Bizprofi\Cdp\DataManagers\SegmentFiltersTable;
use Bizprofi\Cdp\Enums\EntityType;
use Bizprofi\Cdp\Enums\FilterLogic;

class SegmentEntitiesQueryHelper
{
    protected static bool $initAdditionalEntityRelations = false;

    protected ?ConditionTree $ct;

    public function __construct(protected readonly EO_Segments $segment, ?ConditionTree $ct = null)
    {
        if (!Loader::includeModule('crm')) {
            throw new \Exception('Not include module "crm"');
        }
        $this->ct = $ct ?: $this->getSegmentEntitiesConditionTree();
    }

    protected function getSegmentEntitiesConditionTree(): ?ConditionTree
    {
        $segmentFilters = SegmentFiltersTable::query()
            ->addSelect('*')
            ->where('ENTITY', $this->segment->getEntity())
            ->where('SEGMENT_ID', $this->segment->getId())
            ->addOrder('ORDER', 'ASC')
            ->exec()
            ->fetchCollection();

        if ($segmentFilters->count() <= 0) {
            return null;
        }

        if ($segmentFilters->count() === 1) {
            foreach ($segmentFilters as $segmentFilter) {
                return ConditionTreeHelper::arrayToConditionTree($segmentFilter->getFilter());
            }
        }

        $or = (new ConditionTree())->logic(ConditionTree::LOGIC_OR);
        $ct = new ConditionTree();
        foreach ($segmentFilters as $segmentFilter) {
            if (in_array($segmentFilter->getLogic(), [FilterLogic::OR, FilterLogic::NOTOR], true)) {
                $or->where($ct);
                $ct = new ConditionTree();
            }

            $ct->where(
                ConditionTreeHelper::arrayToConditionTree($segmentFilter->getFilter())
                    ->negative(
                        in_array($segmentFilter->getLogic(), [FilterLogic::NOTOR, FilterLogic::NOTAND], true)
                    )
            );
        }

        if ($or->hasConditions() && $ct->hasConditions()) {
            $or->where($ct);
        }

        return $or->hasConditions() ? $or: $ct;
    }

    public function getQuerySelect(): Query
    {
        if (!($query = $this->getEntityQuery())) {
            throw new \Exception('Incorrect entity');
        }

        if (!$this->ct) {
            return $query;
        }

        $this->setConditionsToQuery($query, $this->getModifyMultipleFilterConditions());

        return $query;
    }

    protected function getEntityQuery(): ?Query
    {
        if (!static::$initAdditionalEntityRelations) {
            $this->initAdditionalEntityRelations();

            foreach (GetModuleEvents("bizprofi.cdp", "onGetEntityQuery", true) as $arEvent) {
                try {
                    ExecuteModuleEventEx($arEvent, [$this->segment->getEntity()]);
                } catch (\Exception|\Error $e) {
                    //skip event
                }
            }

            static::$initAdditionalEntityRelations = true;
        }

        return match ($this->segment->getEntity()) {
            EntityType::CONTACT => ContactTable::query(),
            EntityType::COMPANY => CompanyTable::query(),
            default => null,
        };
    }

    protected function getModifyMultipleFilterConditions(): ConditionTree
    {
        // Если выбран 1 фильтр в сегментах возвращаем его
        if (ConditionTreeHelper::isBaseConditions($this->ct)) {
            return clone $this->ct;
        }

        // Если все фильтры логическое И
        if ($this->ct->logic() !== ConditionTree::LOGIC_OR) {
            // Формируем новый ConditionTree в который каждый отдельный фильтр делаем подзапросом.
            $ct = new ConditionTree();
            foreach ($this->ct->getConditions() as $condition) {
                $not = '';
                if ($condition instanceof ConditionTree && ConditionTreeHelper::isNegative($condition)) {
                    $not = 'NOT ';
                }

                $subQuery = str_replace(
                    '%',
                    '%%',
                    $this->getModifyMultipleFilterConditionSubQuery($condition)
                );
                $ct->whereExpr(
                    "%s {$not}IN ({$subQuery})",
                    ['ID']
                );
            }

            return $ct;
        }

        // Если есть фильтр логическое ИЛИ
        $or = new ConditionTree();
        $or->logic(ConditionTree::LOGIC_OR);
        foreach ($this->ct->getConditions() as $condition) {
            $ct = new ConditionTree();
            // Формируем новый ConditionTree в который каждый отдельный фильтр делаем подзапросом.
            foreach ($condition->getConditions() as $c) {
                $not = '';
                if ($condition instanceof ConditionTree && ConditionTreeHelper::isNegative($condition)) {
                    $not = 'NOT ';
                }

                $subQuery = str_replace(
                    '%',
                    '%%',
                    $this->getModifyMultipleFilterConditionSubQuery($c)
                );
                $ct->whereExpr(
                    "%s {$not}IN ({$subQuery})",
                    ['ID']
                );
            }
            $or->where($ct);
        }

        return $or;
    }

    protected function getModifyMultipleFilterConditionSubQuery(Condition|ConditionTree $ct): string
    {
        $subQuery = $this->getEntityQuery()
            ->addSelect('ID');

        if ($ct instanceof Condition) {
            return $subQuery
                ->where($ct)
                ->getQuery();
        }

        // По сути не возможная в данный момент ситуация, просто подстраховался
        // Но если такое возникнет будет проблема, потому что если в $ct есть поля которые должны попасть в having
        // битрикс все запихнет в having и что нужно и что не нужно
        if ($ct->logic() === ConditionTree::LOGIC_OR) {
            return $subQuery
                ->where($ct)
                ->getQuery();
        }

        // Чтобы битрикс все не кидал в having переносим все условия в Query напрямую
        foreach ($ct->getConditions() as $c) {
            $subQuery->where($c);
        }

        return $subQuery->getQuery();
    }

    protected function setConditionsToQuery(Query $query, ConditionTree $ct): void
    {
        if ($ct->logic() === ConditionTree::LOGIC_OR) {
            $query->where($ct);
            return;
        }

        foreach ($ct->getConditions() as $condition) {
            $query->where($condition);
        }
    }
// ... там далее другая логика хелпера
}
```

Таким образом для опледеления контактов которые нужно добавить в сегмент используем вышеуказанный хелпер `\Bizprofi\Cdp\Helpers\SegmentEntitiesQueryHelper`

```php
if (\Bitrix\Main\Loader::includeModule('bizprofi.cdp')) {
    throw new \Bitrix\Main\LoaderException('Not include module "bizprofi.cdp"');
}

if (!($segment = \Bizprofi\Cdp\DataManagers\SegmentsTable::getById(1)->fetchObject())) {
    throw new \Bitrix\Main\ObjectNotFoundException('Segment not found');
}

$entitiesQueryHelper = new \Bizprofi\Cdp\Helpers\SegmentEntitiesQueryHelper($segment);

$limit = 10000;
$offset = 0;

$query = $entitiesQueryHelper->getQuerySelect()
    ->addSelect('ID')
    ->addOrder('ID', 'ASC')
    ->addGroup('ID')
    ->setLimit($limit)
    ->setOffset($offset);

$rows = $query->exec();

$clientIds = [];
while ($row = $rows->fetch()) {
    $clientIds[] = (int) $row['ID'];
}

var_dump($clientIds);
```

Изначально мы не получали отдельно идентификаторы контактов, а сразу формировали прямой запрос на вставку данных в таблицу контактов сегмента

```php
if (\Bitrix\Main\Loader::includeModule('bizprofi.cdp')) {
    throw new \Bitrix\Main\LoaderException('Not include module "bizprofi.cdp"');
}

if (!($segment = \Bizprofi\Cdp\DataManagers\SegmentsTable::getById(1)->fetchObject())) {
    throw new \Bitrix\Main\ObjectNotFoundException('Segment not found');
}

$entitiesQueryHelper = new \Bizprofi\Cdp\Helpers\SegmentEntitiesQueryHelper($segment);

$limit = 10000;
$offset = 0;

$query = $entitiesQueryHelper->getQuerySelect()
    ->registerRuntimeField(
        new \Bitrix\Main\ORM\Fields\ExpressionField('SEGMENT_ID', "{$segment->getId()}")
    )
    ->registerRuntimeField(
        new \Bitrix\Main\ORM\Fields\ExpressionField('ENTITY', "\"{$segment->getEntity()}\"")
    )
    ->setSelect(['SEGMENT_ID', 'ENTITY', 'ENTITY_ID' => 'ID'])
    ->addSelect('ID')
    ->addOrder('ID', 'ASC')
    ->addGroup('ID')
    ->setLimit($limit)
    ->setOffset($offset);

$sqlQuery = $query->getQuery();

\Bitrix\Main\Application::getConnection()->queryExecute(
    new \Bitrix\Main\DB\SqlExpression(
        "INSERT IGNORE INTO ?# (`SEGMENT_ID`, `ENTITY`, `ENTITY_ID`) {$sqlQuery};",
        \Bizprofi\Cdp\DataManagers\SegmentEntitiesTable::getTableName()
    )
);
```

Но как показала практика при сложных фильтрах вставка данных подзапросом выполняется в десятки раз медленнее чем сначала получить идентфикаторы клиентов и потом вставить их с помощью `INSERT VALUES`.

Аналитические сегменты

В CDP можно кластеризовать данные по определенным условиям, например, разделить клиентов по возрастам, по количеству или сумме сделок — по любым числовым показателям. Это позволит маркетологам быстро понять, с какой именно базой он работает, какие предложения можно делать клиентам. Для этого можно использовать два популярных метода.

Метод k-средних

Данный алгоритм разбивает базу клиентов на указанное при создании количество сегментов (по умолчанию 9). При создании k-средних можно ограничить клиентскую базу по типам клиентов, направлениям сделок и периоду по любому из полей (типа date) в сделке.

Далее алгоритм на основе выбранных полей при создании (можно выбрать любые числовые поля и поля типа date) объединяет клиентов по похожести.

Например, необходимо разбить базу контактов на сегменты по возрасту. Система сформирует сегменты (в зависимости от настроек) от 20 до 22, от 22 до 25 и т.д.

Нормализация происходит методом "Минимально максимальной нормализации" Xnorm = (X - Xmin) / (Xmax - Xmin)

Для каждого эксперимента вычисляются свои начальные точки для кластеризации, используя метод k-mean++ , а затем в каждом эксперименте проводится независимая кластеризация до тех пор, пока кластеры не перестают меняться на протяжении двух итераций, либо пока количество итераций не достигнет 100 (это число выбрано как оптимальное, но корректируется в настройках модуля).

Для кластеризации мы для каждого клиента определяем его евклидово расстояние до начальной точки кластера (центроида), корень суммы квадратов разницы параметров центроида и контакта. Контакт считается принадлежащим тому кластеру, расстояние до центроида которого меньше.

Вес эксперимента равен сумме весов его центроидов. Вес центроида равен сумме квадратов расстояний от центроида до контактов.

После того, как определен лучший эксперимент, из вошедших в него контактов формируется сегмент для дальнейшей работы. В зависимости от периода обновления, выбранного при создании k-средних, этот процесс может повторяться с периодичностью от часа до месяца.

Для того, чтобы снизить нагрузку на сервер при кластеризации данных, решили не использовать готовые алгоритмы, которые загружают весь объем данных сразу в оперативную память, а разработали собственный алгоритм, который построчно читает и обрабатывает CSV-файлы. Такой подход показывает высокие результаты даже с учетом того, что все решение написано на PHP. Например, кластеризация базы, содержащей более миллиона записей заняла около 7 минут.

RFM-сегментирование

Этот метод помогает определить наиболее ценных и лояльных клиентов, а также тех, кто требует дополнительного внимания. Клиентская база сегментируется по сочетанием трех параметров: по тому, как давно клиенты делали покупку в последний раз (Recency, давность), как часто они их делают в течение определенного периода (Frequency, частота) и какую сумму денег потратили за указанный период (Monetary, сумма).

По этим признакам в CDP формируется 27 динамических сегментов, которые позволяют маркетологу понимать, в каком состоянии находится на текущий момент база и как происходит переток клиентов из одного сегмента в другой — например, как меняется число активных и спящих клиентов после проведенной рассылки.

Техническая реализация этого алгоритма достаточно проста — это обычный итерационный перебор записей по параметрам RFM. CDP анализирует данные о клиентах (дату последней покупки, количество покупок и общую сумму, потраченную за период) и присваивает каждому показателю RFM оценку по шкале от 1 до 3. Затем оценки объединяются в комбинации, на основе которых клиенты распределяются по группам. Например, группа с оценками 3-3-3 — наиболее лояльные клиенты, а 1-1-1 — наименее лояльные.

В модуле предусмотрены отчеты для отслеживания динамики по аналитическим сегментам. Можно увидеть, как меняется сегмент по количеству элементов в каждом месяце. Это дает очень высокую ценность с точки зрения анализа и принятия управленческих решений.

Плюс этого подхода в том, что CDP анализирует в CRM все данные, ограниченные заданными маркетологи фильтрациями, например, направлением сделки, периоду и другим параметрам, тем самым показывая бизнесу, как, например, проведенные рассылки отразились на продажах.

Как сегментировать миллионы записей быстро

Для возможности работы с миллионными базами генерация сегмента происходит в несколько этапов. В CDP БизнесПрофи специальный агент создает 5 отдельных агентов генерации, которые ищут контакты по заданным условиям. Каждый должен вернуть не менее 10000 контактов. Если по окончании итерации найдено менее 50000 записей, поиск можно завершать. В противном случае запускается следующая итерация. Такой подход позволяет сокращать нагрузку на сервер при работе с большими базами данных и ускорить поиск.

Цифра 5 была выбрана эмпирическим путем. Сегменты размером более 50000 записей встречаются довольно редко, а если и встречаются, то создаются максимум в две итерации. В среднем на создание сегмента уходит минута, но если применяются слишком сложные фильтры, то на формирование уйдет больше времени.

Что дальше?

Собранные сегменты пользователей можно использовать во внешних сервисах, с которыми интегрирована CDP — это DashaMail и аудитории в ВК и Яндекс. Можно организовывать простые и сложные рассылки с использованием любых доступных каналов — email, СМС, мессенджеров. О том, как это работает в связке CDP и Битрикс24 расскажем во второй части статьи.

Рассылка на миллион: как CDP от БизнесПрофи на базе Битрикс24 помогает оперировать реально огромными базами контактов