Автор: Евгений Аралов, SEO TeamLead команды SiteClinic
При продвижении сайта крайне необходимо отслеживать его видимость по собранной семантике в разрезе категорий, подкатегорий и сайта в целом. Контроль видимости позволяет вовремя реагировать на локальные изменения, выбирать приоритетные категории для продвижения и отслеживать эффективность внедряемых изменений.
В этой статье я покажу, как с помощью бесплатных инструментов Power BI и Google Sheets построить удобную платформу для отслеживания изменений видимости.
Пример отчёта по видимости в Power BI по ссылке https://goo.gl/pFrk3j
Из статьи вы узнаете:
● что такое Power BI;
● какие метрики лучше использовать для отслеживания видимости;
● как подготовить данные для отправки в Power BI;
● как с помощью Power BI получать, обрабатывать и визуализировать полученные данные.
1. Что такое Power BI
Power BI — набирающий популярность бесплатный инструмент от Microsoft для бизнес-аналитики, позволяющий получать, обрабатывать и визуализировать данные из различных источников: файлов, баз данных, различных API.
Есть два типа приложений:
● Power BI Desktop — десктопное приложение, обычно используемое для конструирования отчётов.
● Power BI Service — онлайн-приложение, которое отлично подходит для анализа готовых отчётов.
2. Метрики оценки видимости
В работе мы используем следующие метрики:
1. Абсолютное значение запросов в ТОП-10 / ТОП-5 (условное обозначение TOП10);
2. Относительное значение запросов в ТОП-10 / ТОП 5 (%ТОП10);
3. Абсолютное значение частоты, которая находится в ТОП-10 / ТОП 5 (WS-ТОП10);
4. Относительное значение частоты, которая находится в ТОП-10 / ТОП 5 (WS%-ТОП10).
Эти метрики применимы ко всему сайту, группе документов, выборке запросов, документу.
Вот как может выглядеть отчёт по категориям с этими метриками:
Скриншот отчёта видимости по категориям в Power BI
Из отчёта видно, что у категории GIGI низкая видимость (всего 17% запросов в ТОП-10), при этом лишь 6% от Вордстата в ТОП-10. Это говорит о том, что в ТОПе находятся НЧ-запросы.
У категории TIGI обратная ситуация: хорошая видимость (78% запросов в ТОП-10) за счёт ВЧ-/СЧ-запросов (91% от Вордстата в ТОП-10).
Категория Kerastase имеет среднюю видимость, но основная часть запросов находится за пределами ТОП-10.
Больше о метриках видимости вы узнаете из доклада Стаса Паламаря «Метрики и сценарии работы с анализом видимости проектов» (скачать доклад).
3. Подготовка данных для отправки в Power BI
Для формирования отчёта понадобятся следующие данные:
1. Структурированное семантическое ядро в файле Google Sheets;
2. Позиции по датам — удобно получать через API, но можно выгружать в Google Sheets. В статье я использую API сервиса съёма позиций Seolib.ru;
3. Отдельный список продвигаемых URL в Google Sheets — необходимо для группировки страниц, на которых были произведены изменения.
3.1. Отдельный список URL
Допустим, на нескольких страницах было добавлено вхождение в Title и ссылки, был убран текст. Мы хотим отследить в Power BI, как изменения повлияли на видимость URL. Для этого нужно сгруппировать страницы по типу вносимых изменений.
Формируем файл, где присваиваем нужному URL тег, соответствующий изменению: добавлено вхождение Title, убран текст, добавлено вхождение в ссылку. В будущем это позволит группировать страницы по данным тегам.
Файл должен содержать следующие столбцы:
URL | Изменение 1 | Изменение 2 | Изменение 3
Файл необходимо обновлять после каждого изменения.
(Исходник: https://goo.gl/Shgufd )
3.2. Структурирование семантики в Google Sheets
Семантическое ядро необходимо добавить в Google Sheets и структурировать следующим образом:
Категория -> Подкатегория -> Запрос - > URL | Изменение 1 | Изменение 2 | Изменение 3 / WS / “WS” / !WS
где WS — частоты по Яндекс. Вордстату.
Пример реализации
В столбцы Изменение 1, Изменение 2, Изменение 3 необходимо импортировать теги из файла, который мы сформировали в предыдущем пункте. Для этого составим следующую формулу:
=IFERROR(VLOOKUP($D2;IMPORTRANGE("id-файла-с-тегами";"$A$1:$D$10000");2;FALSE);"Без группы")
Где
$D2 — ячейка с нужным URL;
“id-файла-с-тегами” — id файла, из которого мы импортируем теги;
$A$1:$D$1000 — диапазон таблицы с тегами;
2 — номер столбца с нужным тегом.
Результат:
Далее нам нужно опубликовать файл в интернете в формате CSV:
● нажимаем Файл - > Опубликовать в интернете;
● выбираем: Весь документ - > Формат CSV;
● сохраняем полученный URL.
3.3. Позиции по датам
Лучший метод получения данных по позициям — использование API сервиса. API позволяет напрямую отправлять данные в Power BI, минуя экспорт в интерфейсе сервиса.
Обычно работа с API выглядит следующим образом:
● получение уникального токена;
● формирование и отправка запроса;
● получение данных.
Вот так выглядит сформированный запрос по API сервиса SEOlib:
Если по каким-то причинам вы не можете получить доступ по API, позиции можно выгрузить в Google Sheets и опубликовать в формате CSV.
Загружать данные в Power BI будем по полученной ссылке.
4. Подключение и форматирование данных в Power BI
Прежде чем перейти к загрузке данных в Power BI, необходимо отключить политику конфиденциальности: Файл -> Параметры и настройки -> Параметры -> Конфиденциальность -> Игнорировать уровни конфиденциальности
Теперь перейдём к загрузке и обработке данных.
4.1. Загрузка семантики из Google Sheets
Чтобы загрузить сформированную в пункте 3.2. структуру, необходимо сделать следующее:
4.1.1. Получить данные● нажать в ленте навигации Получить данные -> Интернет;
● в появившемся поле вставить сохранённую ссылку на файл с семантикой (см. п. 3.2.);
4.1.2. Изменить кодировку
Нужно выбрать кодировку UTF-8 и нажать на кнопку «Изменить»:
Результат:
4.2. Загрузка позиций
4.2.1. Создать источникНужно выбрать в ленте навигации Создать источник - > Интернет, вставить в него сформированный запрос.
Обычно по API данные отдаются в формате JSON — их нужно преобразовать в таблицу.
4.2.2. Преобразовать данные в таблицуДля работы нужно преобразовать полученный набор данных в привычный табличный вид:
● Нажать «List»;
● Преобразовать список в таблицу;
● Развернуть нужные столбцы;
Результат:
Если импортируете позиции из файла -
Отмечу, что по API данные отдаются в виде несводной таблицы, а когда вы экспортируете позиции в CSV или Excel, данные часто сведены. Со сводными данными неудобно работать, их следует преобразовать следующим образом:
- Выделить все столбцы, кроме столбца с запросами;
- В ленте навигации нажать «Отменить свёртывание столбцов»;
Результат:
4.2.3. Добавить столбец с индексом
Этот столбец поможет нам с вычислением метрик.
● Выбрать в ленте навигации Добавить столбец -> Столбец индекса -> Настроить;
● Начальный индекс — 1; Инкремент — 0;
Результат:
4.2.4. Заменить значения
Обычно сервисы проверки обозначают позиции, которые находятся за пределами ТОПа, как ноль или прочерк.
Нам нужно привести их к виду [максимальная глубина съёма] + 1.
Например, если мы снимаем позиции с глубиной 100, значит, заменяем ноль или прочерк числом 101. Кликаем правой кнопкой мыши на столбце с позициями и в контекстном меню выбираем пункт «Замена значений».
У меня глубина парсинга 150 позиций, значит, я заменяю 0 числом 151.
4.2.5. Объединить данные
Теперь нужно подтянуть данные из таблицы со структурой в таблицу с позициями:
● в таблице с позициями в ленте навигации выбрать Главная -> Слияние запросов;
● в появившемся окне в нижнем выпадающем списке выбрать таблицу, из которой нужно получить данные;
● теперь необходимо выбрать общие сущности в обеих таблицах, т. к. по этим сущностям будет осуществляться объединение. В наших таблицах — Поисковые запросы;
● нажать «Ок» — и мы получим столбец со свёрнутыми таблицами;
● развернуть нужные столбцы;
Таким образом, мы получили структурированную по категориям семантику с позициями.
4.2.6. Преобразование типов данныхВ Power BI нужно очень внимательно следить за тем, какой тип данных имеет каждый из столбцов. Часто бывает так, что числовые данные имеют текстовый тип, а это приводит к ошибке при моделировании данных.
Типы данных отображаются в иконках в заголовках таблиц:
Чтобы преобразовать тип данных, достаточно кликнуть по этой иконке и выбрать нужный тип.
Приведите все столбцы к своему типу данных.
Подробнее о типах данных в справкеПосле того как мы загрузили все данные и произвели нужные манипуляции, можно загружать их в рабочую область:
В навигационной ленте нужно нажать «Закрыть и применить».
5. Моделирование данных
Теперь для отслеживания видимости необходимо вычислить все нужные метрики. В этом нам помогут меры, которые вычисляются с помощью DAX.
DAX — это коллекция функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений.
Чтобы ближе ознакомиться с этими понятиями, почитайте официальные источники:
Чтобы создать меру, нужно в ленте навигации выбрать Моделирование ->Новая мера и в поле формулы с помощью языка DAX написать меру:
Создадим следующие меры:
ТОП10 — абсолютное значение запросов в ТОП-10;
%ТОП10 — относительное значение запросов в ТОП-10;
WS-ТОП10 — сумма частоты по Яндекс. Вордстату в ТОП-10;
%WS-ТОП10 — доля частоты по Яндекс. Вордстату в ТОП-10;
ТОП10
Нам нужно посчитать количество запросов в ТОП-10 за последнюю дату в заданном диапазоне.
Формула DAX:
CALCULATE(SUM(' ваш-набор-данных'[Индекс]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата]));'ваш-набор-данных'[Позиция]<=10)
Здесь мы суммируем значения столбца «Индекс», если значение столбца «Позиция» меньше или равно 10. Суммирование осуществляется только в том случае, если в столбце «Дата» дата соответствует крайней дате в заданном диапазоне.
%ТОП10
Достаточно разделить количество запросов в ТОП-10 на общее количество запросов.
Формула DAX:
[ТОП-5]/CALCULATE(SUM('ваш-набор-данных'[Индекс]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата])))
WS-ТОП10
Аналогично мере ТОП-10, только здесь мы будем суммировать не значения поля «Индекс», а значения поля частот по Яндекс. Вебмастеру.
Формула DAX:
CALCULATE(SUM('ваш-набор-данных'["!WS"]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата]));'ваш-набор-данных'[Позиция]<=10)
%WS-ТОП10
Формула DAX:
[WS-ТОП10]/CALCULATE(SUM('ваш-набор-данных'["!WS"]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата])))
Аналогично следует сделать и для ТОП-5, ТОП-100.
Также нам нужна отдельная мера по позициям за крайнюю дату. Она позволит сформировать отчёт с разницей позиций за крайнюю и первую даты по каждому запросу.
Позиция сегодня:
CALCULATE(SUM('ваш-набор-данных'[Позиция]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата])))
Помимо этих мер, имеет смысл сделать меры, которые отображают, как изменился процент запросов в ТОП-10 по сравнению с предыдущим периодом.
Обозначим эти меры таким образом:
d-Позиций — разница запросов за крайнюю и первую даты;
d-%ТОП10 — разница % запросов в ТОП-10;
d-%WS-ТОП10 — разница доли частоты в ТОП-10.
d-Позиций
Формула DAX:
CALCULATE(SUM('ваш набор данных'[Позиция]);FILTER('ваш-набор-данных';''ваш-набор-данных'[Дата]=MAX('ваш-набор-данных'[Дата]))) - CALCULATE(SUM('ваш-набор-данных'[Позиция]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MIN('ваш-набор-данных'[Дата])))
d-%ТОП10
Разницу вычисляем по формуле: [% запросов в ТОП крайняя дата] - [% запросов в ТОП первая дата].
Формула DAX:
[%ТОП-10] - CALCULATE(SUM('ваш-набор-данных'[Индекс]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MIN('ваш-набор-данных'[Дата]));'ваш-набор-данных'[Позиция]<=10)/CALCULATE(SUM('ваш-набор-данных'[Индекс]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MIN('ваш-набор-данных'[Дата])))
d-%WS-ТОП10
Формула DAX:
[%-WS-ТОП-10]-CALCULATE(SUM('ваш-набор-данных'["!WS"]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MIN('ваш-набор-данных'[Дата]));'ваш-набор-данных'[Позиция]<=10)/CALCULATE(SUM('ваш-набор-данных'["!WS"]);FILTER('ваш-набор-данных';'ваш-набор-данных'[Дата]=MIN('ваш-набор-данных'[Дата])))
6. Визуализация данных
Теперь можно переходить к визуализации данных и построению отчётов.
Отчёт в Power BI может состоять из визуализаций, основанных на полученных наборах данных. Данные могут быть обработаны и отфильтрованы по-разному.
Основными полями для работы являются:
1. Страница — на ней формируется отчёт;
2. Визуализации — различные графики и таблицы;
3. Поля — данные, на основе которых формируются визуализации;
4. Фильтры — удобная фильтрация данных на различных уровнях.
Подробнее об отчётах можно узнать здесь — https://goo.gl/JWXMLn
В Power BI есть стандартные и пользовательские визуализации. Пользовательские можно загрузить с официального сайта.
Нам понадобится визуализация HierachySlicer
Скачайте и установите визуализацию в Power BI:
Рассмотрим, как сконструировать небольшой отчёт:
● Добавить фильтр по датам;
Выберите в панели «Визуализации» иконку с фильтром, а в панели «Поля» — поле «Дата».
● Таким же образом добавить фильтр по полю «Изменение 1»;
● Сформировать таблицу с нашими метриками;
Выберите визуализацию «Таблица» и сформированные меры из набора данных.
● Точно так же добавить таблицу с запросами;
● С помощью визуализации HierachySlicer сформировать навигацию по категориям и подкатегориям.
В итоге мы получили удобный отчёт, благодаря которому можем видеть, как изменилась видимость за нужный период по категории и запросам. Сразу можно переключаться на нужную категорию и просматривать позиции запросов. Благодаря фильтру по изменениям мы можем отдельно отслеживать позиции запросов страниц, на которых вносились изменения.
Посмотреть отчёт можно по ссылке https://goo.gl/pFrk3j
Таким образом, вы можете формировать свои отчёты и всегда держать видимость сайта под контролем. Главное преимущество Power BI в том, что проделать все эти шаги нужно лишь раз, а дальше данные будут обновляться при нажатии кнопки «Обновить».
Полезные ссылки:Исходники — https://goo.gl/tlH8m9
Начало работы Power BI — https://goo.gl/p3Jrg7
Обучение основам DAX за 30 минут — https://goo.gl/4kSEVH
Русскоязычный блог о Power BI — https://goo.gl/HECyzD
