Как проанализировать просадку трафика в Google с помощью Search Analytics for Sheets

1. Определяем диапазон дат для анализа просадки
2. Выгружаем полученные данные в таблицу
3. Сравниваем показатели
4. Создаём сводную таблицу
5. Делаем выводы

Если у сайта просел трафик из Google, и мы не знаем, что послужило причиной падения, первое, что нужно сделать – определить список просевших страниц и поисковых запросов, которые стали приносить меньше трафика.

Неправильно делать выводы о причинах просадки только по 1-2 просевшим страницам или паре «любимых» запросов, которые первыми пришли на ум. Если просадка существенная, такой поверхностный анализ может привести к искажённым выводам и потере времени на действия, которые не принесут результата.

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

• какие типы страниц просели;
• поменялись ли посадочные страницы;
• просели все запросы или с определённым интентом;
• просели все разделы или какие-то определённые и т. д.

Самый простой и быстрый способ определить список просевших страниц – сравнить страницы входа в Google Analytics или Я.Метрике до и после просадки, выгрузить отчёт, посчитать разницу визитов и отсортировать по возрастанию. Но так мы не получим данных о том, по каким запросам просели эти страницы, а это может быть ключом к разгадке причин просадки.

Полноценно решить эту задачу можно другим способом. Для этого нам понадобятся базовые знания формул Google Sheets и плагин Search Analytics for Sheets, который выгружает данные из отчёта «Эффективность» в Google Search Console прямо в таблицу.

Чтобы приступить к генерации отчёта, нужно установить этот плагин и войти в Google – аккаунт, к которому привязана консоль анализируемого сайта.

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

1. Определяем, какой диапазон дат нам нужен

Наша задача – найти страницы и запросы, которые потеряли больше всего трафика после конкретной просадки (резкой или плавной).

Для решения задачи нужно:

• выгрузить статистику по страницам и запросам, которые приносили трафик до просадки;
• выгрузить статистику по страницам и запросам, которые приносят трафик после просадки;
• сравнить их показатели.

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

Важные нюансы:

• периоды должны быть одинаковыми по длительности и содержать одинаковое число рабочих/выходных дней;
• если просадка была резкой и произошла давно, в качестве периода «после» берём диапазон, максимально близкий к просадке;
• если просадка была долгой и постепенной или длится до сих пор, в качестве периода «после» берём период, максимально близкий к сегодняшней дате;
• при резких просадках исключаем из обоих диапазонов по 2-3 дня до и после даты просадки (при сильных апах выдачу Google обычно штормит несколько дней).

При выборе периодов также нужно помнить, что в консоли хранятся данные за последние 16 месяцев, а Search Analytics for Sheets не отдаёт данные за два последних дня, включая текущий.

Сориентироваться по дате просадки по любой системе аналитики (GA, Я.Метрике) либо по кликам в Консоли (отчёт «Эффективность»).

Пример

У интернет-портала наблюдается снижение трафика с начала мая 2020 года. Пик посещаемости приходился на конец апреля (800-1000 посетителей/сутки), но примерно после Google Core Update 4 мая трафик начал волатильно просаживаться и в середине июня устаканился на показателях 300-450 посетителей в сутки.

Выбираем два одинаковых периода по 3 недели каждый:
До просадки: 23.03 – 12.04 (пик трафика, не связанный с сезонностью).
После просадки: 15.06 – 05.02 (максимально близкий к просадке «стабильный» период).

Дальше система аналитики нам не понадобится – переходим к работе с таблицами.

2. Выгружаем данные в таблицу

1. Открываем Google Sheets

Через «Дополнения» → Search Analytics for Sheets → Open Sidebar открываем панель с настройками парсинга консоли:

2. Настраиваем парсинг

Сначала выгружаем период «До просадки»:

• Verified Site – выбираем анализируемый сайт.
• Date Range – указываем «допросадочный» диапазон.
• Group By: Page, Query (нужно выбрать опции именно в таком порядке, чтобы получить группировку запросов по страницам).
• Rows Returned – зависит от объёма сайта (для маленьких может хватить и 1000 строк).

На отдельную страницу загрузится такой отчёт:

Теперь нужно выгрузить такие же данные для периода «после» на отдельную вкладку.

Рекомендую сразу дать вкладкам правильные названия. Например, в виде дат (с какого по какое число выгружены) или просто «До просадки» и «После просадки». В примере далее будем использовать второй вариант.

3. Сравниваем показатели

Дальше нужно перенести данные в одну таблицу и сравнить показатели по каждой странице до и после просадки – вроде ничего сложного.

1. Объединяем данные из «Page» и «Query» в дополнительном столбце

При выгрузке большого объёма данных из GSC есть нюанс – в разное время по одному и тому же запросу могли ранжироваться разные страницы. Вот пример из диапазона «До просадки»:

Разные страницы имеют переходы по одинаковому запросу с разными показателями. Чтобы это не создало проблемы при объединении данных за два периода в одну таблицу, мы предварительно создадим в каждой таблице дополнительный столбец и объединим в нём данные из столбцов Page и Query.

Итак:

Раз: Создаём дополнительный столбец (должен находиться слева – по нему в дальнейшем будем делать вертикальный поиск).

Два: С помощью функции CONCATENATE (СЦЕПИТЬ) объединим данные из колонок Page и Query через знак «+».

Должно получиться примерно так:

Делаем это для обеих таблиц.

2. Подтягиваем все данные в одну таблицу с помощью вертикального поиска (ВПР)

Можно переносить из вкладки «Период после» на вкладку «Период до» (как в примере ниже) или наоборот, главное – сразу правильно назвать колонки и ничего не напутать.

Раз: Переименуем все колонки в таблице «До просадки».

Добавляем суффикс «До» к каждому показателю:

Два: Добавим по 2 дополнительных столбца после каждого показателя и дадим им соответствующие названия.

Три: Подтягиваем данные по каждому показателю из вкладки «Период после» в колонки с названием «После» с помощью функции VLOOKUP (ВПР). Поиск делаем по дополнительному столбцу «Page+Query»:

Четыре: В колонках, где подтягивались данные, удаляем формулу.
Это можно сделать, например, с помощью ctrl+c → ctrl+shift+c в рамках каждого столбца.

Пять: Во всех колонках, кроме колонки с позициями, делаем автозамену «#N/A» на «0» и считаем разницу (= «период после» – «период до»).

Шесть: в колонке с позициями после делаем автозамену N/A на «100» – будем считать, что раз система не зафиксировала данные по средней позиции, то эта позиция ниже сотни.

Семь: Считаем обратную разницу средней позиции (= «период до» – «период после»).
Почему не наоборот, как для других данных? Чтобы отрицательные значения получить для просевшей позиции, иначе можно запутаться.

В итоге у нас все данные подтянуты в одну таблицу и подсчитана абсолютная разница показателей:

По такой таблице мало что понятно, потому что объём данных очень большой. Чтобы увидеть тренд, нужно создать сводную таблицу.

4. Создаём сводную таблицу

Выделяем всю таблицу, кроме первой колонки, где мы объединяли столбцы – эта колонка в сводной таблице нам не понадобится.

Создаём сводную таблицу на отдельном листе (Данные → Создать сводную таблицу).

Базовые настройки:

Строки:
Page
Query

Значения:
ClicksРазница (SUM)
ImpРазница (SUM)
CTRРазница (SUM)
PosРазница (AVERAGE)

Сортировка Page: По возрастанию SUM ClicksРазница
Сортировка Query: По возрастанию SUM ClicksРазница

Настройки таблицы:

В итоге мы получаем список всех страниц, отсортированных по максимальной потере кликов:

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

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

5. Делаем выводы

Теперь мы видим, какие страницы потеряли больше всего трафика, по каким запросам и как именно изменились показатели. В зависимости от того, что мы ищем, в таблицу можно подтягивать и другие данные (например, число кликов до и после просадки и позицию после просадки). На основании этих данных можно искать закономерности и строить гипотезы просадки.

Какие тренды можно обнаружить:

1. Просадка страниц определённого типа

Например, просели только страницы на украинском языке, которые находятся в папке /ua/. Или просели только карточки товаров, которые находятся в папке /product/.

2. Просадка запросов определённого типа

С помощью условного форматирования или фильтра в настройках таблицы можно выделить запросы, содержащие определённые слова. Так можно проверить, например, гипотезу по просадке всех фраз, содержащих «online» или «купить».

3. Изменение структуры выдачи

Бывает, что по многим запросам при значительной просадке кликов и CTR число показов практически не изменилось. Тогда нужно смотреть на сниппеты и новых конкурентов в ТОПе.

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

Еще по теме:

• Асессорская инструкция Google: критерии оценки качества страниц (Часть 1)
Основываясь на инструкции для асессоров Google, англоязычная версия которой недавно просочилась в сеть, мы проанализировали критерии, по которым поисковая система оценивает сайты. Наш авторский перевод...
• Как настроить Google Analytics: отвечаем на частые вопросы
Отвечаем на часто задаваемые вопросы по настройке Google Analytics: создание и настройка событий и целей, отслеживание 404 ошибки, рефспам и его влияние, разделение органического трафика, создание групп...
• Упал трафик с Google? Как выявить причину просадки
Сегодня мы расскажем о причинах, которые приводят к просадке трафика из Google: технических проблемах, ошибках внутренней и внешней оптимизации.  В первую очередь вам нужно убедиться...
• Кто твой конкурент – правильно определяем конкурентов на разных этапах продвижения
Зачем и по каким критериям оцениваем конкурентов Поиск прямых конкурентов Определение страниц-конкурентов Использование сайтов другой направленности как конкурентов Типовые вопросы, при которых грамотный оптимизатор отправит...
• Как Google может отнести сайт к низкокачественным. Ссылочный показатель качества
Подготовили адаптированный перевод статьи зарубежного специалиста Билла Славски, который рассказывает об одном из патентов Google, ориентированном на определение ресурсов как низкокачественных. Читайте, какие ссылочные показатели...