Dashboard-Bericht - Trendsuchebegriffe

Daten & Berichterstattung
, ,
1

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.

--[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
… … … …
5 „Gefällt mir“