Как на коленке посчитать эффективность работы менеджмента

Лучшие брокеры бинарных опционов за 2020 год:
  • Бинариум
    Бинариум

    1 место — лидер рейтинга! Бесплатное обучение и демо счет!
    Контроль честности и надежности!
    Получите бонус за регистрацию на сайте Бинариум:

  • ФинМакс
    ФинМакс

    2 место в нашем рейтинге. Надежный брокер!

RFM-анализ на коленке (Excel)

Добрый день! Летом 2020 года, работая обычным аналитиком и сильно страдая от прокрастинации, поучаствовал в создании онлайн магазина одежды. Успешно «запилив» для этого проекта систему управленческого учета, обрел в глазах собственника ореол бога аналитики в целом, и Excel’я в частности)) С тех пор собственник, будучи человеком неглупым, хотя и жутко ленивым, привлекал меня для решения всех мало-мальски близких к аналитике задач. Результатом одной из этих задач и хочу поделиться. Под катом мой вариант реализации RFM-анализа. Интересно будет владельцам небольшого B2C бизнеса, не имеющим значительного бюджета на исследования, а также всем интересующимся практическим применением Excel в бизнесе.

Офтоп: с тегом RFM на Хабре лишь 2 статьи, и обе из корпоративных блогов. Странно, почему так мало контента по тематике, ведь на Хабре много людей из e-commerce related area?

Однако, бросаю лить воду и предлагаю, для начала, договориться о терминах. Далее под RFM-анализом подразумевается анализ ценности клиента для компании. По сути, слегка продвинутый вариант ABC-анализа, только с фокусом не на товарах, а на клиентах. Во главу угла ставится формализация размера пользы каждого клиента для бизнеса. С целью выявления это пользы каждый клиент рассматривается по следующим параметрам:

Recency — новизна (время с момента последней покупки)
Frequency — частота (частота покупок за период)
Monetary — монетизация (стоимость покупок за период)

1. История продаж интернет-магазина в виде .xlsx выгрузки, наподобие

Sic! Не ищите смысла в цифрах, все полу-рандомно изменено на 1-2 порядка

2. ТЗ от собственника, полная версия которого звучит не сложнее фразы «RFM-анализ сделать можешь?»

Поначалу, полдня потратил на раздумья «Как все это сделать при помощи вычисляемых объектов сводной таблицы, чтобы было красиво». В итоге, забил на красоту и за час сделал с помощью промежуточного листа и обычных формул типа «=ЕСЛИ» и т.д.

3. Промежуточные вычисления

Для вычисления времени с момента последней покупки необходима текущая дата (стандартная функция в Excel =ТДАТА()) и дата последней покупки клиента. Поскольку выгрузка представляла собой неупорядоченный массив «Дата-Клиент-сумма_покупки», существовала сложность выявления последней даты покупки по каждому из клиентов. Проблема была решена сортировкой по всему объему дат в выгрузке (прошу не винить за «колхозный стиль», но в тот момент на красоту забил, так как хотел максимально быстро реализовать имевшееся в голове решение). Зеленым отмечены колонки первоначальной информации. В первой строке оставил формулы для понимания, а сортировал по колонке в порядке убывания (колонка создана при помощи сцепить)

4. Составные части листа «Итог»

Теперь собираем результат RFM-анализа на одном листе. Начинаем со списка клиентов (сортировка не имеет значения) — копируем с первого листа список клиентов оставляем только уникальные записи при помощи стандартного функционала (Данные — Удалить дубликаты). В колонку B при помощи ВПР тянем дату последнего заказа клиента. Формула в колонке С считает количество заказов клиента по всей выгрузке. В колонке D похожим образом считается сумма заказов по клиенту. А столбец E вычисляет для нас количество дней с момента последней покупки клиентом.

Sic! пример формулы для колонки E указан в ячейке K1, а в самом столбце E сохранены лишь значения для демонстрации результата

5. Recency (время с момента последней покупки)

Суть выделенной формулы в следующем: смотрим в каком из пяти равных промежутков от 0 до максимума (подсвечено в формуле красным) находится значение каждой ячейки колонки Е и проставляем оценку от 1 (клиент, купивший у нас нечто год назад) до 5 (клиент купивший что-либо в последнее время).

6. Frequency (частота покупок за период) и Monetary (cтоимость покупок за период).

Формулы идентичны, поэтому рассмотрим на примере Frequency. В данном случае мы разделили всю совокупность на 3 равных по количеству членов совокупности промежутка и смотрим к какому из этих промежутков относится значение в колонке С с выставлением оценок 1(клиент покупающий у нас реже остальных), 3, 5 (клиент покупающий у нас чаще остальных).

Для тех кому сложно или лениво понять определение медианы в википедии : медиана — это значение, делящее совокупность данных на 2 равные по количеству части. Пример: cреднее арифметическое значение 5 клиентов совершивших 1, 2, 2, 2, 100 покупок = 21,4 (ничего не говорящая нам средняя температура по больнице); медиана для этого же ряда = 2.

Заключение: про сложение всех показателей вместе и сортировку в порядке убывания самой правой колонки листа «Итог» писать не стал — думаю, итак понятно)) Моя цель — создать систему «на коленке», была полностью достигнута. Отдаю «как есть». Дописывая эти строчки понимаю, что мое определение медианы и пример тоже не самые легкие (для тех у кого не было в университете мат.статистики). Если кто предложит более простой и понятный вариант — заменю.

Как «на коленке» посчитать эффективность работы менеджмента

На вольных хлебах

Разумеется, больше всего любителей «вольных хлебов» среди молодежи. По данным исследования, проведенного компанией InsightExpress по заказу компании Cisco в 14 странах, включая Россию, семь из десяти студентов колледжей полагают, что регулярно бывать в офисе не обязательно, главное — присутствовать на важных совещаниях. Такое же мнение поддерживает и уже работающая молодежь: 69% из них считают необязательным регулярное присутствие в офисе. Более половины из них хотят получать доступ к корпоративной информации с помощью своих домашних компьютеров (63%) и персональных мобильных устройств (51%), пишет JOB.RU.

Работодатели идут сотрудникам на встречу: 57% работников сегодня могут подключаться к своей корпоративной сети из удаленных точек, 28% могут делать это в любом месте и в любое время. Да и куда деваться работодателю, если 43% работающей молодежи считает это критически важным для работы, а 29% и вовсе готовы уволиться, если в корпоративную сеть нельзя будет войти удаленно!

«Лет семь назад, во время командировки в Лондон, мне повезло попасть на нетипично хорошую для Англии погоду. Солнце, жара… Понятно, что англичане такое редкое явление пропустить не могли. Все повыползали из своих офисов и радостно грелись на солнышке. Изумительная картина: на спинке уличной лавочки висит дорогущий пиджачок, рубашечка, галстучек, под лавочкой стоят ботиночки, а на лавочке лежит довольный клерк с ноутбуком на бледном пузике. Работает и загорает», — вспоминает редактор Юлия Яковлева.

О работе и о свободе

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

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

«В Нью-Йорке — это обычное дело. Во всех кафе есть Wi-Fi, почти все журналисты там и работают. В Москве я нашла несколько отличных кофеен, где очень хорошо работается. Самое главное, что ты не теряешь время, совмещаешь приятное с полезным. Как-то встречалась в кафе с подругой. Она опаздывала. Я залезла посмотреть новости — и как раз узнала про акцию протеста на Уолл-Стрит. Тут же связалась со своим русским редактором, получила заказ на статью, по скайпу пообщалась с оставшимися в Нью-Йорке знакомыми. К тому моменту, когда подруга, наконец, появилась, полстатьи уже было готово», — рассказала журналистка Анна Янова, много лет живущая между Москвой и Нью-Йорком.

Сегодня, по данным Фонда «Общественное мнение», мобильным Интернетом в России пользуется около 21 млн человек — это 17% россиян от 12 лет и старше. Каждый десятый использует услуги Wi-Fi или Wi-Max. Самыми популярными ресурсами среди пользователей мобильного Интернета признаны социальные сети (46%). На втором месте — поисковики (41%).

«Работа перестала ассоциироваться с несвободой, — рассуждает дизайнер Александр Романов, — Когда ты сидишь в офисе и смотришь на то, как на улице светит солнце, люди куда-то идут… Такое впечатление, что за окном жизнь проходит мимо. Дома — тоже не лучший вариант. Постепенно начинаешь чувствовать себя отшельником, все труднее заставить себя куда-то выйти. Кафе — самый лучший вариант. Вокруг люди, жизнь кипит, и нет ощущения, что ты узник в четырех стенах. И работается радостнее, и результат лучше».

Бонус для клиентов

Неудивительно, что в крупных городах становится все больше заведений, где за умеренную плату или вовсе даром можно воспользоваться Wi-Fi. Но их руководство воспринимает работающих клиентов по-разному. В демократичных кафе, барах и кофейнях не видят ничего предосудительного в том, что человек весь день занимает столик и пользуется Wi-Fi, заказав при этом одну-единственную чашку кофе.

«Иногда поход в кофейню — способ скоротать время. Для того, чтобы этот процесс проходил с пользой, мы предлагаем обладателям ноутбуков бесплатный Wi-Fi. И, конечно, мы очень позитивно относимся к тем клиентам, которые приходят к нам поработать. Даже если человек полдня просидел в кофейне, ограничившись единственной чашкой кофе — это не принципиально. Поработать к нам приходят постоянные клиенты, и мы всегда им рады», — говорит директор по маркетингу сети «Кофеин» Никита Житлов.

В солидных дорогих ресторанах «зависающих» в интернете клиентов воспринимают не так однозначно. Здесь Wi-Fi — дополнительный бонус, и к тем, кто им злоупотребляет, забывая сделать заказ, относятся строго.

«Если это происходит днем, когда у нас много свободных мест, и его присутствие никому не мешает — пускай сидит сколько угодно. Но если у нас аншлаг или же время закрытия, то мы либо вежливо объясним ситуацию и попросим освободить столик, либо — бывали и такие ситуации — просто отключим Wi-Fi, пока этот человек не уйдет. А потом перед остальными извинимся, что так произошло. Ничего личного: у нас ресторан, а не молодежное кафе, и сюда люди приходят для того, чтобы поесть», — считает директор «Andiamo cafe» Николай Борисов.

Быть привязанным к рабочему столу компьютерными проводами — уже давно не модно. Вы приходите в кафе, выбираете удобный столик, заказываете кофе и … оказываетесь на работе. Стоит только включить ноутбук.

Прогнозируем LTV в Экселе или «Машинное обучение на коленке»

Главная проблема LTV в том, что на сбор данных требуется время, которого, обычно, нет. Например, вы в течение месяца тестировали новый рекламный канал, и теперь вам нужно решить, продолжать или нет. За месяц покупок было немного, канал еще далек от точки безубыточности, но вы готовы вкладывать, если будете знать, что за год клиенты окупятся.

Сегодня я расскажу, как прогнозировать годовую выручку когорты по первому месяцу её существования. Для прогноза будем использовать линейную регрессию.

  • Эксель (Гугл-таблицы тоже подойдут, но там немного другой интерфейс);
  • исторические данные о продажах (на них будем обучать модель).

Если, вдруг, ваш бизнес молодой, и данных за год пока нет, — ничего страшного. Постройте прогноз на тот период, за который данные есть.

Шаг 1: достаньте данные

Для этого упражнения я подготовил специальный файл, на котором вы можете потренироваться. Скачайте его и откройте в Экселе.

Позже попросите программиста выгрузить ваши данные в таком же формате. Покажите этот файл как пример.

Шаг 2: посчитайте выручку на каждого пользователя

Когорты, в которых больше людей, скорее всего, принесут больше денег просто потому, что там больше людей. Единственный вывод, который вы сделаете из модели: “нужно больше покупателей”. Не очень полезно. Вместо этого спрогнозируем среднюю выручку с покупателя. Для этого поделим итоговую выручку с когорты на количество людей.

Шаг 3: постройте график

Начинается самое интересное. Чтобы построить модель, нужно понять, как взаимосвязаны данные. Для этого построим scatter plot (такие графики мы все в школе строили).

Каждая точка на графике — одна когорта. По оси X — выручка за месяц. По оси Y — выручка за год.

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

Вопрос в том, на сколько больше?

Шаг 4: строим модель

Мы всего в паре шагов от победы. Добавьте на график линию тренда. И не забудьте вывести на экран формулу графика.

Получившаяся формула — и есть нужная нам модель. Напомню, что Y — LTV за год, а X — LTV за первый месяц. То есть:

LTV за год = 4.67 * LTV за месяц – 0.72

«Леша, ты хочешь сказать, что можно вот так вот спрогнозировать выручку за год с помощью сложения и умножения? Не может быть!»

Именно так. Но мы еще не закончили. Остался последний шаг.

Шаг 5: оцениваем точность модели

Под формулой есть показатель R^2. Он показывает, на сколько хорошо модель описывает имеющиеся данные. 0.93 означает «чертовски хорошо описывает».

Но нам гораздо интересней знать, с какой точностью модель прогнозирует будущее.

Скажу сразу, моделей со 100% точностью не бывает. Вообще.

Чтобы оценить точность модели, разделим имеющиеся данные на 2 группы: обучающую и тестовую.

Обучающую группу пометим нулем, тестовую — единицей.

На основе обучающей группы построим модель: добавим график с линей тренда и получим формулу.

Формула немного изменилась. Это нормально.

LTV за год = 5 * LTV за месяц — 1.72

Теперь применим формулу для прогноза на тестовой выборке.

На графике голубая линия — реальный LTV каждой когорты, а оранжевая — прогноз, результат работы модели. Смотрите, как они близко.

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

Вернемся к тому, зачем мы все это делали?

Напомню, что вам нужно принять решение, продолжать ли рекламную кампанию.

Допустим, что за месяц вы потратили $10.000 и привлекли 600 новых пользователей. Эти пользователи за месяц принесли $2400 выручки.

Стоимость одного пользователя = $10.000/600 = $16.7

LTV за первый месяц = $2400/600 = $4

Подставим значения в модель:

Прогнозный ROMI = $18.28/$16.17 = 113%.

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

P.S. Естественно, периоды 30 и 365 дней можно менять на любые другие. Я, например, часто прогнозирую первый месяц по первому дню.

Приглашаю на онлайн-курс по аналитике

15 уроков о работе с данными для маркетологов, менеджеров и предпринимателей с теорией, практикой и домашними заданиями.

Лучшие брокеры бинарных опционов за 2020 год:
  • Бинариум
    Бинариум

    1 место — лидер рейтинга! Бесплатное обучение и демо счет!
    Контроль честности и надежности!
    Получите бонус за регистрацию на сайте Бинариум:

  • ФинМакс
    ФинМакс

    2 место в нашем рейтинге. Надежный брокер!

Добавить комментарий