I’m in the early stages of putting together a weekly report of my site’s data. Right now I’m looking at some of the metrics I want to actively track. I’m interested in the trending search terms report, but as I look at its history, it looks like the data could be heavily skewed by admin/moderator searches.
The forum partially acts as a support knowledge base for the products my company manufactures, and as such, we often search for relevant troubleshooting posts when responding to tickets. Is there an easy way to remove the admin/moderator searches from this report?
--[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
Here is the SQL for the Trending Search Terms dashboard report: