PrintАртем Акулов

Полезные формулы Excel для работы с контекстной рекламой

Артем Акулов

Использование Excel значительно упрощает работу с созданием и ведением ppc-рекламных кампаний. А использование формул уже внутри Excel позволяет еще больше упростить и ускорить работу. Меньше времени тратится на рутину — больше времени для полезной работы.

Ниже рассмотрим 7 формул и приемов в Excel для работы с контекстной рекламой.

  • Удаление лишних пробелов (для ключевых слов и текстов объявлений)
  • Удаление минус-слов
  • Приведение ключевого слова к фразовому соответствию Яндекс Директ
  • Приведение ключевой фразы к модифицированному широкому соответствую Google AdWords
  • Преобразование к первой заглавной букве
  • Автоматическое создание заголовка для Яндекс Директа
  • Автоматическое создание текста объявления для Яндекс Директа

Удаление лишних пробелов

Задача: Часто получается так, что при работе с кампаниями в Excel случайно появляются лишние пробелы между словами в ключевой фразе/тексте объявления (например, 2 пробела подряд) или пробел появляется перед первым словом.

Но каждый символ на счету, ограничения на длину текстов очень жесткие что в Яндекс Директе, что в Google AdWords, что в любой другой системе контекстной рекламы.

Решение: Чтобы удалить лишние пробелы из фразы используется функция Excel «=СЖПРОБЕЛЫ()».

Удаляем лишние пробелы

Удаление минус-слов

Задача: есть выгрузка работающей кампании в Яндекс Директ, в некоторых ключевых словах используются минус-слова на уровне фраз. Нужно очистить ключевые фразы от минус-слов.

Это может понадобиться, например, для дальнейшей генерации текстов объявлений из ключевых фраз.

Решение: Выделяем ключевые слова. Используем инструмент поиска и замены Excel (CTRL+H). В поле поиска вводим следующую фразу « -*». Поле заменить оставляем пустым.

Удаление минус-слов

Все минус-слова удалены.

Приведение ключевого слова к фразовому соответствию Яндекс Директ

Задача: Есть ключевые слова в широком соответствии, необходимо привести их все к фразовому.

Решение: Используем следующую формулу. «=СИМВОЛ(34)&СЖПРОБЕЛЫ(A3)&СИМВОЛ(34)».

Формула для фразового соответствия Яндекс Директа и Google AdWords

Заодно удаляются лишние пробелы.

Эта же задача в Google AdWords решается гораздо быстрее через AdWords Editor (Редактор AdWords).

Приведение ключевой фразы к модифицированному широкому соответствую Google AdWords

Задача: Привести ключевые фразы из широкого соответствия к модифицированному широкому соответствию Google AdWords

Решение: Используем формулу «=”+”&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A3);” “;” +”)».

Формула для модифицированного широкого соответствия AdWords

Преобразование к первой заглавной букве

Задача: Сделать первую букву ячейки заглавной. Используется для генерации заголовков и текстов объявлений. Англоязычная капитализация не подойдет (когда все буквы пишутся с заглавной), т.к. тексты писать таким образом у нас не принято.

Решение: Используем формулу «=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(СЖПРОБЕЛЫ(A3)));ПРАВСИМВ(СЖПРОБЕЛЫ(A3);(ДЛСТР(СЖПРОБЕЛЫ(A3))-1)))».

Делаем первую букву большой

Автоматическое создание заголовка для Яндекс Директа

Задача: Сгенерировать заголовок объявления так, чтобы в нем содержалось ключевое слово и максимально длинная продающая добавка.

Решение: Используем следующую функцию «=ЕСЛИ(ДЛСТР(A11)<=$C$2;СЦЕПИТЬ(A11;$A$2);ЕСЛИ(ДЛСТР(A11)<=$C$3;СЦЕПИТЬ(A11;$A$3);ЕСЛИ(ДЛСТР(A11)<=$C$4;СЦЕПИТЬ(A11;$A$4);СЦЕПИТЬ(A11;$A$5))))».

Формула для заголовков Яндекс Директа и Google AdWords

Если нужно сгенерировать заголовок не для Яндекс Директа, а для Google AdWords. То формулу в С2-С5 как на скриншоте вносится корректировка. Будет не «33-В2», а «30-B2» (если в вашем объявлении AdWords включены удлиненные заголовки)

Корректирова формулы

Автоматическое создание текста объявления для Яндекс Директа

Задача: Аналогичная задача предыдущему пункту, только нужно теперь не заголовок сгенерировать, а текст объявления Яндекс Директ.

Решение:Используем формулу «=ЕСЛИ(ДЛСТР(A10)<=$C$2;СЦЕПИТЬ(A10;$A$2);ЕСЛИ(ДЛСТР(A10)<=$C$3;СЦЕПИТЬ(A10;$A$3);ЕСЛИ(ДЛСТР(A10)<=$C$4;СЦЕПИТЬ(A10;$A$4);ЕСЛИ(ДЛСТР(A10)<=$C$5;СЦЕПИТЬ(A10;$A$5);СЦЕПИТЬ(A10;$A$6)))))».

Формула для генерации текстов объявлений Яндекс Директ

Будут примеры excel-формул для контекстной рекламы — присылайте, расширим набор полезных формул.

  • Спасибо!

  • Дмитрий Болховитин

    Есть в сети еще вот такой набор формул в бесплатном доступе. Там же ссылки на наборы минус-слов

    https://docs.google.com/spreadsheet/ccc?key=0Apkq-HwNBfTIdF84MHViSjl4S0NMQXJVekt3MGVoRXc#gid=0

    • @DimaBolhovitin:disqus, спасибо

      • Dima Mironenko

        Дмитрий подскажи, а как сделать, у меня 10000 заголовков и их автоматом надо сделать 33 символа, как это сделать ?

  • Алексанлр Стасенко

    Так сцепление для ключевого слова в строке А11, для других ключевиков надо менять в формуле А11 на А12, А13 и так далее. Проще вручную сцеплять тогда

    • Александр, ничего менять не нужно. Там, где это необходимо, в приведенной выше формуле стоят относительные ссылки Excel, а в других местах абсолютные ссылки. Поэтому для нового ключевика достаточно только скопировать формулу.

      Формула для сцепления приведена из статьи про генерацию объявлений для Яндекс Директа.
      http://liraltd.com/avtomatiziruem-protsess-sozdaniya-reklamnyih-kampaniy-v-yandeks-direkte

      Вручную там только буллеты придумываются, остальное генерируется приведенной выше формулой.

  • Евгений

    Очень качественный блог, спасибо что делитесь такой практической информацией. Реально чувствуется качественный подход к делу. Спасибо.

  • А есть формула, для перевода матрицы с данными типа 1.КупитьЗаказать 2.Подгузникипамперсы в конкретные ключевые фразы?

    Что бы было Купить Подгузники, Купить памперсы, Заказать Подгузники и т.д….

    • Если, нужно скомбинировать 2 списка, то этот инструмент есть в планировщике ключевых слов AdWords.

      • Михаил LeadMarket

        Я пользуюсь сервисом http://promotools.ru/services/anchors-large.php , очень удобно – можно задать ключевик+запрос(купить/заказать/)+город+еще что-нибудь

    • Rustem

      я пользуюсь сервисом http://adpump.ru/keyword-gen.php

  • Марк

    Кто может посоветовать где найти готовую программу с всеми перечисленными свойствами. Прошу сообщить на marktarasov@gmail.com

  • Vladislav Grekov

    Отличная статья. Постоянно пользуюсь ей. И вообще благодаря вашему блогу очень многому научился. Но вот вопрос. У вас на примере заголовок состоит из ключа + буллетов. Но ключ гораздо меньше 33 символов, так что вы еще туда и буллеты вставляете.

    А как автоматически создать заголовок если длина ключа больше 33 символов?

    • Владислав, полностью автоматизировать процесс пока не получается, при условии, что необходимо создать читабельные заголовки.

      В ситуации, когда ключ очень длинный, не хватает места для УТП, часто целесообразней немного пожертвовать ключом, сократить его, но все таки вставить УТП, чтобы заголовок побуждал целевую аудиторию к прочтению объявления и переходу на сайт.

      • Vladislav Grekov

        Я так и делаю Артем. Я жертвую тем словом или словами в ключе которые не несут или несут несущественную нагрузку. И также благодаря этому контролирую качество рекламы.

        Спасибо вам большое за такую информацию

        • Виталий Койструб

          Но, например заголовок состоит из 4 больших слов и превышает 33 символа, можно удалить последнее слово, как это сделать? Удалить все слова, где текст превышает 33 символа?

          • Полностью процесс не автоматизируется, т.е. все равно необходимо анализировать, какие из слов более важные, какие менее. Какие несут смысловую нугразку, а какие можно убрать, где есть вариант заменить исходное слово более коротким синонимом или допустимым сокращением.

          • Sergei Ivanov

            Артем, если принять что смыслом можно пожертвовать. Ка сократить все заголовки до 33 символов по словам. Т.е. что бы обрезались не символы, а только слова целиком.

          • Не надо жертвовать смыслом, получатся плохие объявления.

          • Sergei Ivanov

            Вы знаете как сократить все заголовки до 33 символов по словам? Т.е. что бы обрезались не символы, а только слова целиком.

          • Готовой такой формулы у нас нет.

          • Sergei Ivanov

            Спасибо за ответ.

      • Дарья

        Т.е. фильтром выбрать ключи, превышающие допустимое кол-во символов и уже руками уменьшать да?

  • Вадим Куркин

    Как в Excel сделать к одному заголовку несколько вариантов объявлений?

  • Roma Beznosiuk

    Добрый день, назрел следующий вопрос:
    есть два столбца в экселе, в первом поисковые запросы, во втором минус слова, которые мы берем из первого.

    Можно ли как-то сделать, чтобы при добавлении слова во второй столбец, оно находилось в первом, и, к примеру, выделялось красным, или помечалось каким-то другим образом. Заранее спасибо.

    • Думаю, что можно, но по конкретным формулам не подскажу.

      • Roma Beznosiuk

        В общем, буду думать, если придумаю что-то, обязательно отпишусь.

  • Алексей

    Если 5000 объявлений, то приходится переносить по 999 объявлений на разные листы Екселя, так как Директ-Коллектор не принимает более 1000 объявлений в одной кампании. Есть ли способ который позволил бы это автоматизировать? Может есть какие-то простые действия, после которых на одном листе умещаются 999 строк, остальные 999 переходят на вторую страницу и т.д. пока не закончатся?

    • Алексей, вот тут статья. Поможет выполнить задачу не силами Экселя, а через Директ Коммандер.
      http://liraltd.com/kak-razdelit-bolshie-kampanii-v-yandeks-direkte

      • Влад Мир

        Артем, подскажите. Как в Excel выделить сразу фразы больше 3-х слов и удалить.
        Ключей порядка 5к. Мне нужно удалить ключ который больше 3 слов. Не знаю как сделать. Помогите!

        • Feliks Nebotov

          Здравствуйте.
          Фильтр: * * * *
          Звёздочка, пробел, звёздочка, пробел, звёздочка, пробел, звёздочка.
          Останутся фразы в которых больше 3 слов.

  • Семен

    Есть столбец минус-слов. подскажите пожалуйста, как вставить МИНУС в начало каждой строки в экселе?

  • Alexey Bykov

    что-то не получается привести все к модифицированному широкому соответствию, #ИМЯ? и все тут. https://uploads.disquscdn.com/images/c4d37e292a3b86e85e2c33b5130cb2e7ac4b606ea43eb982a074b20cdcaed736.png

  • Alexey Bazhenov

    Артем, здравствуйте! Как правильно разбить все ключи на горячие, теплые и холодные. Подскажите, есть ли такая программа в которую загрузив всю выдачу с “Wordstat”, программа сама автоматически разделит на горячие, теплые и холодные ключи или надо самому все время в ручную их распределять. Собрав 2000 ключей долго получается.

    • Алексей, мы не разделяем ключи на горячие, теплые и холодные. Структурируем кампании по другому.

  • Sergei Ivanov

    Есть потребность сократить все заголовки до 33 символов по словам. Т.е. что бы обрезались не символы, а только слова целиком. Поставьте “стрелку вверх”, если это поможет в составлении рекламных компаний.

    • Sergei Ivanov

      =ЕСЛИ(ДЛСТР(A1)<34;A1;ЕСЛИ(ПСТР(A1;34;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;32));ЕСЛИ(ПСТР(A1;33;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;31));ЕСЛИ(ПСТР(A1;32;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;30));ЕСЛИ(ПСТР(A1;31;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;29));ЕСЛИ(ПСТР(A1;30;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;28));ЕСЛИ(ПСТР(A1;29;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;27));ЕСЛИ(ПСТР(A1;28;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;26));ЕСЛИ(ПСТР(A1;27;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;25));ЕСЛИ(ПСТР(A1;26;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;24));ЕСЛИ(ПСТР(A1;25;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;23));ЕСЛИ(ПСТР(A1;24;1)=" ";СЦЕПИТЬ(ПРОПИСН(ЛСИМВ(A1;1));ПСТР(A1;2;22));"нет пробела на 24 позиции"))))))))))))