Если вы продаете онлайн-услугу, вам, вероятно, захочется посмотреть, что происходит на каждом этапе воронки продаж.
Из анализа воронки можно сделать важные выводы: насколько понятен и удобен процесс установки и первоначальной настройки приложения, сколько и какие клиенты становятся активными пользователями сервиса, какой процент переходит с бесплатной версии на бесплатную.
заплатил один.
Кроме того, исходя из динамики показателей конверсии, можно сделать вывод об эффективности принятых мер по увеличению продаж.
Ниже под катом вы найдете описание некоторых приемов работы с Excel, которые могут пригодиться при анализе наборов данных.
Расскажем, как мы ведем управленческую статистику на сервисе.
jivosite.ru с использованием сводных таблиц Excel и подключением к MySQL через ODBC на примере отчета воронки продаж.
Предлагаемый метод достаточно прост и универсален; его можно использовать для создания красивых отчетов за считанные минуты.
Постановка задачи
Вам необходимо построить отчет по воронке продаж для услуги.Это онлайн-консультант для интернет-магазинов, который продается по модели freemium. Пользователи подписываются на двухнедельный демонстрационный период, в течение которого доступна расширенная версия.
Через 2 недели бесплатная версия остаётся и продолжает работать без ограничения по времени.
Таким образом, мы имеем следующую воронку продаж: 
Требуется показать количество клиентов на каждом уровне воронки, а также рассчитать коэффициент конверсии для каждого уровня в разбивке по неделям и месяцам.
Исходные данные находятся в MySQL, отчеты должны строиться автоматически нажатием нескольких кнопок, а также позволять при необходимости строить разделы по различным категориям и вводить фильтры без дополнительного программирования.
Загрузка исходных данных из базы данных
Чтобы загрузить данные из базы данных в Excel, нам понадобится драйвер ODBC. В нашем случае мы будем использовать Коннектор ODBC-MySQL для Windows .На Маке с разъемом у нас что-то не получилось, но возможно это уже исправлено в новых версиях.
После установки драйвера создайте пустую книгу Excel, откройте вкладку «Данные» — «Из других источников» — «Из Microsoft Query».
Затем выберите «Новый источник данных», введите имя подключения, выберите драйвер «MySQL ODBC Driver».
Далее нажмите кнопку «Подключение», введите параметры подключения к нашей базе данных и нажмите «ОК».
После этого, если соединение успешно установлено, Microsoft Query предложит пошаговый мастер запросов.
Закрываем все всплывающие окна с отказом, а затем нажимаем «SQL» и вводим наш SQL-запрос, который вернет исходную таблицу, вручную.
Наш запрос просто делает выборку из таблицы подключенных клиентов с подчиненного сервера базы данных.
В нашей исходной таблице мы будем использовать следующие столбцы:
- создано — дата регистрации клиента
- имя — URL сайта
- was_installed — 1, если клиент установил виджет на свой сайт, 0, если он его никогда не устанавливал
- Chats_count — количество диалогов, произошедших с использованием нашего сервиса
- is_paid — 0, если клиент нам ничего не заплатил, 1 — если заплатил
Эта упорядоченная таблица имеет ряд полезных свойств, которые мы будем использовать позже.
Создание сводной таблицы
Чтобы превратить большой исходный массив данных в удобные и красивые отчеты, воспользуемся сводные таблицы .Нажмите на верхнюю левую ячейку таблицы с исходными данными (ячейка А1), затем «Вставка» — «Сводная таблица» — «ОК».
Таким образом, исходным массивом для сводной таблицы будет весь результат запроса в MySQL; при добавлении новых столбцов и строк сводная таблица будет обновляться автоматически.
Пустая сводная таблица выглядит так: 
Подсчитываем количество клиентов на каждом этапе продаж
Прежде чем формировать такой отчет, для каждой строки исходной таблицы нам необходимо добавить номер недели и год, в котором был подключен клиент. Это необходимо для группировки данных по годам и неделям.Для этого откройте лист с исходными данными, прокрутите вправо до последнего столбца, нажмите на ячейку справа от заголовка последнего столбца и напишите там «Неделя связи».
В таблицу добавлен новый столбец с пустыми значениями в строках.
Теперь в ячейке под заголовком нового столбца пишем формулу «=НОМНЕДЕЛЯ(» и кликаем по ячейке в этой строке, в которой указываем дату подключения клиента.
В этом случае формула будет выглядеть так: «=NOMWEEKS([@created];21)».
Если ячейка находится в той же строке, что и формула, умный Excel генерирует ссылку на нее по имени столбца, а также автоматически заполняет этой формулой все строки таблицы.
При добавлении строк в таблицу исходных данных новые вычисляемые ячейки будут добавляться автоматически.
Удобно, респект Xel :).
Обратите внимание, что существует разные алгоритмы расчета номера недели .
Для себя мы выбрали схему №21. Аналогичным образом добавьте столбец «Год подключения» с формулой «=ГОД([@created])».
После этого переходим на лист с нашей сводной таблицей, кликаем по сводной таблице правой кнопкой мыши – «Обновить», чтобы таблица узнала о новых столбцах в исходных данных.
Конечно, эти столбцы можно было бы добавить в исходные данные с помощью SQL, но в Xel это как-то быстрее и приятнее.
Хотя, это, конечно, дело вкуса :) Теперь перетаскиваем столбцы «Год подключения» и «Неделя подключения» из списка полей в область «Имена строк», а поле «Имя» (в этом поле мы храним URL сайта) в «Значения».
область.
Мы получим аккуратную таблицу, в которой количество подключенных клиентов разбито по неделям года.
Мы перетащили поле «имя» в область значений, чтобы Эxel подсчитывал количество элементов в этом столбце (т. е.
всех элементов), сгруппированных по неделям и годам.
Это и будет количество регистраций (второй этап воронки).
Давайте посчитаем, сколько клиентов, зарегистрировавшихся каждую неделю, установили наш чат на сайт. Для этого перетащите поле «was_installed» в область значений.
Это поле в исходных данных содержит «0», если виджет не установлен, и «1», если он установлен.
Затем щелкните правой кнопкой мыши – «Параметры поля значения» – выберите операцию «Сумма».
Теперь в сводной таблице появился второй столбец, в котором мы видим, сколько клиентов, зарегистрировавшихся за ту или иную неделю, установили виджет на сайт. 
Теперь посчитаем активных клиентов.
Активными мы будем считать тех, кто провел более 20 диалогов с посетителями сайта.
Для этого нам нужно добавить в таблицу исходных данных столбец is_active с формулой ячейки «=IF([@[chats_count]]> 20;1;0)».
В столбце «chats_count» указано количество клиентских чатов.
В результате в столбце «is_active» у нас будет «1», если у клиента более 20 чатов.
Теперь поле is_active также можно перетащить в область значений.
Добавив немного фен-шуй в виде гистограмм и переименовав столбцы, получим вот такую таблицу: 
Теперь мы уже получили приятную статистику, которая также автоматически обновляется из базы данных.
Чтобы обновить данные, необходимо сначала перейти на лист с исходными данными, там нажать правой кнопкой мыши по таблице – «Обновить».
А затем щелкните правой кнопкой мыши по сводной таблице – «Обновить».
Рассчитаем конверсию
Для расчета k0 нужно брать данные об уникальных посетителях из Google Analytics, и это мы оставим за рамками данного руководства (кстати, сейчас мы решаем эту проблему методом копипаста из Google Analytics).Начнем с расчета k1 — отношения количества людей, установивших чат на своем сайте, к общему количеству зарегистрированных клиентов.
Здесь есть один не очень приятный момент, который мы не нашли способа решить напрямую: нужно в таблицу исходных данных добавить столбец «единица» с формулой «=1», чтобы в этой таблице появилась единица измерения.
столбец во всех ячейках исходной таблицы.
Теперь вы можете добавить вычисляемое поле следующим образом: 
Пишем имя «k1», в формуле указываем «=СУММ(был_установлен)/СУММ(один)».
Если отчет сгруппировать по неделям в сводной таблице, мы получим отношение количества клиентов, установивших виджет (SUM(was_installed)) к общему количеству клиентов, зарегистрированных на этой неделе (SUM(один)).
Если отчет сгруппирован по месяцам, коэффициент будет пересчитан соответствующим образом.
Важно отметить, что конверсия показывает, какой процент клиентов установили на своем сайте чат среди тех, кто зарегистрировался на конкретной неделе.
Те.
если клиент зарегистрировался на четвертой неделе, а чат на сайте установил только на 10-й неделе, то цифра в отчете за 4-ю неделю изменится.
Теперь посчитаем конверсию из установленных клиентов в активных: k2 = СУММ(является_активным)/СУММ(был_установлен) Таким же образом добавляем поле для конверсии из активных клиентов в платных: k3 = СУММ(оплачено)/СУММ(активно) К3 на скриншотах показать не можем, это коммерческая тайна :) Теперь в нашей сводной таблице есть поля k1, k2, k3, которые можно перетаскивать в область значений.
Добавив немного фен-шуй, получим следующую таблицу воронки с разбивкой по неделям: 
Из этого уже можно сделать некоторые выводы, но вопросы бизнес-аналитики мы оставим для другого поста; теперь нас интересуют технические аспекты.
Воронка продаж по месяцам
Очень легко сделать ежемесячный отчет из еженедельного отчета.К исходным данным добавьте столбец «Месяц подключения» с формулой «=МЕСЯЦ([@created])», щелкните правой кнопкой мыши по сводной таблице – «обновить» и перетащите поле «Месяц подключения» в сводной таблице на область «Названия строк» (после поля «Год подключения»).
Это будет выглядеть примерно так: 
А вот красивая таблица по месяцам: 
Другие варианты отчета
Если вы еще не знакомы со сводными таблицами, я советую вам поиграть с ними самостоятельно.Это отличный инструмент аналитики, который может помочь выявить интересные зависимости.
Например, интересно посмотреть на конверсию на разных этапах в контексте источника клиентов (рекламных кампаний).
Для этого сохраняем в базе данных UTM-теги при регистрации каждого клиента и строить отчет об эффективности различных рекламных кампаний в абсолютных (рублях) и относительных (конверсия) единицах.
Кстати, двойной клик по каждой ячейке сводной таблицы открывает список строк исходных данных, которые использовались для расчета этого показателя.
Очень удобно, чтобы разобраться, откуда что-то растет. Сводные таблицы обладают множеством дополнительных функций и инструментов, позволяющих быстро получать интересные отчеты.
Я настоятельно рекомендую освоить эти инструменты всем предпринимателям, которые хотят быть в курсе процессов, происходящих в их бизнесе.
Теги: #saas #аналитика #пиар
-
Тыква
19 Oct, 24 -
Аскина. Обои
19 Oct, 24 -
Осторожно, Интервью!
19 Oct, 24 -
Скрытые Цепи Маркова, Алгоритм Витерби
19 Oct, 24
