Как схлопнуть страницы входа с параметрами? Excel-способ

В Яндекс.Метрике есть отчет по страницам входа. Иногда его неудобно использовать из-за URL-адресов, которые дублируются в списке с различными динамическими параметрами (?) и якорями (#).

Визиты на такие страницы-дубли отображаются по отдельности, но их можно объединить, выполнив несколько действий в Excel. Если кратко: Найти и заменить ~?* и #*, а также СУММЕСЛИ. А если подробно..

Открыть Яндекс.Метрику целевого проекта. Для примера буду использовать демо-счетчик Яндекс.Метрики https://metrika.yandex.ru/dashboard?id=44147844 .

Перейти на отчет по страницам входа. Например, сразу готовый вариант в «Отчеты – Содержание– Страницы входа».

Задать необходимые настройки – сегмент (например, оставить только трафик с SEO), период, группировку:

Обычно оставляю единственную группировку «Страница входа», чтобы в отчете не было много уровней.

И экспортировать таблицу в формат .xlsx с помощью кнопки в правом верхнем углу:

Результат скачивания – таблица Excel со вкладкой «Отчет»:

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

Далее нужно открыть инструмент «Найти и заменить» на вкладке «Главная»:

Ввести в поле «Найти» следующие три символа ~?* (тильда, вопросительный знак, звездочка), а поле «Заменить на» – оставить пустым. В результате, в строках таблицы удалятся динамические параметры (всё, что после вопросительного знака в URL):

Затем, ввести в поле «Найти» следующие два символа #* (решетка, звездочка) , а поле «Заменить на» – оставить пустым. В результате, в строках таблицы удалятся якорные ссылки (всё, что после знака решетки в URL):

Таким образом, список страниц с многочисленными «хвостами» превратился в набор чистых URL-адресов, но визиты по ним все еще остаются разрозненными.

В первую очередь нужно создать копию листа Excel «Отчет» и переименовать его – например, «Результат»:

На вкладке «Результат» нужно удалить числовые значения во втором столбце (вручную) и схлопнуть дубли строк в первом столбце (выделить его и выбрать «Данные – Удалить дубликаты»):

Результат – список сократился, в нем больше нет повторяющихся URL:

Подсчет визитов можно сделать с помощью формулы =СУММЕСЛИ(Отчет!A:A;Результат!A2;Отчет!B:B) , которую нужно указать в ячейке В на вкладке «Результат»:

И далее протянуть на все строки:

Готово, дубли страниц и визиты по ним объединены.

Скачать файл с примером обработки – клац.

Прежде чем использовать в работе таблицу с результатом, лучше скопировать и вставить ее в формате простых значений (123):

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

__

В таблице остались странные URL-адреса – например, https://metrica.yandex.com/promo/productd0mxsslocation.href . Их можно быстро вычислить, выполнив проверку на код ответа сервера. Например, спарсить список страниц в Screaming Frog и просмотреть все обнаруженные 404 ошибки и 301 редиректы.

__

Также, в таблице указана главная страница https://metrica.yandex.com/ и ее дубль с незащищенным протоколом http://metrica.yandex.com/ . Его можно было бы удалить на шаге 2 (найти http://, заменить на https://).

__

В ручном режиме все вариации дубликатов перебирать можно долго, поэтому при массовых отчетах удобнее задать один раз алгоритм обработки файла с помощью Power Query (надстройка Excel), и пользоваться им для всех типовых выгрузок из Яндекс.Метрики (но это уже другая история).

Обсудить статью и найти больше материалов: