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

Конверсия офисов продаж

Итак, например, мы оцениваем эффективность работы территориальных офисов продаж. Под эффективностью понимаем отношение числа совершенных продаж к числу заявок (конверсию заявок в продажи, или просто «конверсию»). То есть, если в офисе «Сокольники» за квартал было 19 продаж на 33 заявки, их эффективность будем считать равной 19/33 = 57,6%.

Очевидно, что одни офисы работают эффективнее других: конверсия по офисам меняется от 57,6% до 17,6%. Заметно также, что и число заявок в офисах различно: от 33 заявок в «Сокольниках» до 706 заявок в «Лианозово».

Обычно на этом этапе многие останавливаются, но есть несложный способ воспользоваться понятием «доверительного интервала» или «стандартного отклонения (SD)», чтобы показать то, что, на первый взгляд, не так заметно.

Оцениваем размер выборки и величину SD

Как нетрудно заметить, из-за неравного числа заявок по разным офисам («Сокольники» отличаются в этом смысле от «Лианозово» почти в 22 раза), уверенность в надежности рассчитанной конверсии будет не одинакова. Так, для «Лианозово» результат в 36,1% достигнут на выборке из 706 заявок и может считаться вполне надежным; в «Сокольниках» мы получили результат 57,6% на небольшой выборке в 33 заявки, из-за чего нет уверенности, что, получи со временем последние свои 706 заявок, они бы удержали результат на том же уровне.

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

Уже знакомая нам формула стандартного отклонения (SD), или σ:

где p — величина конверсии, n — число заявок.

Считаем в колонке E:

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

Считаем границы 90%-го доверительного интервала

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

Зная о том, что границы 90%-го доверительного интервала лежат в пределах ±1,645SD, вычитаем и прибавляем 1,645SD для нижней и верхней границ, соответственно. Для «Лианозово» получаем, что их истинная конверсия лежит в пределах от 33,1% до 39,1%. (По-прежнему, в 1 случае из 10 она выходит за границы нашего интервала, но зато в 9 случаях из 10 мы не ошиблись).

Дополняем график, рисуя «свечи»

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

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

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

См. также:

http://italylov.ru/blog/all/ctatisticheskaya-dostovernost-koltrekinga/

Честно говоря, не знаю, как правильно называется эта штука, но пусть у нее будет рабочее название «скользящее среднее».

Очень часто бывает так, что у нас есть данные с разбивкой по дням. Например, заходы на сайт или звонки в отдел продаж. И, в попытке проанализировать динамику происходящего, мы строим график, получая примерно следующее:

Что здесь не так?

Достаточно очевидно, что, во-первых, график постоянно прыгает вверх-вниз, а, во-вторых, имеет некрасивые экстремумы вроде 16 заявок в октябре или 122 заявок в августе. День на день не приходится, и данные слишком сильно колеблются вокруг среднего значения (кстати, среднее тут равно 63).

А там, где что-то так некрасиво прыгает, часто можно что-то сгладить, используя понятие скользящего среднего.

Скользящее среднее. Простой способ.

Попробуем «сгладить» наш прыгающий график путем расчета среднего числа заявок на дату, исходя из предыдущих 6 дней (7-й — текущий день, итого ровно неделя).

Напротив 07.01.2018 напишем формулу

=СРЗНАЧ(B2:B8)

Протянув формулу по всему году до самой последней строки, получим среднее число заявок на каждую дату за предыдущую неделю. Как будто рамку, шириной в одну неделю, мы двигали по году вдоль с шагом в один день.

Визуально ничего не изменилось. Разве что, раньше были целые значения, а теперь, из-за усреднения, вылезли знаки после запятой — 79,9, 84,1. Обновим наш график:

На месте прежнего, «прыгающего», графика, теперь более гладкая линия. Исчезли аномальные дни с 16 и 122 звонками в октябре и в августе, зато теперь, на «недельном» масштабе, стал заметнее провал на майские праздники.

Скользящее среднее. Сложный способ.

Некоторое время попрактиковавшись с вычислением скользящего среднего, вы обратите внимание, что, чем шире вы будете брать «окно» для расчета среднего на дату, тем сильнее будет сглаживаться ваш график. Теоретически, вы можете взять окно шириной в 365 дней... и получится практически ровная линия. А при окне шириной в 1 день — график не сглаживается вообще.

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

Попробуем не задавать жестко ширину нашего «окна», а сделать его параметром нашего графика. Пусть «окно» в 7 дней, используемое для сглаживания графика, будет зависеть от цифры «7», помещенной в ячейку C1. И пусть, если мы меняем «7» на «5» или «30», Эксель перестраивает наш график.

Итак, настало время для красивой формулы в ячейке C8:

=СРЗНАЧЕСЛИМН(B$2:B$365;A$2:A$365;"<="&A8;A$2:A$365;">="&(A8-$C$1+1))

Функция

=СРЗНАЧЕСЛИМН()

берет и считает среднее значение для тех дат, для которых будут выполняться оба условия:

1. Дата, которая участвует в расчете среднего, должна быть больше или равна дате, отстоящей от даты, для которой мы рассчитываем среднее, назад на N-1 дней (где N — ширина нашего «окна»).
2. Дата, которая участвует в расчете среднего, должна быть меньше или равна дате, для которой мы это среднее рассчитываем.

Проще говоря, для расчета среднего числа заявок на 18 апреля при N=7 дней, мы возьмем среднее от числа заявок с 12 по 18 апреля (больше или равно 12 апреля и меньше или равно 18 апреля). Для расчета среднего числа заявок на 19 апреля — среднее от числа заявок с 13 по 19 апреля, и так далее.

Выделим ячейку C1 под наш параметр N, тогда формула для 07.01.2018 выглядит так:

=СРЗНАЧЕСЛИМН(B$2:B$365;A$2:A$365;"<="&A8;A$2:A$365;">="&(A8-$C$1+1))

Итого, теперь мы имеем уже знакомый нам «сглаженный» 7-дневный график, но ширину «окна» задали не строго, а привязали ее к параметру, записанному в ячейке C1:

Меняя значение параметра в ячейке C1, получаем все более и более ровную линию. Вот, для сравнения, наложенные друг на друга графики при N=1, N=5, N=30: