Кейс: вычисляем страницы без трафика при помощи Excel

Показываю простой способ, как вычислить страницы без трафика на вашем сайте. Когда знаешь что "не работает" - можно скорректировать текст, закупку ссылок и т.д. Полуавтоматическая работа.

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

Для задуманного понадобится:

• Данные с Google Analytics
• Какой-нить софт, чтобы узнать URL всех ваших статей сайта
• MS Excel

Кратко алгоритм действий

Для начала выгрузим отчет по страницам входа и трафику на них за любой период с Google Analytics (GA).

Затем при помощи программы соберем все URL вашего сайта. Программу бесплатную подскажу.

Останется получить данные по кол-ву посетителей на каждую страницу вашего сайта, в чем поможет Excel.

Выгрузим статистику из Google Analytics

Заходим в "Страницы входа"

Клик для увеличения

Выбираем отображение на странице нужное кол-во строк (можете выбрать максимум)

Клик для увеличения

Сверху страницы находим "Экспортировать" и делаем это в Excel

Клик для увеличения

Сохранили полученный файл куда-нить себе на компьютер.

Получаем URL всех страниц сайта

Для получения всех страниц сайта прекрасно подходит бесплатная программа Netpeak Spider. Ранее о еще одном нестандартном применении ее я рассказывал в этой статье.

Запускаем программу и дожидаемся окончания сканирования сайта

Клик для увеличения

После чего выгружаем отчет с одним лишь параметром все в тот же файл Excel

Клик для увеличения

В полученном файле можете почистить лишнее, если бот нашел то, что вы безуспешно прятали)

Объединяем полученные данные в один файл

Теперь откроем отчет Google Analytics и скопируем в новый файл Excel нужные нам столбцы. Нас интересует 1 и 4. Можно прихватить вместе со 2 и 3, либо удалить их, чтобы осталось только нужное.

Клик для увеличения

Я "прихватил" все, чтобы информации на выходе получить чуть больше.

Как уже говорил, копируем эти данные в новый файл Excel на Лист1.

Открываем файл с информацией, которую вам спарсил по сайту ранее Netpeak Spider. Как видим, у нас URL сайта в абсолютном виде (присутствует название домена). А в отчете GA относительные ссылки. Приведем к общему виду.

В файле отчета Netpeak Spider выделяем свой домен, при этом / в конце домена не трогаем (должно остаться):

Клик для увеличения

Нажимаем CTRL+F и заменяем автоматически название домена на пустое значение. Таким образом оно исчезнет автоматически во всех ячейках

Клик для увеличения

Полученную информацию копируем полностью и вставляем в файл Excel на Лист2. Если помните, на Лист1 у вас информация из GA.

Используем формулу

Последнее, что осталось сделать, это получить на Лист2 напротив URL страниц вашего сайта соответствующее значение со статистики. Там, где не будет цифры (#Н/Д) с количеством посещений - это искомые статьи, куда к вам не заходят и нужно предпринимать какие-то меры по исправлению ситуации.

Скопируем себе на Лист2 в первую строку названия колонок с отчета GA (Лист1). Это для удобства

А теперь "королева вечера". Ее Величество, формула.

[sociallocker]

=ВПР($A2;Лист1!$A$2:$B$399; СТОЛБЕЦ();0)

[/sociallocker]

Здесь у нас фактически 2 переменные:

• $B - это колонки с Лист1, откуда мы хотим получить значение.
• $399 - количество строк на Лист1, из которых мы хотим получить данные. Выставляете свое значение

Итак, у нас есть на Лист1:

• $B - колонка "Сеансы"
• $C - колонка "Новые сеансы, %"
• $D - колонка "Новые пользователи"

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

Клик для увеличения

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

Смотрим наглядное видео по использовании формулы в Excel

Смотрите в высоком разрешении.

Дополнение

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

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

Итог

Благодаря формуле идет автоматическое сравнение URL по первому ряду и подстановка соответствующих значений в следующий ряды данных.

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

Если используете другие методы вычисления страниц без трафика - буду рад перенять опыт. Напишите в комментарии.