Полезные формулы 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 — незаменимый инструмент маркетолога, который позволяет работать с рекламными кампаниями не только при подключенном интернете, но и оффлайн. Несмотря на появление огромного количества новых сервисов, он заслуженно продолжает пользоваться популярностью.
Excel — очень недооцененная программа, которая на самом деле обладает крутым функционалом. Сегодня РРСшники агентства Penguin-team о том, как этот функционал применить в работе.
Установить надстройку — Excel в контекстной рекламе
Обычно в подобных подборках для большинства задач советуют использовать формулы Excel. Формулы — это классно, но можно добиться того же результата проще, установив надстройку Ёxcel. Это экономит еще больше времени, да и формулы запоминать не надо.
Как это сделать:
- Скачать саму надстройку.
- Распаковать архив.
- Через контекстное меню открыть свойства файла SETUP, на вкладке «Общие» поставить галочку возле «разблокировано».
- Перенести папку со скачанными файлами из «Загрузок» туда, где она будет храниться постоянно, чтобы надстройка работала.
- Запустить файл SETUP — надстройка установится.
Как удалить лишние пробелы в Excel — Excel в контекстной рекламе
Проблема 1: ну кто из нас не пропускал лишние пробелы во время написания объявлений?
Решение: Ctrl + H. В «Найти» ввести два пробела, в «Заменить на» — один пробел.
Если нужно убрать пробелы на всех листах файла, нажмите «Параметры >>», выберите «Искать» → «В книге».
Убрать пробелы на всех листах файлаПроблема 2: после удаления модификатора широкого соответствия в начале ячейки остается один лишний пробел. Через «Найти и заменить» он не удаляется, но оставлять его в начале тоже нельзя.
Решение: формула =СЖПРОБЕЛЫ(область выполнения)
Удалить лишний пробелРешить проблемы с лишними пробелами поможет и функция в Ёxcel «Сжать пробелы»:
Ячейки → Редактировать → Сжать пробелы Как удалить цифры в Excel — Excel в контекстной рекламе
Проблема: если скопировать ключи из Яндекс.Вордстат, к ним «прилепится» частотность запросов.
Решение:
- В нужном файле Excel выделить столбец, в котором нужно удалить числа.
- Открыть вкладку Ёxcel → «Ячейки»→ «Редактировать» → «Оставить в ячейке только текст».
Удалить цифры
Результат — только текст в ячейкеP. S. Если частотность нужно оставить, но в отдельной колонке, то скопируйте исходную колонку со «склеенными» ключе-частотностями. В одной колонке удалите числа, во второй — буквы.
Оставить только числа в ячейке Как добавить одинаковые UTM-метки к URL в Excel — Excel в контекстной рекламе
Проблема: есть много URL, к ним нужно добавить одинаковые UTM-метки. Вручную добавлять одно и то же — слив времени.
Решение:
- Скопировать все URL в один столбец.
- В соседнем столбце поставить нужную UTM-метку.
Поставить нужную UTM-метку- «Растянуть» UTM-метку на все столбцы: для этого выделить ячейку с меткой и потянуть за квадратик в правом нижнем углу.
Растянуть на все строки- Когда возле каждого URL стоит UTM-метка, перейти на вкладку «Ёxcel» → «Ячейки» → «Объединить ячейки без потери данных».
Объединить ячейки с URL и UTM-меткой- Наслаждаться и гордиться собой.
P. S. Сцеплять можно любое количество ячеек. К примеру, объединять все ключи с дополнительными словами «купить» и «цена».
Как объединить данные из нескольких ячеек в одну в Excel — Excel в контекстной рекламе
Проблема: у нас есть список ключей, но к ним еще нужно добавить города. Как это сделать не вручную?
Решение: =(ячейка с ключом)&(ячейка с городом).
Пошагово: ввести в строку формул = → кликнуть на ячейку с ключом → напечатать символ & → кликнуть на ячейку с городом → Enter.
Объединить данные из нескольких ячеекЧтобы распространить формулу на весь столбец, сделайте так в одной ячейке, а потом «растяните» ее вниз на весь столбец.
Растянуть формулу на все строки Как разбить данные из одной ячейки на несколько — Excel в контекстной рекламе
Проблема: как разнести данные из одной ячейки на несколько, ничего не удаляя?
Решение:
- Выделить столбец/ячейку, где нужно разнести значения одной ячейки по нескольким.
Ячейки, где значения нужно разделить на несколько- Выбрать вкладку «Данные» → «Текст по столбцам».
Выбрать «Текст по столбцам»- В открывшемся окне задать разделитель — символ, который будет сигнализировать программе, с какого момента вырезать содержимое ячейки. В нашем случае это пробел.
Задать разделитель- После этого первое слово останется в той же ячейке, второе перенесется в соседнюю ячейку справа, третье — в следующую за ней и так далее.
Разделенные значения Как объединить данные из двух таблиц в одну — Excel в контекстной рекламе
Проблема: в одной таблице собраны id товаров и статистика по ним из Google Shopping. В другой — id товаров, их названия и маржинальность. Количество id в таблицах отличается и все они вперемешку.
К примеру, вот наши две таблицы:
Таблицы, которые нужно объединитьРешение: раздел «Формулы» → «Ссылки и массивы» → «ВПР».
Пошагово:
- Присвоить имя диапазону, откуда будем брать данные, чтобы дальше было легче искать данные. Для этого выделить вторую таблицу (без шапки) → Ctrl+ F3 → кнопка «Создать» → введите имя для обозначения этой части данных.
Присвоить имя диапазону- Кликнуть на ячейку, в которую нужно перенести данные. В нашем случае нужно перенести название товара «кроссовки» из G2 в С16. Кликаем на С16, переходим на вкладку «Формулы» → «Ссылки и массивы» → «ВПР».
Формула для переноса данных- В открывшемся окне заполнить все поля:
Заполнить аргументы функции- искомое значение: ID, по которому нужно найти данные во второй таблице;
- таблица: название второй таблицы, которое вы задали через Ctrl+F3 раньше;
- номер столбца: порядковый номер (не буква!) столбца во второй таблице, из которого нужно взять данные. У нас во второй таблице первый столбец ID, а названия — второй столбец. Ставим номер столбца «2»;
- интервальный просмотр: «ЛОЖЬ» — поиск точного совпадения, «ИСТИНА» — поиск примерного совпадения. Если мы объединяем данные по ID, нам нужны только точные совпадения — пишем «ЛОЖЬ».
- Кликаем «ОК» — готово! В ячейку С16 скопировалось название «кроссовки». Для этого программа взяла ID из заданного искомого значения, нашла такой же ID в заданной таблице и перенесла название из второго столбца той таблицы на указанное место.
Выполнен перенос данныхЧтобы распространить этот результат, как обычно, «растяните» его на все ячейки.
Как посчитать слова в ячейке — Excel в контекстной рекламе
Проблема: в Google размер ключевой фразы ограничен 10 словами, в Яндексе — 7. Чтобы выбрать из семантического ядра только подходящие ключи и не пересчитывать слова в них вручную, РРСшники Penguin-team используют автоподсчет слов.
Решение:
- Возле столбца с ключами создать пустой столбец под количество слов.
- В пустую ячейку возле первого ключа ввести формулу:
=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))+1)
Где все три «А1» заменить на фактическую ячейку с ключом. В нашем случае это В2:
Подсчитать слова в ячейке- Нажать Enter → в столбце появится количество слов в первой ключевой фразе. Дальше «растянуть» формулу на все строки, чтобы подсчитать количество слов во всех фразах.
Растянуть формулу на все строки- Чтобы отобрать только фразы подходящей длины, выделите весь столбец «Слов» → вкладка «Главная» → «Сортировка и фильтр» → «Сортировка по убыванию».
Сортировка по убыванию- Вверху таблицы сразу отобразятся самые длинные ключи, которые теперь можно удалить, если они превышают допустимое количество слов рекламных систем.
Самые длинные ключи – в начале Как подсчитать символы в Excel — Excel в контекстной рекламе
Проблема: в Google Ads есть ограничения по размеру текста — заголовков, описаний.
Решение: формула =Длстр(область выполнения)
Подсчитать количество символов Как изменить регистр в Excel — Excel в контекстной рекламе
Проблема: как массово изменить регистр с прописных букв на строчные? А наоборот? А сделать правильное правописание в текстах объявлений: первое слово с прописной буквы, остальные строчными? Менять это руками — слив времени в никуда.
Решение:
- Выделить область, где нужно внести изменения.
- Перейти на вкладку «Ёxcel» → «Ячейки» → «Регистр».
- Выбрать нужный формат написания.
Изменить регистрКстати! Если выбрать вариант «Предложение», то все первые слова будут написаны с большой буквы, а остальные — строчными.
Плюс ко всему, в настройках можно автоматически добавить в конец текста в ячейках восклицательный знак, знак вопроса и другие символы.
Добавить символы Как удалить минус-слова в Excel — Excel в контекстной рекламе
Проблема: при выгрузке работающей кампании ?