5 + 1 фишка MS Excel для начинающего Директолога

Эта статья будет интересна всем, кто хочет впервые попробовать составить семантическое ядро для сайта или для рекламной кампании в Яндекс Директ. Мы не будем вдаваться во все подробности и тонкости этой работы, а назовем лишь несколько моментов Excel, которые помогут сэкономить время при рутинной работе Директолога.

5 + 1 фишка Excel для Директа

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

Фишки MS Excel для сбора семантического ядра рекламной кампании в Яндекс Директ

Фильтры

В Excel есть замечательная функция под названием «Фильтр». Именно эта функция позволяет отлично ориентироваться в огромном объеме данных, который получается в результате сбора ключевых фраз из Вордстата, парсинга программой или сбора семантического ядра из готовых баз.

Под парсером мы подразумеваем специальную программу, которая позволяет собрать ключевые фразы и слова, которые, скажем, не покажет тот же Wordstat Яндекса из-за погрешностей его работы.

Фильтры Excel значительно упростят работу, когда нам потребуется отсортировать минус-слова на рекламную кампанию или объявление в Директе. Из огромного столбца с ключевыми словами мы можем найти все фразы, включающие в себя то или иное минус-слово.

Список стандартных минус-слов вы сможете найти в интернете. Для своего примера мы возьмем слово «лечение».

Выделяем весь столбец и идем «Данные» - «Фильтр», нажимаем на появившуюся стрелочку напротив самой верхней ячейки, в выпадающем меню выбираем пункт «Текстовые фильтры» и уже из этого выпадающего меню выбираем пункт «содержит»

Фильтры Excel для рекламной кампании в Яндекс Директ

В открывшемся окне, в самом верхнем поле пишем ненужное нам слово, в данном примере это «лечение». Жмем ОК.

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

Яндекс Директ не пропускает фразы и пробелы в минуса (уже пропускает), поэтому функция Фильтра еще не раз пригодится на последующих этапах подготовки кампании.

Дополнительно отметим, что нельзя использовать функцию «Вырезать» когда мы фильтруем минус-слова. Будет намного лучше если вы сначала их скопируете, а потом удалите клавишей Delete.

Удаление пустых ячеек

После того, как мы скопировали минус-слова в отдельный столбец, в основном столбце у нас останутся пустые строки там, где мы их удалили. Какие-то из них будут идти подряд, а какие-то встречаться с самыми разными интервалами. Не вручную же удалять эти пустые строки, правда?

Выделяем столбец, где требуется удаление пустых строк, жмем клавишу F5. В открывшемся окне жмем кнопку «Выделить», затем выбираем «пустые ячейки», жмем кнопку «ОК». Ну а после, вызываем контекстное меню на любой выделенной ячейке и удаляем стандартным образом.

Удаление пустых ячеек в MS Excel

После этой манипуляции все пустые ячейки столбца удалятся.

Транслит

Во время подготовки рекламной кампании в Яндекс Директ может потребоваться подмена URL целевой страницы (лендинг, одностраничник – называйте как хотите). Однако, ссылки могут не совсем правильно выглядеть. Если, скажем, в кириллическом URL будет кусок латиницы или в URL на латинице будет кусок из кириллицы.

Забегая вперед отметим, что Яндекс также учитывает и релевантность самой ссылки к лендингу, а это, в свою очередь, может повлиять на цену клика и CTR…

В общем, нам нужно сделать транслит – перевести текст из кириллицы в латиницу или наоборот. В стандартном Excel такой функции или формулы нет, поэтому мы сделаем свой пользовательский модуль.

Открываем редактор Visual Basic (горячие клавиши Alt+F11).

Транслит в MS Excel

Жмем Insert – Module и вставляем в открывшееся окно, вот этот кусок кода:

Function Translit(Txt As String) As String

 

    Dim Rus As Variant

    Rus = Array("а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", _

    "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _

    "щ", "ъ", "ы", "ь", "э", "ю", "я", "А", "Б", "В", "Г", "Д", "Е", _

    "Ё", "Ж", "З", "И", "Й", "К", "Л", "М", "Н", "О", "П", "Р", _

    "С", "Т", "У", "Ф", "Х", "Ц", "Ч", "Ш", "Щ", "Ъ", "Ы", "Ь", "Э", "Ю", "Я")

 

    Dim Eng As Variant

    Eng = Array("a", "b", "v", "g", "d", "e", "jo", "zh", "z", "i", "j", _

    "k", "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _

    "sh", "sch", "''", "y", "'", "e", "yu", "ya", "A", "B", "V", "G", "D", _

    "E", "JO", "ZH", "Z", "I", "J", "K", "L", "M", "N", "O", "P", "R", _

    "S", "T", "U", "F", "KH", "TS", "CH", "SH", "SCH", "''", "Y", "'", "E", "YU", "YA")

     

    For I = 1 To Len(Txt)

        с = Mid(Txt, I, 1)

     

        flag = 0

        For J = 0 To 65

            If Rus(J) = с Then

                outchr = Eng(J)

                flag = 1

                Exit For

            End If

        Next J

        If flag Then outstr = outstr & outchr Else outstr = outstr & с

    Next I

     

    Translit = outstr

     

End Function

После чего просто закрываем окно, сам редактор и переходим в наш открытый файл Excel. Выбираем ячейку где у нас будет транслит и идем по пути:

  • Для Excel 2013: «Формулы» - «Вставить функцию»;

  • Для более ранних версий Excel: «Вставка» - «Функции».

В поле «категория» выбираем пункт «Определенные пользователем», а уже в поле функций выбираем «Translit» и жмем «ОК»

Категория Определенные пользователем функции MS Excel

В следующем окне мы указываем ту ячейку, где у нас написан текст. Достаточно указать самую первую (на нашем примере это А1), а затем просто растянуть значение на весь столбец.

Ячейка А1

Важно! Транслит будет работать только в том файле, в котором вы кодом прописали дополнительный модуль.

Подстановка УРЛ

Следующим шагом настройки контекстной рекламы в Яндекс Директ может стать подстановка созданных транслитов непосредственно к ссылке. Для этого мы можем использовать функцию Excel под названием СЦЕПИТЬ.

Представим, что нам нужно создать уникальные урлы ссылок, причем наделать их штук 500. Не вручную же копировать из столбца с созданными транслитами.

Поэтому прописываем такую формулу:

=СЦЕПИТЬ("Ваш_домен/";адрес_ячейки)

«На выходе» получаем вот такие красивые и уникальные ссылки с точным вхождением ключевого слова в урл на транслите

Функция Сцепить в MS Excel

Более того, если в эту формулу, через точку с запятой, добавить еще одну ячейку, где у нас, скажем, прописаны utm-метки, то ссылки будут сразу генерироваться вместе с ними. Красота, да и только!

UTM-метки в Excel

Строки в столбцы

При работе в Excel бывает необходимость перевести строки в столбцы или наоборот перевести столбцы в строки. Это делается совсем просто.

Копируем необходимый столбец или строку, и вставляем в нужное место через Специальную вставку (в контекстном меню). В открывшемся окне нужно отметить пункт «транспортировать» и нажать «ОК».

Бонус

При подготовке рекламной кампании можно столкнуться с такой задачей, как перенос значений столбца в строку. Скажем, если вы отфильтровали минус-слова в столбец, а затем, когда будете готовить шаблон файла для загрузки через Директ Коммандер потребуется эти минус-слова разместить в строку, причем в одной ячейке. Для этих целей есть готовый файл, который вы сможете скачать по этой ссылке.

Надеемся, эти простые советы помогут вам при самостоятельном запуске рекламной кампании в Яндекс Директ.



30.11.2017

Возврат к списку

Система Orphus