Массовая внутренняя перелинковка с помощью Screaming Frog и Google Sheets

Недавно мы наткнулись на отличную статью Райана Дарани, опубликованную некоторое время назад на LinkedIn, в которой показано, как найти возможности для внутренней перелинковки с помощью функции пользовательского извлечения Screaming Frog и формул Excel. В двух словах: вы используете Screaming Frog для сканирования заголовка и основного содержимого вашего сайта. После этого вы загружаете данные в Excel, где с помощью расширенной формулы сканируете основной текст, чтобы вернуть значение «True» или «False», если ваше ключевое слово найдено в тексте.

После успешного завершения процесса мы решили заменить формулу ISNUMBER, которую Райан использует в своем анализе и которая возвращает значение TRUE или FALSE, на другую формулу, которая возвращает количество раз, которое ключевое слово встречается в тексте.

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

Прежде чем мы начнем, стоит отметить, что вы можете следовать этому руководству, используя как Excel, так и Google Sheets. Мы будем использовать Google Sheets, так как он бесплатен для любого аккаунта Google.

Для целей этого руководства использованы 13 статей с сайта searchenginejournal.com в качестве образца данных.

Извлечение контента с вашего сайта с помощью Screaming Frog

Чтобы не повторяться, вы можете последовать фантастическому руководству Райана в блоге Craig Campbell SEO, которое объясняет, как извлечь основной контент с вашего сайта с помощью функции пользовательского извлечения Screaming Frog: Создание возможностей для внутренней перелинковки с помощью Screaming Frog

Для этой цели можно использовать и другие SEO инструменты, такие как Sitebulb, Oncrawl и Deepcrawl.

Откройте CSV-файл в Google Sheets

После того как вы извлекли содержимое вашего сайта из Screaming Frog в формате CSV, откройте файл как Google Sheet. Для целей данного руководства я сохранил только колонку «Содержание статьи». Однако в реальном сценарии аудита я бы включил URL, заголовок (H1), содержание статьи, количество слов и другие столбцы в зависимости от моих требований.

Добавьте ключевые слова

Добавьте каждое ключевое слово, которое вы хотите идентифицировать, в заголовок отдельного столбца.

Добавьте формулу Google Sheet для проверки наличия ключевых слов

Выберите первую пустую ячейку под вашим первым ключевым словом (ячейка B2 в моем примере) и скопируйте/вставьте следующую формулу в строку формул:

=(LEN($A2)-LEN(SUBSTITUTE(UPPER($A2),UPPER(B$1),»»)))/LEN(B$1))

Возможно, вам придется изменить ячейки, на которые ссылается формула, в зависимости от столбцов вашего Google Sheet. Ячейка A2 ссылается на местоположение содержимого, которое мы будем искать по выбранному нами ключевому слову. Ячейка B1 — это ссылка на ваше первое ключевое слово. Знаки $ делают формулу масштабируемой, поэтому при перетаскивании ячейки по листу она автоматически корректируется.

Как работает формула?

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

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

Затем функция LEN вычисляет длину текстовой строки без выбранного ключевого слова.

После этого вычитаются оба итоговых значения (исходный текст — текст без ключевого слова).

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

Перетащите ячейки в остальные столбцы

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

Добавить цветовую шкалу (необязательно)

Вы можете добавить цветовую шкалу, чтобы визуально легче определить количество ключевых слов в каждом фрагменте контента или страницы. Выберите один столбец и нажмите Формат > Условное форматирование в меню верхнего уровня. В правой части рабочего листа появится окно Условное форматирование. Выберите вкладку Цветовая шкала и в Правилах форматирования нажмите на поле предварительного просмотра цвета и выберите предпочтительную цветовую шкалу. Я выбрал цветовую шкалу «От белого к красному», так как считаю, что она лучше всего подходит для целей данной задачи.

Готово

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

Дополнительные шаги (по желанию)

Вы можете добавить дополнительные слои данных, чтобы сделать это исследование еще более значимым. Вот несколько идей:

• Сопоставьте свой контент с картой сайта. У Screaming Frog есть подробное руководство по этому вопросу: How To Audit XML Sitemaps
• Подключите Screaming Frog или другие SEO-краулеры к вашему аккаунту Google Analytics и сопоставьте веб-трафик с каждым URL.
• Подключите краулер к аккаунту Google Search Console и добавьте данные о кликах, показах, CTR и позиции в выдаче для каждого URL.
• Получить данные из сторонних SEO-инструментов, таких как Ahrefs или SEMRush, чтобы получить данные об обратных ссылках для каждой страницы с помощью функции vlookup. Screaming Frog даже предлагает API для Ahrefs, если у вас есть учетная запись.
• Есть доступ к лог-файлам сервера? Отлично. Добавьте данные лог-файлов в анализ, чтобы проверить, когда Googlebot в последний раз посещал каждый URL (и частоту).

Оригинал статьи взят с сайта Lead Dealer

Другие статьи:

• Как использовать режим списка в Screaming Frog
• Как спарсить цены конкурентов при помощи Screaming Frog
• Парсим контент конкурента программой Screaming Frog