Dashboard Report - Trending Search Terms

Data & reporting
, ,
1

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
… … … …
5 Likes