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
.
- Both date parameters accept the format of
- Data Selection:
lower(term) AS search_term
: Converts the search term to lowercase for consistent grouping and names the resulting columnsearch_term
.count(id) AS term_count
: Counts the total number of searches for each term and labels this columnterm_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 wheresearch_result_type
isNULL
(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 thecreated_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 theterm_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 |
… | … | … | … |