Видимость сайта: удобный контроль через Power BI 

Автор: Евгений Аралов, SEO TeamLead команды SiteClinic
При продвижении сайта крайне необходимо отслеживать его видимость по собранной семантике в разрезе категорий, подкатегорий и сайта в целом. Контроль видимости позволяет вовремя реагировать на локальные изменения, выбирать приоритетные категории для продвижения и отслеживать эффективность внедряемых изменений.
В этой статье я покажу, как с помощью бесплатных инструментов Power BI и Google Sheets построить удобную платформу для отслеживания изменений видимости.

Пример отчёта по видимости в Power BI

Из статьи вы узнаете:

● что такое 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

Файл необходимо обновлять после каждого изменения.

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:

https://api.seolib.ru/v1/project/history/positions/by/daterange.json?access_token={уникальный-токен}&construct=rel&project_id={id-проекта}&filter_range=30.05.2017-31.05.2017

Если по каким-то причинам вы не можете получить доступ по 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. Преобразовать данные в таблицу

Для работы нужно преобразовать полученный набор данных в привычный табличный вид:

● Нажать «Record»;

● Преобразовать список в таблицу;

● Развернуть нужные столбцы;

Результат:

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
Меры в Power BI

Чтобы создать меру, нужно в ленте навигации выбрать Моделирование ->Новая мера и в поле формулы с помощью языка 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. Фильтры — удобная фильтрация данных на различных уровнях.

Подробнее об отчётах можно узнать здесь

В Power BI есть стандартные и пользовательские визуализации. Пользовательские можно загрузить с официального сайта .
Нам понадобится визуализация HierachySlicer

Скачайте и установите визуализацию в Power BI:

Рассмотрим, как сконструировать небольшой отчёт:

● Добавить фильтр по датам;

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

● Таким же образом добавить фильтр по полю «Изменение 1»;

● Сформировать таблицу с нашими метриками;

Выберите визуализацию «Таблица» и сформированные меры из набора данных.

● Точно так же добавить таблицу с запросами;

● С помощью визуализации HierachySlicer сформировать навигацию по категориям и подкатегориям.

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

Таким образом, вы можете формировать свои отчёты и всегда держать видимость сайта под контролем. Главное преимущество Power BI в том, что проделать все эти шаги нужно лишь раз, а дальше данные будут обновляться при нажатии кнопки «Обновить».

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

Начало работы Power BI
Обучение основам DAX за 30 минут
Русскоязычный блог о Power BI

*Источник: email рассылка Searchengines.ru