Trending Search Terms with Admin/Moderator Searches Removed?

[Kieran_Berryman]

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?

[Lilly (Lillian)]

Hi @Kieran_Berryman

hmmm maybe give this a try?

--[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:

Dashboard Report - Trending Search Terms

This is an SQL version of the Dashboard Report for Trending Search Terms. This report provides insight into the search terms used on a site within a specified date range, and helps understand which terms are most frequently searched for and how often users click on the search results. --[params] -- date :start_date -- date :end_date SELECT lower(term) AS search_term, count(id) AS term_count, (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 10…

[Kieran_Berryman]

Thank you! I’ll check it out.