Полезные формулы Excel и Google Таблиц для маркетолога: от семантики до рекламы

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

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

Формулы Excel и Google Таблиц для работы с рекламой

Заменить значение в большом количестве объявлений

Представьте, вы настроили рекламу на тысячу ключей, например, для санатория, расписали в ней преимущества продукта и успешно запустили. Но через пару недель руководство говорит, что теперь программ в санатории не 24, а 30 — добавили шесть новых.

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

Для Excel и Google Таблиц: =ПОДСТАВИТЬ(text_to_search;search_for;replace_with)
text_to_search — строка или адрес ячейки, где нужно заменить одно слово на другое;
search_for — фрагмент или слово, которые нужно найти и заменить;
replace_with — слово или фрагмент, на которые меняем search_for.

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

Убрать лишние пробелы в объявлениях и минус-словах

Лишние пробелы — зло в любом виде текста, будь то статья, пост в соцсетях или текст на баннере. Но, когда вы создаете объявления, лишние пробелы мешают дважды: во-первых, они съедают драгоценное место, а во-вторых, «Директ Коммандер» ругается, если загрузить минус-слова с лишними пробелами.

Чтобы быстро удалить пробелы, можно использовать функцию «Сжать пробелы» — =СЖПРОБЕЛЫ(). В скобках указывается номер ячейки, из которой нужно удалить лишние пробелы.

Для Excel и Google Таблиц =СЖПРОБЕЛЫ(диапазон)

Показать клиенту сразу все варианты объявлений для РСЯ и КМС

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

=IMAGE(ссылка; [режим]; [высота]; [ширина])
Функция IMAGE есть только в Google Таблицах и только на английском языке.

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

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

Этот способ можно использовать не только в объявлениях. Так удобно согласовывать изображения для каталога товаров или для постов в блоге.

Формулы Excel и Google Таблиц для работы с семантикой

Убрать мусорные запросы из семантики

Собрать семантическое ядро для обладателя Macbook целая проблема. Есть ощущение, что SEOшники не любят яблочников, потому что ни SlovoEb, ни KeyCollector под MAC не адаптированы.

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

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

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

Для Excel и Google Таблиц:
=НАЙТИ(искать; текст; [начиная с])
=ПОИСК(искать; текст; [начиная с])
Искать — текст, который необходимо найти; Текст — где искать; Начиная с — необязательный атрибут, который нужен, если вы хотите пропустить часть символов в строке.

Функции отличаются тем, что «Найти» учитывает регистр, а «Поиск» — нет.

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

После того как вы добавите формулу во все ячейки, нажмите «Сортировать лист А → Я», и все строки, содержащие это слово, окажутся сверху. Теперь их нужно просто удалить. Очередность ключей при этом не нарушится, но если вдруг — то просто отсортируйте этот столбец от Я до А, то есть от большего к меньшему.

Убрать дубли ключей

Собирать ключи можно с помощью разных сервисов: SlovoEb, KeyCollector, а еще можно посмотреть семантику конкурентов. Их данные могут совпадать, а могут отличаться. Чтобы привести все к единому виду без долгого и нудного ручного труда, можно использовать функцию UNIQUE.

Для Excel: Вкладка «Данные» > Удалить Дубликаты
Для Google Таблиц: =UNIQUE(диапазон)

Проверить длину мета-описания

Рекомендуемая длина Title — 70-80 символов, а Description — 160-180. Если вы работаете над мета-тегами для большого количества страниц, то уследить за этими параметрами вручную можно, но сложно. Гораздо проще автоматизировать процесс с помощью формулы «Длина строки».

Для Excel и Google Таблиц: =ДЛСТР(диапазон)

Не буду подробно описывать, как она работает: это видно на примере ниже. То же самое можно проделать и с Description.

Какие мета-теги подходят, а какие требуют доработки, можно также быстро определить с помощью функции «Условное форматирование». Чтобы ее найти, нажмите в верхнем меню кнопку «Формат» и в открывшемся меню — «Условное форматирование».

Дальше выберите столбец «Длина Title», примените к нему условие «Меньше или равно» и укажите максимальную длину мета-тега. В нашем случае это 80. Затем задайте определенное выделение. Например, все правильные title функция выделит зеленым.

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

Формулы Excel и «Google Таблиц» для работы с рассылками

Добавить данные о подписчиках

Представим, что у вас есть база клиентов, которую вы хотите добавить в сервис почтовых рассылок, например, MailChimp. Вы знаете не только e-mail клиента, но и его ФИО. Чтобы сервис точно знал, что из введенных данных имя, что фамилия, а что отчество, их нужно разделить на три столбца и при выгрузке задать значение для каждой группы.

Но о функциях Mailchimp в другой статье, а пока разберемся, как разбить данные. Для этого есть функция SPLIT.

Для Google Таблиц: =SPLIT(диапазон; «разделитель»)
Для Excel: Данные — Текст по столбцам — С разделителями

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

Какие полезные формулы знаете вы? Расскажите в комментариях. Давайте поможем друг другу оптимизировать работу.