МТТ

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

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

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


Читайте нас в 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
Кейс: как продвинуть сайт производителя мебели на заказ в Москве
Art Moderator
2
комментария
0
читателей
Полный профиль
Art Moderator -
Рейтинг «Известность бренда SEO-компаний 2017»: народное голосование
Гость
1
комментарий
0
читателей
Полный профиль
Гость - 1) Ingate 2) Ашманов 3) Кокос 4) Russian Promo 5) Netpeak
32 инструмента в помощь SEO-специалисту
Chaser
1
комментарий
0
читателей
Полный профиль
Chaser - Рекомендую бесплатную альтернативу Frog и Netpeak - SiteAnalyzer ( site-analyzer.ru/ ) Странно что не включили в обзор
Второе дыхание ссылочного продвижения
Rookee.ru
24
комментария
0
читателей
Полный профиль
Rookee.ru - Еще лучше, когда продвижение осуществляется комплексно :)
Исследование: влияние smart-ссылок на продвижение по СЧ-запросам
Анатолий Шевчик
1
комментарий
0
читателей
Полный профиль
Анатолий Шевчик - +1097988
Контекст под SEO. Как поисковая реклама помогает позициям в органической выдаче
Сергей Дембицкий «Sima-Land.ru»
22
комментария
0
читателей
Полный профиль
Сергей Дембицкий «Sima-Land.ru» - Боже мой, неужели SEO-маги вернулись??? Открыть в роботс utm-метки для индексации и наплодить дублей...что? А расчеты в конце статьи про бюджет на SEO и контекст...откуда эти пропорции? Как по мне, SEO-магия вне Хогвартс. Спасибо, развеселили!
Все, что нужно SEO-специалисту. Обзор инструментов
Администратор Сайта
1
комментарий
1
читатель
Полный профиль
Администратор Сайта - Шаришь в seo! Сервис реально хороший
Какая боль: ТОП 10 типичных ошибок в SEO
Alex Fri
1
комментарий
0
читателей
Полный профиль
Alex Fri - Отличная статья, прикольные гифки - вносят разнообразие и прекрасно визуализируют контент))
4 способа быстро собрать теги для сайта
Рустем Низамутинов
5
комментариев
0
читателей
Полный профиль
Рустем Низамутинов - Расписал и закинул в Google Docs, а то здесь в комментариях ссылки на активны. docs.google.com/document/d/1r0TZLNrQyYLdIzDQsD5YKlMG41HUGQgEep3bxE_ij-M/edit?usp=sharing
Яндекс: как мы модерируем объявления
Гость
5
комментариев
0
читателей
Полный профиль
Гость - Это ж Яндекс, чего вы ожидали-то? Коммерческая структура с раздутым штатом, задачей которой является заработать больше денег. Любыми методами. Задача всех пользователей посадить на Директ, даже если придется разрушить суть рунета, реализуется по полной программе. Все возражающие караются и выпиливаются. И каждый из сотрудников делает все возможное и невозможное, чтобы реализовать любую дурацкую идею - за это еще и премию выхватить можно. Даже если потом придется выполнять "откат", премиальные уже заплачены. Так было с одним из фильтров Яндекса, так было с купленным им сайтом Кинопоиска, который из русскоязычной энциклопедии кино был превращен за безумные деньги в банальный платный онлайн-кинотеатр.
ТОП КОММЕНТАТОРОВ
Комментариев
910
Комментариев
834
Комментариев
554
Комментариев
540
Комментариев
483
Комментариев
373
Комментариев
322
Комментариев
262
Комментариев
234
Комментариев
171
Комментариев
156
Комментариев
137
Комментариев
121
Комментариев
97
Комментариев
97
Комментариев
95
Комментариев
86
Комментариев
80
Комментариев
67
Комментариев
60
Комментариев
59
Комментариев
57
Комментариев
55
Комментариев
54
Комментариев
53

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