# Dashboard-Bericht - Trendsuchebegriffe

**URL:** <https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [13. Dezember 2023 um 20:58 UTC](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562 "2023-12-13T20:58:48Z")\
**Posts on this page:** 1\
**Showing post:** 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:** [13. Dezember 2023 um 20:58 UTC](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562/1 "2023-12-13T20:58:48Z")

</div>

Dies ist eine SQL-Version des Dashboards für Trendsuchbegriffe.

Dieser Bericht liefert Einblicke in die auf einer Website innerhalb eines bestimmten Datumsbereichs verwendeten Suchbegriffe und hilft zu verstehen, welche Begriffe am häufigsten gesucht werden und wie oft Benutzer auf die Suchergebnisse klicken.

```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-Abfrageerklärung

Hier ist eine Aufschlüsselung der Abfrage:

- **Parameter** :
  - `:start_date` - Der Beginn des Datumsbereichs für den Bericht.
  - `:end_date` - Das Ende des Datumsbereichs für den Bericht.
    - Beide Datumsparameter akzeptieren das Format `JJJJ-MM-TT`.

- **Datenauswahl** :
  - `lower(term) AS search_term`: Konvertiert den Suchbegriff in Kleinbuchstaben für eine konsistente Gruppierung und nennt die resultierende Spalte `search_term`.
  - `count(id) AS term_count`: Zählt die Gesamtzahl der Suchen für jeden Begriff und bezeichnet diese Spalte als `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`: Berechnet die Klickrate (CTR) für jeden Begriff, indem der Prozentsatz der Suchen ermittelt wird, die zu einem Klick geführt haben. Sie behandelt Fälle, in denen `search_result_type` `NULL` ist (was auf keinen Klick hinweist), indem sie Null zuweist, andernfalls eins. Das Ergebnis wird dann durch die Gesamtzahl der Suchen für diesen Begriff geteilt, um eine Rate zu erhalten, die dann mit 100 multipliziert wird, um einen Prozentsatz zu erhalten.
  - `SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count`: Summiert die Anzahl der Klicks auf Suchergebnisse für jeden Begriff.

- **Filterung** :
  - `WHERE sl.created_at::date BETWEEN :start_date AND :end_date`: Filtert die Suchprotokolle, um nur Einträge einzuschließen, bei denen das Datum `created_at` zwischen den angegebenen Start- und Enddaten liegt.

- **Gruppierung und Sortierung** :
  - `GROUP BY lower(term)`: Gruppiert die Ergebnisse nach dem Suchbegriff in Kleinbuchstaben, um Zählungen und Klicks für jeden eindeutigen Begriff zu aggregieren.
  - `ORDER BY term_count DESC`: Sortiert die Ergebnisse nach `term_count` in absteigender Reihenfolge, wobei die am häufigsten gesuchten Begriffe oben angezeigt werden.

### Beispielergebnisse

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

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562)._