BI конструктор в Битрикс24 позволяет создавать отчеты с большим кастомным (гибким) функционалом потому что работает напрямую с таблицами с базы данных с Битрикс24
Привет, технари, я на Хабре недавно, решил делиться своей экспертизой и общаться с единомышленниками, чтобы иметь окружение схожих себе и обмениваться опытом. Сам своими руками сделал более 70 интеграций Битрикс24 и AmoCRM за 3 года, есть что выложить из опыта.
Отделам продаж постоянно нужны отчеты: конверсии, звонки и так далее, но особо развитым кампаниям надо отчеты уже кастомные, не шаблон.
Если брать шаблонные отчеты в Битрикс24, то там кусок фронта достаточно шаблонный что и обычно для CRM которая «для всех», это же не самописка. Там есть Конструктор отчетов, но там тоже очень много ограничений.
А если РОП или директор по продажам хочет кастомный отчет? В Rest API лезть?
При большом желании можно и в API лезть, но в Битрикс24 есть промежуточный вариант, полу шаблон, полу код (кастом) — BI конструктор. Самая простейшая реализация (все конфиденциальные данные клиента скрыты, согласие клиента на публикацию получено):
Отчеты выше от реального клиента из отдела продаж, формируются отчеты по звонкам: Все звонки, Исходящие, Входящие по новым клиентам, Все входящие, Количество очных встреч и так далее. Так же есть разбивка на дни и месяцы. Т.е. РОП заходит и видит объемы звонков по всему отделу продаж. Причем есть раздел звонков по новым клиентам, это именно по кампаниям которых раньше не было в Битрикс24.
А теперь к тому, как такое делать.
Внутри Битрикс24 слева есть меню, там находим раздел BI Конструктор и заходим в него. Нажимаем Создать и создаем:
Попав в отчет видим фронт дашборда в котором пока ничего нет, его надо начать редактировать. Поэтому нажимаем редактировать:
Внутри настроек несколько вкладок: Дашборды, Графики, Датасеты и SQL. Дашборды это настройка фронта самого дашборда, то есть размещение таблиц, графиков, фильтров, блоков и так далее, туда идти надо только когда уже таблица будет готова, а она пока не сформирована.
Графики это таблицы и построенные из них графики, то есть таблица первична, а график на основе таблицы строится, но в текущей статье только про таблицы. Таблицу можно сформировать обычную, сводную и так далее, ставить разные значения столбцов, строк, мер измерения, шага, промежуточных выводов и прочее. Таблица строится на основании данных, а их пока нет, сюда зайдем позже.
Датасет это набор данных, выведенных упорядочено, короче это то что с БД вытащили и как то структурировали. Т.к. пока ничего не вытащили с БД, то сюда тоже позже войдем.
SQL это формирование запросов к БД, именно это самая основа и именно тут пишем код SQL. Залетаем в SQL Lab и начинаем заливать код и сразу смотрим вывод данных ниже:
Слева фильтры: База данных, Схема и Таблица. База данных trino. Схема bitrix24. А вот таблиц там множество и именно таблицы это списки ключей которые мы вытаскиваем в БД Битрикс24. Документацию по наборам данных для BI конструктора можно легко загуглить и найти, там все предельно подробно описано в каких таблицах что хранится и какие ключи что выдают. Так же есть чаты BI разработчиков где подскажут. Выбрав таблицу снизу появятся ключи и описание какие значения они выводят. Т.е. выбираем таблицу и смотрим что надо в SQL вписать:
Все просто: С БД (база данных) Битрикс24 с таблиц берутся данные и SQL формирует их в таблицы отчета, следом графики строятся уже далее. SQL берет таблицы и выводит.
Мне кстати проще SQL в VS коде писать, а сюда вставлять.
Ниже пример простого кода что бы вывести отчет по Задачам с тегом 'crm'. Берем таблицу bitrix24.task (можно и так bi.bitrix24.task) и говорим что надо условие ts.tags = “crm”, то есть задачи с тегом 'сrm' и условие по дате закрытия задачи в промежуток времени ts.closed_date. А потом группируем одинаковые значения по ответственному и по дате.
SELECT ts.responsible_name AS responsible_name, ts.closed_date AS closed_date FROM bitrix24.task ts WHERE ts.tags = 'crm' AND {% if from_dttm is not none %} ts.closed_date >= from_iso8601_timestamp('2020-01-01') AND {% endif %} {% if to_dttm is not none %} ts.closed_date < from_iso8601_timestamp('2026-12-01') AND {% endif %} true GROUP BY responsible_name, closed_date
Код выше несколько сложнее обычного SQL, так как тут фильтрация по дате через:
{% if from_dttm is not none %} ts.closed_date >= from_iso8601_timestamp('{{ from_dttm }}') AND {% endif %} {% if to_dttm is not none %} ts.closed_date < from_iso8601_timestamp('{{ to_dttm }}') AND {% endif %} true
Это шаблоны Jinja Templates, в документации Битрикс24 можно найти, тут описывать не буду, так как долго, а у них все подробно описано (загуглить).
Но суть этого куска условия в том что SQL будет обращаться к данным с фильтра во фронте на дашборде который пользователь введет. from_iso8601_timestamp переводит дату в нужный формат для SQL, иначе не поймет.
У этих шаблонов есть нюанс: сначала надо сохранить с фиксированной датой например «2020-01-01» (иначе не даст сохранить дата сет, так как переменную не понимает), а после того как отчет будет сформирован менять фиксированную дату на «{{ from_dttm }}» и «{{ to_dttm }}» внутри графика. На самом деле я не нашел другого решения и может быть как то можно сохранять иначе, а не танцевать с бубном, но у меня работает только так.
Кроме этого это обычный SQL с FROM, SELECT и WHERE, то есть откуда взять, по каким условиям и что вывести. Ещё есть GROUP BY что бы с группировало.
Удобно что вставив SQL сразу можем выполнить и получить вывод ниже. Кстати поэтому и надо сначала вставлять фиксированную дату, иначе не выведет так как значения нет.
После того как SQL код сделали и проверили вывод — сохраняем датасет нажав кнопку:
Потом из дата сета надо таблицу сформировать. Например здесь я выбрал сводную таблицу, столбцы дата завершения, строки имя ответственного.
В таблицах опций огромное количество. Слева наши данные что вывелись с датасета, как видим это из SELECT вывод (названия только уже в таблице поменяны). То что вывелось расставляем в строки, столбцы и меры конструктора. Там еще выбираем тип таблицы и график если он нужен. Можно закастомизировать с кучей опций.
По мне очень удобная опция от отчетов отдела продаж что бы выводить промежуточные результаты данных.
Там каждому значению надо задавать название, иначе он выведет в таблицу что то по типу COUNT (*) и так далее, для юзера это будет треш, поэтому называем что бы названия поняла бабушка Маша бухгалтер которой 70 лет.
После сохраняем таблицу и формируем дашборд, в котором надо фильтры настроить:
Как видим что вывелась таже таблица, то есть идем по этапам, от SQL к датасету, от датасета к таблице, от таблицы у дашборду. В общем обычная песня в разработке что от бека идем к фронту и формируем вывод который понятен обычному пользователю.
Таблица выведена, но надо фильтровать и нужны фильтры.
Создаем фильтр по дате:
Там фильтров много, в зависимости от того какие данные выводим — такие фильтры и создаем. Часто делают несколько фильтров. Но некоторые фильтра не получится сделать если не будет выведен нужный столбец. Например фильтр по дате можно будет сделать только если есть столбец дата, иначе откуда брать.
После того как фильтры готовы — надо в SQL запрос вернуть переменные '{{ to_dttm }}' и '{{ to_dttm }}' вместо фиксированной даты. Для этого переходим в графики, нажимаем редактировать дата сет и вставляем в SQL код эти переменные. Сохраняем (ошибка выйдет, внимания не обращаем).
Тут мы увидим наш код, но BI конструктор сюда добавит свои куски кода. Конструктор по сути берет ваш SQL код и его сюда выводит где потом его редактируете но уже вставляя переменные '{{ to_dttm }}' и '{{ to_dttm }}' вместо фиксированной даты потому что сейчас они нужны так как фильтры уже есть и с фильтров будут данные браться.
Повторюсь что такое решения мы нашли, возможно есть какое то лучшее, то есть без пересохранения в коде.
Ничего кроме этого не меняя — сохраняем (если будет ошибка, внимания не обращаем):
Теперь если ставим фильтр по дате — выводит по дату. Т.е. теперь таблица будет меняться в зависимости от значений в фильтре, а они в SQL код будут заводиться через те переменные что добавили:
Это самое простое решение что бы просто вывести задачи. Кроме этого я выводил сделки, звонки в отделе продаж и так далее
Так же у меня были решения и уровня Middle когда надо было выводить продажи по определенным регионам за период и по месяцам ещё. Это уже сложнее в разы, так как идет выборка с нескольких таблиц bi.bitrix24.crm_deal (берем сами сделки), bi.bitrix24.crm_deal_stage_history (берем что сделка переместилась на успешные стадии что бы продажи посчитать), bi.bitrix24.crm_company (берем кампанию потому что это наш клиент), bi.bitrix24.crm_company_uf (берем кастомное поле в кампании что бы нужному региону отсев сделать, у отдела продаж задача продажи по регионам смотреть).
Тут ещё возникла проблема что надо по месяцам вывести, а в конструкторе я не нашел такого и пришлось кодом выводить через CASE, а причем если названия месяцев писать просто «Январь», «Февраль», то он потом в таблице по алфавиту сортирует и Август первый оказывается, пришлось цифры добавить перед названиями.
SELECT SUM(d.opportunity) AS opportunity, d_sh.category_name AS direction, CASE MONTH(d_sh.date_create) WHEN 1 THEN '01 Январь' WHEN 2 THEN '02 Февраль' WHEN 3 THEN '03 Март' WHEN 4 THEN '04 Апрель' WHEN 5 THEN '05 Май' WHEN 6 THEN '06 Июнь' WHEN 7 THEN '07 Июль' WHEN 8 THEN '08 Август' WHEN 9 THEN '09 Сентябрь' WHEN 10 THEN '10 Октябрь' WHEN 11 THEN '11 Ноябрь' WHEN 12 THEN '12 Декабрь' END AS months FROM bi.bitrix24.crm_deal d JOIN bi.bitrix24.crm_deal_stage_history d_sh ON d.id = d_sh.deal_id JOIN bi.bitrix24.crm_company com ON com.id = d.company_id JOIN bi.bitrix24.crm_company_uf com_uf ON com.id = com_uf.company_id WHERE (d_sh.stage_name = 'Сделка успешна' OR d_sh.stage_name = 'Отгружен товар') AND com_uf.uf_crm_1701166670098 = 'Нижегородская область' AND {% if from_dttm is not none %} d_sh.date_create >= from_iso8601_timestamp('{{ from_dttm }}') AND {% endif %} {% if to_dttm is not none %} d_sh.date_create < from_iso8601_timestamp('{{ to_dttm }}') AND {% endif %} true GROUP BY months, direction
Но это не частые запросы такой сложности, это уже средний уровень департамента продаж, а не обычного отдела продаж.
Итого: BI конструктор в Битрикс24 позволяет формировать отчеты для отдела продаж в различных вариациях, более гибко чем стандартные. Но надо знать SQL и сам конструктор.
