×
Россия +7 (495) 139-20-33

Excel на службе специалиста по контекстной рекламе

Россия +7 (495) 139-20-33
Шрифт:
3 29605

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

Задача 1: Как преобразовать ключевое слово в заголовок

Пожалуй, самая распространенная задача. Допустим, у вас есть 1000 ключевых запросов. Чтобы не писать объявления вручную, но при этом контролировать содержание каждого заголовка, предпримем ряд шагов.

Шаг 1. Удаляем минус-слова

Сначала удалим минус-слова из будущего заголовка. В этом нам поможет стандартный инструмент «Заменить». Вводим в поле «Найти» " -*" (перед минусом поставьте пробел, чтобы не убрать слова с дефисом), поле «Заменить» оставляем пустым. Жмем «Заменить все».

Рис. 1 Функция «Найти и заменить»

Шаг 2. Делаем первый символ заглавным

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

В итоге вы получаете объявления вида «Красивые Крокодилы Недорого»

Но Яндекс такие объявления не пропускает, поэтому лучше пользоваться следующей формулой: СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))). Она делает прописными только первые буквы ячеек.

Рис. 2 Пример замены первой буквы в ячейке

Теперь нам останется добавить продающее окончание. Например, «Купите сейчас!» или «Скидки!»

Шаг 3. Добавляем продающую концовку

Под концовкой подразумеваются мотивирующие фразы, вроде «Купить!», «Скидки!» или просто «!». Нужно подобрать несколько вариантов (мы используем обычно от 2 до 4) для заголовков разных длин.

Для добавления окончаний будем использовать функции СЦЕПИТЬ, ЕСЛИ и ДЛСТР.

Сначала определимся, какие окончания нам нужны. Возьмем фразы «Купить со скидкой 50%!» «Скидки 50%!», «!» и будем использовать их в зависимости от длины заголовка.

Затем посчитаем длину этих окончаний с помощью функции ДЛСТР. Замечу, что считать нужно сразу с точкой и пробелом перед добавочной фразой: «. Скидки 50%!». Таким образом, для разных заголовков у нас подобраны разные концовки — где-то длинные, где-то короткие.

Рис. 3 Измеряем длину «продающего хвоста»

Затем применяем несложную формулу, которая проверит размер ячейки и подставит подходящий заголовок:

=ЕСЛИ(ДЛСТР(A2)

Рис. 4 Сцепление заголовков и «продающего хвоста»

Рассмотрим подробнее, что происходит при применении этой формулы. Функция ЕСЛИ проверяет ячейку на соответствие условию. Таким образом, если количество знаков в ячейке А2 меньше 19, подставляется первый заголовок; если количество знаков больше 9, но меньше 20 — то подставляется второе окончание. Если же и это условие не выполняется (количество знаков больше 20), то подставляется третье окончание.

Можете использовать эту же формулу, заменив данные в квадратных скобках на свои данные (скобки тоже необходимо стереть).

=ЕСЛИ(ДЛСТР(A2)

Задача 2. Сцепка сложных текстов. Контроль количества знаков

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

1 Столбец — это номер фразы. Он сохраняет порядок строк для вставки в исходную таблицу.

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

=СЦЕПИТЬ(СТРОЧН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1)))

Рис. 5 Таблица для сцепки сложных текстов

3 Столбец — итог. Тут будет конечная фраза. Она формируется простой сцепкой 2, 4 и 5 столбца. В нашем случае это формула =СЦЕПИТЬ(B2;D2;E2)

4-5 Столбцы — самые важные — начало и конец фразы. С ними мы и будем работать. Необходимо учитывать, что при сцепке не ставятся пробелы. Так что добавляйте пробел в начало и в конец каждой фразы.

6 Столбец — отслеживает оставшиеся символы. Для расчета 75 символов в ячейке берем формулу =75-ДЛСТР(B2)-ДЛСТР(D2)-ДЛСТР(E2). Рекомендуем сразу отсортировать таблицу по длине этого столбца. Таким образом вы сможете сцеплять объявления массивами, не зацикливаясь на подсчете символов в каждой ячейке.

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

Задача 3. Подсчет слов в ячейке

Как известно, Яндекс не позволяет добавлять «ключи» длиннее 7 слов. Поэтому, когда вы формируете ключевые запросы автоматически, необходимо считать их длину. Это можно сделать уже на стадии обработки в Excel. Количество слов в ячейке равно количеству пробелов и дефисов + 1. Мы используем формулу =ДЛСТР(A1)*2-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))-ДЛСТР(ПОДСТАВИТЬ(A1;"-";«"))+1.

Супер-задача

Массовая замена текста в шаблонах. Работа с макросами

Задача следующая: нужно создать 100 похожих кампаний, в которых будут меняться только часть ключевого слова, заголовка и текста. К примеру, сайт отеля. Есть 1000 городов, для каждого города нужна своя кампания, но при этом ключевые слова и тексты особенно не отличаются: все они содержат название города, страну, и ссылку на конкретную страницу.

Обозначим изменяемую часть словом «Параметр»:

Рис. 6 Пример оформления файла для работы с макросом замены по шаблону

Если у вас 1000 таких кампаний, то ручная замена займет много времени. В таких случаях самым разумным будет использование макросов VBA.

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

Работа с шаблонами

Первое, что нам понадобится, — шаблон, в котором наличествуют Параметры (слова в тексте, которые будут меняться от файла к файлу). Например: «Забронировать отели в городе [Параметр1]». Вместо [Параметр1] будем подставлять наше значение.

Таких параметров может быть сколько угодно, но не стоит увлекаться и брать больше 7-10.

1. Добавляем в шаблонный файл ключевые слова, заголовки, тексты и ссылки, в которых изменяющийся параметр отмечен ключевым словом. В начало файла выносим табличку с этими ключевыми словами. В нашем случае — это ключевые слова Параметр1, Параметр2 и Параметр3 и их значения для конкретного файла (Рис. 6)

2. Далее нам нужно создать Макрос: «Вид-Макросы». Вводим в поле «Имя» название и нажимаем «Создать».

Рис 7. Создание макроса

В окне вводим код, примеры которого для решения разных задач можно найти на форумах VBA. Но нам нужно, чтобы макрос искал в поле слова «Параметр1», «Параметр2» и «Параметр3» и заменял их значениями из соответствующих ячеек — С1, С2 и С3 (наши ячейки, содержащие необходимые данные).

Sub ИмяМакроса()

Dim a1 As String

Dim a2 As String

a1 = Range("C1«).Text

a2 = «Параметр1»

Selection.Replace What:=a2, Replacement:=a1, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False

Dim b1 As String

Dim b2 As String

b1 = Range("C2«).Text

b2 = «Параметр2»

Selection.Replace What:=b2, Replacement:=b1, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False

Dim c1 As String

Dim c2 As String

c1 = Range("C3«).Text

c2 = «Параметр3»

Selection.Replace What:=c2, Replacement:=c1, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False

End Sub

Копируем этот код в окно с кодом макроса (Рис. 8):

Рис 8. Рабочая область для редактирования кода макроса.

Макрос ищет сочетание букв «Параметр1» и подставляет вместо него текст из ячейки С1, и так далее. В данном коде a1, a2, b1, b2, c1, c2 — это переменные. Их имена могут быть любыми.

Количество таких параметров не ограничено — копируем код, меняем переменные (отмечены красным) на произвольные буквы и цифры и подставляем нужные значения для поиска и замены. Готово! Одной кнопкой можно производить до 20 разных замен. Задача, раньше занимавшая 2 часа рабочего времени, выполняется за 10 минут.

Теперь настало время применить наш макрос:

Шаг 1. Выделяем область, в которой будет произведена замена

Шаг 2. Выбираем меню Вид — Макросы

Шаг 3. Нажимаем «Выполнить»

Вот и все! Все изменения произведены.

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

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


(Голосов: 5, Рейтинг: 5)
Читайте нас в Telegram - digital_bar

Есть о чем рассказать? Тогда присылайте свои материалы Ане Макаровой


Новые 
Новые
Лучшие
Старые
Сообщество
Подписаться 
Подписаться на дискуссию:
E-mail:
ОК
Вы подписаны на комментарии
Ошибка. Пожалуйста, попробуйте ещё раз.
Отправить отзыв
  • Дарья
    2
    комментария
    0
    читателей
    Дарья
    больше года назад
    =ДЛСТР(A1)*2-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))-ДЛСТР(ПОДСТАВИТЬ(A1;"-";«"))+1 Сбило с толку разные кавычки. Пара есть))
    -
    0
    +
    Ответить
  • Дарья
    2
    комментария
    0
    читателей
    Дарья
    больше года назад
    =ДЛСТР(A1)*2-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))-ДЛСТР(ПОДСТАВИТЬ(A1;"-";«"))+1 В этой формуле в месте «" не хватает пары для ": "«"

    -
    0
    +
    Ответить
  • Evgeniy Cheskidov
    1
    комментарий
    0
    читателей
    Evgeniy Cheskidov
    больше года назад
    СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1)))

    АД)))

    Есть мнение что копипаст столбца с заголовками в ворд, shift+f3 2 раза и копиппаст обратно будет несколько быстрее чем нафигачить такую формулу :)

    Но очень круто, я бы никогда не додумался через "сцепить" реализовать эту задачу.
    -
    2
    +
    Ответить
ПОПУЛЯРНЫЕ ОБСУЖДЕНИЯ НА SEONEWS
Как выбрать CMS для интернет-магазина
Колян Гусляков
2
комментария
0
читателей
Полный профиль
Колян Гусляков - Если же у вас не получилось решить данную проблему, предлагаю воспользоваться авторской сборкой от neoseo. neoseo.ru/internet-magazin-seo-magazin-model. Они предоставляют дополнительные модули для работы и взаимодействия с вашим сайтом, современный дизайн, качественную оптимизацию и продвижение. Советую попробовать, не пожалеете.
«Юзабилити-лаборатория»: оставляйте заявку на участие!
Анна Макарова
378
комментариев
0
читателей
Полный профиль
Анна Макарова - Антон, добрый день! Ваш сайт не попал в основную выборку для юзабилити-анализа, но эксперты постараются сделать по вашему сайту видеоразбор (ю-ревью). Будем держать вас в курсе )
Тест по SEO – проверь свой уровень знаний
Артем Дорофеев
8
комментариев
0
читателей
Полный профиль
Артем Дорофеев - Полный текст вопроса со скриншотом панели прикладываю. Итого, что имеем: - на скриншоте отмечено, что это фильтр МПК - сайт коммерческий - рекламы на сайте нет С вероятностью 95% это ошибка (которая уже дважды случалась в Яндексе), когда они случайно "закосили" неповинные сайты. Тогда по запросу Платону фильтр быстренько снимали. Но вопрос даже не на знание этого нюанса. В любой непонятной ситуации, прежде чем что-либо предпринимать (особенно переписывать весь контент на сайте или менять дизайн, как указано в других вариантах) - фильтр следует подтвердить. Правильный ответ: "Написать письмо в техподдержку Яндекса".
Выбираем CMS для сайта с точки зрения SEO: базовые требования
SEO.RU
6
комментариев
0
читателей
Полный профиль
SEO.RU - Спасибо за замечание, действительно была допущена неточность - возможно информация была не так давно обновилась. Данные в статье поправим на актуальные.
Автоматические SEO-аудиты: как напугать некорректными выводами
Trydogolik
25
комментариев
0
читателей
Полный профиль
Trydogolik - спасибо
Зачем подменять контент на сайте: разбираем инструмент и развенчиваем мифы
Trydogolik
25
комментариев
0
читателей
Полный профиль
Trydogolik - К мифу 1 добавлю Google optimize optimize.google.com Удобен для сотрудников со спец. знаниями и без них. Подмена контента настраивается за счет многих вариантов страниц и условий настроек распределения трафика. Варианты страниц можно легко создать копипастом. На мой взгляд удобно и бесплатно. Тот же Calibri и программисты в любом случае, пусть не за это, но где-то берут свою комиссию. (чисто мое мнение). В качестве цены отдаешь такой ресурс как личное время и копейки за внесение двух первоначальных изменений в html шаблон страниц. Но если ваша digital-кампания имеет совесть то оплата максимум час, а то и бесплатно. Интернет-маркетолог сам должен уметь это делать. Все просто + подробную инструкцию прилагают на языке пользователя. + за поддомены платить не надо, они вообще не нужны. + взаимодействие с: - Google Ads; - Google Tag; - Google Analytics просто прекрасно отслеживается по всем вариантам параллельно. Это помогает до деталей нарисовать профиль ЦА по любому "кредиту - отдельно на машину и отдельно на бизнес". Что с аналитикой у Calibri на этот счет? Сам с Calibri не разу не работал, всегда работаю с первоисточником. + к мифу 3 в том что это и есть детище поисковой системы и рекламного инструмента. Яша тоже спокоен будет так как сам везде навязывает Турбо-страницы (считаю их не удобными).- Как "-" (минус) вижу то, что может повлиять на скорость загрузки сайта. Но если у вас не конструктор, а темболее Landing Page, то о скорости можно не волноваться. Но это чисто мое мнение. В остальном согласен с автором.
100+ ресурсов по SEO для изучения поисковой оптимизации с нуля
Марина Ибушева
59
комментариев
0
читателей
Полный профиль
Марина Ибушева - Спасибо за добавление. Мы уже работаем над отдельным материалом про курсы, потому что одной статьи мало, чтобы охватить все крутое по обучению)
7 способов увеличить авторитетность сайта «в глазах» поисковых систем
Grigo5
4
комментария
0
читателей
Полный профиль
Grigo5 - Понятно.
Монетизируйте свой сайт вместе с VIZTROM
VIZTROM
3
комментария
0
читателей
Полный профиль
VIZTROM - Добрый день! Так как мы сейчас работаем в приватном режиме, Вы врятли можете объективно оценить нашу рекламную площадку. У нас будет официальный запуск 3 марта для вебмастеров. Приглашаем Вас присоединиться к нам и лично посмотреть наш функционал.
SEO must go on! Почему в кризис нельзя останавливать продвижение сайта
everystraus
0
комментариев
0
читателей
Полный профиль
everystraus - Мы даже варианты не рассматривали. Если проект неустойчив, сразу предлагали сбавить обороты до минимума, но и так, чтоб не свалиться в штопор. Именно по СЕО чаще всего.
ТОП КОММЕНТАТОРОВ
Комментариев
910
Комментариев
834
Комментариев
554
Комментариев
540
Комментариев
483
Комментариев
378
Комментариев
373
Комментариев
262
Комментариев
249
Комментариев
171
Комментариев
156
Комментариев
140
Комментариев
121
Комментариев
111
Комментариев
97
Комментариев
97
Комментариев
96
Комментариев
88
Комментариев
80
Комментариев
77
Комментариев
67
Комментариев
60
Комментариев
59
Комментариев
59
Комментариев
57

Отправьте отзыв!
Отправьте отзыв!