Estou nas fases iniciais de elaboração de um relatório semanal dos dados do meu site. No momento, estou analisando algumas das métricas que quero acompanhar ativamente. Estou interessado no relatório de termos de pesquisa em tendência, mas, ao analisar seu histórico, parece que os dados podem ser fortemente distorcidos por pesquisas de administradores/moderadores.
O fórum atua parcialmente como uma base de conhecimento de suporte para os produtos que minha empresa fabrica e, como tal, frequentemente pesquisamos posts relevantes de solução de problemas ao responder a tickets. Existe uma maneira fácil de remover as pesquisas de administradores/moderadores deste relatório?
--[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
Aqui está o SQL para o relatório do painel de Termos de Pesquisa em Alta:
