Объединение данных Google Search Console и Google Analytics 4 в BigQuery
Практическое руководство по объединению данных Google Search Console и Google Analytics 4 в BigQuery, включая примеры SQL-запросов.
Данный материал основан на интереснейшей статье seotistics.com (автор - Marco Giordano), но дополнен моими комментариями, рисунками и уточнениями.
BigQuery - мощный инструмент, который позволяет экспортировать данные из Google Search Console (GSC) и Google Analytics 4 (GA4). Многие не осознают, что результаты, которые вы получите используя облачное хранилище данных Google, значительно отличаются от данных, предоставляемых API и из интерфейса этих систем.
Из этого материала вы узнаете, как структурировать эти таблицы и объединить два набора данных (Google Analytics 4 и Search Console) для SEO-целей, конкретных задач и результатов, которых стремится достичь ваш сайт для улучшения видимости в поисковых системах.
Экспорт данных
И Google Search Console, и Google Analytics 4 имеют возможность экспорта данных в Google BigQuery. Я не буду подробно останавливаться на этих шагах, потому что они досконально разобраны в других материалах моего блога. Обязательно прочитайте их перед тем, как приступать к дальнейшему изучению этого материала:
Таблицы в BigQuery
Примерно через 24 часа после интеграции в Google BigQuery с Google Analytics 4 будут переданы первые данные. В BigQuery это выглядит так:
Данные Google Analytics 4 в BigQuery
Для каждого счетчика Google Analytics 4 в BigQuery добавляется один набор данных (dataset) с названием analytics_property_id, где property_id - идентификатор ресурса GA4, который отображается в настройках ресурса:
Название набора данных с идентификатором ресурса
В зависимости от того, какую частоту передачи данных вы настроили (ежедневную, потоковую передачу или обе), в BigQuery будут созданы таблицы соответствующего назначения. Ежедневная таблица будет иметь название events_ГГГГММДД (будет создана вне зависимости от того, включен ли ежедневный или потоковый экспорт), а таблица потоковой передачи данных имеет название events_intraday_ГГГГММДД. Она заполняется непрерывно по мере регистрации событий. В конце каждого дня эта таблица удаляется (после заполнения events_ГГГГММДД).
Примеры таблиц двух видов
Напротив таблицы events вы можете видеть число в скобках:
Таблицы events_
- events_(1) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущий день;
- events_(2) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущие два дня;
- events_(51) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущие 51 день.
Конкретный день с данными можно выбрать из выпадающего списка сверху:
Выбор конкретной даты из выпадающего списка
Подробнее о схеме данных Google Analytics 4 в BigQuery читайте в этой статье.
Если вы активируете экспорт пользовательских данных (user data):
Экспорт пользовательских данных в BigQuery
То дополнительно в BigQuery получите еще две таблицы - pseudonymous_users_ и users_.
Таблицы pseudonymous_users_ и users_
- pseudonymous_users_ - содержит по строке для каждого идентификатора-псевдонима (он же Client ID). Данные пользователя обновляются, когда изменяется одно из полей;
- users_ - содержит по строке для каждого идентификатора пользователя (он же User ID).
Примечание: если пользователи могут войти на ваш сайт (есть возможность авторизации через личный кабинет), то вы можете использовать User ID (идентификатор пользователя, users). Если такой возможности нет, как правило, используется псевдо-идентификатор (Client ID, pseudonymous_users).
При экспорте данных Search Console в BigQuery создаются другие таблицы в наборе данных searchconsole (если вы его не изменили в процессе настройки):
Таблицы Search Console в BigQuery
- searchdata_site_impression (содержит данные об эффективности ресурсов, сгруппированные по ресурсам);
- searchdata_url_impression (содержит данные об эффективности ресурсов, сгруппированные по URL);
- ExportLog (содержит информацию о каждой успешной операции экспорта в одну из вышеуказанных таблиц).
Search Console:
- осуществляет массовый экспорт данных один раз в день, но не всегда в одно и то же время;
- обычно экспортирует данные в эти таблицы по отдельности. Неудачные попытки экспорта не записываются.
Вложенные данные (Nested Data)
Реальная проблема с таблицами GA4 заключается в наличии вложенных данных. Как правило, данные должны иметь одно значение на ячейку, а не много. Вложенные данные - наоборот, в одной ячейке имеют много данных.
Пример ниже прояснит ситуацию:
Событие first_visit и его параметры
То, что вы видите на скриншоте, на самом деле является одной строкой (1 событие = 1 строка) с ее параметрами, представленными столбцами. Некоторые из этих столбцов являются записями, поэтому в них хранится несколько значений. Запись (RECORD) - это тип данных, доступный в BigQuery.
Поле event_params.value имеет тип данных RECORD (запись)
Событие first_visit имеет параметр события ga_session_number (номер сеанса), равный 1, source (источник трафика) - yandex, medium (канал трафика) - cpc, page_title - … и т.д. Это вся информация об одном событии.
Обратите особое внимание на столбец event_params.key, так как в нем хранится то, что нам понадобится для анализа. Именно с помощью записи event_params можно сохранять параметры события на уровне кампании, контекстные параметры события, а также любые параметры события, определенные пользователем. Запись event_params повторяется для каждого ключа, связанного с событием. В каждой реализации набор параметров, хранящийся в записи event_params, уникален.
Очистка данных (Cleaning Data)
Перед объединением данных Google Search Console и Google Analytics 4 обязательно необходимо очистить данные. Другими словами, преобразовать их.
Этот процесс часто включает в себя:
- удаление бесполезных столбцов (ненужных полей);
- фильтрация страниц, которые вам не нужны;
- фильтрация/группировка по правильному параметру(ам).
Например, массовый экспорт данных Search Console в BigQuery предоставляет гораздо больше столбцов, чем нам нужно, и многие из этих столбцов являются логическими, то есть принимают всего два значения - true и false.
Множество различных столбцов с логическими данными (true/false) в таблице Search Console
Да, они могут вам понадобиться в зависимости от веб-сайта, с которым вы работаете. Например, кулинарному порталу может пригодиться информация о видеороликах и фрагментах рецептов. Но хоть они и являются полезными, от большинства из них (если не от всех) можно отказаться. По крайней мере в нашей задаче по объединению данных ими можно пренебречь.
Расшифровка всех полей из списка доступ в официальной документации 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.
Не забудьте сохранить копию ваших необработанных данных после очистки в облачном хранилище Google.
Объединение таблиц (Joining Tables)
Если вы проводите SEO-анализ, вам необходимо объединить таблицы Search Console и Google Analytics 4 и получить простой вывод. Столбец для присоединения всегда представляет собой URL-адрес, который есть у вас и в событиях GA4, и в URL-адресе GSC.
Существуют следующие типы соединений:
- INNER JOIN или просто JOIN - внутреннее соединение. В результате остаются только те строки, для которых нашлось соответствие;
- LEFT JOIN - левое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по левую сторону ключевого слова LEFT JOIN, не нашлось ни одной строки в таблице, находящейся по правую сторону LEFT JOIN, то строка все равно добавляется в результат, а значения столбцов правой таблицы равны null;
- RIGHT JOIN - правое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по правую сторону ключевого слова RIGHT JOIN, не нашлось ни одной строки в таблице, находящейся по левую сторону RIGHT JOIN, то строка все равно добавляется в результат, а значения столбцов левой таблицы равны null;
- FULL JOIN - полное внешнее соединение. Если для какой-либо из таблиц не нашлось строки в другой таблице, то строка все равно попадает в результат, а значения столбцов другой таблицы равны null;
- CROSS JOIN - перекрестное (или декартово) произведение. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц. Аналогичного результата можно достичь просто перечислив таблицы в FROM через запятую.
При объединении таблиц Search Console и Google Analytics 4 вы можете выполнить левое или внутреннее соединение. Самое простое - использовать левое соединение (LEFT JOIN) и Google Search Console в качестве левой таблицы.
Левое внешнее соединение, Left Join (рисунок seotistics.com)
Это означает, что мы заинтересованы в получении недостающих данных GA4 (пользователей, сеансов и т.д.) для всех URL-адресов, которые мы можем найти в таблице Google Search Console. Таким образом, если в Google Analytics 4 есть URL-адреса, которые не отображаются в Search Console (например, неиндексированные страницы), они НЕ будут включены.
Вы также можете выбрать внутреннее соединение (INNER JOIN):
Внутренне соединение, Inner Join (рисунок seotistics.com)
В этом случае учитывается только то, что совпадает с данными в таблицах Search Console и Google Analytics 4. Например, если страница доступна только в данных GA4 (страницы с параметрами utm), но не доступна в GSC, она не будет учитываться для новой таблицы.
SQL-запрос по URL
Перейдите в BigQuery и вставьте нижеприведенный код:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
WITH ga4_data AS ( SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url, COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions, COUNT(DISTINCT user_pseudo_id) AS users FROM `project_name.table_name.events_*` -- замените на свой путь к данным Google Analytics 4 WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND NOT REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r'#') GROUP BY page_url ), gsc_data AS ( SELECT url AS page_url, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM `project_name.searchdata_url_impression` -- замените на свой путь к данным Google Search Console WHERE data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND search_type = 'WEB' AND NOT CONTAINS_SUBSTR(url, '#') GROUP BY page_url ) SELECT gsc.page_url, gsc.clicks, gsc.impressions, IFNULL(ga4.users, 0) AS users, IFNULL(ga4.sessions, 0) AS sessions FROM gsc_data gsc LEFT JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.clicks DESC |
В местах, где написано:
- -- замените на свой путь к данным Google Analytics 4;
- -- замените на свой путь к данным Google Search Console.
Вам необходимо указать свой путь к таблицам GA4 и GSC.
В этом запросе используется псевдостолбец _ TABLE _ SUFFIX и подстановочный знак со звездочкой * вместо таблицы с конкретным названием, поскольку нам необходимо получить данные за диапазон дат, а не за один день, и несколько таблиц из набора данных. Поэтому используйте конструкцию events_*
Важно использовать кавычки, иначе BigQuery не сможет применить подстановочный знак. У вас должно получиться что-то вроде этого:
Путь к вашим таблицам Google Analytics 4
Аналогичным образом укажите путь до таблицы Search Console. Но поскольку там все данные хранятся в одной таблице (url_impression), подстановочный знак * не нужен:
Путь к таблице Search Console
Для расшифровки этого SQL-запроса вы можете воспользоваться любым доступным чат-ботом ИИ (ChatGPT, Gemini, YandexGPT, DeepSeek, Google AI Studio, GigaChat и др.). Если кратко, то он получает объединенные данные таблиц GSC и GA4 за один год и показывает, сколько кликов, показов, пользователей и сессий было для каждой страницы за указанный период. Статистика отсортирована по убыванию количества кликов.
Результат в BigQuery после выполнения SQL:
Результат выполнения SQL-запроса (LEFT JOIN)
Если вам нужно выполнить внутреннее соединение INNER JOIN вместо левого соединения LEFT JOIN, замените последнюю часть на:
|
1 2 3 4 5 6 7 8 |
FROM gsc_data gsc JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.clicks DESC |
Тогда результат выполнения SQL-запроса будет отличаться:
Результат выполнения SQL-запроса (INNER JOIN)
Как видите, при левом внешнем соединении получилось 1247 записей, а при выполнении внутреннего соединении количество записей в результате сократилось до 982. Так и должно быть, поскольку в результате внутреннего соединения остаются только те строки, для которых нашлось соответствие в двух таблицах (GSC и GA4). А таких всегда меньше.
Расшифровка кода
Первая часть кода (ga4_data) представляет собой CTE (общее табличное выражение) и создает временную таблицу, содержащую нужные нам данные Google Analytics 4.
Общее табличное выражение (CTE, Common Table Expression) - это временный результат выполнения SQL-выражения, который можно использовать в другом SQL-запросе. CTE позволяет упрощать сложные SQL-запросы, разбивать их на составные части и использовать множество раз в других запросах.
Из таблицы events_ (GA4) мы извлекаем статистику по сеансам и пользователям, фильтруем по дате и группируем по URL:
- page_url - извлекается из параметров события (event_params), где ключ равен page_location;
- sessions - уникальные сессии, рассчитанные на основе комбинации user_pseudo_id и ga_session_id;
- users - уникальные пользователи, рассчитанные на основе user_pseudo_id (он же Client ID).
Данные берутся за последний год (от текущей даты минус 1 год до вчерашнего дня), исключаются URL, содержащие символ # . Группировка выполняется по page_url.
Примечание: функция UNNEST - незаменимый помощник для работы с вложенными и повторяющимися полями (параметры событий и свойства пользователей), которые часто встречаются в данных Google Analytics 4 (GA4).
Вторая часть кода (gsc_data) представляет собой CTE и создает временную таблицу, содержащую нужные нам данные Search Console. Из таблицы searchdata_url_impression мы извлекаем статистику по количеству показов и кликам, фильтруем по дате и группируем по URL:
- page_url - URL-страницы;
- clicks - количество кликов;
- impressions - количество показов страницы в поисковой выдаче Google;
Данные берутся с начала текущего года до вчерашнего дня. Учитываются только поисковые запросы типа WEB, исключаются URL, содержащие символ #. Группировка выполняется по page_url.
Заключительная часть - это объединение двух таблиц CTE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT gsc.page_url, gsc.clicks, gsc.impressions, IFNULL(ga4.users, 0) AS users, IFNULL(ga4.sessions, 0) AS sessions FROM gsc_data gsc LEFT JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.clicks DESC |
Примечание: если страница доступна в GSC, но недоступна в GA4, вы получите значения NULL для пользователей и сеансов.
Подробнее:
- gsc.page_url - URL-страницы из таблицы Google Search Console;
- gsc.clicks - количество кликов из таблицы Google Search Console;
- gsc.impressions - количество показов из таблицы Google Search Console;
- ga4.users - количество уникальных пользователей из таблиц Google Analytics 4;
- ga4.sessions - количество сеансов из таблиц Google Analytics 4.
Объединение таблиц GSC и GA4 выполняется по левому внешнему соединению (LEFT JOIN) и по параметру page_url. Результаты сортируются по убыванию количества кликов (gsc.clicks DESC). В ga4.users и ga4.sessions используется функция IFNULL для преобразования значений NULL в 0 (если данных нет, используется 0). Если вы хотите явно показать NULL в итоговой таблице, просто удалите эту функцию.
Обязательно перед выполнением итогового запроса посмотрите на его размер:
This query will process 2.57 GB when run
Например, сообщение This query will process 2.57 GB when run означает, что для выполнения моего запроса система просканирует и обработает 2.57 ГБ данных. Это оценка объема данных, которые будут обработаны для выполнения запроса, и она помогает вам понять, сколько ресурсов будет использовано. Не забывайте, что от объема запрашиваемых данных будут зависеть ваши затраты. Любой неправильный запрос может стоить больших денег.
SQL-запрос по URL и query
BigQuery - незаменимый инструмент для аудита контента на крупных веб-сайтах, поскольку вы можете объединить данные не только Search Console с Google Analytics 4, но и добавить другие источники. Например, информацию по краулингу (crawling - процесс, при котором поисковые роботы (краулеры) обходят веб-страницы для извлечения данных и оценки их релевантности для запросов), данные из CMS (теги, категории, авторов контента) и статистику самого контента (заметки, длина публикации, количество символов и т.д.). И все это группировать по URL. Даже обычный Python/R не может конкурировать с мощью SQL.
Идея состоит в том, чтобы отобразить все важные детали для каждой страницы (SPV / Single Page View):
Информация по каждой странице, Single Page View/SPV (рисунок seotistics.com)
Что, если мы хотим оценить еще и вклад каждого запроса (query), построив объединенную таблицу с данными Search Console и Google Analytics 4. К сожалению, GA4 не сообщает нам о пользователях/сеансах или каких-либо показателях по конкретному запросу со ссылкой BQ. Поэтому невозможно получить правильный вклад каждого запроса.
Взяв нижеприведенный код и запустив его в BigQuery:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
WITH ga4_data AS ( SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url, COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions, COUNT(DISTINCT user_pseudo_id) AS users FROM `project_name.table_name.events_*` WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY page_url ), gsc_data AS ( SELECT url AS page_url, query, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM `project_name.searchdata_url_impression` WHERE data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND search_type = 'WEB' AND NOT CONTAINS_SUBSTR(url, '#') GROUP BY page_url, query ) SELECT gsc.page_url, gsc.query, gsc.clicks, gsc.impressions, IFNULL(ga4.users, 0) AS users, IFNULL(ga4.sessions, 0) AS sessions FROM gsc_data gsc LEFT JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.page_url |
Вы получите неверную информацию. Фактически, это тот же код, что и раньше, за исключением того, что теперь вы получаете столбец запроса (query) и сортируете данные по URL. Но он показывает не то, что нам нужно:
Один URL-адрес страницы, разные запросы -> дублирование данных по пользователям и сеансам GA4
Один URL-адрес с несколькими запросами отображает те же цифры для пользователей и сеансов Google Analytics 4. Это неверно.
Если вы когда-либо планируете агрегировать эти данные по странице или запросу, избегайте суммирования пользователей и сеансов!
Вероятно, вы получите NULL на странице и в запросе для первой строки, если не отфильтруете по search_type из-за discover и googleNews. На самом деле discover и googleNews не имеет никаких запросов и может содержать некоторые анонимные данные (поэтому вы даже не увидите страницу).
Примечание: в целях защиты конфиденциальности пользователей не показываются очень редкие, так называемые анонимизированные запросы. Данные о таких запросах всегда исключаются из таблицы, но учитываются в итоговых значениях на диаграмме, если не выполнена фильтрация по запросу (то есть показаны запросы, которые содержат или не содержат определенную строку). Если среди связанных с вашим сайтом запросов много анонимизированных, то могут наблюдаться значительные расхождения между общим числом запросов и значением, которое рассчитывается по следующей формуле = запросы, содержащие определенную_строку, + запросы, не содержащие определенную_строку. Причина в том, что анонимизированные запросы исключаются из общего количества, как только применяется фильтр. При группировке или фильтрации по запросу данные агрегируются по ресурсам. Подробнее о том, как анонимизированные запросы могут привести к расхождениям в данных, читайте в официальной документации Google. Таким образом, если запрос анонимизированный (поле is_anonymized_query имеет значение true), то поле query будет пустым.
Попробуйте использовать этот SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
WITH ga4_data AS ( SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url, COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions, COUNT(DISTINCT user_pseudo_id) AS users FROM `project_name.table_name.events_*` -- замените на свой путь к данным Google Analytics 4 WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY page_url ), gsc_data AS ( SELECT url AS page_url, ARRAY_AGG(STRUCT(query, clicks, impressions)) AS query_data FROM ( SELECT url, query, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM `project_name.searchdata_url_impression` -- замените на свой путь к данным Google Search Console WHERE data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND search_type = 'WEB' AND NOT CONTAINS_SUBSTR(url, '#') GROUP BY url, query ) GROUP BY page_url ) SELECT gsc.page_url, query_data, IFNULL(ga4.users, 0) AS users, IFNULL(ga4.sessions, 0) AS sessions FROM gsc_data gsc LEFT JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.page_url |
Не забудьте в местах, где написано:
- -- замените на свой путь к данным Google Analytics 4;
- -- замените на свой путь к данным Google Search Console.
Указать свой путь к таблицам GA4 и GSC.
Результат в BigQuery должен выглядеть примерно так:
Данные из GSC сохраняются в виде массива структур, что позволяет анализировать все поисковые запросы, связанные с конкретным URL
Расшифровка кода
Этот SQL-запрос в BigQuery выполняет объединение данных из Google Analytics 4 и Search Console, но с некоторыми отличиями от предыдущего запроса. Основное отличие заключается в том, что данные из GSC агрегируются в массив структур (ARRAY_AGG(STRUCT)), что позволяет сохранить информацию о каждом поисковом запросе, связанном с конкретным URL. Давайте разберем запрос по частям.
Первая часть кода (ga4_data) представляет собой CTE, где создается временная таблица, содержащая нужные нам данные Google Analytics 4. Из таблицы events_ (GA4) мы извлекаем статистику по сеансам и пользователям, фильтруем по дате (_TABLE_SUFFIX ограничен диапазоном дат за последний год) и группируем по URL:
- page_url - извлекается из параметров события (event_params), где ключ равен page_location;
- sessions - уникальные сессии, рассчитанные на основе комбинации user_pseudo_id и ga_session_id;
- users - уникальные пользователи, рассчитанные на основе user_pseudo_id (он же Client ID).
Вторая часть кода (gsc_data) представляет собой CTE, где создается временная таблица для извлечения данных из Search Console за текущий год и агрегирования их в массив структур.
Внутренний подзапрос:
- извлекает URL страницы (url), поисковый запрос (query), количество кликов (clicks) и показов (impressions);
- фильтрует данные по search_type = 'WEB' и исключаются URL-адреса, содержащие символ #;
- группировка выполняется по url и query.
Внешний подзапрос:
- группирует данные по page_url;
- создает массив структур ARRAY_AGG(STRUCT), где каждая структура содержит query, clicks и impressions.
Заключительная часть - это объединение двух таблиц CTE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT gsc.page_url, query_data, IFNULL(ga4.users, 0) AS users, IFNULL(ga4.sessions, 0) AS sessions FROM gsc_data gsc LEFT JOIN ga4_data ga4 ON gsc.page_url = ga4.page_url ORDER BY gsc.page_url |
Подробнее:
- выбирает page_url и query_data (массив структур с данными о запросах, кликах и показах) из gsc_data;
- добавляет данные о пользователях (users) и сеансах (sessions) из ga4_data с помощью LEFT JOIN;
- если данные из GA4 отсутствуют, использует IFNULL, чтобы подставить 0;
- сортирует результат по page_url.
Два варианта работы (SEO + BigQuery)
Марко Джордано, автор оригинального материала, в большинстве своих проектов использует две схемы работы:
-
-
- GA4 + GSC (ориентирован на страницу);
- GSC (ориентирован на страницу + поисковый запрос в течение времени).
-
Две схемы работы SEO + BigQuery (рисунок seotistics.com)
В первом варианте он использует такие метрики, как: url, клики, показы, пользователи, сеансы, ключевые события (key events), пользовательский контент (ugc, user-generated content) и другие. Во втором варианте: поисковый запрос, url, периоды в месяц/год, общее количество кликов, показов, пользовательский контент и т.д.
Объединенная таблицу GA4 + GSC, как правило, предназначена для аудита контента, упрощения анализа и любых решений, связанных с конкретной страницей (ее ценности и вкладу). Марко рекомендует работать только с одной метрикой трафика, чтобы избежать избыточности. Например, добавлять пользователей (users) из Google Analytics 4.
Вариант, при котором вы используете только таблицы Search Console (без GA4), хорош для анализа распределения поисковых запросов и анонимизированных. При этом многие столбцы с логическими значениями true и false (см. выше) можно отбросить, ими можно пренебречь.
GSC сам по себе хорош для измерения прямого влияния SEO и поисковых запросов. Вы также можете сгруппировать данные по поисковому запросу и определить наименее эффективные из них.
Google Analytics 4 не имеет никакой информации о поисковых запросах. Невозможно из интерфейса (даже при настроенной связи GA4 - Search Console) понять, какой вклад они вносят в трафик или в ваш доход ($$$).
Таблица, ориентированная на страницы (по url), следует подходу SPV (Single Page View). Оригинальная таблица URL из Google Search Console также полезна, поскольку, даже имея данные о поисковых запросах, необходимо анализировать сами страницы, на которые пользователи переходят после поиска в Google.
Некоторые рекомендации
- такое объединение данных из разных сервисов Google подходит для одноразового анализа данных или для составления отчетов;
- инженер данных (data engineer) должен помочь вам с подготовкой исходных данных - в наборе данных оставить только нужные столбцы, с которыми вы будете дальше работать;
- не рекомендуется вносить изменения в таблицы до сохранения копии необработанных данных;
- избегайте объединения таблиц в таких инструментах, как Looker Studio (Google Data Studio), поскольку тогда вы получите схожий с примером выше результат, где один URL-адрес страницы, разные запросы -> дублирование данных по пользователям и сеансам GA4.
- веб-сайты с большим количеством трафика следуют другим правилам, поскольку при использовании BigQuery приходится оптимизировать SQL, беспокоиться за использование вычислительных ресурсов и хранение данных. Даже код, представленный в этом руководстве, тоже можно оптимизировать. Поэтому старайтесь не копировать и вставлять код, предназначенный для небольших проектов, в крупные веб-сайты без предварительного анализа.
Google предоставляет вам таблицы с большим количеством данных. Но они далеки от идеала. Только вы и ваша организация должны найти тот способ, который больше всего подходит в вашем проекте. Здесь нет шаблонных решений.
В некоторых случаях вам может потребоваться разделить данные Google Analytics 4 и разбить исходную таблицу событий на несколько подтаблиц. В противном случае вы можете оставить все как есть, чтобы сэкономить место в хранилище. Вы также можете заранее рассчитать некоторые показатели, чтобы вам не пришлось писать для них отдельный SQL-запрос.
Объединение с другими источниками (не Google)
Крупные компании могут использовать и другие источники информации (не только Google Analytics 4 и Search Console). Например:
- данные сканирования (404 ошибки, ссылки nofollow, заблокированные страницы, теги "noindex", дубликаты страниц и прочее);
- журналы доступа и журналы ошибок (log-файлы, доступные на сервере/хостинге);
- контент-планы;
- информацию из CRM/ERP-систем;
- данные CMS;
- и другие.
При желании все эти данные можно объединить в таблицах и построить автоматизированные отчеты (в том же Looker Studio). Однако должны быть веские причины для интеграции вышеупомянутых источников данных, поскольку на это могут потребоваться довольно большие ресурсы.
Перед тем как приступить к реализации, рассмотрите все плюсы и минусы, определите цели, сроки и преимущества, которые вы получите от объединения различных источников данных.
От данных к преимуществам (рисунок seotistics.com)
Быть может, время и ресурсы, которые будут затрачены на выполнение этой задачи, окажутся несопоставимыми с теми результатами и профитом, который вы планируете получить после внедрения. Например, если вы используете сторонние решения, то для выгрузки данных нужно платить за обращения к API того или иного сервиса (Semrush, Ahrefs, SimilarWeb и пр.), что может быть довольно дорого.
При объединении таблиц помните, что первым выбором является страница (url), а уже затем вы можете использовать поисковые запросы (query) и даты/временные метки (timestamp).
На данный момент BigQuery может помочь вам с тем, что находится в правом верхнем квадранте. Это:
- SEO Analysis - анализ SEO-трафика;
- SERP Clustering - группировка ключевых слов на основе результатов поиска в поисковых системах (SERP, Search Engine Results Page).
SEO Analysis и SERP Clustering (рисунок seotistics.com)
SEO-анализ - весьма мощный инструмент в умелых руках. И он становится еще эффективнее, если вы забудете о SEO как об отдельном канале, а будете рассматривать его в комплексе с другими источниками трафика.
