Dashboard Report - Trending Search Terms

[SaraDev (Sara Devlaeminck)]

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) * 100 AS click_through_rate,
	SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date AND :end_date
GROUP BY lower(term)
ORDER BY term_count DESC

SQL Query Explanation

Here’s a breakdown of the query:

• Parameters:
  • :start_date - The beginning of the date range for the report.
  • :end_date - The end of the date range for the report.
    • Both date parameters accept the format of YYYY-MM-DD.
• Data Selection:
  • lower(term) AS search_term: Converts the search term to lowercase for consistent grouping and names the resulting column search_term.
  • count(id) AS term_count: Counts the total number of searches for each term and labels this column term_count.
  • (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate: Calculates the click-through rate (CTR) for each term by determining the percentage of searches that resulted in a click. It handles cases where search_result_type is NULL (indicating no click) by assigning a zero, otherwise one. The result is then divided by the total count of searches for that term to get a rate, which is then multiplied by 100 to get a percentage.
  • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: Sums the number of times search results were clicked for each term.
• Filtering:
  • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: Filters the search logs to only include entries where the created_at date falls between the specified start and end dates.
• Grouping and Ordering:
  • GROUP BY lower(term): Groups the results by the lowercase search term to aggregate counts and clicks for each unique term.
  • ORDER BY term_count DESC: Orders the results by the term_count in descending order, showing the most frequently searched terms at the top.

Example Results

search_term	term_count	click_through_rate	click_count
example search term	756	0	0
#category search term	545	0.3669724770642202	2
in:personal order:latest	70	0	0
test	59	5.084745762711865	3
search	57	14.03508771929824	8
…	…	…	…