Описание некоторых приемов работы с Excel, которые могут быть полезны при анализе массивов данных. Мы расскажем, как мы ведем управленческую статистику по сервису jivosite.ru с помощью сводных таблиц Excel и подключения к MySQL через ODBC на примере отчета по воронке продаж. Предлагаемый способ довольно прост и универсален, с его помощью можно строить красивые отчеты за считанные минуты.
Постановка задачи
Требуется построить отчет по воронке продаж для сервиса jivosite.ru. Это онлайн-консультант для интернет-магазинов, который продается по модели freemium. Пользователи подключаются с двухнедельным демо-периодом, в течение которого доступна расширенная версия. По истечении 2 недель остается бесплатная версия, которая продолжает работать без ограничения срока.
Таким образом, мы имеем следующую воронку продаж:
Требуется показать количество клиентов на каждом уровне воронки, а так же посчитать коэффициент конверсии для каждого уровня, с разбивкой по неделям и месяцам. Исходные данные лежат в 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 — если платил
Результат запроса попадет на лист Excel в виде упорядоченной таблицы.
У этой упорядоченной таблицы есть ряд полезных свойств, которыми мы впоследствии воспользуемся.
Создаем сводную таблицу
Чтобы большой исходный массив данных превратился в удобные и красивые отчеты, мы воспользуемся сводными таблицами. Кликаем на самую верхнюю-левую ячейку таблицы с исходными данными (ячейка А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, надо взять данные по уникальным посетителям из гугл аналитики, и это мы оставим за рамками настоящего мануала (эту задачу, кстати, мы пока решаем копипастом из гугл аналитики).
Начнем с подсчета k1 – отношения числа установивших чат на свой сайт к общему числу зарегистрированных клиентов.
Тут есть один не совсем красивый момент, который мы не нашли, как решить прямым образом: в таблицу исходных данных надо добавить столбец «one» с формулой «=1» — чтобы во всех ячейчах исходной таблицы появилась единица в этом столбце.
Теперь можно добавить такое вычисляемое поле:
Имя пишем «k1», в формуле указываем «=СУММ(was_installed)/СУММ(one)».
Если в сводной таблице отчет будет сгруппирован по неделям, то мы получим отношение количества клиентов, установивших виджет (СУММ(was_installed)), к общему количеству клиентов, зарегистрировавшихся в эту неделю (СУММ(one)). Если отчет будет сгруппирован по месяцам, то коэффициент будет пересчитан соответственно. Важно отметить, что конверсия показывает то, какая доля клиентов установила чат на своем сайте среди тех, кто зарегистрировался в определенную неделю. Т.е. если клиент зарегистрировался на четвертой неделе, и установил чат на сайте только на 10-й неделе, то изменится цифра в отчете за 4-ю неделю.
Теперь считаем конверсию из установленных в активных клиентов:
k2 = СУММ(is_active)/СУММ(was_installed)
Точно так же добавляем поле для конверсии из активных клиентов в платные:
k3 = СУММ(is_paid)/СУММ(is_active)
Только k3 на скриншотах показать не можем, коммерческая тайна
Автоматизация составления исполнительной документации по строительству в Excel
Теперь в нашей сводной таблице появились поля k1, k2, k3, которые можно перетащить в область значений. Добавив немного феншуя, получаем такую таблицу по воронке с разбивкой по неделям:
Из нее уже можно делать некоторые выводы, однако вопросы бизнес-аналитики мы оставим на другой пост, сейчас нас интересуют технические моменты.
Воронка продаж по месяцам
Из недельного отчета сделать отчет по месяцам очень просто. В исходные данные добавляем столбец «Месяц подключения» с формулой «=МЕСЯЦ([@created])», кликаем правой по сводной таблице – «обновить» и перетаскиваем в сводной таблице поле «Месяц подключения» в область «Названия строк» (после поля «Год подключения»). Получится примерно так:
Как не надо «усовершенствовать» чужие модели в Эксель или программист опасен своим креативом
И вот красивая табличка по месяцам:
Другие варианты отчетов
Если вы еще не знакомы со сводными таблицами, предлагаю вам поиграться с ними самостоятельно. Это отличный инструмент аналитики, который погает выявить интересные зависимости. Например, интересно посмотреть конверсию на разных этапах в разрезе источника клиентов (рекламных кампаний). Для этого мы сохраняем в базе метки UTM при регистрации каждого клиента, и строим отчет по эффективности разных рекламных кампаний в абсолютных (рубли) и относительных (конверсия) единицах.
Кстати, двойной клик на каждую ячейку сводной таблицы открывает список строк исходных данных, которые были использованы для вычисления данной цифры. Очень удобно, чтобы разобраться, откуда что растет.
В сводных таблицах есть еще множество фич и инструментов, которые позволяют быстро получать интересные отчеты. Настоятельно рекомендую всем предпринимателям, которые хотят быть в курсе процессов, происходящих в их бизнесе, освоить эти инструменты.
