Аналіз логів сайту за допомогою 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 хвилин.

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

Кориснi посилання:

• Початок роботи Power BI.
• Російськомовний блог про Power BI.

Еще по теме:

• Березневе оновлення алгоритму Google. перші висновки
• Чи є зв’язок Пінгвіна з Мінусинськом?
• Аналіз логів сайту за допомогою Power BI: покрокова інструкція