Рекомендации по работе с запросами и примеры запросов

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

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

query     date       search_type   impressions ...
пушистые котики 10-10-2022      WEB          1000
пушистые котики 10-10-2022      WEB           200
пушистые котики 10-10-2022    IMAGE          5000

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

/* НЕВЕРНЫЙ способ определения самого популярного запроса: будет показана только 1 строка по каждому запросу */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
пушистые котики   5000

/* ВЕРНЫЙ способ определения самого популярного запроса */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Исключить анонимизированные запросы */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
пушистые котики   6200

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

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

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

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

Примеры запросов

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

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

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

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Ниже добавлена единица, так как отсчет ведется от нуля */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

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

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

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

Самые популярные URL в Рекомендациях по числу кликов (на конкретную дату)

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

Общая статистика по запросам и URL для расширенных результатов с часто задаваемыми вопросами (за последние две недели)

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

Статистика по 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

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

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

Эта информация оказалась полезной?

Как можно улучшить эту статью?