Как сделать воронку продаж в excel
Воронка продаж в Excel
Наверняка Вы много раз видели такой инструмент как воронка продаж и интересовались как сделать воронку продаж в Excel.
Мы рассмотрим несколько способов построения воронки в зависимости от конкретных целей.
Способ 1. Фиксированная высота секции.
Предположим у нас есть таблица с данными, где каждому уровню воронки (т.е. этапу продаж) ставится в соответствие её значение (т.е. количество продаж на данном этапе):
Для построения воронки продаж создадим вспомогательную таблицу с видоизмененными данными:
Построение вспомогательной таблицы
Расшифровка формул вспомогательной таблицы
Создание линейчатой диаграммы с накоплением
Скрытие ряда данных диаграммы
Форматирование осей диаграммы
Удаление зазора и добавление подписей данных
Окончательный вид воронки продаж (способ 1)
Способ 2. Плавающая высота секции.
Выделяем диапазон данных и строим пирамидальную гистограмму с накоплением. Для этого переходим в панели вкладок на Вставка -> Диаграмма -> Гистограмма -> Пирамидальная с накоплением:
Создание пирамидальной гистограммы с накоплением
Изменение значений строк и столбцов местами
Преобразование объемной диаграммы в плоскую
Форматирование осей диаграммы
Окончательный вид воронки продаж (способ 2)
Как построить воронку продаж в Excel
Чтобы лучше понимать, на каком этапе клиенты теряют интерес к вашему товару, нужно составить воронку продаж. Проще всего это сделать в Excel.
Раньше мы уже рассказывали, что такое воронка продаж и для чего она нужна, а теперь поговорим, как ее создавать. Для работы с этим маркетинговым инструментом можно использовать специальные программы и приложения. Но самый легкий и бюджетный способ — построить ее в Excel.
Заносим данные в таблицу
Допустим, вы запустили таргетированную рекламу во «ВКонтакте» и хотите посчитать конверсию. Первое, что нужно сделать, — занести в таблицу все этапы продаж и присвоить каждому свое значение.
Считаем конверсию каждого этапа
Потом идем в столбец C и считаем в нем конверсию каждого этапа. В случае с показами и переходами это будет кликабельность или конверсия (CTR). Чтобы узнать, чему она равна, нужно применить формулу:
CTR = количество кликов / количество показов * 100%
По той же формуле рассчитываем показатели для остальных этапов.
Строим шаблон
Дальше открываем меню «Вставка» и выбираем в ней команду «Вставить рисунок SmartArt». Из предложенных шаблонов мы выберем инвертированную пирамиду. Хотя при желании можно использовать более оригинальные шаблоны, к примеру объемную воронку.
После этого на экране появится такая фигура:
Каждый уровень пирамиды соответствует этапу продаж. Для начала подписываем каждый из них. Изначально в шаблон добавлено только три блока, если нужно больше — напишите их название в поле «Добавить текст».
Кроме названия шагов нам нужно отразить на картинке показатели конверсии. Это делается при помощи кнопки «Добавить маркер» во вкладке «Конструктор».
Объединяем ячейки
Теперь нам нужно объединить вместе данные из ячеек в столбце A и B. Это делается так:
- ставим знак «=» в любой свободной области;
- кликаем на А1;
- вводим комбинацию &” — “&;
- кликаем на B1.
В итоге должна получиться ячейка с текстом: «Показы рекламы — 1000». Чтобы применить формулу ко всем этапам, кликните по ячейке и при помощи крестика протяните ее вниз.
Преобразуем объект SmartArt
Чтобы быстро обновлять показатели в воронке продаж, нужно связать между собой данные из объединенных ячеек и саму пирамиду. Элементы SmartArt статичны, а значит, не подходят для этого. Чтобы работать дальше, нужно преобразовать нашу фигуру в обычную во вкладке «Сброс».
Иногда из-за проблем с параметрами Excel на панели может не оказаться кнопки для преобразования. На этот случай есть еще один лайфхак для преобразования SmartArt. Чтобы связать фигуру с формулой, выполните следующие действия:
- кликните по объекту SmartArt;
- нажмите Ctrl+А, чтобы выделить все части;
- скопируйте пирамиду клавишами Ctrl+C;
- нажмите Ctrl+V, чтобы вставить фигуру;
- удалите старую диаграмму.
Создаем динамическую фигуру
Теперь можно связывать новую диаграмму и данные о продажах. Делается это так:
- кликните по одной из частей диаграммы и выделите в ней весь текст;
- перейдите в строку формулы и поставьте знак «=»;
- кликните по нужной ячейке с данными.
В нашем случае самую широкую часть пирамиды нужно связать с ячейкой А7, а следующую за ней — с А8. И так по порядку.
По такому же принципу нужно подставить значения конверсии, которые внесены в столбец С.
Когда все действия выполнены правильно, данные на графике будут меняться автоматически. Если через пару дней число заявок или заказов вырастет, вам не придется перестраивать саму воронку, достаточно будет внести в таблицу новые числа.
Меняем цвет и шрифты
Оформить график по своему вкусу можно сразу при добавлении шаблона или в самом конце. Цвет, контур и стили фигур меняются в разделе «Формат», а настройки шрифта — в главном меню.
Заключение
Мы рассмотрели самый простой способ создания воронки продаж, который подходит скорее для внутренней работы. Если вы хотите использовать диаграмму в докладе или презентации, стоит поискать более интересные варианты и уделить больше внимания оформлению. Подробнее о работе с таблицами и возможностями, которые они дают, можно узнать на курсе «Excel Базовый» + «Excel Эксперт».
- Живая обратная связь с преподавателями
- Неограниченный доступ к материалам курса
- Стажировка в компаниях-партнёрах
- Дипломный проект от реального заказчика
- Гарантия трудоустройства в компании-партнёры для выпускников, защитивших дипломные работы
Воронка продаж наглядно показывает, какие сложности испытывает клиент на каждом этапе взаимодействия с продуктом. Маркетологу нужно не просто научиться строить сам график — гораздо важнее делать на его основе правильные выводы и находить решения.
На курсе «Интернет-маркетолог от Ingate» вы узнаете, как правильно строить воронку продаж и анализировать ее данные.
Создание воронки продаж в Excel
Вариант 1. Использование встроенного графика “Воронка”
Этим способом могут воспользоваться те, у кого установлен Excel 2016 версии (или оформлена подписка Office 365). Также у пользователей вашим графиком должна быть версия не ниже 2016 года. Иначе вместо воронки они увидят пустое пространство. Если есть сомнения – переходите к следующему способу.
Первое, что нужно сделать – подготовить сходные данные. К примеру возьмем продажи квартир, коттеджей.
Выделим диапазон A3:B7 перейдем на вкладку Вставка -> Рекомендуемые диаграммы -> Воронка. Сохраняем выбор нажав кнопку OK.
Воронка продаж готова, осталось по своему вкусу отформатировать ее.
Вариант 2. Использование гистограммы для создания воронки
Этот вариант уже универсальный и годится для всех версий Excel. Идея способа заключается в построении гистограммы с накоплением и промежуточным рядом для сдвига, который мы сделаем бесцветным.
В качестве примера буем использовать те же данные, что и в способе 1. Однако добавим дополнительно столбец Отступ (чуть позже будет понятно для чего).
Выберем диапазон A3:C7 и вставим линейчатую диаграмму с накоплением (Вставка -> Все диаграммы).
Перевернем воронку на 180 градусов. Для этого выберите ось Y и кликните по ней правой кнопкой мыши. Выберите команду Формат оси.
В группе Положение оси отметьте пункт Обратный порядок категорий . Кстати ось X нам не понадобится, можно смело выделить ее и удалить. С легендой можно поступить аналогично.
На данном этапе уже прорисовывается воронка, однако, для большей красоты, не хватает отступов от оси Y. Давайте их добавим. Для этого в столбце Отступ “протянем” формулу =(МАКС($C$4:$C$7)-C4)/2.
Теперь сделаем бесцветным отступы. Для этого выделим ряд Отступ (зеленый). Щелчком правой кнопки мыши откроем контекстное меню и выберем Формат ряда данных.
Воронка готова. Можно дополнительно увеличить ширину этапов воронки и сократить зазор между ними. Откроем окно формата ряда данных со значениями воронки (аналогично формату ряда Отступ). Перейдем в Параметры ряда и уменьшим Боковой зазор (к примеру до 20%)
Источники:
http://skillbox.ru/media/marketing/kak_postroit_voronku_prodazh_v_excel/
http://micro-solution.ru/excel/charts/sales-funnel
Загрузка...
