Rapport du tableau de bord - Tendances des recherches

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les termes de recherche tendance.

Ce rapport donne un aperçu des termes de recherche utilisés sur un site dans une plage de dates spécifiée, et aide à comprendre quels termes sont les plus fréquemment recherchés et combien de fois les utilisateurs cliquent sur les résultats de recherche.

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

Explication de la requête SQL

Voici une ventilation de la requête :

  • Paramètres :
    • :start_date - Le début de la plage de dates pour le rapport.
    • :end_date - La fin de la plage de dates pour le rapport.
      • Les deux paramètres de date acceptent le format AAAA-MM-JJ.
  • Sélection des données :
    • lower(term) AS search_term : Convertit le terme de recherche en minuscules pour un regroupement cohérent et nomme la colonne résultante search_term.
    • count(id) AS term_count : Compte le nombre total de recherches pour chaque terme et nomme cette colonne 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 : Calcule le taux de clics (CTR) pour chaque terme en déterminant le pourcentage de recherches qui ont abouti à un clic. Il gère les cas où search_result_type est NULL (indiquant aucun clic) en lui attribuant zéro, sinon un. Le résultat est ensuite divisé par le nombre total de recherches pour ce terme pour obtenir un taux, qui est ensuite multiplié par 100 pour obtenir un pourcentage.
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count : Additionne le nombre de fois où les résultats de recherche ont été cliqués pour chaque terme.
  • Filtrage :
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date : Filtre les journaux de recherche pour n’inclure que les entrées où la date created_at se situe entre les dates de début et de fin spécifiées.
  • Regroupement et tri :
    • GROUP BY lower(term) : Regroupe les résultats par le terme de recherche en minuscules pour agréger les comptes et les clics pour chaque terme unique.
    • ORDER BY term_count DESC : Trie les résultats par term_count dans l’ordre décroissant, montrant les termes les plus fréquemment recherchés en haut.

Exemple de résultats

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 « J'aime »