Сегодня у бизнеса есть CRM, у аналитика — Power BI, но, к сожалению, часто бывает что между ними пропасть. Как соединить эти миры и превратить ваши лиды, сделки и менеджеров в понятные отчеты и визуализации?
Меня зовут Кажденова Асия, я работаю над шаблонными решениями на основании данных выгрузок mybi connect. Недавно завершила работу над пошаговым курсом по созданию отчетов в Power BI на основании данных Битрикс24. А в этой статье поделюсь текстовой инструкцией для создания одного из листов отчета, который мы, кстати, уже показывали в этой статье –
Мы разберем технику построения одной из ключевых визуализаций курса — «Воронку и текущий статус по трем показателям: сумма, количество или средний чек».
В нашем курсе этот материал находится ближе к середине, поэтому перед тем, как начнем разбирать меры, небольшая предыстория:
- Мы работаем с выгрузкой данных из API Битрикс24, которая находится в БД PostgreSQL, выгрузку и ежедневное обновление данных производит наш сервис mybi connect в соответствии с этой структурой.
- Для быстрого перехода к работе в Power BI мы предлагаем пользователям готовую шаблонную модель, в которой собраны связи между десятками таблиц выгрузки и произведены базовые расчеты.
- А так же шаблонный отчет для Битрикс24, который является примером в нашем курсе.
- Вы можете повторить все эти действия из статьи, используя собственные данные, бесплатный триал и справку mybi connect, либо приобрести курс…но о нем позже сейчас перейдем непосредственно к теме статьи.
Для начала подготовим меру Среднего чека, в которой разделим сумму на количество с использованием функции мягкого деления.
Сделки: Ср.чек = DIVIDE ( [Сделки: Сумма], [Сделки: Количество] )
Меры [Сделки: Сумма] и [Сделки: Количество] мы создали ещё на этапе моделирования данных, чтобы затем использовать их в визуализациях:
Сделки: Сумма = IF ( SUM ( 'B24 Оформленные сделки'[Сумма] ) <> 0, SUM ( 'B24 Оформленные сделки'[Сумма] ) )
Сделки: Количество = DISTINCTCOUNT ( 'B24 Оформленные сделки'[Идентификатор сделки] )
Теперь, чтобы позволить пользователю выбирать один из трех показателей:
Сумма сделок, Количество сделок или Средний чек, перейдите во вкладку «Моделирование» и выберите «Создание параметра – Поля». В появившемся окне задайте имя «Показатель». Из правой части окна (Поля) перетащите в левую часть меры:
- Сделки: Сумма
- Сделки: Количество
- Сделки: Средний чек
- Показатель – этот столбец содержит названия доступных метрик. Он используется для отображения списка значений в срезе, который выбирает пользователь.
- Поля: Показатель– этот столбец содержит ссылки на соответствующие меры, которые были добавлены при создании параметра. Когда пользователь выбирает один из показателей в срезе, Power BI автоматически подставляет нужную меру в визуализации.
- Заказ Показатель– вспомогательный столбец, который отвечает за сортировку значений в параметре. Он помогает Power BI правильно отображать порядок выбора в срезе.
Данная таблица не будет иметь связей с другими таблицами нашей модели.
Чтобы пользователь не мог выбрать сразу несколько показателей одновременно, нужно настроить срез: зайдем в форматирование визуального элемента и в параметрах среза-выбор, включим единичный выбор.
Для среза Воронка-статус мы создадим отдельную таблицу, на основе данных таблицы Справочник, чтобы статусы отображались в той последовательности, через которые проходят сделки во время обработки. Для этого в таблице Справочник создадим два столбца.
Когда компания работает с разными клиентами или типами продаж, удобно разделить сделки по категориям. Каждая категория — это отдельная воронка продаж с своими этапами. Например, компания продаёт товары в магазине и на сайте. Эти продажи идут разными путями и благодаря категориям, воронки не смешиваются. Поэтому создадим поле Статус-Воронка, объединяя два столбца.
Перейдите во вкладку представление таблицы, найдите Справочник и выберите параметр создать столбец.
Статус-Воронка = [Название] & " (" & [Идентификатор категории] & ")"
В таблице Справочники есть поле “Сортировка”, которое отвечает за порядок прохождения этапов сделки . Чем меньше число, тем выше этап находится в списке. Но также мы должны учесть, что могут быть одинаковые этапы в разных категориях. Чтобы избежать путаницы создадим еще один столбец:
Сортировка воронка-статус = [Идентификатор категории] & [Сортировка]
Установим тип данных-целое число и отсортируем столбец Статус-Воронка по столбцу Сортировка воронка-статус, чтобы в визуализациях отображалась корректная последовательность этапов сделки.
Далее создадим таблицу Справочник статусов:
Справочник статусов = SUMMARIZE ( 'B24 Справочники', 'B24 Справочники'[Название категории], 'B24 Справочники'[Название], 'B24 Справочники'[Статус-Воронка], 'B24 Справочники'[Сортировка воронка-статус] )
Функция SUMMARIZE используется для группировки данных из таблицы ‘B24 Справочники’ по столбцам:
- [Название категории],
- [Название],
- [Статус-Воронка],
- [Сортировка воронка-статус]
В результате получается новая таблица, где каждая уникальная комбинация значений из этих четырёх столбцов будет представлена в единственном экземпляре.
Переименуем поле название в Статус и Отсортируем поле Статус-Воронка по столбцу Сортировка воронка-статус.
Создадим аналогичное поле в таблице Параметры сделок.
Статус-Воронка = [Этап сделки] & " (" & CALCULATE ( VALUES ( 'B24 Справочники'[Идентификатор категории] ), FILTER ( 'B24 Справочники', 'B24 Параметры сделок'[Идентификатор направления сделки] = 'B24 Справочники'[Идентификатор категории] ) ) & ")"
Функция CALCULATE() изменяет контекст расчёта (то есть задаёт новые условия) и вычисляет выражение внутри себя. Функция VALUES возвращает уникальные значения из столбца [Идентификатор категории].
Функция FILTER фильтрует таблицу ‘B24 Справочники’ так, чтобы остались только те строки, у которых Идентификатор направления сделки из ‘B24 Параметры сделок’ совпадает с Идентификатором категории из Справочники’, т.е. она ищет совпадения между двумя таблицами по идентификаторам.
Перейдем во вкладку Представление модели и свяжем справочник статусов с параметрами сделок по полю Статус-воронка.
Построим срез с использованием полей Название категории и Статус из Справочники статусов.
Далее построим воронку, в категорию которой положим Название категории и Статус-воронка из справочника статусов вместо источника, а в значения столбец Показатель из одноименной таблицы.
Слегка отформатируем воронку. В форматировании визуального элемента – метки данных-значения поставим 0 для числа десятичных знаков.
Чтобы название воронки менялось в зависимости от выбранного пользователем показателя в срезе, необходимо создать меру:
Title Воронка = VAR SelectedMetric = SELECTCOLUMNS ( SUMMARIZE ( 'Показатель', 'Показатель'[Показатель], 'Показатель'[Поля: Показатель] ), "FiledParameter", 'Показатель'[Показатель] ) RETURN IF ( COUNTROWS ( SelectedMetric ), "Воронка и текущий статус: " & SelectedMetric )
где SUMMARIZE(…) группирует таблицу ‘Показатель’, оставляя только столбцы [Показатель] и [Поля: Показатель], а SELECTCOLUMNS(…) извлекает из неё только столбец [Показатель] и называет его “FiledParameter”.
Функция COUNTROWS(проверяет, есть ли выбранный показатель и если он есть, возвращается статичная строка ” Воронка и текущий статус: ” и название выбранного показателя.
Чтобы применить данную меру к названию, необходимо перейти в форматирование визуального элемента-общие – название и возле текста нажать значок функции. В появившемся окне найдем меру Title Воронка и нажмем ОК. Проверяем.
Следующим шагом поменяем подсказку на пользовательскую. Для начала создадим чистый лист, зайдем в форматирование страницы, сведения страницы и включить тумблер Разрешить использовать в качестве подсказки. При этом размер страницы измениться.
Построим гистограмму с группировкой. В строки положим поле Фамилия Имя из Параметров пользователей, а в ось У – Показатель. В форматировании визуального элемента отключите название гистограммы и название осей Х и У.
Сверху гистограммы вставим вижуал карточка, а в поля положим Показатель. Значение выноски уменьшим до 12 и отключим Подпись категории. Назовем страницу подсказка сделки и скроем ее, чтобы пользователи, которые смотрят отчет в Power BI Service, ее не видели.
Вернемся на страницу Сделки и выделив воронку войдем в форматирование – общий-подсказки. Тип установлен Страница отчета, а страницу необходимо установить подсказка сделки.
Теперь подводя мышь к любому уровню воронки мы увидим гистограмму по пользователям.
А для того, чтобы посмотреть, как распределились сделки в текущем статусе в конкретной воронке, необходимо включить кнопку детализации и тапнуть на определенной воронке.
Например, мы можем выбрать показатель количество, посмотрим Воронку 5 и увидим, что новых сделок на сегодня – 40, а успешных сделок 5 – у четырех менеджеров. Статусы отсортированы в той последовательности, через которые проходят сделки во время обработки.
Другой вариант посмотреть воронку по статусам – в фильтрах выбрать необходимую воронку и нажать на вилку, чтобы спуститься на следующий уровень в иерархии.
Таким образом, эта визуализация позволяет мгновенно оценить, сколько сделок находится на каждом этапе воронки, какая сумма потенциальной выручки или количество там сосредоточено и насколько эффективно работает ваша команда.
Это не просто красивая схема — это инструмент, который помогает находить узкие места, прогнозировать доход, принимать управленческие решения и задавать правильные вопросы отделу продаж.
А главное — вы научитесь создавать такие инструменты сами.
Ещё больше полезных визуализаций, практических примеров, шаблонов и лайфхаков ждёт вас в нашем обучающем курсе. Подключайтесь — и выводите свою аналитику на новый уровень.
В заключении напомним про наш курс по Битрикс24. Сейчас мы рассмотрели один из элементов отчета по Битрикс24, курс содержит разбор сразу нескольких листов отчета в более удобном видео фиормате, за которым можно повторять действия.
В составе курса вы найдете шаблонную модель и шаблонный отчет, доступ к демо базе данных с вышгрузкой из Битрикса, если нет своей. Вот программа:
Будем рады увидеть вас среди слушателей!
