Расчёт программы лояльности, наш подход: Power BI + 400 таблиц в Excel

Выбрали тип программы лояльности

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

Для этого типа нужно посчитать:

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

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

Проанализировали данные о клиентах и покупках

Мы выгрузили данные о покупках из онлайн-магазина и офлайн-касс и на их основе нашли соотношение лояльных и нелояльных клиентов:

А дальше разложили их на более детальный график — рассмотрели временной промежуток между покупками, чтобы видеть, как часто клиенты совершают покупки:

Потом уже рассмотрели лояльных по количеству покупок и временному промежутку между покупками:

Отдельный фильтр позволял узнать количество лояльных клиентов с определёнными суммами покупок:

Построили прогноз продаж

У нас были данные:

• количество клиентов в прошлый период за каждый месяц;
• тенденция продаж;
• какой процент клиентов сколько покупок совершает.

Из этих данных мы собрали прогноз на период с октября 2019 по сентябрь 2020 — как раз тогда, когда новая программа лояльности должна быть внедрена.

Это был «голый» прогноз — какими будут продажи, если не внедрять программу лояльности.

Прикинули возможные финансовые модели

Следующий шаг — гипотеза по поводу сумм покупок и процентов скидок.

На основе этой гипотезы мы потом будем рассчитывать, сколько дополнительной прибыли принесёт программа лояльности бизнесу.

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

Мы с клиентом выбрали три варианта для расчёта:

Однако в расчёте надо учитывать, сколько новых клиентов придёт. Мы не знаем точно, какой будет прирост, но можем предположить из прогнозных данных. Чтобы наши предположения работали лучше, мы предположили три варианта притока новых клиентов: пессимистичный, умеренный и оптимистичный.

Однако это может зависеть и от скидок тоже. Возможно, чем больше скидка, тем больше клиентов придёт. Поэтому в зависимости от модели гипотезы приток может быть разным:

То есть, каждую модель мы в результате просчитывали в трёх вариантах: пессимистичном, умеренном и оптимистичном. Плюс отдельно для онлайна и офлайна, поскольку динамика продаж в них различалась.

Посчитали количество клиентов в потенциальных статусах программы лояльности

Уже имеющиеся данные по клиентам, покупкам и суммам покупок мы разложили по статусам потенциальных программ лояльности. Теперь мы понимали, какой процент людей на каких статусах находится:

Выделили группы тех, кому нужно докупить совсем немного, чтобы перейти в новый статус

В выделенных группах людей есть те, кто совсем далёк от нового статуса, и те, кто близок к получению скидки. Например, статус начинается с 3000 рублей, а покупатель потратил 700 рублей — такой покупатель далёк от получения статуса. Те, кто потратил 2800 рублей, гораздо ближе к скидке. Их проще сконвертировать в новый статус. Так что мы выделили группы клиентов по признаку близости к статусам:

Чем больше клиент покупал, тем он лояльнее. Те, кто уже купил на 15 000 рублей, лояльнее, чем те,у кого накопилось 2400 рублей. Им допродать проще, поэтому шаги приближения к статусам тем крупнее, чем выше статус.

Спрогнозировали дополнительный доход от внедрения программы лояльности

Заключительная часть — посчитать аплифт — какой дополнительный доход может получить компания от внедрения программы лояльности.

Для этого в таблицу собрали данные:

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

Эти расчёты мы провели по месяцам для каждого варианта программы лояльности в трёх прогнозах (пессимистический, умеренный, оптимистический), отдельно для офлайна и онлайна. В результате получили аплифт по месяцам на будущий период для каждого варианта.

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