Как проанализировать просадку трафика в 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 число показов практически не изменилось. Тогда нужно смотреть на сниппеты и новых конкурентов в ТОПе.

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

Еще по теме:

• Eye-tracking в 2014 году: Как пользователи взаимодействуют с современной выдачей Google
Какие изменения ввел Google за последние годы, с какой целью и как они повлияли на взаимодействие пользователя с результатами выдачи. Ребекка Мэйнс делится результатами эксперимента...
• Обзор SEO-новостей за 2019 год: как менялась выдача Яндекса и Google
Мы постоянно следим за новостями SEO-индустрии и каждую пятницу публикуем несколько самых важных в нашем facebook и twitter. В этой статье мы собрали новости с...
• Основы веб-аналитики. Как настроить цели в Google Analytics
Мы продолжаем серию статей «Работа с Google Analytics». Сегодня расскажем вам, как правильно настроить цели в данной системе, чтобы в дальнейшем детально отслеживать, насколько сайт...
• Анализ логов сайта с помощью Power BI: пошаговая инструкция
Анализ логов сайта – важная часть технического аудита, особенно если у сайта есть проблемы с индексацией. В этой статье мы разберём, что такое логи, какую...
• История алгоритмов Яндекса
Хроника изменений Яндекса - мы отследили, как менялись алгоритмы и фильтры поисковой системы за последние 12 лет. По мере появления нововведений материал дополняется. 7 апреля 2017 –...