Как собрать данные из Google Search Console и построить отчетность с помощью Python и Google BigQuery
Привет! Меня зовут Антон Леонтьев, я руководитель группы веб-аналитики в компании eLama.
В 2017 году вышла моя статья «Как обработать данные по поисковым запросам из органики Google в Google BigQuery». В ней мы вручную выгружали CSV-файлы из Google Search Console, складывали их на хранение в Google BigQuery и строили отчетность. В этой статье мы научимся выгружать автоматически с помощью Python-скрипта больше информации из консоли веб-мастера Google и построим большее количество автоматических отчетов.
Что изменилось с момента выхода прошлой статьи:
Во-первых, данные по поисковым запросам в консоли веб-мастера Google и в Google ***ytics теперь хранятся 16 месяцев, а не 90 дней, как раньше.
Во-вторых, появились данные по отдельным типам поиска: Веб, Изображение, Видео. Если выгружать вручную через CSV-файлы, то количество работы увеличивается в три раза.
В-третьих, теперь консоль веб-мастера поддерживает Ресурс на уровне домена, включающий URL с любыми субдоменами (m, www и др.) и различными префиксами протокола (http, https, ftp).
Также добавлю, что вручную скачивать CSV-файлы из консоли и загружать в BigQuery стало сложнее, поскольку все чаще попадаются громоздкие поисковые запросы на несколько строк с разными разделителями (пользователи копируют куски текста и вставляют в поиск). Стандартное форматирование ломается.
Чтобы построить автоматическую отчетность, мы будем каждый месяц скачивать из консоли веб-мастера статистику за предыдущий месяц и загружать ее в облачную базу данных Google BigQuery с помощью Python-скрипта. Затем построим отчетность SQL-запросами.
Пошаговая инструкция
1. Установим последнюю версию Python3 с официального сайта. Скачиваем дистрибутив, при установке выбираем ‘Customize installation’ и устанавливаем в папку ‘C:\Python37’. Остальные параметры можно не менять. Дальнейшие действия рассмотрим на примере операционной системы семейства Windows.
2. Запустим комaндную строку.
3. Перейдем в папку, куда установился Python, и проверим его работоспособность. Для этого запустим по очереди комaнды:
cd \"C:\Python37\"python --version
4. Выполним комaнды для установки библиотек, необходимых для работы скрипта:
cd Scriptspip install --upgrade google-api-python-clientpip install pandaspip install pandas_gbqpip install --upgrade oauth2clientcd ..
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 себе на компьютер, например, в папку ‘C:\Dropbox\gsc\’.
Здесь скрипт google_seo.py. Важно! Файлы 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. Запустите скрипт, набрав в комaндной строке:
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 (эта сумма пpaктически всегда совпадает с общей суммой консоли веб-мастера), и из нее вычитаются суммы кликов и показов по всем выгруженным поисковым запросам. Таким образом, суммы по ‘(other)’, ‘branded’, ‘not branded’ совпадают с общими цифрами в консоли веб-мастера.12. Используя эти данные, можно построить любые отчеты или графики в средствах визуализации или BI-инструментах. Я подготовил пять отчетов в Google Sheets. Откройте документ по ссылке и скопируйте себе, тогда у вас появится доступ на редактирование и изменение графиков. Все цифры в документе демонстрационные.
13. Отчет 1: Queries totals
На графике выводится помecячная динамика указанного показателя по выбранным доменам, типу поиска и типу запроса. Например, динамика кликов по бразильскому домену в типе поиска ‘web’ по всем типам запросов:
Или доля показов по всем доменам во всех типах поиска по поисковым фразам ‘other’ (то есть которые не выгружаются из консоли веб-мастера):
Исходными данными для листа ‘1 Queries totals’ является лист ‘1 source’, в который скопирован результат выполнения SQL-запроса. Чтобы не копировать вручную данные в ‘1 source’, можно воспользоваться аддоном OWOX BI BigQuery Reports, который будет обновлять лист каждый раз по запросу или по расписанию.
Здесь скрипт queries_report_chart.sql.14. Отчет 2: Queries list
Таблица по всем поисковым фразам за каждый месяц: указаны количество кликов и позиция. Исключены поисковые фразы с суммарным количеством кликов за все время меньше десяти. Пользуемся фильтрами, чтобы сфокусироваться на нужных показателях.
Здесь скрипт queries_report_list.sql.15. Отчет 3: Devices
Динамика показателей: CTR, клики, показы в разрезе устройств по доменам и типам поиска. Исходные данные — на листе ‘3 source’.
Здесь запрос devices.sql.16. Отчет 4: Countries
Здесь можно посмотреть статистику по странам. Исходные данные — на листе ‘4 source’.
Здесь запрос countries.sql.17. Отчет 5: Pages
Таблица со статистикой кликов по страницам сайта. Список ограничен страницами с 5 и более кликами за все время.
Здесь скрипт 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, а также автоматизировать отчетность. Если у вас есть вопросы или дополнения — пишите в комментариях.
Комментарии:
Рассказываем про маркетинговые техники продающих email рассылок...
26 04 2024 3:52:33
Рекламные заголовки в гугл адвордс так и пестрят предложениями контекстной рекламы бесплатно — вы когда-нибудь задумывались о том, откуда берутся эти «подарки»?...
25 04 2024 20:45:11
Как не облажаться при выборе подрядчика для SEO-аудита....
24 04 2024 0:14:45
Что такое посол бренда и чем он отличается от адвоката бренда?...
23 04 2024 1:57:44
Сотрудник не справляется? Значит с ним пора прощаться. Эмоции в сторону, действуем по алгоритму....
22 04 2024 0:30:44
Грамотная аналитика в мире мобильных приложений, как и в целом в маркетинге — единственный путь к окупаемости продукта и выходу в высшую лигу брендов...
21 04 2024 10:52:41
Кейс, в котором мы описываем работу с одним из наших старейших клиентов (с 2014 года) и как нам пришлось изменить устоявшийся формат работы и рекламного бюджета, чтобы соответствовать реалиям 2020 года....
20 04 2024 14:44:40
Мы достигли поставленных на старте продвижения целей: вернули потерянный трафик, получили дальнейший рост видимости сайта и увеличили доход....
19 04 2024 8:19:59
Как тратить на ставки меньше денег и почему вырастает цена за клик, а также о том, как ее сделать ниже. Плюс список факторов, которые влияют на ставку для первой страницы для ключевого слова...
18 04 2024 16:47:59
Как мы с помощью Call Tracking узнали настоящую рентабельность контекстной рекламы для магазина мебели...
17 04 2024 20:46:48
Украинцы хотят заниматься коммерцией. И иногда делают это очень нестандартно. Собрали для вас подборку бизнесменов с особенным мышлением....
16 04 2024 2:45:36
Рекомендации и мнения экспертов по одной из самых противоречивых тем украинского интернет-прострaнcтва....
15 04 2024 9:58:48
Женский и мужской таргет отличаются друг от друга. Кейс магазина обуви, одежды и аксессуаров....
14 04 2024 14:48:45
Как создать и внедрить баннеры об использовании куки-файлов с помощью Google Tag Manager и OneTrust...
13 04 2024 12:18:26
3D принтер — заработок миллионеров будущего! Тех, кто сейчас смог купить и начал заниматься ими или использует их в бизнесе, ждет объемный успех через каких-нибудь 3-5 лет....
12 04 2024 4:40:31
Уже сейчас в Google Play и App Store миллионы приложений. Как сделать так, чтобы приложение получило достаточное количество установок и приносило доход? Читайте об этом далее и изучайте наглядную инфографику!...
11 04 2024 12:58:57
Топ doodle games от Google — от менее достойных к самым крутым....
10 04 2024 22:38:46
И зачем топ-менеджерам разбираться в настройках кампаний, повышать свою техническую грамотность? Показываем на примерах и раскладываем по полочкам....
09 04 2024 8:29:32
Темная сторона лайков или правда о продвижении постов в Facebook...
08 04 2024 11:48:36
Фишинговая ссылка означает, что пользователей пытаются ввести в заблуждение в мошеннических целях, имитируя ссылку на авторитетный сайт....
07 04 2024 19:40:50
В арсенале Google Рекламы есть очень ценный инструмент — отслеживание конверсий....
06 04 2024 17:17:14
All about internet mail. Примеры того, что стоит внедрить в рассылках почты онлайн....
05 04 2024 0:14:50
Если вы автор телеграм-канала, то стоит задуматься об альтернативных контент-платформах. Мнение...
04 04 2024 15:22:24
Баннерная реклама в фейсбуке. Обзор семи новых рекламных инструментов...
03 04 2024 23:34:35
Лайфхаки, как упростить работу с соцсетями от специалиста агентства Netpeak и ежедневной работы SMM-специалиста. Телеграм-боты, которые облегчат рутину и сэкономят время. Узнать больше!...
02 04 2024 13:59:14
Новые триггеры, разнообразие рассылок и обновленная форма подписки дали нужный результат....
01 04 2024 4:58:58
Ссылки называются вечными, потому что их внедряют один раз и навсегда, но это не значит, что такие ссылки однажды не удалят (что поведет к потере трафика). Узнать больше!...
31 03 2024 6:41:36
Как специалисту оптимизировать рабочее время, качественно развивать проекты и меньше нервничать...
30 03 2024 7:42:47
SEO для цветочных магазинов с оплатой за трафик и полученные результаты...
29 03 2024 19:35:44
Всем, кто собирается перейти на Single Page Application, но переживает, что они поссорятся с поисковыми системами. Правильно переживаете!...
28 03 2024 10:30:53
Процесс привлечения сторонних ресурсов отнимает основную часть времени у большинства основателей стартапов. Читайте руководство, которое поможет сделать этот процесс во время серии А более эффективным....
27 03 2024 10:14:40
Как упростить знакомство клиента с компанией, продуктом или услугами....
26 03 2024 7:13:36
Расчет вероятности конверсии, быстрое и эффективное создание дашбордов, строение моделей KPI. Примеры аналитики кампаний с помощью Excel и Power BI от спикера 8P 2016 Максима Уварова....
25 03 2024 22:57:46
Наибольший эффект по продвижению сайта при минимальных затратах — именно таков принцип SEO growth hacking. Как найти кратные точки роста, читайте статье....
24 03 2024 23:33:54
Какой контент публиковать в Instagram, зачем ставить хештеги, какие активности интересны пользователям инсты, как ведут свой Инстаграм топовые бренды. Узнать больше!...
23 03 2024 21:53:35
Как использовать сервис Serpstat для оптимизации кампаний по контекстной рекламе...
22 03 2024 3:54:11
Организаторам мероприятий, коллегам из PR-сферы и невестам о том, как делегировать важные мероприятия внешнему подрядчику...
21 03 2024 16:56:22
Кейс продвижения Pizza House — ROMI 709,9%. От старта с дублями страниц и постановки целей и задач продвижения, проведения технического аудита, аналитики, анализа поисковых фраз, выбора контента до экспериментов с метатегами, увеличения среднего чека и планов на будущее...
20 03 2024 22:10:53
Привыкли к недосыпу и жизни «на кофе»? Вы, конечно же, понимаете, что это не дело. Но осознаете ли насколько? Если нет, почитайте....
19 03 2024 17:32:45
Перевод колонки директора по маркетингу Ahrefs: об органическом поисковом трафике, высокопотенциальных темах для бизнеса, уникальности и качестве контента, его продвижении. С реальными рекомендациями и примерами. Узнайте больше!...
18 03 2024 23:41:45
Как мы создаем BI-решение для департамента поискового продвижения — первые шаги...
17 03 2024 20:11:40
Как пользоваться одной из самых важных функций Google Таблиц — подробное руководство по Query...
16 03 2024 0:19:33
Внедрение скрама, чтобы решить проблемы в управлении бизнесом, год работы на нем и отказ от методологии. Читайте об опыте Boosta, которая объединяет 9 SEO-комaнд на удаленке....
15 03 2024 17:55:57
Что делать, если вы специально или нет попали под фильтр: руководство от выявления до спасения...
14 03 2024 18:36:59
Стоит ли подключать автоматические стратегии управления ставками? Мы проверили, как работает «Целевая цена за конверсию» в разных тематиках....
13 03 2024 10:19:42
Раскрутка музыки онлайн: способы, методы, нюансы и примеры...
12 03 2024 20:21:56
Чтобы увеличить продажи и улучшить их эффективность в новых условиях необходимо применять «социальные доказательства»....
11 03 2024 0:34:16
Как за полгода обновить корпоративный блог, зачем это может быть нужно. О том, как начинали с сегментации, внедряли SEO-рекомендации, оптимизировали ЧПУ, трaнcлитерацию, пагинацию и структуру блога вплоть до конкретной работы со статьями, рейтингами и их страницами...
10 03 2024 7:19:26
Как нарисовать круговую или столбчатую диаграмму. Избавляемся от популярных ошибок при оформлении...
09 03 2024 1:17:11
SEO-продвижение онлайн-сервиса доставки еды с новым сайтом. Цели продвижения, составление семантического ядра, формирование URL, поддомены, карточки товаров, тексты, внешняя оптимизация, дополнительные работы, а также полученные результаты...
08 03 2024 21:47:54
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::