Я нахожусь на начальном этапе формирования еженедельного отчёта по данным моего сайта. Сейчас я изучаю метрики, которые хочу отслеживать в режиме реального времени. Меня интересует отчёт по популярным поисковым запросам, но при взгляде на его историю кажется, что данные могут быть сильно искажены поисками со стороны администраторов и модераторов.
Форум частично выполняет функцию базы знаний поддержки для продуктов, которые производит моя компания, и поэтому мы часто ищем соответствующие посты по устранению неполадок при ответе на заявки. Есть ли простой способ исключить поиски администраторов и модераторов из этого отчёта?
--[params]
-- date :start_date
-- date :end_date
WITH admin_moderator_users AS (
SELECT
gu.user_id
FROM
group_users gu
JOIN
groups g ON gu.group_id = g.id
WHERE
g.name IN ('admins', 'moderators')
)
SELECT
lower(sl.term) AS search_term,
count(sl.id) AS term_count,
(COALESCE(SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(sl.id)::float) * 100 AS click_through_rate,
SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM
search_logs sl
LEFT JOIN
admin_moderator_users amu ON sl.user_id = amu.user_id
WHERE
sl.created_at::date BETWEEN :start_date AND :end_date
AND amu.user_id IS NULL
GROUP BY
lower(sl.term)
ORDER BY
term_count DESC
Вот SQL-запрос для отчёта на панели управления «Актуальные поисковые запросы»:
