Создание ложных заказов в эксель
Представим ситуацию: отдел маркетинга уже начал готовиться к новому году (кстати, 1 сентября, уже пора!), придумал концепцию поздравления, три варианта подарка и запросил у всех сотрудников список партнеров с указанием, кому какой подарок дарить.
Наша же задача, собрать эти данные, внести в одну общую таблицу, в одинаковом виде, чтобы с данными потом было удобно работать. Как лучше всего это сделать? Рассмотрим несколько вариантов:
- Онлайн таблица
- Рассылка шаблона и сбор ответных данных с помощью Query (Получить данные)
- Формы
- Автоматизация (сервис Flow)
Вариант первый - онлайн таблица.
Создаем шаблон для ввода данных на листе, оформляем его, как таблицу (Ctrl+T) - это значительно упростит ввод и обработку данных. Если делаете, как в примере выше, нужно отметить галочкой, что в таблице уже есть заголовки (My table has headers).
Теперь данные в таблицу добавляются легко - просто нужно начать писать на следующей, после окончания таблице строчке, и они автоматом добавятся в таблицу. Кстати, после ввода данных в первую ячейку вместо Enter можно нажать Tab, и курсор переместится в следующую ячейку.
Если сотрудников и партнеров немного, то можно так и оставить, и добавить остальных вручную. Если их больше 10-20, это уже займет достаточно много времени, и лучше пусть сотрудники сами заполняют.
Чтобы не было путаницы в вставке данных, можно сделать выпадающие списки и проверку данных. Сделаем выпадающий список на виды подарков, важность подарка, (как сделать выпадающий список я написал здесь: раз , два , три ). Также пометим строки, в которых не вся информация указана красным: выбираем всю таблицу (это можно сделать, поместив курсор в левый верхний угол, так, чтобы он поменял свою форму на стрелку вправо-вниз и нажав левую кнопку мыши - см. галерею ниже), затем создать новое правило условного форматирования: если хотя бы одно поле пустое, то пометить применить формат "красная заливка".
Если автоформатирование применить ко всей таблице, то при расширении таблицы на новые строки к ним также будет применено условное форматирование.
Осталось дело за малым - выложить таблицу на сервер SharePoint или SharePoint Online или OneDrive или OneDrive Business и предоставить доступ на редактирования всем сотрудникам.
После дедлайна таблицу можно "суммировать" с помощью сводной таблицы и предоставить отделу маркетинга готовые данные.
Плюсы : просто, быстро, все сотрудники будут заносить данные самостоятельно.
Минусы : нельзя разграничить права, сотрудники могут случайно или намеренно изменить/удалить внесенные ранее данные.
Рассылка шаблона и сбор данных обратно по почте.
Получив ответные письма, вы можете сохранить приложенные файлы самостоятельно в одну папку, или настроить правила ( потребуется макрос или сервис автоматизации Flow ). В итоге, вы должны получить папку, в которой лежат файлы с названием imya_familiya.xlsx (или любым другим). Создаем правило сбора данных. По шагам (см. также галерею):
Если вы продаете онлайн-сервис, вам, наверное, хотелось бы видеть, что происходит на каждом этапе воронки продаж. Из анализа воронки можно сделать важные выводы: насколько понятен и удобен процесс установки и начальной настройки приложения, как много и какие клиенты становятся активными пользователями сервиса, какой процент переходит с бесплатной версии на платную. Кроме того, по динамике коэффициентов конверсии можно делать вывод об эффективности принимаемых мер для увеличения продаж.
Постановка задачи
Таким образом, мы имеем следующую воронку продаж:
Требуется показать количество клиентов на каждом уровне воронки, а так же посчитать коэффициент конверсии для каждого уровня, с разбивкой по неделям и месяцам. Исходные данные лежат в MySQL, отчеты должны строиться автоматически нажатием нескольких кнопок, а так же позволять, при необходимости, строить разрезы по различным категориям и вводить фильтры без дополнительного программинга.
Загружаем исходные данные из БД
Для загрузки данных из БД в Excel нам понадобится ODBC-драйвер. В нашем случае будем использовать коннектор ODBC-MySQL для Windows. На маке с коннектором у нас что-то не сложилось, но возможно это уже поправили в новых версиях.
После установки драйвера создаем пустую книгу Excel, открываем вкладку «Данные» — «Из других источников» — «Из Microsoft Query»
Затем выбираем «Новый источник данных», вводим название подключения, выбираем драйвер «MySQL ODBC Driver». Затем нажимаем кнопку «Связь», вводим параметры подключения к нашей БД, кликаем «ОК». После этого, если подключение успешно установлено, Microsoft Query предложит пошаговый мастер создания запросов. Закрываем все всплывающие окна с отказом, а затем жмем «SQL» и вводим наш SQL-запрос, который выдаст исходную таблицу, вручную. Наш запрос просто делает выборку из таблицы подключенных клиентов со slave-сервера БД.
- created — дата регистрации клиента
- name — URL сайта
- was_installed — 1 если клиент устанавливал виджет на свой сайт, 0 если никогда не устанавливал
- chats_count — количество диалогов, состоявшихся с помощью нашего сервиса
- is_paid — 0, если клиент нам ничего не платил, 1 — если платил
У этой упорядоченной таблицы есть ряд полезных свойств, которыми мы впоследствии воспользуемся.
Создаем сводную таблицу
Чтобы большой исходный массив данных превратился в удобные и красивые отчеты, мы воспользуемся сводными таблицами. Кликаем на самую верхнюю-левую ячейку таблицы с исходными данными (ячейка А1) затем «Вставка» – «Сводная таблица» – «ОК». Таким образом, исходным массивом для сводной таблицы будет весь результат запроса в MySQL, при добавлении новых столбцов и строк сводная таблица обновится автоматом.
Пустая сводная таблица выглядит так:
Считаем количество клиентов на каждом этапе продаж
Прежде, чем формировать такой отчет, нам для каждой строки в исходной таблице нужно добавить номер недели и год, в котором клиент был подключен. Это нужно, чтобы группировать данные по году и неделе. Для этого открываем лист с исходными данными, листаем вправо до последнего столбца, кликаем на ячейку справа от заголовка последнего столбца, и там пишем «Неделя подключения». В таблицу добавился новый столбец с пустыми значениями в строках. Теперь в ячейке под заголовком нового столбца пишем формулу «=НОМНЕДЕЛИ(» и кликаем по ячейке в этой строке, в которой у нас указана дата подключения клиента.
В этом случае формула будет выглядеть как «=НОМНЕДЕЛИ([@created];21)». Если ячейка находится в той же строке, что и формула, умный Excel формирует ссылку на нее по названию столбца, а так же автоматически заполняет все строки таблицы этой формулой. При добавлении строк в таблицу исходных данных новые вычисляемые ячейки будут добавлены автоматически. Удобно, Экзелю респект :). Обратите внимание, что есть разные алгоритмы вычисления номера недели. Для себя мы выбрали схему №21.
Аналогично добавляем столбец «Год подключения» с формулой «=ГОД([@created])». После этого переходим на лист с нашей сводной таблицей, кликаем по сводной таблице правой кнопкой – «Обновить», чтобы таблица узнала про новые столбцы в исходных данных.
Разумеется, эти столбцы можно было бы добавить в исходные данные средствами SQL, но в Экзеле это как-то быстрее и приятнее. Хотя, это конечно дело вкуса :)
Теперь перетаскиваем столбцы «Год подключения» и «Неделя подключения» из списка полей в область «Названия строк», а поле «name» (у нас в этом поле хранится URL сайта) в область «Значения».
Мы получим аккуратную таблицу, в которой по неделям года разбито количество подключившихся клиентов. Мы перетащили поле «name» в область значений, чтобы Экзель посчитал количество элементов в этом столбце (т.е. все элементы), с группировкой по неделям и годам. Это будет число регистраций (второй этап воронки).
Посчитаем, сколько из зарегистрировавшихся в каждую неделю клиентов установили на сайт наш чат. Для этого перетащим поле «was_installed» в область значений. В этом поле в исходных данных стоит «0», если виджет не установлен, и «1», если установлен. Затем правый клик – «Параметры полей значений» — выбираем операцию «Сумма». Теперь в сводной таблице появился второй столбец, в котором мы видим, сколько из клиентов, зарегистрировавшихся в какую-либо неделю, установили виджет на сайте.
Теперь посчитаем активных клиентов. Активными будем считать тех, у которого состоялось более 20 диалогов с посетителями сайта. Для этого нам понадобится в таблицу исходных данных добавить столбец «is_active» c формулой ячеек «=ЕСЛИ([@[chats_count]]>20;1;0)». В столбце «chats_count» у нас количество чатов клиента. В результате в столбце «is_active» у нас будет «1», если у клиента более 20 чатов. Теперь поле is_active можно так же перетащить в область значений.
Добавив немного феншуя в виде гистограмм и переименовав столбцы, получаем вот такую табличку:
Вот мы уже получили симпатичную статистику, которая к тому же автоматически обновляется из базы данных. Для обновления данных, надо сначала зайти на лист с исходными данными, там правый клик по таблице – «Обновить». А затем правый клик по сводной таблице – «Обновить».
Считаем коэффициенты конверсии
Чтобы посчитать k0, надо взять данные по уникальным посетителям из гугл аналитики, и это мы оставим за рамками настоящего мануала (эту задачу, кстати, мы пока решаем копипастом из гугл аналитики).
Тут есть один не совсем красивый момент, который мы не нашли, как решить прямым образом: в таблицу исходных данных надо добавить столбец «one» с формулой «=1» — чтобы во всех ячейчах исходной таблицы появилась единица в этом столбце.
Теперь можно добавить такое вычисляемое поле:
Имя пишем «k1», в формуле указываем «=СУММ(was_installed)/СУММ(one)».
Если в сводной таблице отчет будет сгруппирован по неделям, то мы получим отношение количества клиентов, установивших виджет (СУММ(was_installed)), к общему количеству клиентов, зарегистрировавшихся в эту неделю (СУММ(one)). Если отчет будет сгруппирован по месяцам, то коэффициент будет пересчитан соответственно. Важно отметить, что конверсия показывает то, какая доля клиентов установила чат на своем сайте среди тех, кто зарегистрировался в определенную неделю. Т.е. если клиент зарегистрировался на четвертой неделе, и установил чат на сайте только на 10-й неделе, то изменится цифра в отчете за 4-ю неделю.
Теперь считаем конверсию из установленных в активных клиентов:
k2 = СУММ(is_active)/СУММ(was_installed)
Точно так же добавляем поле для конверсии из активных клиентов в платные:
k3 = СУММ(is_paid)/СУММ(is_active)
Только k3 на скриншотах показать не можем, коммерческая тайна :)
Теперь в нашей сводной таблице появились поля k1, k2, k3, которые можно перетащить в область значений. Добавив немного феншуя, получаем такую таблицу по воронке с разбивкой по неделям:
Из нее уже можно делать некоторые выводы, однако вопросы бизнес-аналитики мы оставим на другой пост, сейчас нас интересуют технические моменты.
Воронка продаж по месяцам
Из недельного отчета сделать отчет по месяцам очень просто. В исходные данные добавляем столбец «Месяц подключения» с формулой «=МЕСЯЦ([@created])», кликаем правой по сводной таблице – «обновить» и перетаскиваем в сводной таблице поле «Месяц подключения» в область «Названия строк» (после поля «Год подключения»). Получится примерно так:
И вот красивая табличка по месяцам:
Другие варианты отчетов
Если вы еще не знакомы со сводными таблицами, предлагаю вам поиграться с ними самостоятельно. Это отличный инструмент аналитики, который погает выявить интересные зависимости. Например, интересно посмотреть конверсию на разных этапах в разрезе источника клиентов (рекламных кампаний). Для этого мы сохраняем в базе метки UTM при регистрации каждого клиента, и строим отчет по эффективности разных рекламных кампаний в абсолютных (рубли) и относительных (конверсия) единицах.
Кстати, двойной клик на каждую ячейку сводной таблицы открывает список строк исходных данных, которые были использованы для вычисления данной цифры. Очень удобно, чтобы разобраться, откуда что растет.
В сводных таблицах есть еще множество фич и инструментов, которые позволяют быстро получать интересные отчеты. Настоятельно рекомендую всем предпринимателям, которые хотят быть в курсе процессов, происходящих в их бизнесе, освоить эти инструменты.
Доброго дня местным форумчанам!
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Доброго дня местным форумчанам!
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Виолин
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Автор - Виолин
Дата добавления - 29.07.2014 в 18:14
В свое время возникла потребность добавить несколько функций к обычной таблице, дабы было удобнее контролировать процесс изготовления заказов и следить за расходами. Уже после добавления первой упрощенной функции было понятно, что на этом будет сложно остановиться и через пару месяцев постоянных дополнений ту первоначальную табличку с клиентами было совсем уж и не узнать.
В этом примере я постараюсь объяснить, что да как работает в моей “программе” в силу своих возможностей (я не являюсь программистом)
Сначала объясню, что вообще хотелось получить:
1) Нужна была таблица, в которой можно было бы вести учет клиентов, заказов (с возможностью отслеживать стадии от заготовки до отправки), денежных средств (как долги у клиентов, так и общая прибыль по клиентам)
2) На первой странице нужно получить список всех клиентов и тут же увидеть есть ли у них заказы или же денежные долги, а также кол-во материала, требуемое для изготовления заказа.
3) У каждого клиента должен быть свой собственный лист, на котором можно получить детальную информацию по заказам, а также должны быть формы, которые позволяют быстро добавлять информацию о новом заказе, о статусе выполнения заказа и др. вспомогательные элементы. Плюс необходимо иметь возможность быстро ориентироваться в данных таблицы для чего была применена цветовая схема, которая закрашивала строки с разными статусами в определенные цвета.
Посмотреть видео работы таблицы можно тут.
Для корректной работы примера нужно включить вот эти библиотеки в VBA (зайти в Tools / references и поставить галочки):
В свое время возникла потребность добавить несколько функций к обычной таблице, дабы было удобнее контролировать процесс изготовления заказов и следить за расходами. Уже после добавления первой упрощенной функции было понятно, что на этом будет сложно остановиться и через пару месяцев постоянных дополнений ту первоначальную табличку с клиентами было совсем уж и не узнать.
В этом примере я постараюсь объяснить, что да как работает в моей “программе” в силу своих возможностей (я не являюсь программистом)
Сначала объясню, что вообще хотелось получить:
1) Нужна была таблица, в которой можно было бы вести учет клиентов, заказов (с возможностью отслеживать стадии от заготовки до отправки), денежных средств (как долги у клиентов, так и общая прибыль по клиентам)
2) На первой странице нужно получить список всех клиентов и тут же увидеть есть ли у них заказы или же денежные долги, а также кол-во материала, требуемое для изготовления заказа.
3) У каждого клиента должен быть свой собственный лист, на котором можно получить детальную информацию по заказам, а также должны быть формы, которые позволяют быстро добавлять информацию о новом заказе, о статусе выполнения заказа и др. вспомогательные элементы. Плюс необходимо иметь возможность быстро ориентироваться в данных таблицы для чего была применена цветовая схема, которая закрашивала строки с разными статусами в определенные цвета.
Посмотреть видео работы таблицы можно тут.
Для корректной работы примера нужно включить вот эти библиотеки в VBA (зайти в Tools / references и поставить галочки):
Посмотреть видео работы таблицы можно тут.
Для корректной работы примера нужно включить вот эти библиотеки в VBA (зайти в Tools / references и поставить галочки):
_________________ Автор - Antero
Дата добавления - 22.04.2017 в 00:42
За 2-3 года работы у Вас может накопиться несколько десятков постоянных клиентов и заказчиков, кому нужны Ваши услуги время от времени. Кроме них, накопится огромное количество заказчиков, которые обращались за услугами 1-2 раза, и в идеале с ними необходимо поддерживать контакт и постараться перевести в категорию постоянных клиентов.
Решить эти задачи можно при помощи ведения базы клиентов. Для этого существуют различные CRM, но как правило, они платные. Бесплатный вариант – создать и вести базу клиентов в Excel. Давайте посмотрим, как может формироваться база клиентов в данной программе.
В статье рассмотрим два варианта ведения базы - простой и сложный, с большим числом полей и функций.
База клиентов в Excel (простой вариант)
Специально для фрилансеров мы сделали бесплатную программу для ведения базы клиентов в Excel. В принципе, она универсальна и при небольшой адаптации может использоваться в торговых или сервисных компаниях с небольшим числом клиентов. Ниже будут комментарии, как с ней работать.
Лист «Мои услуги» – представляет список, в который можно включить до 10 услуг. Услуги из этого списка Вы сможете выбрать при добавлении информации о клиенте в базу данных.
Лист «Клиенты» – база клиентов, с которыми Вы работаете или работали. База включает следующую информацию:
- Порядковый номер клиента. Позволяет понять, насколько велико число Ваших клиентов.
- Имя клиента – можно вводить имя или ФИО, а также название компании
- Телефон
- Что заказывает – поле заполняется путем выбора услуги из выпадающего списка. Если клиент заказывает несколько услуг, можно выбрать из списка основную, а другие указать в комментариях.
- Комментарий – описание клиента в свободной форме, особенности работы с заказчиком.
- Дата первого заказа – дата получения первого заказа. Позволяет понять, насколько долго Вы уже работаете с клиентом.
- Дата последнего заказа – важный параметр, позволяет отследить последнюю продажу клиенту. Например, Вы можете отсортировать клиентов по дате последнего заказа и посмотреть, кто из клиентов давно ничего не заказывал – написать им, напомнить о себе и, возможно, получить новый заказ.
По каждому полю список клиентов можно сортировать. Например, сделать сортировку по типам заказываемых услуг, чтобы понять, кто из клиентов покупает «копирайтинг» и сделать им специальное предложение на написание текстов (если Вы решили сделать таковое).
При желании количество полей в базе клиентов в Excel можно дополнять, но на мой взгляд, слишком перегружать таблицу не стоит.
Как работать с простой базой клиентов в Excel?
- Добавляйте в базу всех новых клиентов, которые оформили реальный заказ (т.е. тех, кто просто позвонил или один раз что-то написал, но не купил – добавлять не нужно);
- Раз в полгода отслеживайте клиентов, которые давно не делали заказы. Напишите им, напомните о себе. Чаще, чем раз в полгода, писать не стоит – иначе Вы рискуете слишком надоесть клиенту. Но это верно только для фрилансеров, в каких-то сферах стоит чаще напоминать о себе :)
- Если Вы чувствуете спад в количестве заказов, сделайте клиентам специальное предложение. Например, сделайте скидку на копирайтинг и напишите постоянным клиентам, кто заказывает тексты, о снижении цен.
- Используйте столбец с комментариями, чтобы указать особенности каждого клиента, которые помогут Вам эффективно работать с заказчиком. Например, каким-то заказчикам нужно помочь с составлением технического задания – отметьте это в комментариях, чтобы не забыть помочь с ТЗ.
База клиентов в Excel (расширенный вариант)
В расширенном варианте базы у каждого клиента можно указать дополнительные сведения:
- Канал привлечения – источник получения клиента. Список источников можно отредактировать на листе «Каналы привлечения». Допускается указывать до 20 каналов.
- Статус – активный или не активный. По умолчанию ставьте всем клиентам активный статус. Ниже я расскажу, в каких случаях его нужно менять на не активный.
В расширенной базе имеется функция отслеживания клиентов, которым нужно напомнить о своих услугах. Если у активного клиента с момента последнего заказа прошло более 6 месяцев, в столбике «Пора звонить» ячейка станет красной. В примере выше Вы можете увидеть такую ячейку у клиента №1. В этом случае рекомендую написать клиенту и напомнить о себе.
Если Вы получили новый заказ от такого клиента, укажите дату нового заказ в столбике «Дата последнего заказа». Если клиент ничего не ответит, переводите его в не активный статус. По не активным клиентам система не делает напоминаний.
Резюме
Программа Excel позволяет создать еще более сложные и интересные базы клиентов. В наших примерах достаточно простые варианты, но именно из-за простоты они позволяют не тратить много времени на ведение базы клиентов, а с другой – помогают поддерживать отношения с клиентами и получать больше заказов.
Если у Вас есть предложения по доработке шаблонов, представленных в статье, пишите в комментариях.
Дополнительные материалы
Что такое CRM-система?
Обзор бесплатных и платных CRM, помогающих вести проекты и отслеживать важные задачи.
Как планировать рабочее время и вести учет дел в Excel?
Бесплатная программа в Excel, которая поможет вам привести дела в порядок.
Читайте также: