Сведение данных по недостаточно качественным страницам Яндекс

Как известно, из Яндекс вебмастера можно выгрузить данные по «недостаточно качественным» (НК) страницам («малополезным или невостребованным, или …» – после «ребрендинга»).

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

Поэтому сделал эксельку на запросах Power Query, куда подаются 3 файла-выгрузки из Яндекс вебмастера (НК в отчёте исключенных, НК в отчёте последних изменений и добавленные в том же отчёте), и данные обновляются в 1 клик.

Файлы

В этом архиве 4 файла:

1. сведение нк.xlsx
2. выгрузка посл. изменения нк.xlsx
3. выгрузка посл. изменения добавлено.xlsx
4. выгрузка исключенные нк.xlsx

Основной

Первый файл – основной, с запросами Power Query. Когда рядом с ним (в той же папке) лежат прочие 3 файла (с указанными именами), он обновляется по клику «обновить все»:

Выгрузка НК в отчёте последних изменений

Файл №2. В фильтре выбрать статус «Удалено: Малоценная или маловостребованная»

Выгрузка добавленных в отчёте последних изменений

Файл №3. В фильтре выбрать статус «Добавлено»

Выгрузка недостаточно качественных в отчёте исключенных

Файл №4. В фильтре выбрать статус «Малоценная или маловостребованная»

Инструкция

1. Скачать архив;
2. Распаковать в отдельную папку на компе;
3. Переименовать основной файл (по желанию);
4. Скачать из Яндекс вебмастера три выгрузки, о которых речь шла выше (если какой-то нет в Вебмастере, то не страшно); после скачивания каждого файла задаём имя файла в строгом соответствии с указанными;
5. Заменить (перезаписать) старые файлы выгрузок новыми;
6. Открыть основной файл и обновить данные.

На листе «нк консоль» будут данные по НК, которые удалось вытянуть из Я.вебмастера.

Нюанс интерпретации данных

Иногда бывает так, что из заявленных НК (особенно когда их тысячи) только малый процент является действительными «малокачественными». Например, остальные url’ы отдают ошибочный код ответа или закрыты мета-тегом robots, или серверным заголовком x-robots.

Те, которые после обхода пауком будут отдавать серверный ответ 200 и не будут закрыты от индексации поиском, они и являются действительными url’ами НК из базы Яндекса.

Внутренняя механика

Это если интересно. Для непосредственно работы достаточно вышеперечисленного.

Набор колонок

Полезными считаю эти: updateDate, url, lastAccess, title, fromSitemap. Соответственно, итоговый вариант и промежуточные наборы колонок подгоняются, чтобы на выходе получился такой финальный вид.

Выгрузки по последним изменениям

Две соответствующие выгрузки могут содержать дубли url’ов, которые отличаются датой обновления. Следовательно, при обработке обеих выгрузок, надо отсортировать по убыванию updateDate и удалить дубликаты url’ов – оставить последнее состояние в базе Яндекса для каждого url’а в каждой из этих выгрузок.

Действительные данные по НК двух выгрузок

Если просто использовать одну выгрузку НК последних изменений, то для какой-то части url’ов, данные будут недействительны – для тех url’ов, которые были вновь добавлены, спустя событие удаления из-за «некачественности».

Чтобы это устранить, нам и нужна выгрузка «добавленных» (в данном случае, неинтересна причина добавления, главное, что url больше не состоит в базе НК).

Вообщем, один из шагов при сведении данных это выяснить, является ли url действительным НК, путём сравнения дат обновления для одного и того же url’а из двух выгрузок. Если дата добавления позднее даты удаления из-за «некачественности», то url недействительный (не находится в базе НК по логике), и наоборот.

Сделано с помощью служебной колонки isValid с такой формулой:

= if [добавлено.updateDate] <> null then
if [добавлено.updateDate] > [updateDate] then
false
else true
else true	

Последнее слияние

Добавляем данные по НК из отчёта исключённых и удаляем дубли по url’ам.