Как связать поисковые запросы Search Console с покупками Google Analytics 4?

17 февраля, 2025

Как связать поисковые запросы Search Console с продажами Google Analytics 4? Руководство, в котором описывается методология поиска наиболее ценных органических ключевых слов с использованием Google Search Console, Google Analytics 4 и Google BigQuery.

Эта статья - мой вольный перевод публикации Finding the Most Valued Organic Keywords: A Data Story with GSC, GA4, and BigQuery (автор - Ali Izadi)

Содержание

Начало

SEO является важным элементом в мире digital-маркетинга, а поиск правильных ключевых слов подобен поиску золота. Эти поисковые запросы являются ключами к тому, чтобы больше людей увидели ваш сайт и в конечном итоге совершили покупку. Однако существует значительная проблема - связь между Google Search Console (GSC) и Google Analytics 4 (GA4) не идеальна, что приводит к потере важных сведений о том, как ключевые слова влияют на продажи и привлекают клиентов.

Невозможность создать стандартный отчет или построить нужное Исследование с показателями поисковых запросов GSC и конверсиями (=ключевыми событиями) GA4 не позволяет нам определять наиболее эффективные ключевые слова и оптимизировать наше присутствие (видимость) в Интернете для увеличения продаж.

Но способ есть! Объединить данные Search Console и Google Analytics 4 в BigQuery и использовать SQL-запросы для выявления ценных органических ключевых слов, приносящих продажи.

Представьте, что вы детектив в мире цифрового маркетинга. Ваша миссия - раскрыть самые ценные ключевые слова, которые приводят не только к кликам, но и к реальным конверсиям и продажам. В вашем распоряжении два основных инструмента, два ваших лучших союзника:

Google Search Console;
Google Analytics 4.

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

Google Search Console показывает нам, какие ключевые слова (поисковые запросы) приносят трафик, как подсказки, ведущие к скрытому сокровищу. Но он не сообщает нам, какой из этих путей заканчивается главным призом - конверсией.

Google Analytics 4 раскрывает сундук с сокровищами - конверсии и продажи. Однако это как найти сундук без карты, оставляя нас гадать, какие ключевые слова привели искателей к золоту.

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

Какие ключевые слова не просто популярны, но и прибыльны? Как связать свой трафик Search Console с реальными продажами из Google Analytics 4?

Маркетологи сталкиваются с проблемой интеграции GA4 и GSC из-за отсутствующих ключевых слов и неясной атрибуции конверсий. Это затрудняет определение ценных ключевых слов, которые приводят к действиям клиентов. Необходимо решение, которое точно отображает путь от поиска до продажи.

Экспорт данных

SQL - наш путь к успеху, от кликов к конверсиям! Но перед тем, как выполнять сами запросы и находить ценные органические ключевые слова для своего проекта, вам необходимо настроить экспорт данных Google Search Console и Google Analytics 4 в Google BigQuery. Я не буду подробно останавливаться на этих шагах, потому что они досконально разобраны в других материалах моего блога. Обязательно прочитайте их перед тем, как приступать к дальнейшему изучению этого материала:

Примечание: все данные, которые вы будете использовать, собираются со дня настройки вашего экспорта в 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:

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

SQL-запрос

После настройки экспорта данных вы можете выполнить SQL-запрос. Перейдите в BigQuery и вставьте нижеприведенный код:

-- Declaration of date range for analysis
DECLARE start_date DATE DEFAULT '2024-02-09';
DECLARE end_date DATE DEFAULT '2024-02-13';

-- Custom function to transform URLs based on specified type
CREATE TEMP FUNCTION transform_url(page STRING, type STRING) AS (
  CASE
    WHEN type = 'parameters' THEN REGEXP_REPLACE(page, '\\?.*$|#.*', '')
    WHEN type = 'protocol' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)', '')
    WHEN type = 'domain' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)?[^/]+/', '/')
    WHEN type = 'all' THEN REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(page, '\\?.*$|#.*', ''), '^(https?://|ftp://)', ''), '^[^/]+/', '/')
  END
);

-- CTE for extracting conversions from GA4 dataset
WITH conversions AS (
  SELECT
    user_pseudo_id,
    -- Extracting session ID
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,
    -- Calculating purchase value with a combination of potential value types
    (SELECT IFNULL(value.int_value, 0) FROM UNNEST(event_params) WHERE KEY = 'value') + 
    (SELECT IFNULL(value.float_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') + 
    (SELECT IFNULL(value.double_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') AS purchase_value
  FROM
    `GA4_table_id` -- замените на свой путь к данным Google Analytics 4
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
), 

-- CTE for extracting session starts filtered by organic traffic from Google
session_starts AS (
  SELECT
    user_pseudo_id,
    -- Extracting session ID
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,
    -- Extracting page location
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location
  FROM
    `GA4_table_id` -- замените на свой путь к данным Google Analytics 4
  WHERE
    event_name = 'session_start'
    AND concat (collected_traffic_source.manual_source,'/',collected_traffic_source.manual_medium) = 'google/organic'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
),

-- CTE for merging sessions and conversions based on user ID and session ID
filtered_sessions AS (
  SELECT
    ss.user_pseudo_id,
    ss.ga_session_id,
    ss.page_location,
    sus.purchase_value
  FROM
    session_starts ss
  JOIN
    conversions sus
  ON
    ss.user_pseudo_id = sus.user_pseudo_id AND ss.ga_session_id = sus.ga_session_id
),

-- CTE for aggregating Google Analytics data by transformed page location
ga_data AS (
  SELECT
    transform_url(page_location, 'all') AS transformed_page_location,
    COUNT(*) AS num_sessions,
    SUM(purchase_value) AS total_purchase_value
  FROM
    filtered_sessions
  GROUP BY
    transformed_page_location
),

-- CTE for aggregating Google Search Console data by query and transformed URL
gsc_data AS (
  SELECT
    query AS search_query,
    transform_url(url, 'all') AS transformed_url,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks
  FROM
    `GSC_table_id` -- замените на свой путь к данным Google Search Console
  WHERE
    clicks > 0
    AND data_date BETWEEN start_date AND end_date
    AND query IS NOT NULL
    AND url IS NOT NULL
  GROUP BY
    search_query,
    transformed_url
),

-- CTE for calculating percentage of total clicks and share of voice conversion
pot_calculation AS (
  SELECT
    gsc.search_query,
    gsc.transformed_url,
    gsc.total_impressions,
    gsc.total_clicks,
    ga.num_sessions,
    ga.total_purchase_value,
    ROUND(gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS percent_of_total_clicks,
    ROUND(ga.num_sessions * gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS sov_conversion
  FROM
    gsc_data gsc
  LEFT JOIN
    ga_data ga ON gsc.transformed_url = ga.transformed_page_location
  WHERE
    ga.num_sessions > 0
)

-- Final query aggregating the share of voice conversion and purchase value by search query
SELECT
  search_query,
  ROUND(SUM(sov_conversion),2) AS total_sov_conversion,
  ROUND(SUM(sov_conversion * total_purchase_value),2) AS total_purchase_value
FROM
  pot_calculation
GROUP BY
  search_query;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

-- Declaration of date range for analysis

DECLARE start_date DATE DEFAULT '2024-02-09';

DECLARE end_date DATE DEFAULT '2024-02-13';

-- Custom function to transform URLs based on specified type

CREATE TEMP FUNCTION transform_url(page STRING, type STRING) AS (

CASE

WHEN type = 'parameters' THEN REGEXP_REPLACE(page, '\\?.*$|#.*', '')

WHEN type = 'protocol' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)', '')

WHEN type = 'domain' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)?[^/]+/', '/')

WHEN type = 'all' THEN REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(page, '\\?.*$|#.*', ''), '^(https?://|ftp://)', ''), '^[^/]+/', '/')

END

);

-- CTE for extracting conversions from GA4 dataset

WITH conversions AS (

SELECT

user_pseudo_id,

-- Extracting session ID

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,

-- Calculating purchase value with a combination of potential value types

(SELECT IFNULL(value.int_value, 0) FROM UNNEST(event_params) WHERE KEY = 'value') +

(SELECT IFNULL(value.float_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') +

(SELECT IFNULL(value.double_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') AS purchase_value

FROM

`GA4_table_id` -- замените на свой путь к данным Google Analytics 4

WHERE

event_name = 'purchase'

AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)

-- CTE for extracting session starts filtered by organic traffic from Google

session_starts AS (

SELECT

user_pseudo_id,

-- Extracting session ID

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,

-- Extracting page location

(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location

FROM

`GA4_table_id` -- замените на свой путь к данным Google Analytics 4

WHERE

event_name = 'session_start'

AND concat (collected_traffic_source.manual_source,'/',collected_traffic_source.manual_medium) = 'google/organic'

AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)

-- CTE for merging sessions and conversions based on user ID and session ID

filtered_sessions AS (

SELECT

ss.user_pseudo_id,

ss.ga_session_id,

ss.page_location,

sus.purchase_value

FROM

session_starts ss

JOIN

conversions sus

ss.user_pseudo_id = sus.user_pseudo_id AND ss.ga_session_id = sus.ga_session_id

-- CTE for aggregating Google Analytics data by transformed page location

ga_data AS (

SELECT

transform_url(page_location, 'all') AS transformed_page_location,

COUNT(*) AS num_sessions,

SUM(purchase_value) AS total_purchase_value

FROM

filtered_sessions

GROUP BY

transformed_page_location

-- CTE for aggregating Google Search Console data by query and transformed URL

gsc_data AS (

SELECT

query AS search_query,

transform_url(url, 'all') AS transformed_url,

SUM(impressions) AS total_impressions,

SUM(clicks) AS total_clicks

FROM

`GSC_table_id` -- замените на свой путь к данным Google Search Console

WHERE

clicks > 0

AND data_date BETWEEN start_date AND end_date

AND query IS NOT NULL

AND url IS NOT NULL

GROUP BY

search_query,

transformed_url

-- CTE for calculating percentage of total clicks and share of voice conversion

pot_calculation AS (

SELECT

gsc.search_query,

gsc.transformed_url,

gsc.total_impressions,

gsc.total_clicks,

ga.num_sessions,

ga.total_purchase_value,

ROUND(gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS percent_of_total_clicks,

ROUND(ga.num_sessions * gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS sov_conversion

FROM

gsc_data gsc

LEFT JOIN

ga_data ga ON gsc.transformed_url = ga.transformed_page_location

WHERE

ga.num_sessions > 0

)

-- Final query aggregating the share of voice conversion and purchase value by search query

SELECT

search_query,

ROUND(SUM(sov_conversion),2) AS total_sov_conversion,

ROUND(SUM(sov_conversion * total_purchase_value),2) AS total_purchase_value

FROM

pot_calculation

GROUP BY

search_query;

В местах, где написано:

-- замените на свой путь к данным Google Analytics 4;
-- замените на свой путь к данным Google Search Console (таблица url_impression).

Вам необходимо указать свой путь к таблицам GA4 и GSC.

В этом запросе используется псевдостолбец _ TABLE _ SUFFIX и подстановочный знак со звездочкой * вместо таблицы с конкретным названием, поскольку нам необходимо получить данные за диапазон дат, а не за один день, и несколько таблиц из набора данных. Поэтому используйте конструкцию events_*

Важно использовать кавычки, иначе BigQuery не сможет применить подстановочный знак. У вас должно получиться что-то вроде этого:

Путь к вашим таблицам Google Analytics 4

Аналогичным образом укажите путь до таблицы Search Console. Но поскольку там все данные хранятся в одной таблице (url_impression), подстановочный знак * не нужен:

Путь к таблице Search Console

Не забудьте изменить диапазон дат на свой собственный:

начало периода - DECLARE start_date DATE DEFAULT '2024-02-09';
конец периода - DECLARE end_date DATE DEFAULT '2024-02-13';

Результат в BigQuery после выполнения SQL:

Результат выполнения SQL-запрос

Для расшифровки этого SQL-запроса вы можете воспользоваться любым доступным чат-ботом ИИ (ChatGPT, Gemini, YandexGPT, DeepSeek, Google AI Studio, GigaChat и др.). Если кратко, то он связывает данные GSC и GA4 в BigQuery, чтобы выявить поисковые запросы, которые привели к конверсиям.

Но давайте разберем SQL-запрос Али Изади подробнее.

1. Объявление диапазона дат

-- Declaration of date range for analysis
DECLARE start_date DATE DEFAULT '2024-02-09';
DECLARE end_date DATE DEFAULT '2024-02-13';

-- Declaration of date range for analysis

DECLARE start_date DATE DEFAULT '2024-02-09';

DECLARE end_date DATE DEFAULT '2024-02-13';

В самом начале определяется период, за который нужно проанализировать данные. Делается это с помощью start_date и end_date. Таким образом, мы рассматриваем только статистику, относящиеся к выбранному нами временному интервалу.

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

2. Преобразование URL-адресов для анализа

-- Custom function to transform URLs based on specified type
CREATE TEMP FUNCTION transform_url(page STRING, type STRING) AS (
  CASE
    WHEN type = 'parameters' THEN REGEXP_REPLACE(page, '\\?.*$|#.*', '')
    WHEN type = 'protocol' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)', '')
    WHEN type = 'domain' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)?[^/]+/', '/')
    WHEN type = 'all' THEN REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(page, '\\?.*$|#.*', ''), '^(https?://|ftp://)', ''), '^[^/]+/', '/')
  END
);

-- Custom function to transform URLs based on specified type

CREATE TEMP FUNCTION transform_url(page STRING, type STRING) AS (

CASE

WHEN type = 'parameters' THEN REGEXP_REPLACE(page, '\\?.*$|#.*', '')

WHEN type = 'protocol' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)', '')

WHEN type = 'domain' THEN REGEXP_REPLACE(page, '^(https?://|ftp://)?[^/]+/', '/')

WHEN type = 'all' THEN REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(page, '\\?.*$|#.*', ''), '^(https?://|ftp://)', ''), '^[^/]+/', '/')

END

);

Пользовательская функция transform_url создана для преобразования/трансформации URL-адресов различными способами (например, удаления параметров или протоколов). Эта стандартизация имеет решающее значение для точного сопоставления и сравнения данных из GSC и GA4, поскольку мы удаляем ненужные элементы из URL-адреса и приводим все ссылки к единому формату. В зависимости от структуры URL-адресов вы можете использовать каждую из определяемых пользователем функций. Просто убедитесь, что функция создает одинаковые URL-адреса для URL-адресов GSC и GA4.

3. Определение ценных действий с конверсиями

-- CTE for extracting conversions from GA4 dataset
WITH conversions AS (
  SELECT
    user_pseudo_id,
    -- Extracting session ID
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,
    -- Calculating purchase value with a combination of potential value types
    (SELECT IFNULL(value.int_value, 0) FROM UNNEST(event_params) WHERE KEY = 'value') + 
    (SELECT IFNULL(value.float_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') + 
    (SELECT IFNULL(value.double_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') AS purchase_value
  FROM
    `GA4_table_id` -- замените на свой путь к данным Google Analytics 4
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
),

-- CTE for extracting conversions from GA4 dataset

WITH conversions AS (

SELECT

user_pseudo_id,

-- Extracting session ID

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,

-- Calculating purchase value with a combination of potential value types

(SELECT IFNULL(value.int_value, 0) FROM UNNEST(event_params) WHERE KEY = 'value') +

(SELECT IFNULL(value.float_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') +

(SELECT IFNULL(value.double_value, 0.0) FROM UNNEST(event_params) WHERE KEY = 'value') AS purchase_value

FROM

`GA4_table_id` -- замените на свой путь к данным Google Analytics 4

WHERE

event_name = 'purchase'

AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)

Этот код создает временную таблицу (CTE) conversions, содержащую данные о конверсиях, включая идентификатор пользователя (user_pseudo_id), идентификатор сессии (ga_session_id) и доход от покупки (purchase_value) за указанный период времени.

Общее табличное выражение (CTE, Common Table Expression) - это временный результат выполнения SQL-выражения, который можно использовать в другом SQL-запросе. CTE позволяет упрощать сложные SQL-запросы, разбивать их на составные части и использовать множество раз в других запросах.

Примечание: чтобы это сработало, у вас должна быть настроена электронная торговля.

Промежуточный результат в BigQuery:

Промежуточный результат выполнения conversions

4. Отслеживание начальной точки с началом сеанса

-- CTE for extracting session starts filtered by organic traffic from Google
session_starts AS (
  SELECT
    user_pseudo_id,
    -- Extracting session ID
    (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,
    -- Extracting page location
    (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location
  FROM
    `GA4_table_id` -- замените на свой путь к данным Google Analytics 4
  WHERE
    event_name = 'session_start'
    AND concat (collected_traffic_source.manual_source,'/',collected_traffic_source.manual_medium) = 'google/organic'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
),

-- CTE for extracting session starts filtered by organic traffic from Google

session_starts AS (

SELECT

user_pseudo_id,

-- Extracting session ID

(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS ga_session_id,

-- Extracting page location

(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location

FROM

`GA4_table_id` -- замените на свой путь к данным Google Analytics 4

WHERE

event_name = 'session_start'

AND concat (collected_traffic_source.manual_source,'/',collected_traffic_source.manual_medium) = 'google/organic'

AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)

В CTE session_starts фильтруются данные GA4 для сеансов, которые начались с переходов из органического поиска Google.

5. Объединение аналитических данных с отфильтрованными сеансами

-- CTE for merging sessions and conversions based on user ID and session ID
filtered_sessions AS (
  SELECT
    ss.user_pseudo_id,
    ss.ga_session_id,
    ss.page_location,
    sus.purchase_value
  FROM
    session_starts ss
  JOIN
    conversions sus
  ON
    ss.user_pseudo_id = sus.user_pseudo_id AND ss.ga_session_id = sus.ga_session_id
),

-- CTE for merging sessions and conversions based on user ID and session ID

filtered_sessions AS (

SELECT

ss.user_pseudo_id,

ss.ga_session_id,

ss.page_location,

sus.purchase_value

FROM

session_starts ss

JOIN

conversions sus

ss.user_pseudo_id = sus.user_pseudo_id AND ss.ga_session_id = sus.ga_session_id

CTE filtered_sessions объединяет начало сеансов с конверсиями, сопоставляя идентификаторы устройств (user_pseudo_id, он же Client ID) и сеансов (ga_session_id). Этот шаг является ключевым для связывания начала пути пользователя (переходы из органического поиска Google) с его ценными действиями (совершение покупки).

Промежуточный результат в BigQuery:

Промежуточный результат после выполнения этой части SQL

6. Агрегирование данных Google Analytics 4

-- CTE for aggregating Google Analytics data by transformed page location
ga_data AS (
  SELECT
    transform_url(page_location, 'all') AS transformed_page_location,
    COUNT(*) AS num_sessions,
    SUM(purchase_value) AS total_purchase_value
  FROM
    filtered_sessions
  GROUP BY
    transformed_page_location
),

-- CTE for aggregating Google Analytics data by transformed page location

ga_data AS (

SELECT

transform_url(page_location, 'all') AS transformed_page_location,

COUNT(*) AS num_sessions,

SUM(purchase_value) AS total_purchase_value

FROM

filtered_sessions

GROUP BY

transformed_page_location

CTE ga_data агрегирует эти объединенные данные по нескольким метрикам:

transformed_page_location - преобразованный URL-адрес благодаря предыдущей функции transform_url (по параметру page_location);
num_sessions - подсчет количества сеансов;
total_purchase_value - суммарная стоимость покупок.

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

Промежуточный результат выполнения SQL

7. Объединение с данными Search Console

-- CTE for aggregating Google Search Console data by query and transformed URL
gsc_data AS (
  SELECT
    query AS search_query,
    transform_url(url, 'all') AS transformed_url,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks
  FROM
    `GSC_table_id` -- замените на свой путь к данным Google Search Console
  WHERE
    clicks > 0
    AND data_date BETWEEN start_date AND end_date
    AND query IS NOT NULL
    AND url IS NOT NULL
  GROUP BY
    search_query,
    transformed_url
),

-- CTE for aggregating Google Search Console data by query and transformed URL

gsc_data AS (

SELECT

query AS search_query,

transform_url(url, 'all') AS transformed_url,

SUM(impressions) AS total_impressions,

SUM(clicks) AS total_clicks

FROM

`GSC_table_id` -- замените на свой путь к данным Google Search Console

WHERE

clicks > 0

AND data_date BETWEEN start_date AND end_date

AND query IS NOT NULL

AND url IS NOT NULL

GROUP BY

search_query,

transformed_url

CTE gsc_data собирает информацию о поисковых запросах из Search Console, включая количество показов (total_impressions) и кликов (total_clicks). Эта часть SQL фокусируется на том, как пользователи находят наш сайт через поиск Google.

Промежуточный результат выполнения этого фрагмента SQL:

Результат выполнения CTE gsc_data

8. Расчет ценности поискового запроса

-- CTE for calculating percentage of total clicks and share of voice conversion
pot_calculation AS (
  SELECT
    gsc.search_query,
    gsc.transformed_url,
    gsc.total_impressions,
    gsc.total_clicks,
    ga.num_sessions,
    ga.total_purchase_value,
    ROUND(gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS percent_of_total_clicks,
    ROUND(ga.num_sessions * gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS sov_conversion
  FROM
    gsc_data gsc
  LEFT JOIN
    ga_data ga ON gsc.transformed_url = ga.transformed_page_location
  WHERE
    ga.num_sessions > 0
)

-- CTE for calculating percentage of total clicks and share of voice conversion

pot_calculation AS (

SELECT

gsc.search_query,

gsc.transformed_url,

gsc.total_impressions,

gsc.total_clicks,

ga.num_sessions,

ga.total_purchase_value,

ROUND(gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS percent_of_total_clicks,

ROUND(ga.num_sessions * gsc.total_clicks / SUM(gsc.total_clicks) OVER (PARTITION BY gsc.transformed_url), 2) AS sov_conversion

FROM

gsc_data gsc

LEFT JOIN

ga_data ga ON gsc.transformed_url = ga.transformed_page_location

WHERE

ga.num_sessions > 0

)

Этот шаг оценивает влияние определенных поисковых запросов на органические конверсии и стоимость покупки для всех страниц. CTE pot_calculation объединяет данные Search Console и Google Analytics 4 для расчета процента от общего числа кликов и доли голоса по конверсии (share of voice, сокращенно sov) каждого органического ключевого слова:

percent_of_total_clicks - вычисляется как отношение общего количества кликов (gsc.total_clicks) из таблицы Search Console для каждого преобразованного URL к сумме всех кликов (gsc.total_clicks) из той же таблицы Search Console для этого URL (результат округляется до двух знаков после запятой);
sov_conversion - вычисляется как произведение количества сеансов (ga.num_sessions) из таблицы Google Analytics 4 на количество кликов (gsc.total_clicks) из таблицы Search Console, деленное на сумму всех кликов для этого URL (результат также округляется до двух знаков после запятой).

В выборке данных добавлено условие, что количество сеансов ga.num_sessions должно быть больше нуля, что исключает записи без сеансов.

Промежуточный результат после выполнения этой части SQL

О том, как производить расчет процентной доли от общего количества в BigQuery, подробнее рассказано в видео ниже:

9. Вывод результатов

-- Final query aggregating the share of voice conversion and purchase value by search query
SELECT
  search_query,
  ROUND(SUM(sov_conversion),2) AS total_sov_conversion,
  ROUND(SUM(sov_conversion * total_purchase_value),2) AS total_purchase_value
FROM
  pot_calculation
GROUP BY
  search_query;

-- Final query aggregating the share of voice conversion and purchase value by search query

SELECT

search_query,

ROUND(SUM(sov_conversion),2) AS total_sov_conversion,

ROUND(SUM(sov_conversion * total_purchase_value),2) AS total_purchase_value

FROM

pot_calculation

GROUP BY

search_query;

Оператор SELECT объединяет все эти вычисления, чтобы представить общую долю голоса по конверсии (total_sov_conversion) и связанную с ней стоимость покупки (total_purchase_value).

Запрос выбирает три поля:

search_query - поисковый запрос;
ROUND(SUM(sov_conversion), 2) AS total_sov_conversion - суммирование значений столбца sov_conversion для каждой группы search_query, а затем результат округляется до двух десятичных знаков и обозначается как total_sov_conversion;
ROUND(SUM(sov_conversion * total_purchase_value), 2) AS total_purchase_value - суммирует произведение значений sov_conversion и total_purchase_value для каждой группы, также округляя результат до двух десятичных знаков и называя его total_purchase_value.

Этот результат напрямую связывает органические поисковые запросы Search Console с их вкладом в продажи на вашем сайте, зарегистрированными благодаря настроенным конверсиям в Google Analytics 4 (в данном случае - событию purchase, являющемуся частью электронной торговли).

Пример результата

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

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

Открыть в Looker Studio

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

Данные в Looker Studio по умолчанию

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

Пример визуализаций в Looker Studio

Как видите, наиболее ценные органические ключевые слова, которыми завершаются покупки на моем сайте osipenkov.ru - это запросы, связанные с прохождением экзаменов/сертификации по Яндексу и Google. И несколько брендовых запросов по моей фамилии и имени:

сертификация яндекс директ ответы
сертификация яндекс метрика ответы 2024
яндекс метрика сертификация ответы
сертификация яндекс метрика ответы
ответы на сертификацию яндекс директ
яков осипенков
осипенков
сертификация метрика
osipenkov
ответы на сертификацию яндекс директ 2024
ответы на сертификацию яндекс метрика

Так оно и есть на самом деле. Наибольшее количество органического поиска и продаж из SEO приходится как раз на ответы.

Заключительные слова

Описанный в этом руководстве метод является мощным подходом для SEO-специалистов и маркетологов, стремящихся понять истинную ценность своих органических ключевых слов Google.

Вот упрощение того, чего достигает этот метод, и значимость извлеченных столбцов:

Подсчет органического трафика. Метод начинается с подсчета всех посещений страниц, которые пришли из органических результатов поиска Google. Это важно, поскольку это создает основу для понимания того, какие страницы привлекают наибольшее внимание потенциальных клиентов;
Извлечение органических конверсий. Далее определяется, сколько из этих посещений привели к конверсии - будь то покупка (purchase), регистрация или другое ценное для вас действие, которое вы можете изменить под свои задачи в event_name. Этот шаг связывает первоначальный интерес (трафик) с ощутимыми результатами (конверсиями);
Расчет доли голоса (Share of Voice, SOV). Для каждого ключевого слова метод рассчитывает его долю голоса в виде кликов из органического поиска. Это включает в себя выяснение того, какой процент от общего числа кликов представляет каждое ключевое слово, что дает представление об относительной важности или доминировании ключевого слова в привлечении трафика;
Назначение конверсий ключевым словам. Затем он назначает конверсии, извлеченные из GA4, ключевым словам на основе их доли голоса. Это означает, что если ключевое слово обеспечило 50% кликов на странице, предполагается, что оно вносит вклад в 50% конверсий с этой страницы;
Обработка рейтингов нескольких страниц. Если ключевое слово ранжируется на нескольких страницах, запрос вычисляет его влияние на конверсию на каждой странице отдельно. Затем он суммирует эти влияния по всем страницам, предлагая комплексное представление общего вклада ключевого слова в конверсии.

В итоговой таблицы вы получаете три столбца:

search_query - конкретное ключевое слово (поисковый запрос), которое пользователи искали в Google;
total_sov_conversion - общая доля голоса по конверсии, указывающая пропорциональный вклад ключевого слова в конверсии на основе его доли кликов;
total_purchase_value - сумма значений конверсии (например, сумма покупок), присвоенных ключевому слову, что обеспечивает прямую связь между поведением при поиске и получением дохода.

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

Тонкая настройка рекламных кампаний Google Ads

А теперь представьте, что вы выделяете свой бюджет на SEO и поисковые запросы, которые основаны не на догадках, а гарантированно эффективные. Определив органические ключевые слова, которые являются настоящими источниками конверсий, вы можете создавать кампании в Google Ads, точно нацеленные не только на привлечение трафика, но и на совершение конверсий. Это похоже на ставки, в которых вы уверены в победе, что гарантирует разумное использование каждого доллара, потраченного на рекламу.

Оптимизация контента

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

Улучшение SEO для максимальной рентабельности инвестиций

Расставьте приоритеты в своих задачах SEO, сосредоточившись на оптимизации под высококонверсионные ключевые слова. Такой подход обеспечит не только улучшение позиций в поисковых системах, но и привлечение качественного трафика с более высокой степенью конверсии. Это стратегический переход от простого анализа цифр к созданию ценности, что позволяет синхронизировать ваши усилия в SEO с реальными бизнес-результатами.

Внедряя эти SQL-инсайты в свою стратегию, вы не просто принимаете обоснованные решения - вы создаете прочную основу для беспрецедентного роста и эффективности. Это касается не только digital-маркетинга. Речь идет о более умном подходе с четким фокусом на том, что действительно влияет на успех вашего бизнеса.

Метки: Google Analytics 4, Google BigQuery, Search Console, SQL

Вход

Регистрация

Как связать поисковые запросы Search Console с покупками Google Analytics 4?

Начало

Экспорт данных

Таблицы в BigQuery

SQL-запрос

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

Заключительные слова

Тонкая настройка рекламных кампаний Google Ads

Оптимизация контента

Улучшение SEO для максимальной рентабельности инвестиций

Получайте бесплатные уроки и фишки

Еще статьи