Me encuentro en las primeras etapas de elaboración de un informe semanal de los datos de mi sitio. Ahora mismo estoy analizando algunas de las métricas que quiero seguir activamente. Me interesa el informe de términos de búsqueda en tendencia, pero al observar su historial, parece que los datos podrían estar fuertemente sesgados por las búsquedas de administradores/moderadores.
El foro actúa en parte como una base de conocimientos de soporte para los productos que fabrica mi empresa y, como tal, a menudo buscamos publicaciones relevantes de solución de problemas al responder a tickets. ¿Hay alguna forma sencilla de eliminar las búsquedas de administradores/moderadores de este informe?
--[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
Aquí está el SQL para el informe del panel de Términos de búsqueda de tendencias:
