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

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

1. Отделяем домен или поддомен от URL.

Ну вот как всегда. Вы загрузили кучу ссылок из разных источников и теперь для предварительной оценки хотите отсортировать их по домену. Проблема в том, что многие домены могут иметь ТЫСЯЧИ ссылок, ведущие на разные страницы сайта. Рассмотрим, как можно вычленить по одной ссылке с каждого домена. Первым шагом здесь будет выделение домена или поддомена в каждом URL.
В своих примерах я использовал Google spreadsheet, но с тем же успехом можно работать в Excel. Вся последовательность действий наглядно проиллюстрирована ниже.
Вот как можно выделить домен или поддомен в URL:

Создайте новый столбец слева от столбца с URL (теперь столбец с URL стал B, а вновь созданный получил букву А);
Введите и протяните сверху вниз эту формулу:

=LEFT(B1,FIND("/",B1,9)-1)

Эта формула удалит все знаки после слэша за названием домена.

http://www.example.com/article.html теперь превратится в http://www.example.com, а http://www.subdomain.example.com/article.html станет http://www.subdomain.example.com.

Скопируйте наш новый столбец A и сразу же вставьте его в самого себя, при вставке обязательно выберите «Вставить как значения». Если этого не сделать, невозможно будет использовать функцию «Найти и Заменить», так как в ячейках столбца А с точки зрения электронной таблицы фактически останутся формулы. Функцией «Вставить как значения» мы «убиваем» сделавшие своё дело формулы и «фиксируем» полученные по ним значения;
Используйте функцию «Найти и Заменить» для замены последующего на пробел.

http://
https://
www.

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



2. Выбираем одну ссылку с каждого домена.

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

Вот как мы это сделаем:

Выделяем нашу колонку доменов и сортируем их в алфавитном порядке.
Создаём столбец слева от наших доменов, так что домены теперь будут находиться в столбце B.
Пишем эту формулу:

=IF(B1=B2,"duplicate","unique")

Протягиваем формулу
Используем функцию фильтра для поиска дубликатов
Удаляем дубли, (но не через функцию «Удалить дубли»!). Если нужно удалить десятки тысяч строк, то таблица может сбиться или зависнуть. Чтобы этого не произошло, используем "Очистить строки" вместо "Удалить строки", а затем сортируем по A-Z.

Теперь мы знаем, с каких доменов ссылаются на нас, откуда нам идут ссылки.

Вам наверное интересно, почему я не использовал функцию «Удалить дубли»? Как выяснилось, Excel от этого почему-то зависает... Оставим это на откуп разработчикам.



3. Поиск FTW шаблонов

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

Содержат в url …/computers/internet/item40682/
Все ссылки идут с бесплатных платформ blogspot, wordpress, weebly и подобных.
Многие ссылки имеют конкретное ключевое слово в качестве анкора.

Вы можете быстро найти эти ссылки и пометить их как "disavow" следующим образом:

Создайте новый столбец. В моем примере, я собираюсь создать новый столбец в колонке C и искать закономерности в URL, которые находятся в колонке В.
Используйте эту формулу:

=FIND("/item40682",B1)
(Вам нужно заменить "item40682" на фразу, которую ищите)

Скопируйте эту формулу
Примените фильтр, чтобы увидеть все строки, содержащие цифры. Если фраза не присутствует в URL, они будут помечены "N/A" и их можно проигнорировать.
Теперь их все можно отклонить.



4. Проверьте disavow файл.

Ваша цель здесь — проверить ссылки, которые вы отклонили, чтобы потом не тратить время на их последующую оценку. Этот способ работает только для проверки ссылок, которые вы дезавуировал на уровне домена.

Первое, что вам нужно сделать, это загрузить ваш текущий файл для отклонения ссылок от Google (ссылка). По непонятной мне причине, Google предоставляет этот файл в формате CSV. Самое интересное то, что в ответ он хочет получить файл в .txt.

То что вам нужно, будет в колонке А файла CSV:



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

Ваша работа состоит в следующем:

Создать новый лист в текущей таблице
Скопируйте и вставьте колонку А в новый лист. Или, в качестве альтернативы, используйте функцию импорта, чтобы импортировать весь CSV на этот лист.
В B1 напишите «ранее отклоненные» и скопируйте весь столбец
Удалите "domain:" из каждой записи с помощью «Найти и заменить». Заменять следует пробелом.

Вернемся теперь к первой таблице. Если ваши домены находятся в колонке А и вам нужно удалить примерно 1500 доменеов из вашего файла, то формула будет иметь следующий вид:

=VLOOKUP(A1,Sheet2!$A$1:$B$1500,2,FALSE)

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

Здесь наглядно представлена все последовательность действий.



5. Облегчаем себе последующую работу.

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

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

Оригинал статьи на английском языке: https://moz.com/blog/5-spreadsheet-tips-for...ual-link-audits