Сравнение email: как вычитать списки подписчиков — Практичный email маркетинг
>
В прошлый раз мы разбирались с DOI и SOI. В этот займёмся обработкой данных о подписчиках в Экселе — проведём сравнение списков email для их последующего вычитания один из другого.
| Кстати, в блоге уже есть пара статей на тему работы с Экселем: Как делать выборку «каждый N-й» / Узнаём пол подписчика по имени |
За время, прошедшее с их публикации, они нисколько не утратили своей универсальности и актуальности. Так что продолжим развивать тему Экселя (или его прямого аналога — онлайн-таблиц Google Sheets), и ещё расширим свой арсенал работы с данными. Тем более, умение обращаться с массивами в тысячи строк как раз входит в прямые обязанности email-маркетолога.
—
Когда нужно вычитать списки
Как правило, задача сравнить списки, а затем исключить совпадающие значения — то есть вычесть один столбец с email-ами из другого — возникает на ранних этапах наладки email-маркетинга. В этот момент нужно обработать уже накопленный за время существования проекта объём данных перед тем, как импортировать его в сервис рассылок.
| Также необходимость работы со списками появляется при смене сервиса, когда данные сначала экспортируются из одного места в виде таблиц, а затем загружаются в другое. Иногда перед загрузкой с ними нужно произвести некоторые предварительные манипуляции. В том числе, сравнение списков — скажем, для исключения отписавшихся, невалидных email, сопоставления с имеющимися данными в CRM и т.п. |
Наконец, сервис рассылок мы можем не менять, но нам понадобится навести в нём порядок. Например, проект «зарос» множеством хаотичных списков, email-адреса в которых дублируются и пересекаются не по одному разу:
Это может порождать проблемы с частотой рассылок: мы отправляем копии одного и того же письма по нескольким спискам и в итоге заваливаем подписчиков, которые могут оказаться в каждом из них, однотипными сообщениями.
Такой беспорядок в целом затрудняет работу с базой. Мы до конца не контролируем не только частоту рассылок, но и не понимаем охват пользователей нашими предложениями: не перегружаем ли мы какой-то сегмент базы письмами, в то время, как какие-то другие сегменты полностью теряются из вида.
Тогда выходом становится выгрузка всех списков в Эксель, их сравнение, переработка и последующий «перезалив» в сервис в уже упорядоченном виде.
| В общем, во всех случаях, когда мы наводим порядок в данных, нам может пригодиться операция вычитания одного столбца из другого. |
—
Вычитание списков
Скажу сразу, что не являюсь каким-то крутым специалистом в Экселе и не уверен, что данный способ вычитания самый оптимальный. Тем не менее, он весьма несложный и быстрый — так что, опробовав его раз в деле, я вооружился им без каких-либо последующих усовершенствований и доработок.
Предположим, у нас есть 2 списка с email-ами в Экселе: A и B. Для наглядности сделаем их не очень большими — 10 и 7 строк соответственно — хотя в реальности, конечно речь будет идти скорее всего про тысячи строк (иначе мы без проблем могли бы сравнить данные чисто визуально).
| Также, кроме email, в списках могут быть и другие параметры — имена, гео, прочее — но их для простоты мы тоже отложим в сторону. Сосредоточимся только на email, как на уникальных идентификаторах подписчиков. Прочие данные всегда можно будет «пристегнуть» к ним задним числом, уже после сравнения. |
Нам известно, что среди A и B могут быть пересечения. Поэтому мы хотим сравнить их, чтобы в результате устранить дубли: оставить в списке A только тех подписчиков, которых нет в B, и наоборот.
Воспользуемся для этих целей экселевской формулой СЧЁТЕСЛИ.
Работает она следующим образом: берёт значение из одного столбца, последовательно сравнивает его с диапазоном значений в другом столбце, и, если находит совпадение, то возвращает 1 (в противном случае, 0):
Делаем сравнение списков email:
→ Берём список A и размещаем его на листе Экселя в первом столбце.
→ Берём список B и копируем его в соседний столбец на том же листе, чтобы значения A и B наглядно и компактно оказались друг рядом с другом:
→ В третьем столбце (C) вставляем озвученную ранее формулу =СЧЁТЕСЛИ($A$2:$A$11;B2):
Значки $ фиксируют нам диапазон: копирование формулы в другие ячейки не будет сдвигать его так, как это делал бы Эксель без дополнительного «закрепления».
Буквально же эта формула означает следующее:
| Мы берём значение из ячейки B2 — наш первый email из списка B. «Прогоняем» его по всем адресам из столбца A. Если находится совпадение — email из B2 есть где-то в A — мы получаем напротив исходной ячейки 1. Если значение B2 в столбце A отсутствует, мы получаем на выходе 0. |
→ Распространяем формулу на всю длину столбца B, получая значения 1 и 0 напротив каждого из наших сравниваемых email-ов:
→ Копируем результаты сравнения в четвёртый столбец (D), используя опцию «Вставить только значения», чтобы «отвязаться» от исходной формулы, которая зависит от содержания конкретных ячеек:
Если мы этого не сделаем, результаты сравнения у нас собьются в ходе последующей сортировки. При этом столбец C можно далее скрыть, чтобы «не путался под ногами».
→ Выполняем сортировку в столбце D от больших значений к меньшим, распространяя её на соседние столбцы:
Вуаля: теперь в начале списка у нас оказались те email из столбца B, которые совпали со значениями из столбца A.
Далее мы уже можем воспользоваться этим результатом: скопировать его, перенести в другое место — например, сервис рассылок — удалить из списка и т.п. Сравнение email выполнено 🙂
—
Бонус: как перемешать значения в Эксель
Напоследок посмотрим ещё на один небольшой приём работы с данными в Экселе: как случайным образом перемешать значения в столбце.
Это тоже иногда пригождается при работе с рассылками: при формировании случайных выборок, перемешивании списка для его равномерного «прогрева» и других похожих задачах.
| Используем для этого формулу СЛЧИС, которая возвращает в ячейки случайные десятичные дроби меньше 1. |
→ Берём список email, который нужно перемешать, и размещаем его в первом столбце Экселя:
→ В соседний столбец рядом с каждым email вставляем формулу =СЛЧИС(), получая в ячейках случайное число меньше 1:
Значения формулы в соседний столбец можно не копировать, т.к. в данном случае нас интересуют не столько конкретные значения, сколько сам случайный результат срабатывания формулы.
→ Выполняем сортировку во втором столбце (по возрастанию или убыванию, особо не принципиально), распространяя её на соседний столбец с email-ами:
Всё готово — наши email-адреса перемешаны случайным образом.
—
Резюме
Несмотря на обилие сервисов и программ по работе с данными, Эксель по-прежнему крутая и полезная штука:) Пользуемся!
 |
[В следующий раз займёмся постановкой целей для email-маркетинга]. |
P.S. Моя книга «Практичный email маркетинг» теперь доступна и в электронном виде. Если ещё не купили, всячески рекомендую.
Также, если вы пока не подписались на рассылку моего блога — самое время это сделать 😉
