# Dashboard Report - Trending Search Terms

**URL:** https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 13, 2023, 8:58pm UTC](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562 "2023-12-13T20:58:48Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [December 13, 2023, 8:58pm UTC](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562/1 "2023-12-13T20:58:48Z")

</div>

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.

```sql
--[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 |
| … | … | … | … |