Автор: Евгений Аралов, 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. Фильтры — удобная фильтрация данных на различных уровнях. 
Подробнее об отчётах можно узнать здесь — https://goo.gl/JWXMLn В Power BI есть стандартные и пользовательские визуализации. Пользовательские можно загрузить с официального сайта.
Нам понадобится визуализация HierachySlicer Скачайте и установите визуализацию в Power BI: 
Рассмотрим, как сконструировать небольшой отчёт: ● Добавить фильтр по датам; Выберите в панели «Визуализации» иконку с фильтром, а в панели «Поля» — поле «Дата». 
● Таким же образом добавить фильтр по полю «Изменение 1»; ● Сформировать таблицу с нашими метриками; Выберите визуализацию «Таблица» и сформированные меры из набора данных. 
● Точно так же добавить таблицу с запросами; ● С помощью визуализации HierachySlicer сформировать навигацию по категориям и подкатегориям. В итоге мы получили удобный отчёт, благодаря которому можем видеть, как изменилась видимость за нужный период по категории и запросам. Сразу можно переключаться на нужную категорию и просматривать позиции запросов. Благодаря фильтру по изменениям мы можем отдельно отслеживать позиции запросов страниц, на которых вносились изменения. 
Таким образом, вы можете формировать свои отчёты и всегда держать видимость сайта под контролем. Главное преимущество Power BI в том, что проделать все эти шаги нужно лишь раз, а дальше данные будут обновляться при нажатии кнопки «Обновить». Полезные ссылки: Начало работы Power BI — https://goo.gl/p3Jrg7
Обучение основам DAX за 30 минут — https://goo.gl/4kSEVH
Русскоязычный блог о Power BI — https://goo.gl/HECyzD | 
Комментариев нет:
Отправить комментарий