Смотрите SERPs, не тратя раунд (учебник)

1. Первый шаг должен: скребок!
2. Операция очистки
3. Иногда scraperr.com генерирует очень грязный .CSV, иногда нет ...
4. Создание файла анализа
5. Макрос VBA, который делает работу
6. Графическая визуализация, кривые, спарклайны и информационные панели
7. Sparklines для Excel (SfE)
8. Суперпозиции кривых

Будь то SEO или издатель с хорошим фундаментом, может быть очень полезно изучить колебания SERP (страниц результатов поисковой системы) в выбранном выражении, а также подробно узнать, что делать. Кроме того, некоторые уже делают, эта статья не претендует на изобретение горячей воды. Я просто хочу дать несколько шариков, чтобы показать, что вполне возможно сделать это самостоятельно, не разрабатывая инструмент и не кладя руку в карман ... за исключением возможной стоимости лицензии Excel. Вы увидите, этот урок очень прост .

Сначала мы должны объяснить, что может быть интересом следовать вихрям SERP следующим образом:

• Чтобы сделать небольшую погоду из поисковой выдачи по одному (или) выражению
• Найти сайты, которые были застрелены Google во время обновления (или нет)
• Улучшите мониторинг позиции: занимать свои позиции - это хорошо, но поднять все сайты вокруг одного и того же выражения - это определенно лучше!
• Лучше анализировать и предвидеть конкуренцию
• Измерьте стабильность сайта в поисковой выдаче
• Сделайте часы с электронной репутацией
• и т.д.

3, 2, 1 ...

Первый шаг должен: скребок!

Идея состоит в том, чтобы собирать через регулярные промежутки времени список URL-адресов, которые отвечают выражению по вашему выбору. Чтобы проиллюстрировать эту статью, я возьму, например, выражение «погашение кредита» ... случайно, да!
Для этого нужен скребок, программа, предназначенная для извлечения данных в Интернете, в данном случае это результаты Google. Мы часто говорим о популярном Scrapebox, или RDDZ Scraper, более интуитивно понятном и французском. У обоих есть сильные стороны, но поскольку мы скупимся на этот вопрос, нет необходимости тратить ни копейки на лицензии и особенно на покупку прокси-серверов, которые по-прежнему важны для этих инструментов. Scraperr.com - это небольшой онлайн-инструмент, который собирает 500 лучших результатов Google по любому запросу и возвращает их в формате CSV. Гипер простой, gratos, без прокси, тихо.

Выберите Google.com, введите свой запрос, нажмите «Получить результаты». Как только записка будет завершена, кнопка предложит вам загрузить CSV-файл из первых 500 результатов. Затем необходимо повторять операцию через равные промежутки времени. Давайте скажем 1 раз в неделю на 5-недельной истории, чтобы иметь речевой результат.

Операция очистки

С этого момента все делается в Excel. Я полагался на Excel 2010 , но версия 2013 года тоже должна это делать. Я не тестировал с версией Mac, но в теории, это должно работать.

Иногда scraperr.com генерирует очень грязный .CSV, иногда нет ...

Я не знаю почему, но качество экспортируемых файлов не всегда постоянно. Поэтому невозможно полностью автоматизировать процесс очистки. Во всяком случае, это не так много, чтобы сделать. Итак, две вещи одна:

Ваш CSV-файл чистый: все URL не паразитируются строками в начале и конце URL (будьте осторожны!). В этом случае делать нечего, и переходите к следующему шагу.

Ваш файл грязный: в начале URL есть некоторые дополнения, например, http://www.google.fr/url?url= или даже / url? Q = , а в конце URL мы имеем Добавлены параметры, которые обычно всегда начинаются с одной и той же строки символов (например: & amp; sa = * ). Короче говоря, мы должны очистить все это.
Используйте функцию поиска / замены и при необходимости удалите параметры URL-адресов, добавленных scraperr, с помощью приведенного ниже макроса (для адаптации, и для этого необходимо выбрать весь диапазон URL-адресов для очистки):

Sub CleanURLs () Dim cl As Range Dim stVal As String Dim iLeft As Long Dim iRight так же долго, как и для каждого cl в выделении iLeft = InStr (cl.Value, "http") iRight = InStr (cl.Value, "& amp; ) stVal = Mid (cl.Value, iLeft, iRight - iLeft) cl.Value = stVal Next cl End Sub

Проще говоря, в файлах CSV нас интересуют только URL. Остальное нас не касается.

Выше приведен пример файла CSV, созданного Scraperr.com. Красным цветом, что удалить.

Очень важно: не беспокойтесь о сортировке или изменении порядка URL-адресов! Лучше предотвратить, ты никогда не знаешь.

Создание файла анализа

Создайте новый пустой файл Excel. Столбец А, который можно назвать «рейтинг» в А1, определит позиции от 1 до 500. Нет ничего проще, мы ставим 1 в А2 и 2 в А3, выбираем два поля и рисуем целое до строки 501 список автоматически увеличивается.

Скопируйте и вставьте все свои списки URL, ранее очищенные в столбцы B, C, D, E и т. Д. Оставьте первую строку для заголовков, или вы, например, укажите дату записи. Что дает нам что-то вроде этого:

Здесь у нас теперь есть таблица с несколькими рейтингами, многими URL, но читабельность практически равна нулю . Чтобы облегчить чтение колебаний и составить статистику по всем направлениям, необходимо «перевернуть» таблицу . Но будьте осторожны, здесь сводной таблицы недостаточно!

Чтобы преуспеть, нужно как минимум:

• Перечислите все URL-адреса и дедуплицируйте их.
• Рассчитайте и отобразите положение каждого URL для каждого проведенного опроса (записка).
• Наконец, убедитесь, что этот процесс полностью автоматизирован ;-)

Макрос VBA, который делает работу

Предположим, что наша таблица SERP находится на листе Excel с именем «Sheet1» (обычно это имя по умолчанию). Создайте (или переименуйте) второй лист и назовите его «tablo» (если вы дадите ему другое имя, он изменит макрос VBA соответственно). Затем щелкните правой кнопкой мыши вкладку листа «tablo», затем нажмите «View Code», как показано ниже:

В открывшемся окне скопируйте и вставьте код VBA ниже:

Private Sub Worksheet_Activate () Application.ScreenUpdating = False Dim Dercol как целое число, i как целое число, j как целое число, строка как целое число Данные Солнца Dim . Диапазон столбцов ("A2: A65536") ClearContents Для i = 2 ТБ Dercol Line = Диапазон ("A65536"). Конец (xlUp). Строка + 1 с листами ("Sheet1"). Диапазон (.Cells (2, i), .Cells (501, i)) Диапазон копирования («A» и строка) заканчивается следующим i Установить данные = CreateObject («Scripting.Dictionary») tablo = Sheets («TABLO»). Range («A2: A "& Range (" A65536 ") End (xlUp) .Row) Для j = 1 ТБ UBound (tablo) data.Item (tablo (j, 1)) = data.Item (tablo (j, 1)) Далее j Range («A2: A» & Range («A65536»). End (xlUp) .Row) .ClearContents Range («A2», Ячейки (data.Count + 1, «A»)) = Application.Transpose (данные .keys) Линия = Диапазон ("A65536"). Конец (xlUp). Диапазон строк (Ячейки (2, 2), Ячейки (Линия, Dercol)) FormulaR1C1 = _ "= IFERROR (INDEX (Лист1! R1C1: R501C1, MATCH (RC1, Sheet1! R1C: R501C, 0)), "" "") "Range (ячейки (2, 2), Cells (Line, Dercol)) = диапазон (ячейки (2, 2), Cells (Li gne, Dercol)) Значение End Sub

Закройте окно VBA, повторно отобразите лист «Лист1», затем снова «Табло». Бинго!

Все, что вам нужно сделать, это передать заголовки (даты). Наша диаграмма теперь намного более читаема, но все же требует немного графики, раскраски и кривых, чтобы сделать чтение и анализ еще проще. Оттуда это становится вольным стилем. Более опытный может даже установить сложную панель инструментов, с KPI и всем тремором. На моем скромном уровне я дам вам только несколько треков, которые уже позволят вам получить очень пригодный для использования результат.

Графическая визуализация, кривые, спарклайны и информационные панели

Условное форматирование

Например, желательно выделить хорошие позиции, закрасив клетки зеленым, плохие красным и мягкий живот желтым.
Мы выбираем диапазон позиций, затем применяем условное форматирование> нюанс цветов> затем выбираем красно-желто-зеленый градиент (а не наоборот!):

Это уже становится немного сексуальнее, но с точки зрения SEO, зеленая зона просто слишком широка, по шкале 500 баллов. Поэтому мы бы хотели, чтобы он был ограничен первой страницей, первой десяткой. Мы повторно выбираем диапазон позиций, затем снова «условное форматирование», а затем «управляем правилами ...». В появившемся диалоговом окне нажмите «изменить правило», затем измените правило для середины, изменив «Centile» на «Number», и укажите в качестве значения чуть ниже «10», затем Ok> Ok. Позиции, полученные на первой странице, теперь гораздо более заметны. Мы, конечно, можем уточнить и сделать другие правила намного тоньше. Вам решать играть.

Sparklines для Excel (SfE)

В Excel изначально имеются графические инструменты с искривленными линиями (в меню вставки), но они не будут использоваться, поскольку они непригодны: например, 500-я позиция для него самая высокая, а кривые полностью перевернуты.
Но есть плагин Sparklines для Excel (SfE) гораздо более мощный и гибкий, чем у Microsoft, и, конечно, именно этот мы будем использовать для обогащения нашей панели инструментов.

Цель здесь - связать с каждым URL его кривую позиционирования в поисковой выдаче. Необходимо в первый раз установить плагин SfE. После того, как скачать здесь Вы можете временно активировать его, открыв его в Excel или навсегда, перейдя к параметрам в Excel (2010)> Дополнения, а затем в правой части в разделе Управление: «Надстройки Excel»> Перейти к ... Появится новое диалоговое окно, нажмите кнопку "Обзор" и выберите только что загруженный файл. Затем проверьте, что это проверено, затем подтвердите все. Теперь у вас есть новая вкладка «Sparklines» перманентная.

Справа от отчетов о местоположении выберите первую ячейку рядом с первой строкой (на моем рисунке G2). На вкладке / ленте Sparklines выберите инструмент «Линия». Появится экран для заполнения аргументов функции. Заполните это следующим образом:

1. Точки1 (обязательно): диапазон данных первой строки. В моем примере: B2: F2.
2. HLine (необязательно): если вы установите значение «10», например, горизонтальная красная линия будет 10-й позицией на спарклайнах.
3. MinZone (опционально): это позволяет закрасить серым цветом зону 10 первых позиций. Поставьте «1» в качестве минимального значения.
4. MaxZone (необязательно): введите 10, чтобы материализовать первую страницу Google.
5. Теги: добавить очки для каждой позиции с изменением цвета для минимумов / максимумов. Введите здесь значение «6».
6. VerticalFlip (очень важно): поставить «1». Это переворачивает кривую, чтобы иметь положение 1 вверху и 500 внизу.

Пусть следующая формула (без необязательного элемента): = линейная диаграмма (B2: F2 ;;;;;;; 6 ;;;;;;;;;;; 1)
Проверьте и распространите формулу на весь столбец. Чтобы знать: кривая не будет отображаться, если все позиции URL-адресов одинаковы, что часто происходит в верхних позициях. Не удивляйтесь, когда создаете формулу в первой строке, если она пуста.

Суперпозиции кривых

Можно наложить еще одну степень чтения путем наложения кривых, и это очень просто. Снова выберите диапазон позиций, остановившись на 20-й позиции и учитывая заголовки, как показано на скриншоте ниже. На ленте «вставка», затем нажмите «Линия» (рисунок) и выберите «Кривые с отметками».

Затем вы получаете графическое suberbe, но, увы, полностью перевернутый. Переверните его следующим образом: убедитесь, что выбран график. На ленте «Макет» инструментов диаграммы нажмите «Оси»> «Основная вертикальная ось»> «Другие параметры для основной вертикальной оси ...». В диалоговом окне «Ось форматирования» отметьте «значения в обратном порядке» и все!

Если у вас есть немного воображения, вы можете пойти гораздо дальше с SfE, например, для создания 10 самых больших мисок, лучших достижений и т. Д. Возможности бесконечны ;-)

Похожие

Комментарии