Полезные формулы в excel яндекс директ
Всем-всем привет!
В работе каждого специалиста в совершенно любой области должны быть особые, полезные в работе инструменты, с помощью которых можно ускорить тот или иной процесс. В области контекстной рекламы основным рабочим инструментом является Excel, так как в нем намного быстрее и удобнее создавать рекламные кампании. К тому же он позволяет работать с действительно большими массивами данных.
В этой статье я бы хотел поделиться с Вами некоторыми полезными формулами и возможностями программы Excel, которые реально помогают ускорить рабочий процесс либо сделать его более удобным.
Погнали!
Заголовки объявлений
Любое предложение в русском, да и не только, языке должно начинаться исключительно с заглавной буквы. В Яндекс.Директ или Google Adwords заголовки рекламных объявлений обычно составляются из ключевых слов (принцип “1 ключ – 1 группа объявлений”). В таком случае нужно превратить обычные ключевые слова в заголовки, а вернее озаглавить первую букву ключевой фразы.
Сделать это можно с помощью простой и не объемной формулы:
=ЗАМЕНИТЬ(N;1;1;СИМВОЛ(КОДСИМВ(ПРОПНАЧ(N))))
, где N – номер ячейки исходных данных.
Формулу вписываем в соседний столбец. В примере ниже ключевые слова находятся в столбце A начиная с ячейки A1, следовательно, и в соседнем столбце B вписываем формулу в ячейку B1.
Пример:
Достаточно ввести формулу один раз и размножить, как она применится ко всем остальным ячейкам с исходными данными. Если кампания создается по принципу “1 ключ – 1 группа объявлений”, то данная формула станет незаменимым помощником.
К сожалению, Excel устроен так, что конечный результат работы формулы не преобразуется в обычные символы. То, что мы видим в столбце B, все еще формула, поэтому редактировать конечный результат мы не можем. Чтобы это исправить нужно проделать одну несложную операцию:
- Выделите весь диапазон ячеек с результатом работы формулы. В данном конкретном примере, это диапазон с B1 по B8:
- Далее скопируйте этот диапазон, используя сочетание клавиш CTRL+C (ну или через правую кнопку мыши):
- Вставьте все, что скопировали в соседний столбец C. Затем под тем, что Вы скопировали появится небольшая кнопка, по нажатию на которую будет вызвано меню параметров вставки. Нажмите на него:
Далее нажмите на кнопку “Только значения”:
После такой несложной, но странной процедуры Вы сможете редактировать конечный результат работы формулы. Данные из столбца B можно удалить и перенести данные из столбца C.
Количество символов в ячейке
Что в Google Adwords что в Яндекс.Директ имеются жесткие требования по количеству символов на каждый элемент рекламного объявления, которые обязательно нужно соблюдать. Поэтому создав заголовки, мы просто обязаны проверить количество символов в каждом заголовке.
Делается это с помощью следующей формулы:
=ДЛСТР(N)
, где N – номер ячейки с исходными данными.
Пример использования формулы:
Теперь Вы видите из скольких символов состоят Ваши заголовки. Данную формулу удобно использовать в сочетании со встроенным в Excel инструментом “Условное форматирование”. С его помощью Вы можете выделить ячейки, где превышено требование по количеству символов, определенным цветом.
Данное сочетание очень удобно в том случае, когда заголовков и ключевых фраз действительно много. Глазами найти ячейки в которых больше символов, чем нужно, не так-то просто, скажу я Вам.
Итак. Чтобы воспользоваться данным сочетанием инструментов нужно сделать следующее:
- Выделить диапазон ячеек с результатом работы формулы =ДЛСТР(N):
- Далее в верхней панели инструментов найти инструмент “Условное форматирование”, затем в появившемся меню выбрать пункт “Правила выделения ячеек” и нажать на пункт “Больше”:
- Далее задаем условие форматирования (выделения цветом). В первой слева строке вводим количество символов, которое превышать нельзя. В данном случае, я сюда ввожу цифру “35”, так как именно столько символов допускается в заголовке в Яндекс.Директ. Можете также выбрать цвет выделения ячеек. Нажимаем на кнопку “ОК”:
Результат работы такого сочетания: 
Теперь можно приводить к требованиям заголовки напротив которых подсветились ячейки с количеством символов. Представьте, насколько сильно облегчает жизнь специалисту по контекстной рекламе это сочетание инструментов.
Лишние пробелы
При работе с большим количеством данным в Excel довольно часто случаются небольшие косяки по тому же количеству символов, а именно местами появляются лишние пробелы. Лишние пробелы перед или после фразы, между словами тоже являются символами, а значит Директ или Adwords учтет их таковыми и может какие-то заголовки, тексты не пропустить.
Чтобы этого избежать, нужно (именно нужно!) воспользоваться формулой:
=СЖПРОБЕЛЫ(N)
, где N – номер ячейки с исходными данными.
Пример: 
Теперь такого косяка не будет. Придется повторить то же самое, что мы делали с заголовками, а именно скопировать и вставить только значения.
Массовое редактирование текстовых элементов
Превращая ключевые фразы в заголовки объявлений, мы всегда должны приводить их в читабельный вид, чтобы пользователь, видя наши рекламные объявления, воспринимал их правильно. То есть, мы должны исправить определенные ошибки, связанные с названиями городов, аббревиатур и других элементов заголовка.
Выполнять такую работу проще и удобнее с помощью инструмента “Найти и заменить”. Вызвать в Excel его можно сочетанием клавиш CTRL+F:
К примеру. Мне нужно, чтобы во всех заголовках в столбце B слова “спб” выглядели, как “СПб”. Для этого я выделяю искомый столбец, в инструменте “Найти и заменить” я перехожу во вкладку “Заменить” и в поле “Найти” пишу исходное значение, то есть “спб”, а в поле ниже вписываю “СПб”. После чего нажимаю на кнопку “Заменить все”: 
Теперь мои заголовки выглядят чуть лучше:
Затем мне нужно поправить все словоформы слова “квартир” и привести их к одному виду. Чтобы справиться с этой задачей можно использовать специальные операторы в инструменте “Найти и заменить”. Для этого во вкладке “Заменить” в поле “Найти” вписываю следующее “квартир? ” (обязательно поставьте пробел после знака “?”, чтобы не удалить лишнее), в поле “Заменить” вписываю значение на которое нужно заменить исходное – “квартир ” (тоже обязательно проставьте пробел после слова).
Результат: 
Ну а далее, что можно поправить руками правим руками. Все просто!
В работе я использую все эти возможности Excel и мне вполне хватает для быстрой и качественной работы с большими объемами данных. Надеюсь, эта статья будет для Вас полезной и поможет Вам в решении каких-то определенных задач.
До свидания!
Настройка Яндекс Директ под ключ
Настройка рекламных кампаний в Яндекс Директ под ключ. Без каких-либо ограничений. Только полноценная настройка Директа с настройкой аналитики.
Обучение Яндекс Директ
Обучение настройке кампаний в Яндекс Директ: начиная с самых азов, заканчивая ведением кампаний. Записи каждого урока + дополнительные материалы.
Аудит кампаний Яндекс Директ
Анализ рекламных кампаний в Яндекс Директ. Выявление причин плохой, нестабильной работы Директа + рекомендации по улучшению сайта.
Хотите быстро настраивать рекламную кампанию? Используйте формулы Excel для Директа и Google Ads. Найдите время, чтобы разобраться, как устроен этот инструмент, и потраченные часы окупятся с лихвой.
Зачем Excel интернет-маркетологу
С помощью такого инструмента специалист по рекламе решает следующие задачи:
- Создает рекламные кампании. Используйте XLS-шаблоны для подготовки объявлений. Выгружайте заранее подготовленный файл в Яндекс.Директ или Google Рекламу, чтобы экономить время.
- Вносит изменения в действующие объявления. Вам не придется долго разбираться с тем, как пользоваться Директ Коммандером или AdWords Editor. С помощью специальных функций можно успешно решить эту задачу в Excel.
- Анализирует статистику. Сортируйте и фильтруйте полученные данные и создавайте структурированные подробные отчеты. Анализируйте информацию по рекламной кампании и вносите изменения, чтобы повысить ее эффективность.
На что способен Excel
Сортирует и фильтрует
Упорядочивайте статистику по рекламным кампаниям. Используйте фильтры, чтобы скрыть часть содержимого в области данных. Сортировку можно выставлять по-разному, чтобы максимальное значение оказалось вверху или внизу столбца.
Фильтры бывают текстовыми и числовыми. Первые позволяют быстро найти запросы, которые содержат конкретное слово. К примеру, нужно быстро найти все ключи со словом «цена». Нажимаем на значок фильтра в столбце.
Выбираем «Текстовые фильтры» и «Содержит». Появится такое окно:
Указываем слово, которое нас интересует, и получаем перечень всех запросов с употреблением данного ключа.
Числовые фильтры выполняют аналогичные функции и работают по схожему принципу. Только вместо текста обрабатывают цифры.
Удаляет дубли
При сборе семантического ядра с этой задачей хорошо справляется KeyCollector. Excel — прекрасная альтернатива при очистке ключей для контекстной рекламы. Чтобы избавиться от одинаковых ключевых слов, загрузите список фраз в программу и нажмите эту кнопку:
Выделяет цветом ячейки
Используйте опцию «Условное форматирование», чтобы выделить ключевые слова, которые не соответствуют конкретным условиям. К примеру, фразы длиной больше 30 символов или с CTR меньше 5.
Цвет ячеек и условия форматирования можно настроить самостоятельно.
Разбивает данные по столбцам
Представьте, что вы собрали ключи в таком неудобном виде:
С помощью Excel вы можете разделить полученные данные на столбцы. Так работать с ключами будет намного проще.
Чтобы получить результат, выделяем столбец и поочередно нажимаем «Данные» и «Текст по столбцам».
Указываем формат данных «С разделителями» и выбираем символы, которые выступают в качестве этих разделителей. В нашем случае это будет точка с запятой.
Нажимаем «Готово» и получаем такую трансформацию.
Определяет длину строки
Это удобно при настройке Яндекс.Директ через Excel или при подготовке кампании для Google Ads. К примеру, в Google Рекламе длина заголовка не должна превышать 30 символов. Установите такое ограничение для определенных столбцов — и вы сразу увидите, какие объявления подходят под стандарты Google, а какие нет.
Полезные формулы Excel для Директ
Возьмите на вооружение несколько полезных формул. Они существенно сэкономят время при подготовке рекламной кампании Яндекс.Директ в Excel.
- СЖПРОБЕЛЫ
Что делает: удаляет лишние пробелы.
Когда пригодится: при копировании поисковых фраз из Яндекс.Вордстата или Key Collector.
Как использовать: выделите столбцы, с которыми будете работать, и введите формулу СЖПРОБЕЛЫ. Дополнительно укажите адрес ячеек, которые нужно проверить (А1, А2…).
- СЦЕПИТЬ (ПРОПИСН (ЛЕВСИМВ (А1);ПРАВСИМВ (А1;ДЛСТР (А1) -1)))
Что делает: меняет регистр, добавляет заглавную букву в первое слово.
Когда пригодится: когда ключевую фразу нужно трансформировать в заголовок объявления.
Как использовать: зайдите в «Мастер функций» и введите нужную формулу. Добавьте аргументы функции, указанные в формуле, и нажмите ОК. Если вы не допустили ошибок, все первые слова объявления будут начинаться с заглавной буквы.
- СИМВОЛ (34)&СЖПРОБЕЛЫ (A3)&СИМВОЛ (34)
Что делает: приводит ключевое слово к фразовому соответствию.
Когда пригодится: если в Excel надо указать, что ключевые слова для Яндекс.Директ должны использоваться в точном вхождении. Таким образом мы привлекаем горячих клиентов и не расходуем бюджет на лишние показы.
Как использовать: выбираем нужные ячейки, вводим формулу СИМВОЛ и добавляем «34″ — этот знак обозначает кавычки. После & добавляем формулу СЖПРОБЕЛЫ и указываем ячейку (А3). В конце вводим еще один СИМВОЛ, чтобы закрыть кавычки, и получаем такой результат:
- СЦЕПИТЬ, ЕСЛИ и ДЛСТР
Что делает: дополняет текст и заголовок объявлений.
Когда пригодится: когда в рекламу нужно добавить продающее окончание: «Купить со скидкой 33%», «Заказать по акции» и т. д.
Как использовать: придумайте продающее окончание. В конце текста первых ячеек добавьте знак препинания, а продающее окончание начинайте с пробела. Так не возникнет проблем с отображением объявления. Должно получиться так:
Затем определите длину строки с помощью формулы ДЛСТР. Это необходимо для того, чтобы определить оптимальное количество символов при написании объявления. Если текст будет слишком длинным, его не пропустит система.
Далее используем такую формулу: ЕСЛИ (ДЛСТР (A2)<7;СЦЕПИТЬ (A2;”. Купить недорого”);ЕСЛИ (ДЛСТР (A2)<20;СЦЕПИТЬ (A2;”. Доставка бесплатно”);СЦЕПИТЬ (A2;”!”))). Она автоматически высчитывает оптимальную длину объявления и следит за тем, чтобы текст не превышал 33 символа, рекомендованные Директом.
- (Ctrl + F), (Ctrl + H), ?, *
Что делает: находит словоформы и запросы в разбавленном вхождении.
Когда пригодится: если нужно найти фразы, которые используются в другом падеже, числе, роде.
Как использовать: выгрузите список ключевых слов в Excel и нажмите CTRL+F. Если вам нужно найти слово в любой словоформе, пропишите запрос в таком виде: «дров?». Нажимаем «Найти все», и в нижней части документа система предлагает все слова с корнем «дров».
Чтобы найти разбавленные ключевые фразы, нажимаем CTRL+F и вводим ключ, который нас интересует — «купить*дрова».
В ответ на запрос система вернет список фраз, где между «купить» и «дрова» есть дополнительные слова.
Это лишь несколько формул, которые облегчат работу по подготовке рекламных кампаний. На самом деле формул очень много — начните использовать эти и далее просто совершенствуйте навыки.
Как настроить Яндекс.Директ и Google AdWords через Excel
Настроить рекламу для интернет-магазина с большим ассортиментом можно и через фиды. Но Excel практически ни в чем не уступает этому инструменту, если не превосходит его. Вот почему:
- В Excel-файле для Директа или Google Ads можно генерировать объявления по конкретному запросу (ключу). Для фида такая функция недоступна.
- В Excel можно сделать уникальный заголовок и текст для отдельно взятого объявления. Дополнительно можно проставить быстрые ссылки.
- Excel подходит для работы не только с интернет-магазинами, но и с сайтами информационной направленности. В таблицу можно выгружать как коммерческие, так и информационные запросы.
И еще один важный момент. Работая в Excel, вы ни от кого не зависите. Чтобы подключить фиды, может понадобиться помощь программиста. Это выливается в дополнительные траты времени и средств.
Далее расскажем об особенностях оформления кампании на примере Директа. Перед тем, как настроить Яндекс Директ через Excel, зайдите в личный кабинет. В нижней части страницы вы увидите ссылки XLS/XLSX, переходим по ним.
Попадаем на такую страницу.
Здесь вы можете выгрузить данные по кампаниям, которые уже запущены, и загрузить новую рекламную кампанию.
Для загрузки можно использовать файлы в формате XLS, XLSX и CSV.
Для того, чтобы избежать проблем при оформлении кампании, используйте готовый шаблон Excel для Яндекс.Директ. Его можно скачать на этой же странице справа.
Перед запуском кампании необходимо заполнить все поля.
Не забывайте про вкладку с контактной информацией:
И регионами:
Упрощенный шаблон Excel для Google AdWords выглядит так:
Как перенести рекламную кампанию из Яндекс.Директ в Гугл Адвордс
Работайте поэтапно.
- Шаг 1. Создаем шаблон для переноса
Создаем в Excel две вкладки — «Группы + слова» и «Группы + объявления».
Во вкладке со словами создаем три столбца и подписываем их, как на скриншоте.
Там, где объявления, столбцов понадобится больше. Их тоже надо подписать соответствующим образом.
Шаблон готов, переходим к следующему этапу.
- Шаг 2. Добавляем ключевые слова и минус-слова
Копируем нашу кампанию и переносим в подготовленный шаблон (раздел «Группы + слова»). Будьте внимательны — при копировании минус-слов система может выдать ошибку. Чтобы избежать этого, перед списком минус-слов добавьте апостроф — «‘».
Затем переходим во вкладку «Данные» и нажимаем «Текст по столбцам».
В следующем окне выбираем «С разделителями» и из предложенных символов выбираем «Другой». В него вписываем «-», которым разделяем минус-слова.
Теперь осталось превратить строку в столбец. Этого можно добиться, если выделить нужные ячейки, кликнуть правой кнопкой мыши и выбрать «Специальная вставка», «Транспортирование».
Все лишние символы можно удалить функциями CTRL+F и CTRL+H. Когда список будет полностью очищен, можно копировать его и вставлять в интерфейс Google AdWords.
- Шаг 3. Добавляем объявления
Работаем с вкладкой «Группы + объявления». Заголовок и текст вставляем в столбцы «Headline» и «Description Line 1» нашего шаблона, ссылки — в раздел «Destination URL» и так далее. Должно получиться так:
- Шаг 4. Настраиваем кампанию
Переносим кампанию в интерфейс Гугла с помощью AdWords Editor, проставляем ставки, выбираем тип рекламы, настраиваем геотаргетинг и вуаля — все готово! Не забудьте сохранить внесенные изменения и тщательно все проверить перед запуском.
Что в итоге?
Excel — незаменимый инструмент маркетолога, который позволяет работать с рекламными кампаниями не только при подключенном интернете, но и оффлайн. Несмотря на появление огромного количества новых сервисов, он заслуженно продолжает пользоваться популярностью.
Тип кампании
Заполняется только при создании новой кампании.
Справочник значений приведен на вкладке Словарь значений полей.
№ заказа
Не заполняется и не редактируется.
Минус-фразы на кампанию
Необходимо разделять запятой. До 20 000 символов.
Подробнее о минус-фразах.
Валюта
Не заполняется и не редактируется.
Доп. объявление группы
Обязательное поле.
– — главное объявление группы;
+ — остальные объявления группы.
Заполняется только при создании новой кампании.
Справочник значений приведен на вкладке Словарь значений полей.
Мобильное объявление
Обязательное поле.
+ — мобильное объявление;
– — десктопное объявление.
Подробнее о мобильных объявлениях.
ID группы
Не заполняется и не редактируется.
Название группы
Обязательное поле.
Номер группы
Допускаются только цифры.
ID фразы
Не заполняется и не редактируется.
Фраза (с минус-словами)
Заполняется только для главного объявления группы.
До 4096 символов (включая минус-слова), не больше 35 символов в слове.
ID объявления
Не заполняется и не редактируется.
Внимание.
Если удалить значение в этом поле, то в существующей кампании будут созданы новые объявления, а старые будут удалены.
Заголовок 1
Обязательное поле.
До 35 символов, включая пробелы, и до 15 знаков препинания (точек, запятых, двоеточий, точек с запятой, кавычек и восклицательных знаков). В одном слове не может быть больше 22 символов.
Заголовок 2
До 30 символов, включая пробелы, и до 15 знаков препинания. В одном слове не может быть больше 22 символов.
Текст
Обязательное поле.
До 81 символа, включая пробелы, и до 15 знаков препинания. В одном слове не может быть больше 23 символов.
Длина заголовка/текста
Не заполняется и не редактируется.
Ссылка
Обязательное поле.
Адрес сайта или страницы с предложением.
До 20 символов. Допускаются буквы, цифры и символы «-», «№», «/», «%», «#». Домен указывать не нужно.
Подробнее об отображаемой ссылке.
Регион
Заполняется только для главного объявления группы.
Справочник значений приведен на вкладке Регионы. Приоритет отдается регионам, выставленным на странице загрузки файла (флажок Установить регион для всех загружаемых объявлений).
Ставка
Заполняется только для главного объявления группы.
Ставка объявления на поиске. Значение поля игнорируется, если:
включена стратегия с автоматическим управлением;
включена стратегия с ручным управлением и выбраны показы только в сетях.
Ставка в сетях
Заполняется только для главного объявления группы.
Ставка объявления в сетях (РСЯ и внешние сети). Значение поля игнорируется, если:
включена стратегия с автоматическим управлением;
включена стратегия с ручным управлением и выбраны показы только на поиске;
включена стратегия с ручным управлением, выбраны показы на всех площадках и управление ставками на поиске и в сетях не разделено.
Если поле пустое, используется значение поля Ставка.
Контакты
+ — объявление с визиткой (данные заполняются на вкладке Контактная информация);
– — объявление без визитки.
Подробнее о визитке.
Статус объявления
Не заполняется и не редактируется.
Статус фразы
Не заполняется и не редактируется.
Заголовки быстрых ссылок
До 30 символов, включая пробелы. В одном слове не может быть больше 23 символов. Заголовки необходимо разделять с помощью «||». Суммарная длина заголовков не может быть больше 66 символов.
Подробнее о быстрых ссылках.
Описания быстрых ссылок
До 60 символов, включая пробелы. В одном слове не может быть больше 23 символов. Описания быстрых ссылок необходимо разделять с помощью «||».
Адреса быстрых ссылок
Необходимо разделять с помощью «||».
Турбо-страницы для быстрых ссылок
ID Турбо-страниц для быстрых ссылок. Необходимо разделять с помощью «||».
Параметр 1 и Параметр 2
Значения подставляются в ссылку вместо {param1} и {param2}.
Подробнее о параметрах.
Метки
Заполняется только для главного объявления группы.
Необходимо разделять запятой. До 25 символов в каждой метке.
Подробнее о метках.
Изображение
Обязательное поле для графических объявлений.
Ссылка на изображение (для текстово-графических объявлений) или на креатив (для графических объявлений).
ID креатива видеодополнения. Подробнее о видеодополнениях.
Уточнения
Необходимо разделять с помощью «||». До 25 символов в каждом уточнении.
Подробнее об уточнениях.
Минус-фразы на группу
Необходимо разделять запятой. До 4096 символов.
Примечание. Если в разных строках, относящихся к одной группе, будут указаны разные минус-фразы, то при загрузке они будут объединены.
Подробнее о минус-фразах.
Ссылка на приложение в магазине
Не заполняется и не редактируется.
Тип устройства
Не заполняется и не редактируется.
Тип связи
Не заполняется и не редактируется.
Версия ОС
Не заполняется и не редактируется.
Трекинговая ссылка
Не заполняется и не редактируется.
Иконка
Не заполняется и не редактируется.
Рейтинг
Не заполняется и не редактируется.
Количество оценок
Не заполняется и не редактируется.
Цена