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

Категории


Наша рассылка


Мы ВКонтакте


30.11.2017 Руслан Ахтамьянов 384

Эта статья будет интересна всем, кто хочет впервые попробовать составить семантическое ядро для сайта или для рекламной кампании в Яндекс Директ. Мы не будем вдаваться во все подробности и тонкости этой работы, а назовем лишь несколько моментов 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