В 2017 году вышла моя статья «Как обработать данные по поисковым запросам из органики Google в Google BigQuery». В ней мы вручную выгружали CSV-файлы из Google Search Console, складывали их на хранение в Google BigQuery и строили отчетность. С помощью этих данных можно отслеживать эффективность SEO-продвижения и сохранять данные, которые исчезают со временем из консоли веб-мастера. В этой статье мы научимся выгружать автоматически с помощью Python-скрипта больше информации из консоли веб-мастера Google и построим большее количество автоматических отчетов.

Что изменилось с момента выхода прошлой статьи:

  • Данные по поисковым запросам в консоли веб-мастера Google и в Google Analytics теперь хранятся 16 месяцев, а не 90 дней, как раньше.
  • Появились данные по отдельным типам поиска: Веб, Изображение, Видео. Если выгружать вручную через CSV-файлы, то количество работы увеличивается в три раза.
  • Теперь консоль веб-мастера поддерживает Ресурс на уровне домена, включающий URL с любыми субдоменами (m, www и др.) и различными префиксами протокола (http, https, ftp).
  • Также добавлю, что вручную скачивать CSV-файлы из консоли и загружать в BigQuery стало сложнее, поскольку все чаще попадаются громоздкие поисковые запросы на несколько строк с разными разделителями (пользователи копируют куски текста и вставляют в поиск). Стандартное форматирование ломается.

Чтобы построить автоматическую отчетность, мы будем каждый месяц скачивать из консоли веб-мастера статистику за предыдущий месяц и загружать ее в облачную базу данных Google BigQuery с помощью Python-скрипта. Затем построим отчетность SQL-запросами.

Итак, подробнее по шагам:

1. Установим последнюю версию Python3 с официального сайта. Скачиваем дистрибутив, при установке выбираем ‘Customize installation’ и устанавливаем в папку ‘C:\Python37’. Остальные параметры можно не менять. Дальнейшие действия рассмотрим на примере операционной системы семейства Windows.

2. Запустим командную строку.

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

cd "C:\Python37"

python --version

4. Выполним команды для установки библиотек, необходимых для работы скрипта:

cd Scripts

pip install --upgrade google-api-python-client

pip install pandas

pip install pandas_gbq

pip install --upgrade oauth2client

cd ..

5. В Google BigQuery создадим dataset, например, ‘search_console_google’. О том, как начать работать с BigQuery, можно прочитать в одной из моих предыдущих статей.

6. Нужно создать сервисный аккаунт в разделе IAM и администрирование в Google Cloud Platform, затем создать для него JSON-ключ и сохранить себе на компьютер, например, в папку ‘C:\Dropbox\gsc\’.

7. Подключите Google Search Console API для приложения в Google API Console в текущем проекте Google Cloud. Затем создайте учетные данные.

Скачайте JSON-файл с учетными данными, переименуйте его в client_secrets.json.

8. Скачайте скрипт google_seo.py из проекта на GitHub себе на компьютер, например, в папку ‘C:\Dropbox\gsc\’.

Важно! Файлы google_seo.py и client_secrets.json должны находиться в одной папке.

Замените в коде переменные:

  • ‘gcloud_key’ — путь к JSON-ключу от сервисного аккаунта Google Cloud из пункта 6;
  • ‘gbq_project_id’ — идентификатор проекта Google BigQuery;
  • ‘gbq_dataset’ — название dataset'a в BigQuery, куда вы хотите сохранить данные;
  • ‘domains’ — список доменов-ресурсов, подтвержденных в консоли вебмастера;
  • ‘first_month’, ‘last_month’ — с какого по какой месяц выгружаем данные;
  • ‘dimensions’ — метрики, которые будем выгружать (подробнее об этом поговорим дальше).

9. Запустите скрипт, набрав в командной строке:

python "C:\Dropbox\gsc\google_seo.py"

При первом запуске откроется браузер: нужно будет авторизоваться в Google и выдать разрешение приложению на доступ к вашему аккаунту. Также при первом запуске появится предупреждение об отсутствующем файле webmasters.dat; в этом нет ничего страшного, он будет создан позднее.

Если все пройдет гладко, то в консоли после выполнения скрипта будет суммарная информация по выгрузке. Также лог сохранится в файле google_seo_log.txt в папке со скриптом.

  • search_type — тип поиска (Веб, Изображение, Видео);
  • all_values — сколько всего значений было выгружено (сколько строк);
  • values with clicks — сколько из них было с кликами (остальные — 0 кликов, то есть были только показы);
  • clicks sum — сумма кликов;
  • impressions sum — сумма показов;
  • dimension1 и dimension2 — выгружаемые метрики. Скрипт по умолчанию выгружает восемь метрик, которые я посчитал самыми важными; если каких-то не хватает — просто добавьте их в скрипт. Что это за метрики:

9.1. device: Устройства. Аналогичный отчет из консоли веб-мастера имеет вид:

9.2. country: Страны.

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

9.4. query: Поисковые запросы. Обычно самая востребованная метрика. К сожалению, через Google API (так же, как и через обычные CSV-выгрузки в интерфейсе консоли веб-мастера) выгружаются не все поисковые запросы (сумма кликов и показов меньше, чем в нашей выгрузке по метрике 9.1 device, и меньше, чем в суммарной информации в веб-интерфейсе консоли).

9.5. query - device. 9.6. query - country: Поисковые запросы с разбивкой по устройствам; по странам. В этих двух метриках сумма кликов совпадает с 9.5 query, но сумма показов может быть больше т.к. добавляются новые запросы с показами, но без кликов (особенности API Google).

9.7. query - page: Поисковые запросы с разбивкой по страницам сайта. Сумма кликов и сумма показов больше, чем, например, по метрике 9.4 query, так как за один результат поиска в Google может выдаваться несколько страниц одного сайта, и все они суммируются.

10. Также в результате выполнения скрипта для каждого домена за каждый месяц создается таблица в Google BigQuery с сырыми данными:

Чтобы посмотреть содержимое таблиц, откройте предварительный просмотр.

Содержание полей:

  • clicks — клики;
  • ctr — CTR;
  • impressions — показы;
  • position — позиция в поиске;
  • search_type — тип поиска;
  • domain — домен;
  • period — месяц;
  • dimension1 и dimension2 — названия метрик, а value1 и value2 — их значения.

Например, на скриншоте ниже dimension1 содержит ‘query’, а dimension2 — пустое. Значит, это поисковые запросы, которые хранятся в поле value1, а поля value2 — пустые.

11. Чтобы было легче работать с поисковыми запросами (то есть метрикой 9.4 query), создадим виртуальную таблицу (view) search_console_google.queries, содержащую SQL-скрипт queries.sql.

В этом скрипте вам нужно заменить ‘gbq_project_id’ — идентификатор проекта BigQuery — и подправить определение брендированных запросов. Затем запустите скрипт и сохраните view (представление). Эта виртуальная таблица будет содержать подробную информацию по каждому поисковому запросу за каждый отчетный месяц по каждому домену-ресурсу.

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

Колонки аналогичны таблице из пункта 10, за исключением:

query — поисковый запрос, уже отдельное поле;

query_type — тип поискового запроса, определяется в SQL-запросе. Он принимает три значения: ‘(other)’, ‘branded’, ‘not branded’.

‘(other)’ — это искусственно созданный поисковый запрос, клики и показы по которому равны сумме кликов и показов, которые не выгрузились из консоли. Рассчитывается это следующим образом: берется сумма кликов и показов по метрике device (эта сумма практически всегда совпадает с общей суммой консоли веб-мастера), и из нее вычитаются суммы кликов и показов по всем выгруженным поисковым запросам. Таким образом, суммы по ‘(other)’, ‘branded’, ‘not branded’ совпадают с общими цифрами в консоли веб-мастера.

Используя эти данные, можно построить любые отчеты или графики в средствах визуализации или BI-инструментах. Я подготовил пять отчетов в Google Sheets. Откройте документ по ссылке и скопируйте себе, тогда у вас появится доступ на редактирование и изменение графиков. Все цифры в документе демонстрационные.

Отчет 1: Queries totals

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

Или доля показов по всем доменам во всех типах поиска по поисковым фразам ‘other’ (то есть которые не выгружаются из консоли веб-мастера):

Исходными данными для листа ‘1 Queries totals’ является лист ‘1 source’, в который скопирован результат выполнения SQL-запроса queries_report_chart.sql. Чтобы не копировать вручную данные в ‘1 source’, можно воспользоваться аддоном OWOX BI BigQuery Reports, который будет обновлять лист каждый раз по запросу или по расписанию.

Отчет 2: Queries list

Таблица по всем поисковым фразам за каждый месяц: указаны количество кликов и позиция. Исключены поисковые фразы с суммарным количеством кликов за все время меньше 10. Пользуемся фильтрами, чтобы сфокусироваться на нужных показателях. SQL-запрос queries_report_list.sql

Отчет 3: Devices

Динамика показателей: CTR, клики, показы в разрезе устройств по доменам и типам поиска. Исходные данные — на листе ‘3 source’. SQL-запрос devices.sql.

Отчет 4: Countries

Здесь можно посмотреть статистику по странам. Исходные данные — на листе ‘4 source’. SQL-запрос countries.sql

Отчет 5: Pages

Таблица со статистикой кликов по страницам сайта. Список ограничен страницами с 5 и более кликами за все время. SQL-запрос pages.sql:

В этой статье приведены примеры отчетов по следующим выгруженным метрикам из Google Search Console: 9.1 device, 9.2 country, 9.3 page, 9.4 query. Данные по: 9.6 query - device, 9.7 query - country, 9.8 query - page не используются, иначе статья будет очень громоздкой. Но все данные хранятся в BigQuery, и вы сможете запрашивать оттуда данные, если потребуется. Используйте и модифицируйте приведенные SQL-запросы под свои потребности или стройте отчеты на основе сырых данных в средствах визуализации и BI-инструментах: Google Data Studio, Tableau, Power BI или других.

Таким образом, мы разобрались, как можно сохранить статистику переходов из органики Google, а также автоматизировать отчетность. Также мы научились писать простые скрипты на Python и работать с BigQuery / SQL. Если у вас есть вопросы или дополнения — пишите в комментариях.

eLama.ru, руководитель группы веб-аналитики