Анализ логов сайта с помощью Power BI: пошаговая инструкция

Анализ логов сайта – важная часть технического аудита, особенно если у сайта есть проблемы с индексацией.
В этой статье мы разберём, что такое логи, какую информацию из них можно получить, и научимся делать анализатор с помощью BI-системы Power BI.

Что такое лог сайта

Польза для SEO

Чем анализировать

Анализатор логов на Power BI

Шаг 1. Подготовка данных

Шаг 2. Загрузка данных в Power BI

Шаг 3. Форматирование загруженных данных

Шаг 4. Объединить наборы данных

Шаг 5. Визуализация данных

Заключение

Что такое лог сайта

По сути, журнал, в который записываются все обращения клиента к серверу:

1. Пользователь вбивает в адресную строку URL https://site.ru/url.html.
2. Браузер отправляет GET-запрос к серверу.
3. Если запрос корректен сервер отправляет статус «200 ОК».
4. Сервер начинает отправлять файлы сайта.

Так вот, сервер записывает в лог следующую информацию:

• IP-адрес клиента;
• дата, время запроса и часовой пояс;
• тип запроса: GET или POST;
• page/2 — к какой странице было обращение;
• протокол;
• код ответа сервера;
• реферр — откуда было обращение;
• User-Agent клиента;
• количество байт полученных клиентом.

Клиентом также выступают различные боты, в том числе и боты Google и Яндекса.

Пример записи:

Тут мы видим, что 11 июня был сделан запрос /kovry/klassicheskie-kovry/ovalnyj-kover-elite-472-61659-floare-carpet с IP-адреса 46.229.168.151, судя по User-Agent эту страницу запрашивал бот сервиса Semrush.com. Статус ответа 200, успешно!

Хранятся логи на сервере в текстовом файле и выглядят следующим образом:

Польза для SEO

Благодаря тому, что в логе есть информация о IP-адресе и User-Agent клиента, мы можем видеть, как боты ходят по нашему сайту, что, в свою очередь, позволяет узнать массу интересного:

• На какие страницы бот ходит чаще. Поняв это, мы можем на этих страницах размещать ссылки на новые страницы или на те, что не индексируются.
• На какие страницы расходуется краулинговый бюджет.
• Заходит ли бот на нужные страницы. Особенно важно, когда есть проблемы с индексацией.
• Выявить мусорные страницы, которые не выявляются при парсинге сайта.
• Своевременно увидеть ошибки редиректов, ошибки 404 и т. д. Особенно актуально при переезде или смене протокола.
• Частота захода ботов по датам.

И много ещё чего, всё зависит от задач и интерпретации данных.

Чем анализировать

В связи с тем, что логи хранятся в текстовом формате, вариантов для анализа достаточно много. Рассмотрим несколько программ:

Excel — самый простой вариант. Если нужно быстро проанализировать небольшой лог, подойдёт, но в весьма неудобном виде.

Screaming Frog Log Analyzer — хороший инструмент от разработчиков культовой программы Screaming Frog SEO Spider. Бесплатный до 1000 строк, более 1000 – стоимость 99 фунтов в год.

Из плюсов — простой, есть базовые отчёты.

Минус — платный и нет возможности настраивать кастомные отчёты.

JetOctopus — отличное облачное решение. Система платная, от 20 евро, есть семидневный триал. Если нужен анализ сайта с количеством страниц более 100к — это лучшее решение.

Из плюсов — позволяет сканировать большие сайты, много фильтров для составления отчётов.

Из минусов — только цена.

Есть ещё различные варианты анализаторов, но сегодня я хочу подробно остановиться на Power BI.

Power BI — бесплатный инструмент от Microsoft для бизнес-аналитики, позволяющий получать, обрабатывать и визуализировать данные из различных источников: файлов, баз данных, различных API. Это мощнейший инструмент, который можно применять для различных задач аналитики.

Из плюсов — можно склеивать несколько логов в один, объединить данные парсера и логи, удобная визуализация и гибкая система настройки дашбордов, широкие возможности по работе с типами данных, можно моделировать данные.

Из минусов — нужно самому подготавливать данные, на больших объёмах может тормозить.

Теперь давайте сами сделаем анализатор на Power BI.

Анализатор логов на Power BI

Процесс создания дашборда для анализа логов состоит из нескольких этапов: подготовка данных → загрузка → форматирование → визуализация.

Шаг 1. Подготовка данных

Для формирования отчёта понадобятся следующие данные:

• Папка с файлами логов за период.

У меня тут собраны логи за несколько недель.

• Список URL сайта, полученные с помощью парсера. У меня немного обработанный файл – экспорт Screaming Frog.

Ну и, конечно, скачать Power BI.

Шаг 2. Загрузка данных в Power BI

Прежде чем перейти к загрузке данных в Power BI, необходимо отключить политику конфиденциальности:

Файл → Параметры и настройки → Параметры → Конфиденциальность → Игнорировать уровни конфиденциальности

2.1. Загружаем логи из папки
2.1.1. Подключаем источник

Нажимаем на ленте навигации Получить данные → Из Папки.

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

2.1.2. Объединяем файлы

У нас в папке лежит несколько файлов, и для работы их нужно объединить в один. В Power BI это делается в три клика. Примечательно ещё то, что добавив новый файл в папку, он автоматически объединится с остальными.

Итак, в открывшемся окне нажимаем кнопку Редактировать.

Далее выделяем первый столбец → нажимаем правой кнопкой мыши → нажимаем Удалить другие столбцы.

У оставшегося столбца нажимаем на стрелки, которые находятся справа на заголовке.

Все файлы объединились в единый набор данных.

2.2. Загружаем файл с URL

На ленте навигации выбираем Получить данные → Excel.

Указываем путь к нашему файлу.

Готово.

Теперь у нас есть два набора данных:

• log_1 — таблица логов;
• url — таблица с URL.

Шаг 3. Форматирование загруженных данных

Теперь нам нужно подготовить данные для дальнейшей работы: разделить нужные столбцы, назвать их, выбрать нужный формат данных.

3.1. Форматирование набора данных с логами
3.1.1. Удалим ненужные столбцы

В нашем наборе есть два пустых столбца, их нужно удалить:

Правая кнопка мыши → Удалить столбец

3.1.2. Форматируем столбец с датой

Дата начинается с ненужного символа «[», удалим его:

Правая кнопка мыши → Заменить значение

Ищем значение, заменяем на пустоту.

Сейчас у нас дата и время находятся в одном столбце, для корректного отображения даты их нужно разделить:
Правой кнопкой мыши → Разделить столбец → По разделителю

Разделить нужно по самому левому разделителю «:».

Дата и время должны встать в разные столбцы.

3.1.3. Форматируем столбец URL

Точно таким же образом, как мы разделили даты, нам нужно отделить тип метода и протокол от URL.

Сейчас:

Используем разделитель «Пробел».

Стало:

3.1.4. Пропишем заголовки

Дадим название каждому столбцу. Для этого кликните по заголовку два раза.

3.1.5. Выделим ботов

Теперь нам нужно выделить основных ботов. Для этого создадим отдельный столбец. В Power BI есть возможность создать условный столбец, ячейки будут заполняться, если выполняется условие.

В нашем случае мы создадим столбец, ячейки которого будут заполняться по следующей логике: если столбец User-Agent содержит название бота, то ячейка называется «Название бота».

На ленте навигации переходим в «Добавить столбец» → «Условный столбец».

В открывшемся окне прописываем условия:

• если содержит «Googlebot-Image/1.0» → Googlebot-Image/1.0
• если содержит «Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)» → Mobile Googlebot
• если содержит «Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)» → Googlebot
• если содержит «Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)» → YandexBot

У нас появится столбец с юзерагентами бота*:

*Важно — название юзерагента не подтверждает, что это бот. Нужно делать обратный DNS-запрос IP-адреса для подтверждения принадлежности бота поисковой системе, подробнее тут. Если вы видите аномальные всплески ботов, обязательно проверяйте по IP.

3.1.6. Изменим тип данных у столбца Status

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

Шаг 4. Объединить наборы данных

Теперь нужно подтянуть данные из таблицы с URL в таблицу логов:

1. В ленте навигации выбираем Главная → Слияние запросов.
2. В появившемся окне в нижнем выпадающем списке выбрать таблицу, из которой нужно получить данные.
3. Объединяем по URL.

Получим столбец со свёрнутой таблицей.

Нажав на стрелки, мы можем выбрать, какие столбцы хотим подтянуть.

Подтянем данные об индексации и URL:

После того как мы загрузили все данные и произвели нужные манипуляции, можно загружать их в рабочую область:

В навигационной ленте нужно нажать «Закрыть и применить».

Шаг 5. Визуализация данных

Отчёт в Power BI может состоять из визуализаций, основанных на полученных наборах данных. Данные могут быть обработаны и отфильтрованы по-разному.

Основными полями для работы являются:

• Страница — на ней формируется отчёт.
• Визуализации — различные графики и таблицы.
• Поля — данные, на основе которых формируются визуализации.
• Фильтры — удобная фильтрация данных на различных уровнях.

Подробнее об отчётах можно узнать в инструкции от Microsoft.

Итак, давайте сформируем небольшой дашборд.

5.1. Добавим фильтр по ботам

Выберем в панели «Визуализации» иконку с фильтром, а в панели «Поля» — поле «Bot».

5.2. Добавим таблицу с количеством посещений бота по страницам

И отфильтруем ботов.

5.3. Построим график посещения ботов по датам

Получаем вот такую красоту:

5.4. Узнаём, какие страницы не нашёл парсер, но посетил бот

Помните, на четвёртом шаге мы объединили данные? Данные подтянулись по столбцам URL: если есть общий URL, столбец заполняется, если нет — остаётся пустое значение.

Отфильтровав пустые значения по столбцу, мы поймём, каких URL не нашёл парсер.

Сделаем фильтр:

Отфильтруем.

Созданная нами ранее таблица отфильтруется, и в ней будут URL-фантомы.

Надо проверить, что за статусы у этих URL. Добавим в таблицу столбец Status.

301 редирект, так и думал 🙂

Простой дашборд может выглядеть так:

Файл с примером можно скачать по ссылке.

Заключение

Таким образом, можно проверять различные гипотезы и анализировать лог. Возможностей очень много: URL можно разбить на подкатегории и анализировать сканирование по разделам; можно строить свои счётчики посещения; смотреть, с каких URL чаще заходят боты; в какое время наибольшая нагрузка и т. д.

Кажется, что сформировать такой отчёт в Power BI сложно и муторно, но это обманчиво – всё делается за 10 минут.

Важно! Сформировав отчёт единожды, вам не нужно будет его формировать каждый раз, достаточно обновить файлы логов!
Всем хороших логов!

Полезные ссылки:

• Начало работы Power BI.
• Русскоязычный блог о Power BI.

Еще по теме:

• Есть ли корреляция между процентом эффективных страниц сайта и трафиком из ПС Google?
Есть ли какая-либо корреляция между % эффективных страниц сайта в отчете об удобстве страниц и трафиком из ПС Google? У нас сейчас в отчете пишет...
• Как Google может оценивать сайты. Новая модель Knowledge-Based Trust (КВТ)
Google представил свою новую разработку, модель ранжирования, основанную на оценке достоверности фактов на сайтах, - Knowledge-Based Trust. Мы подготовили перевод документа, в котором представители Google описывают, как работает модель KBT,...
• SEO vs PPC: почему продвижение в органической выдаче более эффективно и менее затратно?
При выборе стратегии продвижения в органической выдаче или контексте лучше выбрать… оба варианта. Если, конечно, бюджет позволяет. Если нет, то в первую очередь нужно задать...
• SEO-гайд для новичков: анализ просадки позиций в Яндексе
Порой падение позиций в Яндексе бывает резким, а иногда просадка происходит медленно, но верно. В этой статье мы расскажем, как по характеру просадки позиций выявить...
• Что такое Passage Ranking, стоит ли что-то ждать от обновления?
Что такое Google Passage Ranking? Кого затронет Passage Ranking? Полезное о Passage Ranking Как оптимизировать сайт под Google Passage Ranking До недавнего времени Google оценивал...