В начале сентября Яндекс порадовал нас новым отчетом по поисковым запросам, который теперь показывает не только запросы, по которым были клики, а и те, по которым мы получили показы. Это дает нам потенциальную возможность избавиться от лишних показов и, как следствие, поднять CTR, а также сократить количество нежелательных кликов в будущем.
В этой статье мы поделимся нашим скриптом для Microsoft Excel, который позволяет вычленять из поисковых запросов будущие минус-слова быстро и эффективно, а также значительно сократить время на работу с отчетом, т.к. мы не будем тратить время на просмотр запросов, которые уже одобрены в виде фраз.
Этап №1. Сохранение отчета по поисковым запросам
В данной статье, для простоты, мы будем показывать работу на примере одной РК.
Итак, первое, что мы делаем — выбираем нужную РК и переходим в раздел статистики.
Выбираем вкладку «Поисковые запросы». При желании можно сразу отфильтровать по количеству конверсий, чтобы исключить из отчета те запросы, что дали нам конверсии – их минусовать смысла нет. Нажимаем Экспорт в xlsx, т.к. работать дальше будем в Microsoft Excel.
Этап №2. Выгрузка ключевых фраз из соответствующей РК
Суть работы скрипта – оставить из всех запросов только те слова, что мы предварительно не одобрили как «плюс-слова». То есть те, что встречаются в наших ключевых словах (логично, что если они есть в ключевых словах, значит, мы дали добро на их открутку), любые другие списки, которые мы не хотим минусовать — например, бренды, города и т.п.
Делаем выгрузку фраз. Можно это сделать как с помощью веб-интерфейса:
Так и через Директ Коммандер
Этап №3. Сводим все вместе
Итоговый файл должен иметь примерно такую структуру:
Ключевая фраза | Поисковый запрос | Показатели
За базовый берем отчет по поисковым запросам, в который просто переносим столбец с ключами. Это, собственно, единственная необходимая манипуляция на этом этапе. Также чисто из эстетических соображений удаляем ненужные строки и столбцы.
Итоговая таблица примет приблизительно такой вид:
Этап №4. Установка и запуск макроса
Для запуска открываем редактор Visual Basic. Делается это или через интерфейс Excel:
Если у вас этой вкладки нет, ее нужно добавить. Для этого заходим в настройки (в английском Excel: File > Options > Customize Ribbon > Main Tabs > ставим галочку на Developer).
Или же просто используем сочетание клавиш ALT + F11.
В открывшемся редакторе создаем модуль:
В который вставляем текст скрипта:
disclaimer: Скрипты написаны не профи-программистом: пожелания, замечания и изменения кода приветствуются
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
Sub SearchTerms() 'Updateby20160628 by UAWC. Ivan Gurin Dim x Dim arr() As String Dim xValue As String Dim aValue As String Dim Splitted() As String Dim InputRng As Range, OutRng As Range, Keywords As Range xTitleId = "UAWC Agency SearchTerms Analyzer Yandex" Set InputRng = Application.Selection Set InputRng = Application.InputBox("SearchTerms + Impressions + Clicks + Cost :", xTitleId, InputRng.Address, Type:=8) Set Keywords = Application.Selection Set Keywords = Application.InputBox("Keywords :", xTitleId, Keywords.Address, Type:=8) Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set dicKeywords = CreateObject("Scripting.Dictionary") dicKeywords.CompareMode = vbTextCompare 'case insensitive Set dicFinal = CreateObject("Scripting.Dictionary") dicFinal.CompareMode = vbTextCompare 'case insensitive With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With For j = 1 To Keywords.Columns.Count For i = 2 To Keywords.Rows.Count xValue = Keywords.Cells(i, j).Value Dim indexOfDash As Integer indexOfDash = InStr(1, xValue, "-") If indexOfDash > 0 Then Dim newxValue As String newxValue = Left(xValue, indexOfDash - 2) Else: newxValue = xValue End If For Each x In Split(RuOnly(newxValue), " ") If x <> "" And Not dicKeywords.Exists(x) Then dicKeywords(x) = "" End If Next Next Next For i = 2 To InputRng.Rows.Count aValue = InputRng.Cells(i, 1).Value For Each x In Split(RuOnly(aValue), " ") If x <> "" And Not dicKeywords.Exists(x) Then If dicFinal.Exists(x) Then Set dic2 = dicFinal.Item(x) dic2.Item("Impressions") = dic2.Item("Impressions") + InputRng.Cells(i, 2).Value dic2.Item("Clicks") = dic2.Item("Clicks") + InputRng.Cells(i, 3).Value dic2.Item("Cost") = dic2.Item("Cost") + InputRng.Cells(i, 4).Value Else Set dic2 = CreateObject("Scripting.Dictionary") dic2.CompareMode = vbTextCompare dic2.Add "Impressions", InputRng.Cells(i, 2).Value dic2.Add "Clicks", InputRng.Cells(i, 3).Value dic2.Add "Cost", InputRng.Cells(i, 4).Value dicFinal.Add x, dic2 End If End If Next Next Set OutRng = OutRng.Offset(0, 1) OutRng.Value = "Impressions" Set OutRng = OutRng.Offset(0, 1) OutRng.Value = "Clicks" Set OutRng = OutRng.Offset(0, 1) OutRng.Value = "Cost" Set OutRng = OutRng.Offset(1, -3) For Each Key In dicFinal OutRng.Value = Key Set OutRng = OutRng.Offset(0, 1) For Each kk In dicFinal(Key).keys OutRng.Value = dicFinal(Key).Item(kk) Set OutRng = OutRng.Offset(0, 1) Next Set OutRng = OutRng.Offset(1, -4) Next With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With MsgBox "Naslajdaytes'" End Sub Function RuOnly(strSource As String) As String Dim i As Integer Dim strResult As String For i = 1 To Len(strSource) Select Case Asc(Mid(strSource, i, 1)) Case 32, 48 To 57, 60 To 90, 97 To 122, 128 To 255: '32 - probel, 48 to 57 - cyfry, 60 to 90, 97 to 122- english Letters, 128 to 255 - russian strResult = strResult & Mid(strSource, i, 1) End Select Next RuOnly = strResult End Function |
Запускаем скрипт, нажав зеленый значок Play (или клавишу F5). Выскакивает диалоговое окно с просьбой выделить колонки «поисковый запрос» + «показы» + «клики» + «стоимость». В моем случае, это столбцы B-E.
По следующему запросу выбираем столбец с ключевыми словами:
И, наконец, указываем ячейку-ориентир для вывода информации:
Получаем итоговый список потенциальных минус-слов:
Работа со списком потенциальных минус-слов
Конечно, в идеале нужно полученные слова прогнать через нормализатор, который поможет свести вместе статистику слов в разных формах (например, «красный», «красная», «красными»). Существует несколько сервисов, которые помогут это сделать. Один из них — http://py7.ru/tools/norm/.
Далее сведенную информацию мы можем отфильтровать по различным критериям.
Например:
— запросы с большим количеством показов и малым количеством кликов для оптимизации CTR;
— самые дорогие запросы;
— запросы с наибольшим количеством кликов.
Тюнинг скрипта
В нижней части кода находится функция, которая «чистит» слова от лишних символов. С ее помощью можно исключить из нашего отчета цифры, английские буквы, специальные символы и т.п.
Править нужно строчку:
|
1 |
Case 32, 48 To 57, 60 To 90, 97 To 122, 128 To 255: '32 - probel, 48 to 57 - cyfry, 60 to 90, 97 to 122- english Letters, 128 to 255 - russian |
Сейчас она учитывает пробелы, цифры, английские и русские буквы.
Если эта строка будет выглядеть так:
|
1 |
Case 32, 128 To 255 |
В отчете останутся только кириллические символы.
Вот и все. Успешной борьбы с нежелательным трафиком!