Блог Аралова Евгения

Как быстро объединить вкладки в Excel и получить сводную таблицу по семантике

Бывает, приходит файл с семантикой, в котором категории разбиты по разным вкладкам:

На первой вкладке обычно находится навигация, на остальных — категории с ключевыми словами.
Мне неудобно работать с такой структурой, т. к. я не могу сразу получить нужную информацию: сколько всего ключевых слов, сколько слов в каждой категории, какая общая частотность, какая частотность у каждой из категорий и т. д.

Вместо того, чтобы посмотреть на общую картину, нужно щёлкать по каждой вкладке и вникать отдельно.
Возможно, для точечной работы это и ОК, но для понимания общей картины — весьма неудобно.

Что делать?

Чтобы увидеть всю семантику в одном месте, в удобном виде, лучше всего использовать сводные таблицы.
Вот так может выглядеть семантика в сводной таблице:

Здесь мы видим количество слов по каждой категории (ранее было вкладкой), суммарную частоту, можем посмотреть ключевые слова по каждой категории — замечательно!

Как сделать?

Чтобы получить такую табличку из файла с вкладками, прежде всего надо объединить вкладки. Для этого можно воспользоваться надстройкой в Excel — Power Query.

С версии 2016 она входит по умолчанию, до этой версии — нужно скачать и установить отдельно.

Шаг 1. Загружаем файл с семантикой в Power Query

Данные → Создать запрос → Из книги

Нажимаем кнопку «Изменить»:

Шаг 2. Удаляем лишние столбцы

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

Шаг 3. Раскрываем данные

У нас осталось два столбца:

Name — название вкладок
Data — в этом столбце скрыты ключевые слов

Чтобы получить ключевые слова, на столбце Data нужно кликнуть на стрелочки справа от заголовка:

Появится контекстное меню со столбцами вкладок:

Выбираем нужные и нажимаем ОК:

Мы раскрыли вкладки.

Шаг 4. Форматирование данных

Теперь нужно удалить лишнее и оставить только семантику и название категории.
Например, нам не нужны строчки из вкладки «#Карта загрузки», отфильтруем их:

Таким же образом поступаем со словами «Фраза», «Осталось» и «null» во втором столбце. В общем, нужно удалить всё ненужное.

Шаг 5. Приводим данные к нужному типу

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

Шаг 6. Выгрузка данных в Excel

Теперь нажимаем на кнопку «Закрыть и загрузить»:

Мы получили таблицу с данными из вкладок, где название категорий — названия вкладок.

Шаг 7. Строим сводную таблицу

7.1. Выделяем таблицу

Кликам на таблицу и нажимаем CTR + C

7.2. Вкладка Вставка → Сводная таблица

7.3. В панели управления сводной таблицы открывшейся вкладки переносим:

«Категории» → Строки
«Фраза» → Строки
«Частота» → Значения
«Фраза» → Значения (посчитает количество слов)

Таблица готова:

7 декабря   excel   power_query   семантика

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

Как известно, Гугл умеет видеть весь контент, который загружается на странице. Если Яндекс видит только исходный HTML, то Google видит всё что подгружается скриптами. Это отличная возможность оптимизировать важные фрагменты, по разному под каждый поисковик.

Берём Google Tag Manger и с помощью скриптов отдаём Гуглу свои тексты, тайтлы и всё что душе угодно — Яндекс получает контент из HTML. Очень интересно!

Я решил поискать, что есть в сети по этому поводу и нашёл отличную статейку на Медиуме под названием «Как Google реагирует на изменения страницы с помощью Диспетчера тегов (GTM)».

Ребята провели различные эксперименты: заменили тайтлы, ставили ссылки через GTM и т. д.  В итоге Google действительно индексирует всё, но очень долго. Сроки могут доходить до двух месяцев. Такая скорость убивает всю целесообразность. 

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

Уже сейчас могу сказать, что прошло две недели и страница по текстовому фрагменту из скрипта не находится. Ждём дальше.

А у вас есть опыт с индексацией Гуглом динамического контента? Делитесь в комментариях!

Как и зачем отслеживать количество страниц, приносящих трафик?

Если мы постоянно увеличиваем число страниц на сайте и они индексируются, но количество точек входа в Яндекс Метрике не увеличивается, значит, мы что-то делаем не так: пишем контент на который накладываются постфильтры, мы делаем страницы низкого качества, допускаем технические ошибки и т. д.

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

Количество страниц приносящих трафик — одна из метрик, которая позволяет вовремя обнаружить ошибки на сайте.

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

Важность данной метрики описал Алексей Трудов в своей статье «Количество страниц, приносящих трафик — удобный показатель для анализа сайта»

В данной статье я хочу показать, как легко можно отслеживать данную метрику с помощью инструмента Power BI.

Шаг 1

Скачайте и установите Power BI Desktop на сайте разработчика.

Шаг 2

Скачайте архив «PQYandexMetrica.1.15-Power.BI.zip» с гитхаба Максима Уварова.
В архиве содержится специальный коннектор, который позволяет легко подключиться к Яндекс Метрике нужного проекта.

Распакуйте архив и откройте файл.

Шаг 3

Для подключения к своим проектам в Яндекс Метрике, нужно получить уникальный токен вашего аккаунта — перейдите вот по этой ссылке и скопируйте полученный токен.

Шаг 4

Теперь, в Power BI, нажмите «Edit Queries» (Редактор запросов) и в открывшемся окне выберите готовую функцию PQYM.

Шаг 5

Функция PQYM формирует запрос к Яндекс Метрике. В пустые поля нужно добавить метрики и параметры, которые вы хотите получить. Подробнее о метриках и параметрах можно почитать в документации API Яндекс Метрики.

Для нашей задачи поля нужно заполнить следующим образом:

ids: id-проекта, который можно узнать в списке проектов Яндекс Метрики.

metrics: ym:s:visits
dimensions: ym:s:startURLDomain,ym:s:startURL,ym:s:SearchEngine,ym:s:date
date1: дата начала периода в формате ГГГГ-ММ-ДД
date2: дата конца периода
token: токен, который был скопирован на шаге 3

После заполнения нажмите «invoke»

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

Шаг 6

Теперь необходимо все данные привести к нужному типу.

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

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

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

Приведите все столбцы к своему типу данных и нажмите кнопку «Close & Apply» (Закрыть и загрузить).

Шаг 7

Осталось визуализировать полученные данные. Нам понадобятся фильтр по датам, фильтр по источникам и линейный график.

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

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

Добавить фильтр по источниками

Выберите в панели «Fields» (Поля) поле «Источник трафика».
В панели «Visualization» (Визуализации) иконку с фильтром.

Линейный график

Выберите визуализацию «Line chart» (Линейный график).
Из панели «Fields» выберите поля «Дата визита», «Страницы входа» и «Поисковая система»

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

Отчёт можно улучшать, формируя различные визуализации и добавляя различные параметры в запрос к API Яндекс Метрики на шаге 5:

— «ym:s:TrafficSource» — подробные данные каналам трафика.
— «ym:s:startURLPathLevelN», где N уровень страниц входа — поможет разделить данные по уровням страниц входа, где N — уровень страницы входа.

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

Хороший пример использования в SEO я описывал в своей статье «Power BI для SEO — как удобно контролировать видимость сайта». Рекомендую к прочтению.

Подписывайтесь на канал телеграмме

2017   power_bi   seo

Падение позиций из-за путаницы с кириллицей и латиницей

Всем известно, что у кириллицы и латиницы есть схожие буквы: «с», «B», «a» и т. д.

Во время аудитов проверяйте, чтобы латинских букв не было в русских словах и наоборот. Такие слова Яндекс не понимает и из-за этого падают позиции.

Например, в «Вывоз снега» заменим кириллицу на латиницу: «B», «с», «е», «о», «а» — получим «Bывoз cнeгa». Визуально разницы нет никакой, но это совсем разные слова. 

Как проверить?

С помощью поиска по регулярным выражениям: [а-яА-Я]. Для поиска я использую редактор sublime text.

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

Будьте внимательными — бывает, что прошлые оптимизаторы, так пакостят!

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

Sublime Text

Плагин поиска по регулярным выражениям для Chrome

Подписывайтесь на телеграм канал

2017   seo   аудит

Кто я? SEO-специалист Аралов Евгений

Здравствуйте, меня зовут Евгений Аралов, я SEO-оптимизатор. Продвигаю сайты в поисковых системах Яндекс и Google с 2009 года.

Сейчас руковожу SEO-отделом в компании Siteclinic: строю и координирую команду, обучаю специалистов. Делаю так, чтобы SEO было самым эффективным каналом продаж для бизнеса.

Публикую полезные статьи на различных блогах: siteclinic.ru, optimizatorsha.ru, searchengines.ru. Выступаю на профильных конференциях: Optimization, AllInTop и т. д. Список ссылок на мои статьи можете найти в сервисе закладок Django.com

Кроме SEO, интересуюсь построением аналитических систем и автоматизацией отчётности в Power Bi. 

Чем могу быть полезен

Если ваш сайт потерял трафик, упали позиции или нет роста, вы можете заказать аудит или Skype-консультацию. Лично беру не все проекты. Если хотите гарантированно получить аудит от нашей команды пишите на почту doctor@siteclinic.ru.

Бесплатно отвечаю на интересные вопросы по SEO — пишите на почту с пометкой «вопрос на сайт».

Для связи:

evgeniy@aralov.me
Скайп: mskrus09
Фeйсбук
Канал в телеграмме

2017