После обновления выгрузки из Битрикс24 мы решили сделать на ее основе несколько шаблонных отчетов в разных BI-системах. Мы уже публиковали решения в Power BI, бесплатном Apache Superset, отечественном Analytic Workspace и, конечно, не могли обойти стороной Yandex Datalens. Для этого мы привлекли стороннего разработчика отчетов и сейчас делимся результатами и описанием.
Кстати, шаблонные отчеты в нашем случае это те, в которых не используется пользовательских полей CRM, они работают только на дефолтных (базовых), за счет этого шаблоны разворачиваются на выгрузке mybi connect из любого аккаунта Битрикс24. Такие шаблоны размещены в нашем маркетплейсе mybi market для разных источников.
Добрый день! Общая схема работы выглядит следующим образом: данные из Битрикс24 передаются в промежуточное хранилище etl сервиса mybi connect – базу данных, после чего BI-инструмент Yandex Datalens подключается к этому хранилищу и строит на основе этих данных различные визуализации. Mybi connect периодически отправляет запросы на получение изменений из Битрикс24 и распределяет эти данные по таблицам в базе данных. После обновления информации в базе происходит перерасчет показателей и актуализация визуализаций на дашборде Yandex Datalens.
Демо-версию шаблона вы можете изучить самостоятельно.
Дашборд включает четыре вкладки по типам сущности Битрикс24:
- Вкладка «Лиды» показывает динамику числа лидов, какие источники дают наибольшую/ наименьшую лидогенерацию, можно оценить эффективность отработки лидов менеджерами и увидеть какая конверсия из лидов в сделку, сколько лидов в работе и на каких этапах.
- Вкладка «Сделки» показывает динамику количества и объема сделок на разных стадиях и этапах воронки; здесь мы сможем понять, какие источники приносят больше сделок, а с какими источниками нецелесообразно сотрудничать, а также оценить нагрузку и эффективность менеджеров.
- Вкладка «Реестр сделок в работе» показывает детальную информацию по сделкам; здесь можно отследить сделки, которые зависли на том или ином этапе воронки.
- Вкладка «Задачи» показывает нагрузку на менеджеров в части задач, а так же дает оценку доле выполненных или просроченных задач.
На каждой вкладке предусмотрена фильтрация по периодам создания лидов/сделок/задач, по их статусу, типу, воронке, источнику привлечения, по ответственному сотруднику и более специфические, например, срок «зависания» на стадии для сделок.
Помимо стандартной фильтрации с использованием панели фильтров, в дашборде реализован механизм кросс-фильтрации, позволяющий динамически изменять аналитические данные на основании выбранных показателей на графиках или таблицах.
Битрикс24 обладает богатым набором разных сущностей и параметров, что отражается на структуре выгрузки mybi connect, а точнее, на количестве таблиц в ней. Перед визуализацией данные обрабатываются и передаются с помощью SQL-запросов. Давайте более подробно разберем SQL-запрос по выбору идентификатора сделки и их статусы («Успех», «Провал» или «В работе») на основе последней стадии, на которой находилась сделка:
SELECT
t1."Идентификатор сделки"
,MAX(
CASE WHEN t1."Идентификатор стадии" LIKE '%WON%' THEN 'Успех'
WHEN t1."Идентификатор стадии" LIKE '%LOSE%' THEN 'Провал' ELSE 'В работе'
END
) AS "Статус-тип1"
FROM public."B24 История статусов сделок" t1
JOIN
(SELECT
"Идентификатор сделки"
MAX("Дата и время попадания на стадию") AS "Дата и время попадания на стадию"
FROM public."B24 История статусов сделок"
GROUP BY "Идентификатор сделки") t2
ON t2."Идентификатор сделки" = t1."Идентификатор сделки"
AND t2."Дата и время попадания на стадию" = t1."Дата и время попадания на стадию"
GROUP BY t1."Идентификатор сделки"
PS. Извините, не самое удобное форматирование для SQL, но оно позволит вам скопировать запрос…
Итак, шаг 1: Подзапросы
1. Основной подзапрос (t2):
SELECT
"Идентификатор сделки"
MAX("Дата и время попадания на стадию") AS "Дата и время попадания на стадию"
FROM public."B24 История статусов сделок"
GROUP BY "Идентификатор сделки
Этот подзапрос находит для каждой сделки последнюю дату и время, когда она была переведена на какую-то стадию. Это делается через агрегацию MAX() по полю «Дата и время попадания на стадию» с группировкой по «Идентификатору сделки».
2. Внешний запрос (t1):
Основной запрос соединяет результат подзапроса t2 с исходной таблицей «B24 История статусов сделок» по полям «Идентификатор сделки» и «Дата и время попадания на стадию». Таким образом, он получает данные о последней стадии для каждой сделки.
Шаг 2: Определение статуса сделки
Для определения статуса используется конструкция CASE, которая проверяет значение поля «Идентификатор стадии»:
CASE WHEN t1."Идентификатор стадии" LIKE '%WON%' THEN 'Успех'
WHEN t1."Идентификатор стадии" LIKE '%LOSE%' THEN 'Провал' ELSE 'В работе'
END
Итоговый запрос возвращает два столбца:
- «Идентификатор сделки»
- Статус сделки («Статус-тип1») на основании последней стадии.
Таким образом, этот запрос позволяет получить список всех сделок вместе со статусом на текущий момент времени.
Надеюсь, этот разбор поможет вам или подтолкнет к более глубокому изучению SQL.
Сейчас отчет развернут на демо БД, но он и называется шаблоном, потому что его можно развернуть на других аналогичных выгрузках. Для этого вам понадобится выгрузка из Битрикс24, которую можно получить бесплатно, используя триал mybi connect.
После этого напишите в мой Telegram (@Inna_V_Starchenko) сообщение с текстом «Шаблон mybi», и я свяжусь с вами, чтобы передать и помочь настроить шаблон на ваших данных Битрикс24. Пока мы не добавляли этот шаблон в маркетплейс Datalens и процедуру нового подключения необходимо производить вручную.
Если вам потребуется решение более сложных задач, мы также можем предложить создать отчеты, полностью адаптированные под ваши индивидуальные потребности и специфику работы в Битрикс24.
Будем рады быть полезными для Вас!
