Je suis aux premières étapes de la mise en place d’un rapport hebdomadaire sur les données de mon site. Pour l’instant, je me penche sur certaines des métriques que je souhaite suivre activement. Je m’intéresse au rapport sur les termes de recherche tendance, mais en examinant son historique, il semble que les données pourraient être fortement faussées par les recherches des administrateurs/modérateurs.
Le forum sert en partie de base de connaissances de support pour les produits que mon entreprise fabrique, et à ce titre, nous recherchons souvent des publications pertinentes de dépannage lorsque nous répondons aux tickets. Existe-t-il un moyen simple de supprimer les recherches des administrateurs/modérateurs de ce rapport ?
--[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
Voici le SQL pour le rapport du tableau de bord des termes de recherche tendances :
