تقرير لوحة التحكم - المصطلحات البحثية الرائجة

البيانات والتقارير
, ,
1

هذا هو إصدار 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

فيما يلي تفصيل للاستعلام:

  • المعلمات:
    • :start_date - بداية النطاق الزمني للتقرير.
    • :end_date - نهاية النطاق الزمني للتقرير.
    • تقبل كلتا معلمتي التاريخ تنسيق YYYY-MM-DD.
  • اختيار البيانات:
    • lower(term) AS search_term: يحول مصطلح البحث إلى أحرف صغيرة للتجميع المتسق ويسمي العمود الناتج search_term.
    • count(id) AS term_count: يحسب العدد الإجمالي لعمليات البحث لكل مصطلح ويسمي هذا العمود 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: يحسب نسبة النقر إلى الظهور (CTR) لكل مصطلح عن طريق تحديد نسبة عمليات البحث التي أدت إلى نقرة. يتعامل مع الحالات التي يكون فيها search_result_type هو NULL (مما يشير إلى عدم وجود نقرة) عن طريق تعيين صفر، وإلا واحد. ثم يتم تقسيم النتيجة على العدد الإجمالي لعمليات البحث لهذا المصطلح للحصول على معدل، والذي يتم بعد ذلك ضربه في 100 للحصول على نسبة مئوية.
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: يجمع عدد المرات التي تم فيها النقر على نتائج البحث لكل مصطلح.
  • التصفية:
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: يقوم بتصفية سجلات البحث لتضمين الإدخالات فقط حيث يقع تاريخ created_at بين التواريخ المحددة للبداية والنهاية.
  • التجميع والترتيب:
    • GROUP BY lower(term): يجمع النتائج حسب مصطلح البحث بالأحرف الصغيرة لتجميع الأعداد والنقرات لكل مصطلح فريد.
    • ORDER BY term_count DESC: يرتب النتائج حسب term_count بترتيب تنازلي، مما يعرض المصطلحات الأكثر بحثًا في الأعلى.

نتائج مثال

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 إعجابات