Отчет панели управления: популярные поисковые запросы

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета по дашборду «Актуальные поисковые запросы».

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

--[params]
-- date :start_date
-- date :end_date

SELECT 
	lower(term) AS search_term,
	count(id) AS term_count,
	(COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate,
	SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date AND :end_date
GROUP BY lower(term)
ORDER BY term_count DESC

Объяснение SQL-запроса

Ниже приведена расшифровка запроса:

• Параметры:
  • :start_date — начало диапазона дат для отчета.
  • :end_date — конец диапазона дат для отчета.
    • Оба параметра даты принимают формат YYYY-MM-DD.
• Выбор данных:
  • lower(term) AS search_term: Преобразует поисковый запрос в нижний регистр для единообразного группирования и присваивает результирующему столбцу имя search_term.
  • count(id) AS term_count: Подсчитывает общее количество поисков для каждого запроса и присваивает этому столбцу имя term_count.
  • (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate: Вычисляет показатель кликабельности (CTR) для каждого запроса, определяя процент поисков, приведших к клику. Обрабатывает случаи, когда search_result_type равен NULL (что означает отсутствие клика), присваивая ноль, иначе — единицу. Затем результат делится на общее количество поисков для данного запроса, чтобы получить показатель, который умножается на 100 для получения процента.
  • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: Подсчитывает количество кликов по результатам поиска для каждого запроса.
• Фильтрация:
  • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: Фильтрует логи поиска, включая только записи, где дата created_at находится между указанными датами начала и конца.
• Группировка и сортировка:
  • GROUP BY lower(term): Группирует результаты по поисковому запросу в нижнем регистре для агрегации подсчетов и кликов для каждого уникального запроса.
  • ORDER BY term_count DESC: Сортирует результаты по столбцу term_count в порядке убывания, показывая наиболее часто используемые поисковые запросы вверху.

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

search_term	term_count	click_through_rate	click_count
example search term	756	0	0
#category search term	545	0.3669724770642202	2
in:personal order:latest	70	0	0
test	59	5.084745762711865	3
search	57	14.03508771929824	8
…	…	…	…