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

Россия+7 (495) 960-65-87
Шрифт:
3 22594

Ни для кого не секрет, что объемы контекстной рекламы постоянно растут. И со временем специалисту по контекстной рекламе приходится сталкиваться с ситуацией, когда необходимо подготовить несколько тысяч уникальных объявлений с вхождением ключевого слова в заголовок и текст. И все это в максимально сжатые сроки. Именно в этот момент на помощь приходят функции 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 в рекламе — пожалуйста, оставляйте примеры в комментариях к материалу!


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


Новые 
Новые
Лучшие
Старые
Сообщество
Подписаться 
Подписаться на дискуссию:
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
SEOnews и Serpstat запускают конкурс для интернет-маркетологов
Marina Lagutina
1
комментарий
0
читателей
Полный профиль
Marina Lagutina - Добрый день! Видимо я из тех, кто пытается последней вскочить в уходящий поезд. Ночью написала статью на тему "обзор инструментов контент-маркетинга". Своего блога нет. Отправила вам не мейл. Я еще могу у вас разместиться или искать, кто возьмет статью к себе в блог?
«Я оптимизировал сайт, а он не в ТОП! Что делать?»
Павел Горбунов
7
комментариев
0
читателей
Полный профиль
Павел Горбунов - Как можно в инструменте tools.pixelplus.ru/tools/text-natural сравнить текст со страницы конкурента и со своей страницы? Я вижу возможность только для проверки одного урла.
Монетизация сайта. Как, когда, сколько?
Гость2
1
комментарий
0
читателей
Полный профиль
Гость2 - Руслан! Спасибо за ваш сервис и за данную статью в частности! С апреля являюсь вашим пользователем - очень доволен как сервисом, так и уровнем заработка! Еще раз спасибо, удачи вашему проекту!
Мир глазами поисковых систем
Александр Рунов
7
комментариев
0
читателей
Полный профиль
Александр Рунов - Какой регион, если не секрет? В Мск, в ряде ВК тематик (в тех же "окнах" или "колесах"), без работы с внешними факторами по ВЧ запросам в ТОП не выплывешь. Хотя в большинстве направлений вполне реально.
Влияние HTTPS на ранжирование региональных поддоменов в Яндексе
Екатерина Иванова
1
комментарий
0
читателей
Полный профиль
Екатерина Иванова - Посмотрите на сколько упал трафик и на сколько потом вырос:упал на 10-20% на 1 месяц, а вырос в итоге в 5 раз. Одним мартовским трафиком всё падение перекрыли. Или можно ждать Яндекс неопределённое количество времени со стартовым уровнем трафика. Упущенные возможности и всё-такое.
7 причин не работать на биржах копирайтинга
Dasha Shkaruba
6
комментариев
0
читателей
Полный профиль
Dasha Shkaruba - Спасибо за мнение! Кстати, на бирже главреда прием анкет закрыт
День рождения SEOnews: 12 лет в эфире!
Анна Макарова
308
комментариев
0
читателей
Полный профиль
Анна Макарова - Ура )
Google.ru внесли в реестр запрещенных сайтов
Гость
1
комментарий
0
читателей
Полный профиль
Гость - Гон, все работает и будет работать. Да и пусть банят, будет как с рутрекером.
Инфографика: самые распространенные SEO-ошибки Рунета
Alex Wise
3
комментария
0
читателей
Полный профиль
Alex Wise - Спасибо, Женя, за рекомендацию! :) Андрей, чтобы понять, какой программой пользоваться, нужно сделать несколько вещей: 1. Попробовать обе: у нас в Netpeak Spider бесплатный триал на 14 дней с полным функционало; у SFSS до 500 URL всегда бесплатно, но с ограниченным функционалом. 2. Понять свой стиль работы – если вы любите полный контроль и из-за этого более высокую скорость пробивки, тогда выбирайте Netpeak Spider. Если для вас не так важна скорость и количество пробитых URL, то можно остановиться на SFSS. 3. Определиться с нужными функциями: их в обоих программах очень много и как в Netpeak Spider есть уникальные, так и в SFSS есть свои уникальные. Мы всегда ориентируемся на то, чтобы быстро и чётко показать ошибки – для этого у нас вся таблица красится в соответствующие цвета. Думайте!) И, если что, обращайтесь с вопросами – мы будем рады помочь!)
Конкурс: угадайте пятерку лидеров рейтинга «SEO глазами клиентов 2017»
Оля
1
комментарий
0
читателей
Полный профиль
Оля - 1 Ingate 2 Wezom 3 Bynjg vtlbf 4 seo energy 5 директ лайн
ТОП КОММЕНТАТОРОВ
Комментариев
910
Комментариев
834
Комментариев
554
Комментариев
540
Комментариев
483
Комментариев
373
Комментариев
308
Комментариев
262
Комментариев
224
Комментариев
171
Комментариев
156
Комментариев
137
Комментариев
121
Комментариев
97
Комментариев
97
Комментариев
95
Комментариев
80
Комментариев
77
Комментариев
67
Комментариев
60
Комментариев
59
Комментариев
55
Комментариев
53
Комментариев
52
Комментариев
46

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