В этой статье мы разберем RFM-анализ с помощью Excel и построим простую визуализацию в Power BI. Вы узнаете, как анализировать ваших клиентов и почему клиенты, которые недавно совершили покупку, с большей вероятностью откроют вашу email-рассылку!
Что такое RFM-анализ
Для начала давайте вспомним основные тезисы. RFM-анализ — анализ клиентов по давности, частоте и ценности покупок. Это один из классических и эффективных методов прогнозирования поведения клиентов. Преимущества RFM-анализа:
— не требует особых знаний. Не нужно привлекать аналитиков, математиков, анализ можно сделать на листе Excel;
— не требует временных затрат. За 25 минут можно сделать анализ базы до 50-70 тысяч клиентов, зная несколько формул Excel;
— не требует дополнительных данных. Нужна только выгрузка из CRM-системы с информацией о текущих клиентах;
— не требует дополнительных затрат и даже позволяет экономить на email-рассылках и коллцентре.
Применение RFM
Один из самых эффективных методов работы с базой клиентов — RFM-анализ. Применяется для эффективной коммуникации со своей базой клиентов посредством email и смс-рассылок, обзвона по базе клиентов. Ранее в директ-мейлинге и каталогах по почте. Эффект достигается за счет персонализации рекламного предложения с помощью самых простых данных о клиенте — датах его покупок.
Не все клиенты одинаково полезны
Концепция RFM
Есть 3 параметра, по которым будем классифицировать клиентов:
— R (Давность)
Давность последней покупки. Самый важный показатель. Покупатель, который неделю назад открыл счет в банке, с большей вероятностью откроет ваше письмо или вступит в диалог с менеджером, чем клиент, который уже 5 лет у вас обслуживается. Методология проста: разбиваем базу на 5 абсолютно равных частей (квинтилей), назначая каждой группе номера от 1 до 5. Самые «свежие» клиенты будут иметь номер 5, а те, кто давно не покупал, — номер 1. Разбивка может иметь разные методологии (через нахождение медианы или среднего, средневзвешенных значений), но обычно установка абсолютно правильных границ не стоит затрачиваемых усилий.
— F (Частота)
Частота покупок или сумма всех покупок клиента за период. Показатель, который говорит, как часто клиенты совершают покупки. Показатель частоты может также показать тенденцию покупательского поведения. Например, в среднем воду на дом заказывают 1 раз в 6 недель, а в офис — 1 раз в 4 недели.
Соответственно, офисным клиентам нужно звонить с напоминанием или делать email и смс-рассылку чаще. В книге «Маркетинг на основе баз данных» , Артур М. Хьюз утверждает, что Частота в меньшей степени помогает спрогнозировать поведение клиентов, чем Давность. Метод разбивки точно такой же: от 1 до 5.
— M (Деньги)
Разбиваем клиентов на сегменты в зависимости от их денежной ценности. Критерий Деньги имеет нюансы: для небольших сумм (товары народного потребления) денежная ценность практически не имеет значений. По данным книги «Маркетинг на основе баз данных», отклик на ваше предложение у сегментов 1 и 5 фактически одинаковый. Это объясняется тем, что клиенты, которые покупают у вас много (сегмент 5) — люди занятые и меньше читают промо-рассылки и идут на контакт по телефону. Для крупных сумм показатель имеет уровень отклика, похожий на предыдущие, чем выше цифра, тем выше отклик.
Источник: needfordata.ru
База данных в Excel
Хотя в MS Office для создания обширных сложно связанных баз данных и последующей работы с ними предназначена программа Access, миллионы пользователей по всему миру предпочитают создавать простые (и не очень) базы данных в Excel. Причин этому несколько, и самая.
. главная из них – широчайшая распространенность, доступность и известность программы Excel, имеющей огромную аудиторию пользователей в отличие от программы Access, в которой работают в основном профессиональные программисты!
Если вы задумали вести дневник затрат на автомобиль, составить фильмотеку и управлять ей, вести учет выпускаемой продукции на работе, грамотно вести учет закупок и продаж в бизнесе, то вам неизбежно придется создавать базы данных и использовать при обработке данных разнообразные фильтры и генераторы всевозможных отчетов.
Этот пост открывает небольшой, но важный цикл коротких простых статей об азах грамотного хранения информации и способах обеспечения быстрого и удобного доступа к этим данным.
Адресован этот цикл статей в основном начинающим, но, возможно, он будет полезен и более опытным пользователям для систематизации и повторения ранее полученных знаний.
Чуть-чуть теории.
Обычная двухмерная таблица Excel, созданная с соблюдением некоторых определенных правил является таблицей базы данных.
Столбцы таблицы Excel – это поля базы данных, а строки – это записи базы данных.
Поле (столбец) содержит информацию об одном признаке для всех записей базы данных.
Запись (строка) состоит из нескольких (по числу полей) разнообразных информационных сообщений (признаков), характеризующих один объект базы данных.
Расширяемая база данных – это таблица, в которую постоянно добавляются новые записи (строки) информации. При этом названия и количество полей (столбцов) остаются неизменными.
Все вышесказанное очень важно понимать, знать, и помнить!
Создавая таблицу базы данных в Excel, необходимо выполнять несколько важных правил, которые облегчат в будущем вашу жизнь при извлечении информации! Их нетрудно запомнить!
Основные правила создания базы данных в Excel.
1. Первая строка базы данных должна содержать заголовки столбцов!
2. Каждая последующая строка базы данных должна содержать хотя бы одну заполненную данными ячейку.
3. Не следует применять объединенные ячейки в пространстве таблицы базы данных!
4. Каждый столбец должен содержать только один тип данных – или текст, или числа, или даты!
5. Вокруг базы данных ячейки должны быть пустыми, либо это должен быть край рабочего пространства листа.
6. Необходимо присвоить диапазону базы данных имя.
7. Следует объявить диапазон базы данных списком.
Пример.
В качестве примера создадим небольшую учебную базу данных в Excel, представляющую записи информации о работе участка завода, выпускающего строительные металлоконструкции.
Начальник участка ведет базу данных в Excel. Он ежедневно вводит информацию о выпущенной продукции. Для чего он это делает и ответы, на какие вопросы может получить в будущем, мы узнаем в последующих статьях. А пока займемся созданием информационного пространства для хранения важных данных о производстве.
Загружаем программу MS Excel и приступаем к работе.
Написанный заголовок не будет находиться в области базы данных! Строка №6 не имеет отношения к таблице базы данных, обратите на это внимание.
2. В ячейки A7…F7 записываем заголовки столбцов – полей базы данных.
3. Далее построчно в ячейки A8…E17 заносим записи о выпуске металлоконструкций.
4. В ячейку F8 вписываем формулу: =D8*E8 и копируем ее в ячейки F9. F17 .
5. Присваиваем диапазону базы данных имя.
Для этого выделяем область базы данных вместе с заголовками столбцов A7…F17; в главном меню выбираем «Вставка» — «Имя» — «Присвоить…». В выпавшем окне «Присвоение имени» пишем имя, например — БД2 и нажимаем на кнопку «ОК» – диапазону присвоено имя!
6. Объявляем диапазон базы данных списком.
Для этого вновь выделяем область базы данных вместе с заголовками столбцов; в главном меню выбираем «Данные» — «Список» — «Создать список».
В выпавшем окне «Создание списка» проверяем правильность указанной области расположения данных и наличие галочки у надписи «Список с заголовками». Нажимаем на кнопку «ОК» – список создан!
База данных в Excel готова!
Итоги.
Если активировать любую из ячеек внутри списка («встать мышью»), то мы увидим:
1. Объявленный список окаймлен синей жирной граничной линией.
2. На заголовки списка наложен автофильтр, кнопки которого появились в ячейках с заголовками столбцов.
3. В самом низу списка появилась пустая строка со звездочкой в начале, приглашающая добавить очередную запись в базу данных.
Итак, созданный список является автоматически расширяющейся базой данных в Excel с именем БД2. Можно продолжать наполнять ее информацией. По мере готовности к отгрузке изделий (в производстве металлоконструкций они называются отгрузочными марками или просто — марками) начальник участка должен делать очередные построчные записи .
Для чего мы уделили столько внимания созданию элементарной таблицы?! Это всем станет понятно, когда будем фильтровать, сортировать данные и формировать различные отчеты по запросам, но об этом читайте в последующих статьях. Эти азы работы с информацией в Excel я постараюсь изложить максимально кратко, интересно и просто. Правильная и продуманная предварительная подготовка «скелета» для хранения информации — это залог того, что в будущем вы легко найдете необходимые данные!
Ссылка на скачивание файла с примером: database (xls 31,0 KB).
Статьи с близкой тематикой
- Формула массива в Excel
- Волшебные сводные таблицы Excel!
- Ввод данных в Excel через форму
- Расширенный фильтр в Excel
- Автофильтр в Excel
Отзывы
19 комментариев на «База данных в Excel»
-
Анатолий 30 Апр 2014 04:12
Спасибо! Интересно и поучительно.
Отлично. С нетерпением буду ждать продолжение.
если нужна простая база, делаю в My visual database
My visual database — неплохая альтернатива MS Access.
Александр, спасибо за статью. Как в excel 2010 создать список, не нашел в главном меню «Данные» — «Список» — «Создать список».
Эдуард, я не нашел «с ходу» возможности объявлять списком таблицу в Excel 2010. Удивительно! Видимо, посчитали разработчики эту функцию ненужной, или так хорошо «закопали», что мы ее не видим. Я продолжаю работать в Excel 2003, не вижу кардинальных преимуществ в более новых версиях.
Александр, прошу прощения, статью не прочитал (нет времени в данный момент), но я ее скопировал, установил слежку за Вашей темой. И Вам огромное спасибо! Пользоваться таблицами я начал еще с ДОСовского Supercalkа! От Excelа я в восторге!
Просто, понятно, доходчиво описано русским языком ПОЛЬЗОВАТЕЛЯ, а не программиста. Спасибо
Добрый день Александр. У меня Ексель 2010. Путь к списку: «параметры Ексель»-Основные- установить флаг «показывать вкладку» «Разработчик» на ленте-далее «Разработчик»-элементы управления-вставить-Список. Но дальше непонятно. Mit freundlichen Grüssen, Евгений.
Что произошло со списками Excel? Средство «Список Excel» отсутствует на ленте, которая входит в компонент «Microsoft Office Fluent». Причина Средство «Список Excel» существует, но оно переименовано. Списки Excel теперь называются таблицами Excel.
Решение Для создания таблицы Excel (ранее известной как список Excel) выберите диапазон, который нужно преобразовать в таблицу, а затем выполните одно из следующих действий: На вкладке Вставка в группе Таблицы выберите команду Таблица. Клавиши быстрого доступа Для создания таблицы, кроме клавишей CTRL+L, теперь можно нажать клавиши CTRL+T. На вкладке Главная в группе Стили выберите команду Форматировать как таблицу, а затем выберите нужный формат. СОВЕТ. Появится окно Работа с таблицами с новой вкладкой Конструктор, обеспечивающей доступ к дополнительным возможностям, таким как параметры таблицы и стили. Для получения дополнительных сведений см. статью Создание и удаление таблицы Excel. см. справку Exel
Спасибо, Евгений. Только жаль строка для ввода новой информации (со «звездочкой» — *) отсутствует. По крайней мере в Excel-2007. Но это — мелочи.
То, что я разглядел, обнадёживает. Извини, скопировал текст, чтобы прочитать крупнее, — к старости слаб глазами стал. (не так красиво звучит, как у Крылова про мартышку, зато правда 🙂
Зажми, Валера, на клавиатуре Ctrl, покрути колесико мышки — буквы станут большими-пребольшими, и будет счастье. 🙂
Статья интересная и полезная. Вот только назвать таблицу эксель таблицей базы данных. имхо, изврат, не дотягивает эксель до БД, собственно, для этого есть аксесс. С таким же успехом можно назвать пистолет артиллерией) А так, все здорово, спасибо!
Спасибо большое!
Добрый день. Я должна решить вопрос какую crm выбрать для интернет магазина Посмотрела и нагуглила пару обзоров fastweb.ru/razrabotka-crm/ crm.realect.ru/ bogdanmind.com/crm-sistema-dlja-uchebnogo-centra-dlja-chego-nuzhna-kakuju-vybrat-obzor-2018/ Мне не подходит ничего. Может подскажете как найти? Может есть какие критерии выбора crm?
Здравствуйте, Anikakix. Кроме Вас никто не может знать — что Вы хотите. Критерии Вы должны сформулировать (или подглядеть у коллег-конкурентов).
Источник: al-vo.ru
Сводим данные по рекламе и продажам из CRM «Битрикс24» в Excel
Сквозную аналитику невозможно построить без реальных данных о сделках и их суммах. В этом материале маркетолог Павел Злобин рассказывает, как свести данные о продажах со статистикой по рекламе в Excel и обойтись без подключения дополнительных систем.
Чтобы правильно оценить эффективность рекламы, важно понимать, сколько продаж приносит реклама и кампании. Сложность заключается в том, что данные по рекламе и по продажам находятся в разных системах, и их нужно свести. Для одного из своих клиентов, российского интернет-магазина строительных товаров, я построил отчет в Excel и сегодня расскажу, как это сделать можете вы.
С чем работаем
У заказчика работает контекстная реклама в Google Ads и ведет на сайт компании. Заявки с форм, почты и телефонные звонки падают в CRM «Битрикс24» с различными статусами. Чтобы свести данные, будем использовать Microsoft Excel с надстройками Power Query и Power Pivot.
Для наглядности я ограничил количество источников данных. По-хорошему, нужно использовать больше источников: Яндекс.Директ, myTarget, Google Analytics, складские программы по остаткам — например, «Мой Склад».
Также ради упрощения я не буду использовать разбивку по датам. Если вам интересно, как делать полноценный отчет по всем источникам с разбивкой по датам, напишите об этом в комментариях — я подготовлю статью.