Обработка данных поисковых запросов из органики Google

Практически каждый сайт так или иначе оптимизируют для поисковых систем, а в рунете для двух — Яндекса и Google. Для оценки эффективности SEO-продвижения, необходимо отслеживать переходы на сайт из органики и поисковые запросы. С Яндексом все достаточно просто: в отчете «Поисковые системы» в Метрике доступны список поисковых фраз в Яндексе и динамика переходов по ним.

C Google все несколько сложней. В Google Analytics нет статистики по поисковым фразам. Можно только связать ресурс Google Analytics с Google Search Console, откуда и будут подгружаться данные в систему аналитики, но со следующими ограничениями:

1. Не будет привязки конкретной поисковой фразы к сеансу на сайте. Поисковые фразы доступны только в единственном отчете ‘Источники трафика’ -> ‘Search Console’ -> ‘Запросы’, и нет возможности использовать их в сегментах или любых других отчетах.
2. Отслеживаются не все поисковые фразы, многие помечаются как ‘(other)’. Например, на сайте eLama.ru таких поисковых фраз около 40%, а на ppc.world — до 80%.
3. В Google Analytics для импорта из Search Console можно настроить только один ресурс, а не набор ресурсов. Это имеет значение если у вас есть поддомены.
4. Сейчас нет возможности дать доступ другому пользователю к набору ресурсов в Search Console.
5. В нем хранятся данные за последние 90 дней:

Первые две проблемы невозможно решить без серьезных изменений на стороне Google. Но как решить последние три, мы рассмотрим в этой статье. Заодно построим два отчета. Первый — с динамикой переходов из поиска Google с разбивкой на брендовые и небрендовые запросы:

Показатели в таблице:

period — отчетный месяц;

b_clicks — количество кликов по брендовым запросам;

b_percent — доля кликов по брендовым запросам;

b_impressions — количество показов по брендовым запросам;

b_ctr — CTR брендовых запросов;

nb_clicks — количество кликов по небрендовым запросам;

nb_percent — доля кликов по небрендовым запросам;

nb_impressions — количество показов по небрендовым запросам;

nb_ctr — CTR небрендовых запросов;

o_clicks — количество кликов по неизвестным запросам (others);

o_part_off — какую долю от всех переходов из поиска Google составляют эти фразы (others);

o_impressions — количество показов по неизвестным запросам (others);

o_ctr — CTR неизвестных запросов (others).

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

Показатели:

search_term — поисковая фраза;

clicksAllTime — количество кликов за все время;

c_17_03 — количество кликов в марте 2017 г.

p_17_03 — позиция, которую занимала фраза в марте 2017 г.

Чтобы получить такие отчеты, мы будем каждый месяц скачивать из Google Search Console CSV-файл со статистикой за предыдущий месяц и загружать его в облачную базу данных Google BigQuery, где данные будут храниться и обрабатываться. Итак, подробнее по шагам:

1. Нужно зайти в Search Console и скачать статистику за определенный месяц. Ограничение — доступно максимум 999 поисковых запросов. Если вам нужно больше данных — они доступны в Google Search Console API.

В Google BigQuery создадим dataset, например ‘search_console_google’. Загрузим полученные CSV-файлы в созданный dataset:

У вас, скорее всего, сначала получится загрузить статистику только за 2-3 месяца. Для ppc.world выгружали статистику из Search Console раньше, поэтому у нас данных больше. Вот так выглядят загруженные таблицы в веб-интерфейсе BigQuery:

4. Теперь в нужно создать виртуальную таблицу (view) search_console_google.all, содержащую этот SQL-скрипт.

Отредактируйте его под свой проект, запустите в BigQuery и сохраните view. Эта таблица будет содержать подробную информацию по каждому поисковому запросу за каждый отчетный месяц по вашему сайту или нескольким поддоменам. Ее нужно будет дополнять каждый месяц.

Посмотрим на результат выполнения этого скрипта (нужно нажать Edit Query, Run Query):

Показатели отчета:

period — отчетный период, соотносится с каждой таблицей из Search Console, прописывается в SQL-запросе;

domain — домен, соотносится с каждой таблицей из Search Console, прописывается в SQL-запросе;

search_term — поисковая фраза;

type — тип ключевой фразы, определяется в SQL-запросе, принимает три значения: ‘(other)’, ‘branded’, ‘not branded’;

clicks — количество кликов; для фраз ‘(other)’ значение нужно взять из Google Analytics, потому что в Search Console эти значения не отображаются, и прописать в SQL-запросе (соответственно если у вас несколько поддоменов, то получиться указать значения только для одного из них) ;

impressions — количество показов, аналогично как с кликами;

ctr — CTR поисковой фразы, берется из загруженного CSV-файла без изменений; для фраз ‘(other)’ не указывается (NULL);

position — позиция поисковой фразы, берется из загруженного CSV-файла и преобразуется в тип FLOAT; для фраз ‘(other)’ не указывается (NULL).

5. Чтобы получить первый отчет о динамике переходов из поиска Google с разбивкой на брендовые и небрендовые запросы, создайте view search_console_google.months, содержащий следующий SQL-скрипт.

В нем ничего менять не нужно кроме project-id из BigQuery, он сразу должен заработать. В скрипте рассчитываются параметры на основе значений из search_console_google.all, который мы настроили в предыдущем пункте.

6. Чтобы получить второй отчет со статистикой по небрендовым запросам, создайте view search_console_google.kewords на основе следующего SQL-скрипта.

В этом скрипте необходимо заменить project-id из BigQuery, и потом нужно будет ежемесячно добавлять новые строки для обработки статистики по новому месяцу.

7. Отчеты созданы. Теперь можно расшарить своим коллегам dataset и входящие в него отчеты.

8. С такими отчетами можно работать не только в веб-интерфейсе Google BigQuery, но и просто сохранить в Google Sheets или скачать CSV. Можно поступить другим образом — создать Google Sheets с доступом коллегам, а в него данные подтягивать через OWOX BI BigQuery Reports. Или же подключить инструменты визуализации: Google Data Studio, Redash, Tableau и другие.

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

Автор: Антон Леонтьев, руководитель отдела веб-аналитики eLama

Если вам нужно создать сайт, продвинуть сайт в ТОП 10, или вести рекламные кампании, обращайтесь к нашим специалистам.