Abc- и xyz-анализ

Почему не стоит использовать Excel для ABC XYZ-анализа

Часто произвести расчеты ABC XYZ анализа предлагают в Excel. Действительно, функционал электронных таблиц позволяет производить вычисления. Но, когда речь идет о реальном бизнесе с огромной номенклатурой, множеством точек продаж, развитой партнерской сетью, — использование Excel только затрудняет бизнес-процессы.

Проблемы начинаются сразу же:

Представьте, что вам нужно сделать ABC XYZ-анализ для хотя бы 50000 товарных позиций. Уверены, что хотите сделать это вручную?

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

Excel позволяет использовать только 1 048 576 строк

Если данных больше, то уже не важно, насколько быстро он будет работать.

Проблема с открытием больших файлов — довольно распространенное явление в работе Excel. Связана она с использованием вычислительных функций, условным форматированием, созданием больших массивов данных и сводных таблиц и др.

Повторное использование и формирование отчетов затруднено из-за «тормозов» и больших массивов данных

После каждого действия приходится ждать.

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

Что такое ABC-анализ

В основу метода положен принцип Парето 20/80. Да-да, тот самый, который уже много лет звучит «из каждого утюга», но от этого не теряет своей эффективности. В применении к этому методу сформулировать его можно, например, следующим образом:

Всего 20% любых товаров, клиентов и т. п. приносят 80% всей прибыли компании.

Но как же определить эти 20% звезд? Именно для этого нужен ABC-анализ продаж. Он позволяет выявить лидеров и сосредоточить на них основные усилия.

В результате анализа товаров по этому методу можно выделить группы:

• А, куда относятся не более 20% позиций, но приносят они от 70 до 90% дохода;
• В, в которой сосредоточены середнячки, то есть порядка 30% позиций, дающих примерно 20% выручки;
• С — самая многочисленная группа, где обычно оказывается порядка 50% всех реализуемых товаров.

Цель ABC-анализа выделить приоритетную группу по количественным показателям и сосредоточить усилия на работе с ними.

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

Если полученные в результате ABC-анализа показатели на 10-15% отличаются от перечисленных выше, это допустимое отклонение. Как правило, чем больше объектов участвует в анализе, тем ближе результаты к классическим параметрам распределения.

Bottom-up подход

Рассчитывается путем оценки потенциальных продаж с целью определения общего объема продаж. Анализ «снизу вверх» оценивает, где продукты могут быть проданы, кому они могут быть проданы. Например, вы решили предоставлять сервис выгула собак. Допустим, вы находитесь в России, а точнее в Москве. Тогда вы можете прикинуть, сколько есть хозяев с собаками. Постарайтесь найти наиболее точные статистические данные из доверенных источников. Можно оценить, оттолкнувшись от количества населения в городе. К примеру, в Москве 600к собак, и только 8% из них будут гулять каждый день. Тогда, если ваш средний чек 1000р, то максимально возможная выручка в день будет равна 600,000*8%*1000 = 48 млн руб. Тогда в год весь рынок выгула собак — это 17 млрд. руб. Однако тут нужно учесть, что большую сумму вы будете отдавать выгульщикам собак. Поэтому скорее ваш рынок можно оценить в 600,000*8%*200*365= 3,5 млрд. Руб. Где 200 рублей — это то, что получаеты вы за выгул одной собаки.

Хотя оценка с помощью bottom-up анализа требует гораздо больших усилий, результат, как правило, намного точнее.

TAM, SAM, SOM

Теперь перейдем к аббревиатурам. Они расшифровываются так:

• TAM (Total Addressable Market) — общий объём целевого рынка;
• SAM (Serviceable Addressable Market) — доступный объём рынка;
• SOM (Serviceable & Obtainable Market) — реально достижимый объём рынка.

Анализ продукта и бренда

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

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

Какие данные собирать:

• представленные бренды и торговые марки;
• категории, по каким критериям они сформированы;
• количество единиц в каждой категории.

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

Позиционирование конкурентов

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

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

Позиционирование компании на главной – решаем любые задачи бизнеса в соцсетях

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

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

Ценовая политика, скидки и акции

Дальше нужно посмотреть, сколько стоят продукты, особенно те, которые есть у вас

Если стоимость выше среднего, обратите внимание на обоснование таких цен

Затем нужно проанализировать специальные предложения конкурентов:

• скидки, акции, распродажи;
• персональные предложения;
• способы повышения чека – например, бесплатная доставка от 3000 ₽;
• программы лояльности;
• реферальные программы.

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

Услуги и помощь клиентам

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

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

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

Процент выкупа

В продажах на маркетплейсе есть два важных параметра: количество заказов и процент выкупа. Заказ — это когда клиент добавил товар в корзину и оформил доставку в пункт выдачи. Выкуп — когда забрал его из пункта выдачи.

Процент выкупа — сколько из заказанных товаров покупатели забрали.

Чем процент выкупа выше, тем лучше. Норма для каждой категории своя. Самый низкий нормальный процент выкупа у товаров, которые нужно примерять, например одежды и обуви. Если выкупают больше 30% заказанного, все хорошо. Для остальных товаров норма выше — 80%.

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

• товар не соответствует описанию;
• с товаром что-то не так;
• товар не подошел.

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

Клиентам нравится товар, но размер не соответствует описанию — маломерит. Продавец не указал это в карточке. Покупатели делают возврат и повторный заказ: с товаром все хорошо, но процент выкупа падает

С товаром что-то не так. Если партия бракованная или товар низкого качества, его будут возвращать часто. Есть три способа это исправить:

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

Игнорировать бракованные партии товаров нельзя, они плохо влияют на репутацию продавца.

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

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

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

Клиент не всегда может оценить по фото, подходит ему товар или нет. Это связано с субъективным восприятием, на него не получится повлиять. Ткань может оказаться неприятной, звучание колонки — недостаточно чистым, посадка наушников — неудобной. Такие ситуации нормальны и случаются даже с товарами из категории A: невозможно описать ткань так, чтобы каждый покупатель определил ее текстуру.

Если с карточкой товара все в порядке и партия не бракованная, значит, товар относится к категории C — его можно смело выводить из ассортимента.

Что такое XYZ-анализ

Что такое ABC-анализ разобрали, но причём тут XYZ? Этот метод часто применяется в дополнение к первому и вместе они создают синергетический эффект.

XYZ-анализ — это метод определения вариативности показателя. То есть с его помощью можно определить, насколько величина имеет свойство отклоняться от средних значений.

Например, при проведении XYZ-анализа продаж с точки зрения спроса на товары можно выделить три категории:

• Х с колебанием от 0 до 10%, то есть товары, всегда приобретаемые примерно в одинаковом количестве;
• Y, где вариация составляет от 10 до 25%. Это продукция, которая имеет относительно стабильный спрос, но подвержена колебаниям, например, по сезону;
• Z – это группа, где колебания спроса могут составлять от 25% и выше. В этом случае его можно называть случайным и прогнозировать что-либо очень сложно.

Цель XYZ-анализа — определить колебания величины относительно среднего значения и учитывать уровень нестабильности показателя при дальнейшем развитии бизнеса.

Такая диверсификация крайне оправдана, ведь если товар попал в группу B по средней выручке, но продавался хорошо только в декабре и январе (например, ёлочные игрушки), будет странным делать на него ставку в июле. Не всегда показатели выглядят столь очевидными и заметны невооруженным глазом, но методика XYZ-анализа позволяет выявить такие колебания в любом случае.

Пример XYZ анализа объемов продаж продукции в Excel

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

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

Ассортимент и объем продаж продукции

Далее необходимо рассчитать изменчивость объема продаж по каждой товарной группе. На рисунке ниже показан итог расчета коэффициента вариации  по продукции. Формула расчета в Excel будет иметь следующий вид:

Коэффициент вариации объемов продаж =СТАНДОТКЛОН(B5:G5)/СРЗНАЧ(B5:G5)

Расчет коэффициента вариации продаж товаров

Сейчас необходимо классифицировать товары в группу – «X»,»Y» или «Z». Для этого напишем формулу определяющую класс товара, и воспользуемся встроенной формулой «ЕСЛИ» в Excel. Формула будет иметь следующий вид:

Группа товара =ЕСЛИ(H5<10%;»X»;ЕСЛИ(H5<25%;»Y»;»Z»))

Группировка продукции по классам XYZ в Excel

Чтобы было наглядно видно структуру устойчивости продаж необходимо провести группировку по классам: главное меню Excel → «Данные» → «Сортировка».

XYZ анализ продаж продукции в Excel. Пример расчета

В результате мы получим следующую классификацию товаров. Телефоны «Lenovo» имеют самый устойчивый спрос, так как коэффициент вариации составляет всего 8%. То есть объем продаж в среднем по месяцам отклоняется на 8%

Компании следует уделить особое внимание складским запасам данных товаров. В группу «Y» попали довольно много различных видов продукции, компании следует оперативно отслеживать складские запасы

Товары группы «Z» продаются не регулярно,  их запасы могут быть сокращены и фирма может работать по предзаказу. XYZ анализ хорошо себя зарекомендовал на практике управления запасами и ассортиментом товаров, также его часто объединяют с ABC анализом, который позволяет провести двухкритериальную группировку товаров. Более подробно о ABC анализе вы можете узнать в моей статье: «ABC анализ продаж. Пример расчета в Excel«.

Резюме

Метод XYZ анализа позволяет прогнозировать устойчивость спроса на продукции, объемы продаж и запасов. Использование метода высвобождает дополнительные ресурсы компании и оптимизирует их бизнес-процессы.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Принципы АВС- и XYZ-анализа

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

• Сущность ABC-анализа

ABC-составляющая базируется на принципе Парето, в основе которого идея о том, что 80 % результата бизнес получает за счёт 20 % усилий

Выявив в результате анализа эти 20 %, важно сосредоточить на них основное внимание

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

По результатам ABC-анализа выделяют 3 группы объектов:

• А — 20 % самых ценных ресурсов (именно эта группа даёт 80 % результатов);
• В — 30 % средних по ценности ресурсов, их доля в общем результате — 15 %;
• С — 50 % ресурсов, которые дают лишь 5 % результатов.

АВС-анализ позволяет провести ранжирование ресурсов в зависимости от их ценности для бизнеса.

• Сущность XYZ-анализа

В отличие от ABC-составляющей задача XYZ состоит в том, чтобы определить стабильность спроса на группу товаров или услуг. Данный метод применяют для определения эффективности сбыта и стабильности запросов клиентов. Группировка бизнес-объектов происходит в зависимости от равномерности и сезонности распределения продаж.

ABC-анализ выделяет наиболее прибыльные категории продукции, а XYZ-составляющая добавляет информации о стабильности спроса на них.

В случае продажи услуг ранжируют клиентов:

• X — категория клиентов, стабильно и регулярно оформляющих заказы (прогноз продаж по данной группе достаточно точен).
• Y — категория клиентов, делающих заказы нерегулярно.
• Z — категория клиентов, сделавших заказ единожды.

• Цель исследований

Методы ABC- и XYZ-анализа применимы для оптимизации следующих бизнес-составляющих:

• ассортимента продукции (анализ прибыльности позиций);
• целевых категорий клиентов (анализ базы по объёму заказов);
• поставщиков (анализ объемов поставляемой продукции);
• дебиторов (анализ сумм и динамики задолженностей).

Проведя ABC-анализ, вы выделите клиентов, заплативших вам больше других. А XYZ-анализ покажет, насколько их заказы стабильны.

Рекомендуемые статьи по данной теме:

• Как сделать бизнес успешным и не стать «пожарником» своей компании
• Продвижение через социальные сети: разработка стратегии и основные ошибки
• Как написать предложение о сотрудничестве

Самый эффективный метод — применение ABC- и XYZ-анализов в комплексе. Благодаря такому подходу вы не только выделите самые доходные группы товаров, но и обозначите те, которые отличаются стабильным спросом.

Если же говорить о клиентах, то выделяют следующие группы:

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

Совокупность действий по результатам анализа в любом случае увеличит доход бизнеса.

Сложности ABC-анализа

1. Нельзя сравнивать неоднородные товары. Если компания продает дорогие станки с небольшой наценкой и гвозди с высокой, может показаться, что гвозди нужнее в ассортименте. Чтобы избежать этого искажения, нужно группировать однородные товары и сравнивать их между собой.
2. Небольшой период анализа может исказить результаты. Лучше брать данные за год, чтобы исключить сезонность, даже неочевидную.
3. Проанализировать новые товары не получится. ABC-анализ предполагает взгляд в прошлое. Лучше по умолчанию включать новые товары в группу А и и уже на старте уделить им как можно больше внимания.
4. Внешние факторы. Если поставщик доставляет товары с перебоями, менеджеры ошибаются с выставлением счетов, а продавцы забывают выставлять товар на полки, продажи могут быть ниже. От качеств товара это не зависит. Учитывать такие факторы сложно, но если товар вдруг попал из группы А в группу С, стоит проанализировать, с чем это связано.

Антон Варламов, начальник производства CWS-group:

«Мы продаем оборудование для моек самообслуживания и комплектующие. ABC-анализ не совсем подходит для нашего ассортимента. Независимо от того, какой будет спрос, линейка оборудования должна быть полной. Чтобы клиент мог приобрести то, что нужно именно ему.Зато мы используем этот анализ для оптимизации запасов. Какие-то комплектующие: пистолеты, держатели, консоли нужны очень часто. Какие-то — реже. Неразумно вкладывать все деньги в невостребованный товар. Поэтому мы проанализировали потребности производства и разделили все запасы на три группы по частоте использования

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

Просто заказываем их меньшими партиями»

АВС – анализ стандартными средствами MS EXCEL

В качестве примера возьмем компанию, занимающуюся продажей товаров с широким ассортиментом (около 4 тыс. наименований). В качестве исходных данных возьмем объемы продаж по каждой позиции (цена * количество) за определенный период.

Примечание

: АВС-анализ также можно проводить для определения ключевых клиентов, оптимизации складских заказов и бюджетных расходов компании.

Прежде чем, приступить к расчетам ответим на несколько вопросов, которые помогут нам эффективно использовать АВС – анализ.

1. Какова цель анализа? Увеличить выручку компании.
2. Какие действия по итогам анализа будут предприняты? Обеспечить обязательное наличие на складе товаров, вносящих в выручку основной вклад (для исключения потерь выручки).
3. Что является объектом анализа и параметром анализа? Объект анализа — перечень товаров, которые вносят наибольший вклад в выручку (выручка — параметр анализа).

Алгоритм выполнения АВС – анализа:

• Сортируем список товаров по убыванию их вклада в выручку.
• Формируем столбец с выручкой накопительным итогом (для каждой позиции товара складываем его выручку со всеми выручками от предыдущих, более прибыльных товаров).
• Определяем долю выручки для каждого товара накопительным итогом (значения столбца, рассчитанного выше, делим на общую выручку всех товаров). По этому столбцу будем определять границы классов.
• Определяем границы классов в долях от выручки. В данном случае используем стандартные значения долей (в %): 80%, 15% и 5%. Т.е. группа наиболее прибыльных товаров должна вносить суммарный вклад в выручку в размере 80%. Все товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А.
• Выделяем классы А, В и С: присваиваем значения классов соответствующим товарам.

Теперь реализуем этот алгоритм на листе MS EXCEL (см. файл примера , лист АВС формулами).

Отсортировать список товаров можно с помощью функции РАНГ() – каждому товару будет присвоен ранг в зависимости от его вклада в выручку. Товару, обеспечивающему максимальную выручку, будет присвоен ранг = 1.

С помощью формулы =СУММЕСЛИ($H$7:$H$4699;» формируем столбец с выручкой накопительным итогом. У товара, обеспечивающего максимальную выручку (первый в списке), значение выручки накопительным итогом будет совпадать с его выручкой. У второго товара выручка накопительным итогом будет равна его собственной выручке + выручка первого товара, и т.д.

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

С помощью формулы =ИНДЕКС($N$7:$N$9;ПОИСКПОЗ(J7;$P$7:$P$9;1)) присвоим названия классов каждому товару:

• товары, у которых доля выручки накопительным итогом менее или равна 80%, входят в класс А;
• товары, у которых доля выручки накопительным итогом более 80% и менее 95% (80%+15%), входят в класс В;
• остальные товары принадлежат классу С.

Для наглядности товары, принадлежащие классу А, можно выделить Условным форматированием , а также построить диаграмму Парето (по оси Х указывается количество проданного товара, по оси Y — % выручки накопительным итогом).

Примечание

: Границы классов выделены на диаграмме бордовыми линиями (технически это сделано с помощью горизонтальных и вертикальныхпланок погрешностей ).

Можно также рассчитать сколько позиций товаров входит в каждый класс. Так в класс А входит 342 товара. В класс А входят товары, которые обеспечивают 79,96% выручки (максимальный % меньше 80%). Общая сумма выручки, приходящаяся на эти товары равна 2 116 687,3 руб. Максимальная выручка (у первого товара в классе) равна 76 631,1 руб., а минимальная 1 574,0 руб. (у последнего товара в классе). Часть информации можно найти в таблице в строках расположенных на границах классов (строки 348 и 349).

Как видно из примера, вышеуказанные вычисления, относятся довольно трудоемкими. Есть ли возможность ускорить выполнение АВС-анализа? Безусловно, есть, и одним из решений является надстройка ABC Analysis Tool от компании fincontrollex.com. Ниже рассмотрим ее подробнее.

Примечание

: АВС-анализ относится к числу стандартных и часто используемых инструментов, поэтому он доступен во многих популярных программах бухгалтерского и управленческого учета. Например, в программе «1С: Управление торговлей» (версия 10) существует возможность для проведения анализа клиентов и номенклатуры товаров по следующим параметрам: сумма выручки, сумма валовой прибыли, количество товаров. Причем границы классов не вычисляются, а задаются произвольно, по умолчанию используются значения 80%, 15%, 5%.

Анализ поисковой оптимизации сайта

Анализ SEO-продвижения конкурентов покажет, по каким ключевым запросам конкуренты выходят в топ и собирают трафик из поисковиков. Также можно узнать, откуда приходят клиенты, сколько времени проводят на сайте, насколько сайт хорош технически. Все это – важная информация, которая поможет потеснить конкурентов и собрать бесплатный трафик в интернете.

Источники трафика

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

Пример анализа источников трафика smmplanner.com

Поведение и характеристики аудитории

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

Технические параметры

Есть сервисы типа WooRank, которые показывают ключевые данные страниц, помогающие сайту выходить в топ поисковиков.

К этой информации относятся:

• Мета*данные;
• элементы разметки;
• содержание страниц – заголовки, ключевые слова, ссылки, описания;
• информация о мобильной версии;
• информация о протоколах безопасности и конфиденциальности.

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

Анализ email-рассылки

Для этого нужно подписаться на рассылку заранее, чтобы накопились письма хотя бы за месяц, в идеале – 2-3 месяца. Тогда у вас будет достаточно данных, чтобы оценить все необходимые параметры: частоту, типы писем и их содержание, а также увидеть сильные и слабые стороны рассылки.

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

Сбор контактов

Проанализируйте, как собирают базу для рассылки. Это могут быть пуш-уведомления, форма на сайте, формы-«нападайки», чекбокс с подпиской на этапе оформления заказа. Также посмотрите, на что привлекают клиентов – что в тексте формы, есть ли оффер на подписку, какой визуал.

Формат писем

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

Примеры полезного и скидочного письма

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

График рассылок

Изучив формат писем, отметьте их периодичность – сколько раз в месяц приходят письма каждого формата, каких больше. Также посмотрите на время доставки, чтобы понять, настраивают ли персональное время отправки для каждого подписчика.

Содержание писем

Оцените контент письма – о чем пишут, какие темы обсуждают, используют ли инфоповоды

Также обратите внимание на tone of voice – как общаются с клиентами, отличается ли стилистика от текстов на сайте или в соцсетях, есть ли обращения по имени

Посмотрите общий дизайн и верстку:

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

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

Воронка продаж

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

ABC анализ в сводной таблице в Excel ассортимента по выручке

Для начала необходимо определиться с периодом анализа. Наиболее удачный вариант — взять в анализ 12 месяцев, чтобы учесть все сезонные колебания. Однако, иногда нужен анализ более короткого периода — например, в нашем примере для магазина садового инвентаря мы возьмем период 6 месяцев (наивысший спрос в дачный сезон). 

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

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

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

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

В нашем примере АВС-анализа таблица с исходными данными выглядит так.

В таблице содержится различная информация, но для анализа будем использовать только два столбца: Наименование товара и Стоимость.

1. Создадим сводную таблицу для АВС анализа

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

В поле Строки помещаем наименование товаров, а в поле Значения — сумму по полю Стоимость.

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

Как создать сводную таблицу

Также желательно убрать пустую строку внизу таблицы, которая всегда по умолчанию создается в сводных таблицах. Для этого в фильтре столбца снимите “галочку” с пункта (пусто).

Мы получили список товаров и суммы выручки за каждый из них.

2. Получим доли каждого товара

Теперь нам необходимо посчитать, какую долю занимает выручка по каждому товару в общей выручке. 

Для этого добавим столбец Стоимость в поле Значения еще раз, просто перетянув его еще раз.

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

Получили два одинаковых столбца с суммами выручки. Теперь из второго столбца с выручкой нужно сделать доли от выручки по данному товару в общей выручке.

Для этого щелкните правой кнопкой мыши в любом месте второго столбца с суммой и выберите: Дополнительные вычисления — % от суммы по столбцу.

Получили доли выручки от каждого товара. Переименуем столбец с процентами, назовем его Доля, %.

3. Сортируем по убыванию доли выручки

Вспомним, что нам нужно получить в итоге АВС-анализа ассортимента — это разделить товары на категории по убыванию их полезности. 

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

Для этого щелкнем на фильтре столбца Названия строк (т.е. столбца с наименованиями товаров) и выберем Дополнительные параметры сортировки.

В окне сортировки нужно выбрать переключатель “по убыванию”, и в выпадающем списке выбрать столбец Доля, %. 

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

На этом этапе уже видно, какие товары попали в группы А, В и С. Однако, это можно увидеть без расчетов лишь потому, что таблица в нашем примере маленькая. А что если у ней сотни или тысячи строк?

4. Получаем долю выручки нарастающим итогом

Как в пункте 2, снова добавляем поле стоимость в поле Значения, и вместо показателя Количество указываем Сумма. Сразу лучше переименовать поле (в примере —  Доля нараст. итогом, %). 

Теперь опять щелкаем правой кнопкой мыши на любом месте нового поля — Дополнительные вычисления — % от суммы с нарастающим итогом в поле — появляется окно Дополнительные вычисления — нажимаем Ок.

В поле Доля нарастающим итогом считаются доли выручки из предыдущего столбца нарастающим итогом, на картинке показан смысл:

Мы практически достигли цели провести ABC анализ в сводной таблице в Excel. Ведь нам нужно было узнать, какие товары дают примерно 80% выручки, какие — еще 15% (т.е. от 80 до 95%), а какие оставшиеся 5% (от 95 до 100%). И поле “Доля нараст. итогом, %” это показывает.