Передача данных Search Console в Google BigQuery

Итак, для настройки трансфера вам необходимо выполнить следующие шаги:

• создать проект в Google Cloud;
• активировать несколько служб API;
• привязать биллинговый аккаунт;
• разрешить доступ Search Console к данным в вашем проекте;
• выполнить настройки в интерфейсе Search Console.

Давайте рассмотрим каждый этап детальнее.

Сразу отмечу, что данные в BigQuery будут поступать только с момента настройки трансфера. Если вы хотите посмотреть статистические данные за период до того, как вы настроили экспорт, воспользуйтесь Search Console API или отчетами в интерфейсе.

Создание проекта в Google Cloud

Чтобы создать новый проект, используйте любую учетную запись Gmail и перейдите в Google Gloud по ссылке. Если вы работаете с облаком Google впервые, то вас попросят указать страну и согласиться с условиями использованиями Google Cloud Platform:

Начало работы с Google Cloud

В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации данной статьи вы не сможете привязать свою банковскую карту, выпущенную на территории РФ, чтобы использовать все возможности Google Cloud Platform, а также выбрать страну Россия из списка. Поэтому выберете из списка наиболее подходящую и нажмите Agree and continue.

На открывшейся странице Google Cloud в левом верхнем углу нажмите на Select a project:

Select a project

И создайте проект - NEW PROJECT:

Создание проекта

В поле Project name вы можете оставить автоматически добавленное название проекта, а можете указать свое собственное название, например osipenkovru:

Название проекта

В завершение нажмите кнопку Create (Создать). В вашем Google Cloud будет создан новый проект. Об этом будет свидетельствовать уведомление в правом верхнем углу (Notifications):

Уведомление о создании проекта

Активация BigQuery API и BigQuery Storage API

Когда вы создали новый проект, API BigQuery включается автоматически. Но я рекомендую перепроверить это. Для этого в Google Cloud выберите свой проект из выпадающего списка:

Выбор проекта в Google Cloud

Затем перейдите по ссылке. Проверьте, что вы вносите изменения в выбранный проект и нажмите Next:

Подтверждение изменений в проекте

Включите BigQuery API:

Включение BigQuery API

Проверьте, чтобы у вас так же был активен BigQuery Storage API. Для этого в поиске введите BigQuery Storage API и выберите соответствующий результат из выпадающего списка:

BigQuery Storage API

На открывшейся странице убедитесь, что данная служба активна:

BigQuery Storage API включена

Если нет, нажмите на кнопку Enable и включите BigQuery Storage API:

Активация BigQuery Storage API

На этом активация служб API для экспорта данных Search Console в BigQuery завершена.

Биллинг и привязка платежного аккаунта

Чтобы ваши данные из Search Console ежедневно передавались в BigQuery, вам необходимо создать платежный аккаунт и прикрепить его к своему проекту в Google Cloud. Для этого в левом меню (или в поиске сверху) найдите Billing и на открывшемся странице перейдите в управление аккаунтами:

Billing - Manage billing accounts

Если в вашей учетной записи еще нет платежного аккаунта, нажмите кнопку ADD BILLING ACCOUNT:

Создание платежного аккаунта

На открывшейся странице укажите страну:

Информация аккаунта (Страна)

Затем нажмите Agree & continue. На следующем шаге вас попросят указать ваши персональные данные, включая тип аккаунта (можете выбрать Individual), имя, адрес и реквизиты банковской карты. Если у вас еще нет его, создайте через Create new payments profile:

Создание платежного профиля

Заполните персональные данные и нажмите Create:

Заполнение персональных данных (Individual profile)

После создание платежного профиля добавьте платежные реквизиты через Add payment method:

Добавление платежной карты

Примечание: Google приостановил работу для пользователей из России. На момент публикации этого материала вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность пользоваться Google Cloud и оплачивать счета.

Данные банковской карты

Сохраните карту.

Новые пользователи могут воспользоваться Google Cloud Platform Free Program, в которую входит:

• 90-дневная бесплатная пробная версия и 300$ - новые пользователи Google Cloud и Google Maps Platform могут воспользоваться 90-дневным пробным периодом, который включает 300 долларов США в виде бесплатных кредитов Cloud Billing, для изучения и оценки продуктов и услуг Google Cloud и Google Maps Platform. Вы можете использовать эти кредиты для одного или нескольких продуктов;
• уровень бесплатного использования - все клиенты Google Cloud могут использовать некоторые продукты Google Cloud, такие как Compute Engine, Cloud Storage и BigQuery, бесплатно в рамках установленных месячных ограничений. Если вы не превысите порог в 300$, эти ресурсы не будут списаны с вашего счета после окончания пробного периода.

Чтобы воспользоваться этим предложением, нужно соблюсти несколько условий:

• вы никогда не были платным клиентом Google Cloud, Google Maps Platform или Firebase;
• вы ранее не использовали этот trial-период (пробную версию) на своем аккаунте Gmail.

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

Таким образом, ваша бесплатная пробная версия заканчивается, когда происходит одно из следующих событий:

• вы потратили 300$;
• с момента регистрации прошло 90 дней.

В течение всего периода бесплатного пробного использования ваши оставшиеся кредиты и дни отображаются на странице обзора платежной учетной записи в Google Cloud Console.

Примечание: о том, как контролировать свои расходы в Google Cloud, читайте в моем руководстве.

Если вы подходите под программу Google Cloud Platform Free Program, на последнем шаге вам нужно будет нажать кнопку Start free. Именно с этого момента начнется отсчет 90 дней, и вам будут доступны 300$.

Start free

В завершение вас перенаправит на главную страницу Google Cloud. Если у вас уже когда-то ранее был заведен платежный аккаунт, то на странице биллинга перейдите на вкладку My projects и добавьте ваш платежный аккаунт к нужному проекту:

Привязка платежного аккаунта к проекту

Если там указан неверный Billing account, измените его с помощью Change billing:

Изменение платежного аккаунта

После этого можно переходить к разрешению прав доступа.

Доступы BigQuery Job User и BigQuery Data Editor

Чтобы разрешить доступ Search Console к данным в вашем проекте Google Cloud, перейдите в раздел IAM & Admin - IAM:

IAM & Admin - IAM

На открывшейся странице нажмите на GRANT ACCESS:

Предоставление доступов

В поле New Principals добавьте search-console-data-export@system.gserviceaccount.com. В Assign roles добавьте две роли:

• BigQuery Job User (bigquery.jobUser в интерфейсе командной строки);
• BigQuery Data Editor (bigquery.dataEditor винтерфейсе командной строки).

BigQuery Job User и BigQuery Data Editor

Нажмите Save. В результате в таблице прав доступа вы должны видеть новую строку для Search Console:

Доступы для Search Console

На этом настройка в Google Cloud завершена.

Настройки в Search Console

Перейдите в интерфейс Search Console. Выбрав свой сайт, откройте Настройки - Массовый экспорт данных:

Настройки - Массовый экспорт данных

1. Скопируйте идентификатор проекта Project ID (его не следует путать с номером проекта Project number) в Google Cloud в поле Идентификатор проекта Google Cloud. Идентификатор проекта указан на странице настроек проекта.

Идентификатор проекта Google Cloud (Project ID)

2. Укажите название набора данных. По умолчанию в качестве названия устанавливается searchconsole. Чтобы выполнить экспорт из нескольких ресурсов Search Console для одного идентификатора проекта, вам потребуется задать для каждого из них отдельное название набора данных. Даже если указать собственное название набора данных, в его начале будет строка searchconsole.

3. Выберите из списка местоположение для вашего набора данных. Search Console создаст ваш набор данных в этом местоположении для первого экспорта. Помните, что вы не сможете изменить это местоположение после того, как экспорт будет запущен.

Пример настроек для моего проекта osipenkov.ru:

Настройки экспорта в Search Console (для osipenkov.ru)

Нажмите Продолжить. В открывшемся окне вас попросят перепроверить настройки экспорта. Если все указано верно, нажмите кнопку Настроить экспорт:

Проверка настроек

На этом настройка массового экспорта данных из Search Console в BigQuery завершена. Первая попытка экспорта будет предпринята в течение 48 часов и будет включать данные за день осуществления экспорта. При возникновении незначительной ошибки Search Console предпримет повторную попытку экспорта на следующий день согласно расписанию. О проблемах, которые могут быть обнаружены сразу (таких как ошибка доступа), вы будете уведомлены в короткий срок. Если таких проблем обнаружено не будет, то Search Console начнет экспорт в течение суток.

Завершение настройки массового экспорта

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

В Search Console можно просматривать статус последней попытки экспорта данных. Для этого откройте Настройки и просмотрите статус рядом с элементом Массовый экспорт данных:

Статус массового экспорта

Нажмите на него, чтобы перейти на страницу настроек массового экспорта данных и ознакомиться с подробными сведениями. Если при экспорте данных возникнет серьезная проблема, вы получите уведомление. Если нет - будет отображаться статус Массовый экспорт данных активен:

Таблицы Search Console в BigQuery

После того, как массовый экспорт данных успешно настроен, в вашем проекте BigQuery будет создано несколько таблиц в наборе данных searchconsole (если вы его не изменили в процессе настройки, см. выше):

Таблицы Search Console в BigQuery

• searchdata_site_impression (содержит данные об эффективности ресурсов, сгруппированные по ресурсам);
• searchdata_url_impression (содержит данные об эффективности ресурсов, сгруппированные по URL);
• ExportLog (содержит информацию о каждой успешной операции экспорта в одну из вышеуказанных таблиц).

Поскольку в BigQuery загружается две таблицы, то для каждого дня вы должны видеть две успешные записи - по одной для каждой из таблиц. Вот так выглядит таблица ExportLog с загруженными данными за единственный день:

ExportLog с успешными операциями экспорта за единственный день

Примечание: Search Console обычно экспортирует данные в эти таблицы по отдельности. Неудачные попытки экспорта не записываются.

Search Console осуществляет массовый экспорт данных один раз в день, но не всегда в одно и то же время. В случае временной ошибки, такой как проблема с подключением, Search Console сразу же попытается выполнить операцию еще раз без отправки каких-либо уведомлений.

Если при попытке записи возникнет более серьезная ошибка, например ошибка доступа, Search Console не будет пытаться экспортировать данные в таблицу повторно до наступления даты следующего запланированного экспорта. Search Console будет пытаться экспортировать данные за пропущенный день примерно на протяжении недели, а затем прекратит попытки сохранить данные за этот день. Например, если при попытке Search Console сохранить данные за День 1 возникнет ошибка аутентификации, которая будет исправлена только в День 9, то данные за День 1 и 2 не будут экспортированы, тогда как данные за остальные дни будут добавлены в таблицу при восстановлении доступа в День 9.

Если Search Console потребуется изменить ранее сохраненные данные из-за ошибки обработки, которая позже была исправлена, это будет сделано автоматически, то есть все данные за указанный день будут изменены за одну операцию. Исправление ранее сохраненных данных происходит нечасто и не сопровождается уведомлением по электронной почте, но эта операция отражается в журнале экспорта.

Данные об эффективности накапливаются в Search Console инкрементно, в результате чего в таблице образуются строки с повторяющимися ключами. Перед экспортом в таблицы данные не сжимаются. Поэтому следует всегда группировать данные в полях при помощи таких функций, как SUM, COUNT и др.), чтобы получить консолидированные значения в строках. Несколько примеров SQL-запросов мы с вами разберем далее.

Срок хранения таблиц

По умолчанию срок хранения таблиц searchdata_site_impression, searchdata_url_impression и ExportLog не ограничен.

Срок хранения таблиц - NEVER (без ограничений)

В связи с этим объем ваших данных будет постоянно увеличиваться. Данные в BigQuery будут накапливаться бесконечно, если вы не установите срок их хранения (месяц, полгода, год или любой другой срок).

Срок хранения данных в таблице - Expiration Time - Specify date

В то же время Google не советует устанавливать срок хранения для всей таблицы, так как эта настройка приведет к удалению всех данных.

Таблицы Search Console в BigQuery являются секционированными. Об этом свидетельствует надпись This is a partitioned table после выбора любой из них:

This is a partitioned table

Partitioned table (партиционирование) - это разделение таблицы на отдельные части (партиции) с целью улучшения производительности выполнения SQL запросов. При работе с такой таблицей фактически ничего не меняется, но сама таблица состоит из как бы отдельных частей, из которых в нужный момент вы получаете запрашиваемые данные. Синонимы: секционированная таблица, partitioned table, партиционирование.

Чтобы обновить время истечения срока действия раздела такой таблицы, используйте нижеприведенный SQL-запрос:

• ALTER SCHEMA - команда для изменения существующего датасета;
• mydataset - имя датасета, для которого выполняется изменение (измените его на свое);
• SET OPTIONS - часть команды используется для изменения параметров конфигурации;
• default_partition_expiration_days = 60 - параметр используется для установки значения по умолчанию для срока действия (в днях) партиций в таблице. Это значение определяет, сколько дней партиции будут храниться в системе, прежде чем они будут автоматически удалены.

Таким образом, после выполнения этого запроса, все новые разделы, созданные в этом датасете, будут автоматически удаляться через 60 дней, если не будет указано другое значение для времени жизни раздела таблицы. Его вы можете установить сами.

Как вы видите, в названиях всех таблиц присутствует префикс с идентификатором вашего проекта <your_project_ID>.searchconsole.

Префикс <your_project_ID>.searchconsole в названиях таблиц

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

Таблица searchdata_site_impression

Данные в этой таблице сгруппированы по ресурсам. Таблица содержит следующие поля:

Схема данных в таблице searchdata_site_impression

• data_date - день, в который были сгенерированы данные в указанной строке (по тихоокеанскому времени);
• site_url - URL ресурса. Для доменных ресурсов он будет выглядеть как sc-domain:название-ресурса. Для ресурсов с префиксом в URL он будет представлять собой полный URL согласно определению ресурса. Примеры: sc-domain:developers.google.com, https://developers.google.com/webmaster-tools/
• query - запрос пользователя в поиске Google. Если запрос анонимизированный (поле is_anonymized_query имеет значение true), это поле будет пустым;
• is_anonymized_query - обозначение редких запросов, известных как анонимизированные (см. выше). Поле query для такого запроса будет оставаться пустым для защиты конфиденциальности пользователей, отправивших этот поисковый запрос;
• country - страна, из которой поступил запрос, в формате ISO-3166-1 alpha-3;
• search_type - одно из следующих строковых значений:
  • web - вкладка по умолчанию ("Все") в Google Поиске;
  • image - вкладка "Картинки" в Google Поиске;
  • video - вкладка "Видео" в Google Поиске;
  • news - вкладка "Новости" в Google Поиске;
  • discover - раздел "Рекомендации";
  • googleNews - страница news.google.com и приложение "Google Новости" для Android и iOS;
• device - устройство, с которого был отправлен поисковый запрос;
• impressions - количество показов;
• clicks - количество кликов;
• sum_top_position - сумма самых высоких позиций сайта в результатах поиска для каждого показа в этой строке таблицы, где 0 означает верхнюю позицию в результатах поиска. Чтобы рассчитать среднюю позицию (минимальное значение которой равно 1), используйте формулу: SUM(sum_top_position) / SUM(impressions) + 1

Перейдя на вкладку PREVIEW, вы сможете увидеть поля и данные в них в предварительном просмотре:

Пример данных таблицы searchdata_site_impression

Таблица searchdata_url_impression

Данные в этой таблице сгруппированы по URL. Таблица содержит следующие поля:

Схема данных в таблице searchdata_url_impression

• data_date - день, в который были сгенерированы данные в указанной строке (по тихоокеанскому времени);
• site_url - URL ресурса. Для доменных ресурсов он будет выглядеть как sc-domain:название-ресурса. Для ресурсов с префиксом в URL он будет представлять собой полный URL согласно определению ресурса. Примеры: sc-domain:developers.google.com, https://developers.google.com/webmaster-tools/
• url - полный URL страницы, на которую переходит пользователь, когда нажимает на результат поиска или рекомендацию;
• query - запрос пользователя в поиске Google. Если запрос анонимизированный (поле is_anonymized_query имеет значение true), это поле будет пустым;
• is_anonymized_query - обозначение редких запросов, известных как анонимизированные (см. выше). Поле query для такого запроса будет оставаться пустым для защиты конфиденциальности пользователей, отправивших этот поисковый запрос;
• is_anonymized_discover - определяет, нужно ли анонимизировать данные. Если это так, некоторые другие поля (такие как URL и страна) будут пустыми для защиты конфиденциальности пользователей;
• country - страна, из которой поступил запрос, в формате ISO-3166-1 alpha-3;
• search_type - одно из следующих строковых значений:
  • web - вкладка по умолчанию ("Все") в Google Поиске;
  • image - вкладка "Картинки" в Google Поиске;
  • video - вкладка "Видео" в Google Поиске;
  • news - вкладка "Новости" в Google Поиске;
  • discover - раздел "Рекомендации";
  • googleNews - страница news.google.com и приложение "Google Новости" для Android и iOS;
• device - устройство, с которого был отправлен поисковый запрос;
• is_[search_appearance_type] - несколько логических полей, в частности s_amp_top_stories, is_job_listing и is_job_details, используются для обозначения вида в поиске. Поле имеет значение true, если указанная строка появляется в расширенных результатах определенного типа;
• impressions - количество показов;
• clicks - количество кликов;
• sum_top_position - наилучшая позиция URL в результатах поиска по этому запросу (минимальное значение равно 0 и соответствует верхней позиции в результатах поиска). Чтобы рассчитать среднюю позицию (минимальное значение которой равно 1), используйте формулу: SUM(sum_position)/SUM(impressions) + 1

Расшифровка всех полей из списка доступ в официальной документации Google. Это: is_amp_top_stories, is_amp_blue_link, is_job_listing, is_job_details, is_tpf_qa, is_tpf_faq, is_tpf_howto, is_weblite, is_action, is_events_listing, is_events_details, is_search_appearance_android_app, is_amp_story, is_amp_image_result, is_video, is_organic_shopping, is_review_snippet, is_special_announcement, is_recipe_feature, is_recipe_rich_snippet, is_subscribed_content, is_page_experience, is_practice_problems, is_math_solvers, is_translated_result, is_edu_q_and_a, is_product_snippets, is_merchant_listings, is_learning_videos.

Поля, поддержка которых была прекращена, хотя они по-прежнему существуют в таблицах экспорта - is_events_listing, is_events_details, is_page_experience, is_tpf_howto, is_organic_shopping, is_weblite.

Перейдя на вкладку PREVIEW, вы сможете увидеть поля и данные в них в предварительном просмотре:

Пример данных таблицы searchdata_url_impression

Таблица ExportLog

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

Схема данных в таблице ExportLog

• agenda - тип сохраненных данных. В настоящее время единственным значением является SEARCHDATA;
• namespace - таблица, в которую были сохранены данные. Как правило, Search Console экспортирует данные в каждую таблицу по отдельности, поэтому каждому дню соответствуют две строки об экспорте данных;
• data_date - дата клика или показа по тихоокеанскому времени. Совпадает с датой соответствующего раздела в таблицах;
• epoch_version - целое число, где значению 0 соответствует первое сохранение данных в эту таблицу в указанную дату. В редких случаях, например ввиду отклонений в данных, Search Console может изменять данные за прошлые периоды. Это значение будет увеличиваться на 1 при каждом изменении данных в этой таблице в указанную дату;
• publish_time - время завершения экспорта по тихоокеанскому времени.

Перейдя на вкладку PREVIEW, вы сможете увидеть поля и данные в них в предварительном просмотре:

Пример данных таблицы ExportLog

Примеры SQL-запросов и рекомендации

Всегда используйте функции агрегирования

Нет никакой гарантии, что содержащиеся в таблицах данные будут консолидированы по дате, URL, сайту или какому-либо сочетанию ключей. Поэтому следует всегда группировать данные в полях (при помощи таких функций, как SUM, COUNT и др.), чтобы получить консолидированные значения в строках.

Например, в таблице может быть несколько строк с запросом "пушистые котики":

Запросу "пушистые котики" может соответствовать несколько строк даже при указании конкретной даты и типа запроса. Поэтому для суммирования числа показов вам необходимо сгруппировать данные следующим образом:

НЕВЕРНЫЙ СПОСОБ

ВЕРНЫЙ СПОСОБ

Ограничивайте запросы по дате для сокращения расходов на обработку

Помните, что при выполнении запроса BigQuery взимается плата, а таблицы могут быть очень большими. Экспортируемые таблицы разбиты по датам, поэтому вы можете ограничить объем сканирования, указав только нужные даты, – для больших сайтов экономия может быть весьма существенной. Хорошим способом сэкономить на запросах является использование условия WHERE для ограничения диапазона дат в таблице.

Исключайте строки с пустым запросом

Анонимизированные запросы часто являются самым распространенным запросом на сайте. Такие запросы отображаются в виде пустых значений. Если вам нужно определить наиболее популярный запрос по определенному критерию, то вам следует исключить из SQL-запроса строки с пустыми значениями.

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

Результаты по сайту

В официальной справке Google приводятся рекомендации по работе с запросами и примеры SQL. Давайте проверим их работоспособность.

Вся поисковая статистика с разбивкой по дням (за последние две недели):

Вместо searchconsole.searchdata_site_impression вам необходимо добавить путь к таблице searchdata_site_impression для своего проекта.

Результат выполнения запроса в Google BigQuery выглядит так:

Результат в BigQuery

Поскольку мой экспорт данных был настроен вчера, в таблице представлены данные только за один день. У вас результат SQL-запроса может быть иным.

Следующий запрос выводит самые популярные поисковые запросы на мобильных устройствах в России по числу кликов (за последние две недели):

Вместо searchconsole.searchdata_site_impression вам необходимо добавить путь к таблице searchdata_site_impression для своего проекта.

Пример результата в Google BigQuery:

Результат в BigQuery

Результаты по URL

Самые популярные URL для веб-сайта по числу кликов (на конкретную дату):

Вместо searchconsole.searchdata_url_impression вам необходимо добавить путь к таблице searchconsole.searchdata_url_impression для своего проекта. Вы также можете изменить дату в DATE (2025, 01, 06), задав ее в формате (ГГГГ, МЕСЯЦ, ДЕНЬ).

Результат в BigQuery:

Результат в BigQuery

Следующий запрос - статистика по URL всего вашего трафика (за последние две недели):

SELECT url, search_type, sum(impressions) as impressions, sum(clicks) as clicks FROM searchconsole.searchdata_url_impression WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() GROUP BY 1,2 ORDER BY 3 desc LIMIT 1000

1 2 3 4 5 6 7 8 9 10

SELECT   url,   search_type,   sum(impressions) as impressions,   sum(clicks) as clicks FROM searchconsole.searchdata_url_impression WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() GROUP BY 1,2 ORDER BY 3 desc LIMIT 1000

Вместо searchconsole.searchdata_url_impression вам необходимо добавить путь к таблице searchconsole.searchdata_url_impression для своего проекта.

Результат в BigQuery:

Результат в BigQuery

Показы по запросам, содержащим название вашего бренда (за последние две недели):

SELECT regexp_contains(query, 'your_brand') AS contains_brand, sum(impressions) AS impressions, sum(clicks) AS clicks FROM searchconsole.searchdata_url_impression WHERE search_type = 'WEB' AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() GROUP BY 1 ORDER BY 2 desc LIMIT 1000

1 2 3 4 5 6 7 8 9 10

SELECT   regexp_contains(query, 'your_brand') AS contains_brand,   sum(impressions) AS impressions,   sum(clicks) AS clicks FROM searchconsole.searchdata_url_impression WHERE search_type = 'WEB'   AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() GROUP BY 1 ORDER BY 2 desc LIMIT 1000

Вместо searchconsole.searchdata_url_impression вам необходимо добавить путь к таблице searchconsole.searchdata_url_impression для своего проекта. Замените 'your_brand' на значение своего брендового запроса.

Результат в BigQuery может выглядеть примерно так:

Результат в BigQuery

Столбец contains_brand содержит информацию о входящем поисковом запросе, который вы указали. В моем примере - это осипенков. Значение true - статистика по запросам, содержащим название бренда, false - все остальные данные. А строка null - это анонимизированные запросы. Если запрос анонимизированный (поле is_anonymized_query имеет значение true), это поле будет пустым. Так оно и произошло.

Мы можем выполнить схожий SQL-запрос, добавив поле is_anonymized_query:

SELECT regexp_contains(query, 'your_brand') AS contains_brand, SUM(impressions) AS impressions, SUM(clicks) AS clicks, is_anonymized_query FROM searchconsole.searchdata_url_impression WHERE search_type = 'WEB' AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() GROUP BY contains_brand, is_anonymized_query ORDER BY impressions DESC LIMIT 1000

1 2 3 4 5 6 7 8 9 10 11

SELECT   regexp_contains(query, 'your_brand') AS contains_brand,   SUM(impressions) AS impressions,   SUM(clicks) AS clicks,   is_anonymized_query FROM searchconsole.searchdata_url_impression WHERE search_type = 'WEB'   AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() GROUP BY contains_brand, is_anonymized_query ORDER BY impressions DESC LIMIT 1000

В результатах запроса вы должны увидеть новый столбец is_anonymized_query, где как раз напротив строки null будет отображаться значение true:

Анонимизированные запросы (пустые, null) с is_anonymized_query = true

Сравнение трафика по URL и запросам с течением времени:

SELECT query, url, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN impressions ELSE 0 END) AS impressions_14d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN clicks ELSE 0 END) AS clicks_14d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN impressions ELSE 0 END) AS impressions_14_28d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN clicks ELSE 0 END) AS clicks_14_28d FROM searchconsole.searchdata_url_impression WHERE query IS NOT NULL AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE() GROUP BY 1, 2 ORDER BY 2, 3 DESC;

1 2 3 4 5 6 7 8 9 10 11 12

SELECT query, url, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN impressions ELSE 0 END) AS impressions_14d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN clicks ELSE 0 END) AS clicks_14d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN impressions ELSE 0 END) AS impressions_14_28d, SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN clicks ELSE 0 END) AS clicks_14_28d FROM searchconsole.searchdata_url_impression WHERE query IS NOT NULL AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE() GROUP BY 1, 2 ORDER BY 2, 3 DESC;

Вместо searchconsole.searchdata_url_impression вам необходимо добавить путь к таблице searchconsole.searchdata_url_impression для своего проекта.

Результат в BigQuery:

Результат в BigQuery

Итоговый результат запроса будет содержать сумму показов и кликов для каждого уникального сочетания запроса и URL за последние 14 дней и за последние 28 дней.

Визуализация данных

Полученные результаты от выполнения SQL-запросов вы можете быстро исследовать в самом BigQuery, воспользовавшись вкладкой CHART:

Визуализация в BigQuery

А можете отправить в Looker Studio и построить нужные срезы там. Для этого над результатом запроса в правом верхнем углу нажмите на OPEN IN - Looker Studio:

OPEN IN - Looker Studio

Вас перенаправит в Looker Studio, где автоматически на холсте будет создано несколько визуализаций:

Данные SQL-запроса в Looker Studio

Вы можете их отредактировать или создать свои собственные диаграммы. Но и это еще не все! Теперь вы можете не только выполнять SQL-запросы к данным Search Console, но и настроить подключение в Looker Studio, выбрав в качестве источника коннектор BigQuery. Добавив к нему таблицы searchdata_site_impression и searchdata_url_impression, вы получите безграничные возможности для анализа:

Подключение BigQuery в Looker Studio для таблиц Search Console

Но помните, что объем данных в этих таблицах постоянно увеличивается. Статистика для вашего проекта будут накапливаться бесконечно, если вы не установите срок хранения таблиц. И с каждым новым запросом в BigQuery вы будете все больше и больше расходовать ресурсов. Что, в свою очередь, может привести к существенным финансовым затратам.

К счастью, в Google Cloud есть Google Cloud Platform Free Program и 300$ на 90 дней (можно потестировать). И первый 1 ТиБ данных запроса, обработанных в месяц, предоставляется бесплатно. Далее - 6,25$ в месяц за каждый ТиБ. Google Cloud может изменять стоимость хранения данных и выполнения запросов.