Как составить таблицу для клиентов

За 2-3 года работы у Вас может накопиться несколько десятков постоянных клиентов и заказчиков, кому нужны Ваши услуги время от времени. Кроме них, накопится огромное количество заказчиков, которые обращались за услугами 1-2 раза, и в идеале с ними необходимо поддерживать контакт и постараться перевести в категорию постоянных клиентов.

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

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

База клиентов в Excel (простой вариант)

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

Скачать файл для ведения базы клиентов (.xls)

Лист «Мои услуги» – представляет список, в который можно включить до 10 услуг. Услуги из этого списка Вы сможете выбрать при добавлении информации о клиенте в базу данных.

Лист «Клиенты» – база клиентов, с которыми Вы работаете или работали. База включает следующую информацию:

• Порядковый номер клиента. Позволяет понять, насколько велико число Ваших клиентов.
• Имя клиента – можно вводить имя или ФИО, а также название компании
• Телефон
• E-mail
• Что заказывает – поле заполняется путем выбора услуги из выпадающего списка. Если клиент заказывает несколько услуг, можно выбрать из списка основную, а другие указать в комментариях.
• Комментарий – описание клиента в свободной форме, особенности работы с заказчиком.
• Дата первого заказа – дата получения первого заказа. Позволяет понять, насколько долго Вы уже работаете с клиентом.
• Дата последнего заказа – важный параметр, позволяет отследить последнюю продажу клиенту. Например, Вы можете отсортировать клиентов по дате последнего заказа и посмотреть, кто из клиентов давно ничего не заказывал – написать им, напомнить о себе и, возможно, получить новый заказ.

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

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

Как работать с простой базой клиентов в Excel?

• Добавляйте в базу всех новых клиентов, которые оформили реальный заказ (т.е. тех, кто просто позвонил или один раз что-то написал, но не купил – добавлять не нужно);
• Раз в полгода отслеживайте клиентов, которые давно не делали заказы. Напишите им, напомните о себе. Чаще, чем раз в полгода, писать не стоит – иначе Вы рискуете слишком надоесть клиенту. Но это верно только для фрилансеров, в каких-то сферах стоит чаще напоминать о себе 🙂
• Если Вы чувствуете спад в количестве заказов, сделайте клиентам специальное предложение. Например, сделайте скидку на копирайтинг и напишите постоянным клиентам, кто заказывает тексты, о снижении цен.
• Используйте столбец с комментариями, чтобы указать особенности каждого клиента, которые помогут Вам эффективно работать с заказчиком. Например, каким-то заказчикам нужно помочь с составлением технического задания – отметьте это в комментариях, чтобы не забыть помочь с ТЗ.

База клиентов в Excel (расширенный вариант)

Скачать шаблон базы данных (расширенный вариант) (.xls)

В расширенном варианте базы у каждого клиента можно указать дополнительные сведения:

• Канал привлечения – источник получения клиента. Список источников можно отредактировать на листе «Каналы привлечения». Допускается указывать до 20 каналов.
• Статус – активный или не активный. По умолчанию ставьте всем клиентам активный статус. Ниже я расскажу, в каких случаях его нужно менять на не активный.

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

Если Вы получили новый заказ от такого клиента, укажите дату нового заказ в столбике «Дата последнего заказа». Если клиент ничего не ответит, переводите его в не активный статус. По не активным клиентам система не делает напоминаний.

Резюме

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

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

Дополнительные материалы

Что такое CRM-система?
Обзор бесплатных и платных CRM, помогающих вести проекты и отслеживать важные задачи.

Как планировать рабочее время и вести учет дел в Excel?
Бесплатная программа в Excel, которая поможет вам привести дела в порядок.

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

Внешний вид рабочей области программы – таблица. А реляционная база данных структурирует информацию в строки и столбцы. Несмотря на то что стандартный пакет MS Office имеет отдельное приложение для создания и ведения баз данных – Microsoft Access, пользователи активно используют Microsoft Excel для этих же целей. Ведь возможности программы позволяют: сортировать; форматировать; фильтровать; редактировать; систематизировать и структурировать информацию.

То есть все то, что необходимо для работы с базами данных. Единственный нюанс: программа Excel – это универсальный аналитический инструмент, который больше подходит для сложных расчетов, вычислений, сортировки и даже для сохранения структурированных данных, но в небольших объемах (не более миллиона записей в одной таблице, у версии 2010-го года выпуска ).

Структура базы данных – таблица Excel

База данных – набор данных, распределенных по строкам и столбцам для удобного поиска, систематизации и редактирования. Как сделать базу данных в Excel?

Вся информация в базе данных содержится в записях и полях.

Запись – строка в базе данных (БД), включающая информацию об одном объекте.

Поле – столбец в БД, содержащий однотипные данные обо всех объектах.

Записи и поля БД соответствуют строкам и столбцам стандартной таблицы Microsoft Excel.

Если Вы умеете делать простые таблицы, то создать БД не составит труда.



Создание базы данных в Excel: пошаговая инструкция

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

Как создать базу данных клиентов в Excel:

1. Вводим названия полей БД (заголовки столбцов).



2. Вводим данные в поля БД. Следим за форматом ячеек. Если числа – то числа во всем столбце. Данные вводятся так же, как и в обычной таблице. Если данные в какой-то ячейке – итог действий со значениями других ячеек, то заносим формулу.



3. Чтобы пользоваться БД, обращаемся к инструментам вкладки «Данные».



4. Присвоим БД имя. Выделяем диапазон с данными – от первой ячейки до последней. Правая кнопка мыши – имя диапазона. Даем любое имя. В примере – БД1. Проверяем, чтобы диапазон был правильным.

Основная работа – внесение информации в БД – выполнена. Чтобы этой информацией было удобно пользоваться, необходимо выделить нужное, отфильтровать, отсортировать данные.

Как вести базу клиентов в Excel

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

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



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

Данные в таблице распределились по сроку заключения договора.

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

БД в процессе деятельности фирмы разрастается до невероятных размеров. Найти нужную информацию становится все сложнее. Чтобы отыскать конкретный текст или цифры, можно воспользоваться одним из следующих способов:

1. Одновременным нажатием кнопок Ctrl + F или Shift + F5. Появится окно поиска «Найти и заменить».



2. Функцией «Найти и выделить» («биноклем») в главном меню.

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

В программе Excel чаще всего применяются 2 фильтра:

• Автофильтр;
• фильтр по выделенному диапазону.

Автофильтр предлагает пользователю выбрать параметр фильтрации из готового списка.

1. На вкладке «Данные» нажимаем кнопку «Фильтр».



2. После нажатия в шапке таблицы появляются стрелки вниз. Они сигнализируют о включении «Автофильтра».



3. Чтобы выбрать значение фильтра, щелкаем по стрелке нужного столбца. В раскрывающемся списке появляется все содержимое поля. Если хотим спрятать какие-то элементы, сбрасываем птички напротив их.



4. Жмем «ОК». В примере мы скроем клиентов, с которыми заключали договоры в прошлом и текущем году.



5. Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры».



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

Готово!

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

1. Выделяем те данные, информация о которых должна остаться в базе видной. В нашем случае находим в столбце страна – «РБ». Щелкаем по ячейке правой кнопкой мыши.



2. Выполняем последовательно команду: «фильтр – фильтр по значению выделенной ячейки». Готово.

Если в БД содержится финансовая информация, можно найти сумму по разным параметрам:

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

Порядок работы с финансовой информацией в БД:

1. Выделить диапазон БД. Переходим на вкладку «Данные» – «Промежуточные итоги».



2. В открывшемся диалоге выбираем параметры вычислений.

Инструменты на вкладке «Данные» позволяют сегментировать БД. Сгруппировать информацию с точки зрения актуальности для целей фирмы. Выделение групп покупателей услуг и товаров поможет маркетинговому продвижению продукта.

Готовые образцы шаблонов для ведения клиентской базы по сегментам.

1. Шаблон для менеджера, позволяющий контролировать результат обзвона клиентов. Скачать шаблон для клиентской базы Excel. Образец:



2. Простейший шаблон.Клиентская база в Excel скачать бесплатно. Образец:

Шаблоны можно подстраивать «под себя», сокращать, расширять и редактировать.

Этот текст написан в Сообществе, в нем сохранены авторский стиль и орфография

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

Особенности: автоматическая сортировка базы клиентов по алфавиту и легкое открытие новой карточки. После работы с любой карточкой её нужно закрыть сохранением. Единственное условие!
Приложение легко адаптируется из-под Эксель в Гугл таблицы или Оупен офис, работает в WPS офисе.
Алгоритм работы — макрос, нужно использовать не только в стоматологии, а для ведения любой базы данных в Эксель, таблицах Гугла или в любых других электронных таблицах!

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

Ссылка на шаблоны таблицы

Как подготовить данные для сводной таблицы Excel?

Как составить и проанализировать отчеты о прибыльности клиентов, об объемах продаж клиентам, в разрезе ассортимента продукции?

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

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

К сведению

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

Подготавливаем данные для сводной таблицы Excel

Почему именно сводные таблицы Excel? Это продиктовано двумя важными факторами:

1) учет продаж ведут в специализированных базах данных, набор собственных отчетов в которых ограничен, а для создания дополнительных нужны услуги программиста. Одновременно большинство современных учетных систем позволяют рядовым пользователям экспортировать данные в Excel;

2) сведения о продажах в нужных аналитиках ведут в Excel.

Чтобы воспользоваться инструментом Excel «Сводные таблицы», важно корректно подготовить таблицу-источник.

Требования к таблице-источнику:

• все столбцы таблицы должны иметь уникальные заголовки;

• названия столбцов («шапка») должны помещаться в одну строку, многоуровневые «шапки» недопустимы;

• в каждом столбце данные одного типа — только текст, только суммы и т. д.;

• внутри таблицы не должно быть полностью пустых строк и столбцов;

• не должно быть объединенных ячеек;

• таблицу следует преобразовать в «умную»:

вкладка Главная → Стили → Форматировать как таблицу.

Дополнительное требование — в таблице-источнике нужно сформировать данные в разрезе аналитик, которые:

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

• во-вторых, это достоверные, полные, актуальные данные.

К сведению

Порядок расположения столбцов в таблице значения не имеет. Перечень граф в таблице-источнике определяется учетной политикой и спецификой компании.

В таблице 1 представлены данные о продажах условной компании-производителя обуви, сумок и прочей кожгалантерейной продукции. Как видим, при ее составлении соблюдены все требования, которые мы перечислили.

И теперь нам нужно определить, кто из клиентов наиболее выгодный для компании: ООО «Крокус» (строка 212), которое закупило сапоги женские на сумму 128 216 руб., или ООО «Стэп» (строка 214), которое закупило туфли женские на сумму 143 152 руб.

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

Составляем отчеты о прибыльности клиентов

На этом этапе мы с помощью сводных таблиц определяем клиентов-лидеров,  приносящих компании максимум валовой прибыли.

Отчеты в сводных таблицах предназначены:

• для систематизации анализа больших объемов данных;

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

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

• перемещения строк в столбцы или столбцов в строки;

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

• формирования кратких наглядных отчетов.

Отчет в виде сводной таблицы часто используют, если нужно проанализировать связанные итоги, особенно в тех случаях, когда нужно сложить длинный перечень показателей (например, перечень сделок (см. табл. 1)), а объединенные данные или промежуточные итоги позволят взглянуть на данные с различных точек зрения или сравнить значения для схожих данных.

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

вкладка Вставка → Таблицы → Сводная таблица → На новый лист.

Список полей сводной табл. 2 (см. также рис. 1):

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

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

• значения — Валовая прибыль. По полям, которые перенесены в эту область, выполняются все расчеты исходных данных;

• фильтр отчета. Эта область является необязательной и находится в верхней части сводной таблицы. Помещение полей в область фильтра отчета позволяет фильтровать данные в таблице. Мы применять фильтр не будем.

Рис. 1. Список полей сводной таблицы

Чтобы визуализировать наиболее выгодных клиентов, но при этом обойтись без дополнительных диаграмм, в табл. 2 применим условное форматирование двух видов (рис. 2):

1) вкладка Главная → Стили → Условное форматирование → Правила выделения ячеек → Больше — задано условие «Форматировать ячейки, которые БОЛЬШЕ 500 000 руб.» и в нашем примере выбрана зеленая заливка и темно-зеленый текст (см. табл. 2). При необходимости экономист может задать другой нужный ему критерий визуализации;

2) вкладка Главная → Стили → Условное форматирование → Гистограммы (красные).

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

Рис. 2. Визуализация наиболее выгодных клиентов

Далее определим процентный вклад клиентов в прибыль (табл. 3).

Список полей сводной табл. 3:

• названия строк — Клиент;

• значения — валовая прибыль.

В таблице 3 применены дополнительные вычисления:

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

Мы рекомендуем

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

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

Сегодня мы сделаем то же самое, но уже в настоящей базе данных и с таблицами. 

Что понадобится

Это простой проект, поэтому всё, что нам будет нужно, — это установленная MySQL на домашнем компьютере или на сервере. Удалённо подключаться к самой базе мы пока не будем, а вместо этого попрактикуемся в SQL-запросах. Если базы нет ни там ни там, поработайте в онлайн-компиляторе SQL — главное, не перезагружайте страницу.

Мы уже коротко писали о том, что такое SQL-запрос и как он выглядит, поэтому, чтобы было проще, перечитайте статью про язык SQL, а потом возвращайтесь сюда.

Таблица с товарами

В таблице с товарами у нас будет три столбца, причём главным будет название товара:

Связь между таблицами нам понадобится для связи товаров с покупками — при продаже мы будем брать из товаров цену и уменьшать остаток на складе.

Чтобы сделать такую таблицу, откроем консоль MySQL командой mysql -u root и выберем нашу учебную базу thecodeDB командой USE thecodeDB:

Теперь создадим в базе таблицу с товарами:

CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INT,
price INT
);

Разберём команду подробнее:

Заполняем товары

Сейчас таблица пустая — мы в этом убедимся, выполнив команду SELECT * FROM goods;, что означает «Выбери все записи из таблицы goods»:

Заполним таблицу товарами, используя расширенную версию команды INSERT, — в ней нужно явно указать, какое значение в какое поле отправляется:

INSERT INTO goods SET
product = 'стол',
count = 2,
price = 3000;

Проверим, добавилась ли запись — выведем всё содержимое таблицы с товарами:

Точно так же добавим два остальных товара:

Заполняем таблицу с клиентами

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

Ещё мы сделаем поле id, где будет храниться код покупателя. Обратите внимание на параметр AUTO INCREMENT — он означает, что при каждом добавлении нового клиента в базу код покупателя будет автоматически увеличиваться на единицу.

CREATE TABLE clients (
name VARCHAR(40),
phone VARCHAR(10) UNIQUE,
id INT AUTO_INCREMENT PRIMARY KEY
);

Заполним таблицу первыми клиентами, при этом id нам указывать не нужно — база сама будет вести нумерацию клиентов:

INSERT INTO clients SET
name = 'Миша',
phone = 9208381096;

INSERT INTO clients SET
name = 'Наташа',
phone = 9307265198;

INSERT INTO clients SET
name = 'Саша',
phone = 9307281096;

Cоздаём таблицу с покупками и связываем всё вместе

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

Чтобы это сделать, нам понадобится параметр FOREIGN KEY, который отвечает за связь главной и зависимой таблицы. Работает он так: 

Сделаем тестовую покупку — добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:

INSERT INTO orders SET
product = 'табурет',
amount = 2,
client_id = 1;

Но если мы попробуем добавить в таблицу запись о покупке товара, которого нет в таблице с товарами, база выдаст ошибку. Всё дело в том, что параметр FOREIGN KEY сначала проверит, есть ли указанный товар в таблице с товарами, и если его нет — не даст ничего записать в таблицу:

Что дальше

Чтобы посмотреть информацию о клиенте, который сделал заказ, можно использовать команду SELECT * FROM orders, clients WHERE orders.client_id = clients.id;. 

Распарсим этот запрос: 

SELECT — «выбери», то есть «выведи», «достань»;

* — «всё»;

FROM orders, clients — из таблиц orders и clients;

WHERE — если оно подходит под условие, что…;

orders.client_id = clients.id; — …айдишник клиента в таблице orders совпадает с айдишником клиента в таблице clients.

В ответ на такой запрос база данных выведет все записи из обеих таблиц с заказами и клиентами, где совпадает id клиента:

SELECT — одна из основных команд в SQL-запросах, и с ней мы будем работать чаще всего. У неё много параметров и возможностей для конструирования запросов, поэтому в следующий раз мы займемся только ей.