Дашборд для анализа конверсии воронки продаж в Excel скачать

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

Пример построения дашборда конверсии воронки продаж в Excel

Для примера смоделируем ситуацию. Из CRM системы было экспортировано в формате таблицы Excel 2 отчета (в двух таблицах) для последующего визуального анализа конверсии и эффективности воронки продаж топ 5-ти менеджеров:

1. Владимир Лисин.
2. Алексей Мордашов.
3. Леонид Михельсон.
4. Вагит Алекперов.
5. Геннадий Тимченко.

Забегая вперед сейчас стоит обратить внимание на то, что оба отчета представлены в таблицах с объединенными ячейками. Поэтому при их обработке данных будет использоваться функция СМЕЩ для последовательной выборки значений показателей из таблиц через одну строку с четными и нечетными номерами срок:

1. Первый отчет отображает подробную информацию о первом этапе воронки продаж «Поиск клиентов». В этом отчете выражена эффективность в результатах работы менеджеров по продажам, поэтому взяты показатели соотношения количества лидов (заказов) к суммам закрытых сделок – продаж. Все значения показателей «Лиды и Средний чек» сегментированные не только по менеджерам, но и отдельно по источнику для поиска и привлечения клиентов:

2. Второй отчет расположен в таблице на этом же листе под названием «Данные». В нем представлена общая информация о прохождении всех этапов воронки продаж. Благодаря датам мы можем рассчитать сколько дней ушло на каждый этап воронки начиная от даты старта проекта проведения рекламной компании. Но благодаря визуализации данных на дашборде этот показатель мы можем наглядно увидеть и более эффективно с комфортом проанализировать чтобы сделать правильные выводы для быстрого принятия правильного решения. Также здесь представлены относительные показатели отказов, что важно при анализе конверсии воронки продаж в Excel:

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

Обработка исходных данных для визуализации показателей на диаграммах

На втором листе «Обработка» дашборда находятся формулы с расчетами. Все наименования показателей взяты с первого листа «Данные» с помощью ссылок:

Благодаря вешним ссылкам в обработке на исходные данные первого листа можно использовать данный дашборд в качестве шаблона. Достаточно всего лишь изменить наименования с показателями в исходных данных, и шаблон автоматически обновит все значения на главном листе «Дашборд».

Далее переходим непосредственно к анализу устройства самого дашборда и его визуализации данных изначально представленных в табличном виде.

Создание дашборда для анализа конверсии клиентов в лиды и продажи

Дашборд для анализа конверсии воронки продаж по менеджерам, состоит из 7+1 блоков. Почему +1 узнаете в конце описания визуализации. Дашборд обладает не только динамическими диаграммами и графиками, а также интерактивными возможностями. Благодаря ним мы можем исключать по отдельности менеджеров из отчета, чтобы анализировать на сколько изменится общая картина. Или посмотреть эти же показатели по каждому менеджеру по отдельности чтобы сравнить с общими результатами выполненной работы:

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

Как видно по формуле, указанной выше на рисунке для вычисления данного показателя аргументы ссылаются внешними ссылками на оба листа: «Данные» и «Обработка».

Анализ прибыльности источников привлечения клиентов в Excel

Второй блок сверху по средние показывает на кольцевой диаграмме какую процентную долю в объеме продаж в деньгах составляет каждый источник привлечения клиентов:

Формулы вычисления данных для этой диаграммы находятся на листе «Обработка» в диапазоне ячеек H3:I6. По этой визуализации мы сразу ориентируемся не только в том какой сегмент оказался наиболее прибыльным, а и насколько существенные отличия по отношению к другим сегментам.

Также обратите внимание что третий менеджер «Леонид Михель» теперь содержит только нулевые значения в таблице, так как он был выключен на дашборде. Об этом свидетельствует значение ЛОЖЬ в ячейке D3 в строке с заголовком «Включен». На этом построен принцип функционирования интерактивности дашборда при переключении пользователем кнопок на главном листе. А после от этой таблицы играет вся общая картина отчета, потому что большинство формул связанны между собой ссылками. Это правильный подход при создании шаблона отчета.

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

Анализ сроков конверсии на каждом этапе воронки продаж в Excel

Следующий блок 3 «Этапы воронки продаж в днях» показывает сколько в среднем потребовалось дней на каждый этап:

1. Поиск клиентов.
2. Презентация предложения.
3. Проведение переговоров перед заключением сделки.
4. Закрытие сделки – факт продажи.

Для построения данной визуализации была использована нормированная линейчатая гистограмма с накоплением на основе значений из диапазона T6:W9 как показано выше на рисунке. Также обратите внимание что столбец «Леонид Михель» пуст в диапазоне ячеек P2:P9. В этой таблице N1:R9 находятся промежуточные формулы выборки необходимых значений из листа «Данные». Далее в диапазоне ячеек T2:T5 с помощью функции СМЕЩЬ выполняется сборка значений в один столбец выбранных через одну строку (с парными номерами). А после диапазон T2:T5 транспонирован функцией массива {CTRL+SHIFT+Enter} ТРАНСП в диапазон T6:W9, для построения нормированной линейчатой гистограммы с накоплением.

Конверсия количества лидов в объем продаж по менеджерам

4-й блок в центре «Продажи и Лиды» отображает уровни продаж и количества лидов на разных осях вертикальных осях Y. Все уровни представлены попарно для каждого источника привлечения клиентов. Таким образом можно проанализировать взаимосвязь роста количества лидов и размера продаж. Составить такой график получилось за счет наложения двух гистограмм с группировкой. На верхнем слое график с прозрачным фоном. Подписи осей и оформление легенды также построены из фигур и надписей для более эстетичного вида:

Значения для визуализации графики берут из диапазонов I3:I6 и J3:J6 на листе «Обработка».

Динамический график рейтинга менеджеров по объемам продаж

В нижнем правом углу находится 5-й блок «Рейтинг менеджеров». Это обычная линейчатая гистограмма с группировкой отображающая объемы продаж для каждого менеджера. Ее единственная особенность в том, что она постоянно сортирует подписи оси X и свои показатели по убыванию благодаря использованию функции НАИБОЛЬШИЙ в источнике ее данных на листе «Обработка»:

Диапазон ячеек данных для линейчатой гистограммы это A21:E22. Две строки этого диапазона заполнены двумя разными формулами. Рассматривать формулы следует сначала из второй строки данного диапазона ячеек:

• Во второй строке формула из комбинации функций НАИБОЛЬШИЙ и СТОЛБЕЦ, которые позволяют сортировать значение итоговой строки первой таблице на этом листе в диапазоне B11:F11:
• В первой строке формула из комбинации функций ИНДЕКС и ПОИСКОПЗ для выборки имен и фамилий менеджеров из первой таблицы по значениям второй строки данного диапазона. Таким образом порядок заполнения ячеек первой строки именами и фамилиями менеджеров заполняются в соответствии с сортировкой по убыванию их показателей объемов продаж:

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

Процент отказов на всех уровнях воронки продаж при анализе конверсии

Внизу по средние находится 6-й блок «Процент отказов». Он представляет собой умную таблицу с тепловой картой, созданной с помощью условного форматирования ячеек в Excel:

Сначала с помощью формулы из внешних ссылок и функций массива ТРАСП, заполняется заголовки строк в столбце «Менеджеры» значениями из диапазона ячеек A21:E21 листа «Обработка».

А затем на основе этих данных выполняется выборка всех необходимых значений из диапазона B13:F19 для заполнения табличной части.

Динамическая воронка продаж в Excel

Важный момент! На данном блоке имеется переключатель (Option Button) «Таблица/Воронка» С помощью него мы можем активировать и включить скрытый еще +1 блок «Средний показатель». В результате чего вместо таблицы шестого блока будет отображаться диаграмма воронки продаж с усредненными значениями размера доли на каждом этапе воронки:

Диаграмма воронки продаж построена из комбинации нормированной гистограммы с накоплением и фигур нарисованных и объединенных в программе MS PowerPoint:

Просто для каждого ряда гистограммы следует скопировать соответственную фигуру и вставить ее прямо в ряд CTRL+V. Фигуры прилагаются на листе «Обработка» в файле Excel с примером шаблона данного дашборда, который можно скачать по ссылке в конце статьи.

Для функционирования переключателя и скрытия диаграммы воронки продаж используется следующий код макроса:

Sub Voronka()
Dim list As Worksheet
Dim opt1 As Shape
Dim charvoronka As ChartObjects
 
Set list = Sheets("Дашборд")
Set manag1 = list.Shapes("Option Button 8")
'Set charvoronka = list.ChartObjects("Диаграмма 32")
 
 
If manag1.OLEFormat.Object.Value = 1 = True Then
list.ChartObjects("Диаграмма 32").Visible = msoFalse
Else
list.ChartObjects("Диаграмма 32").Visible = msoTrue
End If
End Sub

Чтобы добавить сам переключатель выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель».

Интерактивные кнопки управления дашбордом и его диаграммами

И наконец седьмой блок «Топ 5 Менеджеров» представляет собой панель управления дашбордом из 5-ти кнопок:

Кнопки сложены из фигур и надписей с внешними ссылками на соответственные значения ячеек листа «Обработка». Кроме фигур в конструкцию скрыто (с прозрачным фоном) включены элементы управления «Флажок» (Check Box). Каждому элементу присвоен код макроса, который при нажатии изменяет цвет шрифтов надписей и заливок фигур. Кроме того, в свойствах настроек флажка указывается связь с ячейкой первой таблицы на листе «Обработка» куда флажок отправляет ключевое значение ИСТИНА или ЛОЖЬ.

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

Скачать дашборд конверсии воронки продаж в Excel

Данный дашборд можно использовать в качестве шаблона для Ваших показателей. Но не только показатели, а и названия наименований можно изменять на первом листе «Данные» и в результате они будут изменены на всех остальных листах.