Dashboard Report - Termini di ricerca di tendenza

Dati e reportistica
, ,
1

Questo è una versione SQL del Report della Dashboard per i Termini di Ricerca di Tendenza.

Questo report fornisce informazioni sui termini di ricerca utilizzati su un sito all’interno di un intervallo di date specificato e aiuta a capire quali termini vengono cercati più frequentemente e quante volte gli utenti fanno clic sui risultati di ricerca.

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

Spiegazione della Query SQL

Ecco una ripartizione della query:

  • Parametri:
    • :start_date - L’inizio dell’intervallo di date per il report.
    • :end_date - La fine dell’intervallo di date per il report.
      • Entrambi i parametri di data accettano il formato AAAA-MM-GG.
  • Selezione dei Dati:
    • lower(term) AS search_term: Converte il termine di ricerca in minuscolo per un raggruppamento coerente e denomina la colonna risultante search_term.
    • count(id) AS term_count: Conta il numero totale di ricerche per ogni termine e etichetta questa colonna 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: Calcola il tasso di clic (CTR) per ogni termine determinando la percentuale di ricerche che hanno portato a un clic. Gestisce i casi in cui search_result_type è NULL (indicando nessun clic) assegnando zero, altrimenti uno. Il risultato viene quindi diviso per il conteggio totale delle ricerche per quel termine per ottenere un tasso, che viene poi moltiplicato per 100 per ottenere una percentuale.
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: Somma il numero di volte in cui i risultati di ricerca sono stati cliccati per ogni termine.
  • Filtraggio:
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: Filtra i log di ricerca per includere solo le voci in cui la data created_at rientra tra le date di inizio e fine specificate.
  • Raggruppamento e Ordinamento:
    • GROUP BY lower(term): Raggruppa i risultati per il termine di ricerca in minuscolo per aggregare conteggi e clic per ogni termine univoco.
    • ORDER BY term_count DESC: Ordina i risultati per term_count in ordine decrescente, mostrando i termini cercati più frequentemente in cima.

Risultati di Esempio

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 Mi Piace