[5 шагов] Готовим базу телефонов к СМС-рассылке, чтобы увеличить контактность и сэкономить

Предположим, вам нужно оповестить клиентов магазина о скидках на товары — по СМС. Вы отсегментировали базу в CRM и выгрузили список телефонов. Конечно, его можно сразу, без проверок импортировать в сервис рассылок. Но при таком подходе возникают две проблемы: 

1. Часть клиентов останется без СМС. В списке окажутся телефоны с опечатками, рассыльщик посчитает их мусором и выкинет из базы.
2. Вы переплатите за рассылку. В базе будут лишние номера: стационарные телефоны, контакты горячих линий и колл-центров, несуществующие номера. Сервис рассылок отправит на эти телефоны СМС, но сообщения никто не прочитает.

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

Сейчас покажем, как это сделать. 

1. Разделяем слипшиеся номера

Бывает, в одной ячейке пишут сразу несколько телефонов. Если их не разделить, то сервис рассылок не импортирует номера, и клиенты не получат СМС.

Чтобы разделить телефоны, применим встроенный инструмент таблиц — «Разделение текста на столбцы»:

• откроем вкладку «Данные»;
• нажмем «Разделить текст на столбцы»;
• в появившемся окне укажем разделитель — знак между двумя телефонами в ячейке. Как правило, это запятая, точка с запятой или пробел.

Останется лишь скопировать полученные номера в общий столбец:

Первым делом исправим опечатки в номерах. Существует бесчисленное множество опечаток, но мы поговорим о самых частых. И рассмотрим только те случаи, которые можно исправить массово. 

Чаще всего встречается такая опечатка: вместо цифры пишут похожую на нее букву. Легко спутать, например: 

• цифру 0 и букву «О» — и русскую «О», и английскую «O». 8-913-96O-15-5О вместо 8-913-960-15-50;
• цифру 1 с буквами «I» и «l» — большой «i» и маленькой «L». 8-9I6-3I5-20-17 вместо 8-916-315-20-17;
• цифру З и букву «3». 8-927-З40-25-17 вместо 8-927-З40-25-17; 
• цифра 4 и буква «Ч». 8-999-3Ч5-20-17 вместо 8-999-345-20-17.

Чтобы исправить опечатки, мы будем использовать автозамену. Найдем, к примеру, все буквы «O» и разом заменим их на «0». Для этого нужно:

• выделить колонку с телефонами в таблице;
• перейти в раздел «Правка» и нажать «Найти и заменить»;
• в поле «Найти» нужно ввести букву «O», в поле «Заменить» — цифру 0;
• кликнуть на «Заменить все».

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

Теперь перейдем к другим опечаткам.

3. Удаляем слова в номерах

Еще одна частая проблема — слова в номере.

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

Для поиска будем использовать регулярное выражение [A-Za-zА-яЁё]. Это специальная команда, которая говорит таблицам: «Выделите в ячейке только то, что состоит из русских и английских букв, заглавных и строчных».

Действовать будем так.

• снова перейдем в раздел «Правка» и нажмем «Найти и заменить»;
• в поле «Найти» запишем наше регулярное выражение: [A-Za-zА-яЁё]. Прямо со скобками;
• поле «Заменить на» оставим пустым;
• поставим галочку напротив «Поиск с использованием регулярных выражений». И уберем галочку напротив «Учитывать регистр». Иначе автозамена не сработает как надо.
• нажмем на «Заменить все» — и тогда во всех номера исчезнут буквы.

4. Исправляем и добавляем код страны

Существует международный формат телефона. Такие номера начинаются с «+» и кода страны. Например, российские номера — с «+7», а белорусские — с «+375».

Иногда в базу попадают номера неправильного формата.

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

Поменяем «+8» на «+7». В этом нам снова поможет привычная автозамена:

Заменим «8» на «+7». Обычная автозамена здесь не подойдет, так как она не учитывает местоположение цифры: заменит на «+7» все восьмерки, которые есть в номере.

Чтобы заменить нужную «8», применим регулярное выражение ^8. Эта команда говорит таблицам: «Найдите только восьмерки, которые стоят в начале ячейки».

Действуем по старинке:

• выделяем колонку с телефонами;
• переходим в раздел «Правка» → «Найти и заменить»;
• в поле поиска вводим «^8», а в поле замены — «+7»;
• ставим галочку напротив пункта «Поиск с использованием регулярных выражений».

Добавим +7 к номерам без кода страны. 

Сначала выведем на экран все номера, у которых отсутствует +7. Используем для этого фильтр по колонке:

• создаем фильтр для колонки с телефонами;
• нажимаем на иконку фильтра → «Фильтровать по условию» → «Ваша формула»;
• в открывшемся поле вводим формулу с регулярным выражением: =REGEXMATCH(B2:B25;"^[^+7]"). В нем «B2:B25» — диапазон ячеек с номерами. В вашем файле диапазон будет отличаться.

Дальше план такой:

1. Добавляем пустую колонку левее номеров и проставляем в каждой ячейке «+7». В нашем примере это будет колонка «B».
2. Объединяем колонки с кодами страны и телефонами — в колонке «D». Для используем формулу СЦЕПИТЬ.

Осталось скопипастить номера с кодом страны из колонки «D» в колонку с телефонами. Только для вставки будем использовать не ctrl+v, а ctrl+shift+v (cmd+shift+v на Mac). Иначе перенесем не сам телефон, а функцию, которая объединяет колонки.

5. Удаляем номера колл-центров и горячих линий на + 7 800 или 8 800

В России легко вычислить номера колл-центров и горячих линий. Они начинаются на +7 800 или 8 800.

Отправлять СМС на такие телефоны бесполезно, поэтому мы уберем их из базы. Для этого отфильтруем все телефоны, которые содержат +7 800, а затем разом их удалим:

Имейте в виду, что номер колл-центра может быть записан по-разному: «+7 800…», «+7800…», «7 800…», «7800…». Полезно проверить все варианты.

Ну все. Получилась база номеров, которую можно загружать в сервис рассылок: