تقرير لوحة المعلومات - أبرز مصادر الزيارات

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

هذا هو إصدار SQL لتقرير لوحة المعلومات لأهم مصادر الزيارات.

يوفر تقرير لوحة المعلومات هذا تحليلاً لأهم المصادر الخارجية التي ترتبط بالمشاركات على موقع Discourse ضمن نطاق زمني محدد و، اختياريًا، فئة معينة أو فئاتها الفرعية.

من خلال تحديد المجالات الخارجية التي ترتبط بمجتمعهم، يمكن للمسؤول تقييم فعالية جهود التسويق، وتحسين استراتيجية المحتوى الخاصة بهم لجذب جمهور أوسع، واستكشاف فرص تعاونية مع المواقع الجذابة، والتحسين لمحركات البحث (SEO)، وإدارة أي أزمات محتملة تنشأ عن مصادر زيارات غير مرغوب فيها.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true

SELECT
  ind.name AS domain,
  COUNT(*) AS clicks,
  COUNT(DISTINCT p.topic_id) AS topics
FROM incoming_links il
  INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
  INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
  INNER JOIN categories c ON c.id = t.category_id
  INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
  INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
  OR t.category_id = :category_id
  OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit

المعلمات

  • معلمات التاريخ:
    • تقبل الاستعلام معلمتين للتاريخ، :start_date و :end_date، اللتين تحددان نطاق التاريخ للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
  • معلمات الفئة:
    • :category_id: معلمة عدد صحيح يمكن تعيينها إلى معرف فئة معينة لتضييق نطاق التحليل ليشمل المشاركات داخل تلك الفئة. إذا تم تعيينها إلى null أو لم يتم توفيرها، يتم النظر في المشاركات من جميع الفئات.
    • :include_subcategories: معلمة منطقية تتحكم فيما إذا كان سيتم تضمين المشاركات من الفئات الفرعية للفئة المحددة :category_id. إذا تم تعيينها إلى true، فسيتضمن التقرير روابط للمشاركات في كل من الفئة المحددة وفئاتها الفرعية؛ إذا كانت false، فسيتم النظر فقط في الفئة المحددة.
  • حد النتائج:
    • :limit: معلمة عدد صحيح تحدد عدد الصفوف التي يعيدها الاستعلام إلى أهم N نتائج بناءً على عدد النقرات. القيمة الافتراضية المقدمة في الاستعلام هي 10.

شرح استعلام SQL

  • SELECT: يتم تنظيم التقرير لاختيار ثلاث قطع من المعلومات:
    • ind.name AS domain: سيعرض هذا العمود اسم كل مجال قام بإحالة المستخدمين إلى المنتدى.
    • COUNT(*) AS clicks: يوضح هذا العمود العدد الإجمالي للنقرات على الروابط الواردة من كل مجال.
    • COUNT(DISTINCT p.topic_id) AS topics: يمثل هذا العمود عدد الموضوعات الفريدة التي تم الوصول إليها من خلال تلك الروابط الواردة.
  • FROM AND JOIN: يجمع الاستعلام البيانات من عدة جداول:
    • incoming_links il: هذا هو الجدول الأساسي حيث يتم تخزين بيانات الروابط الواردة.
    • posts p: الجدول المتعلق بمنشورات المنتدى.
    • topics t: الجدول الذي يحتوي على بيانات الموضوعات.
    • categories c: الجدول الذي يحتوي على معلومات حول الفئات.
    • incoming_referers ir: الجدول الذي يخزن معلومات حول روابط الإحالة.
    • incoming_domains ind: الجدول الذي يصنف المجالات التي تنشأ منها الروابط إلى المنتدى.
    • تضمن INNER JOINs أننا ننظر فقط إلى المشاركات والموضوعات التي لم يتم حذفها ونربط بيانات الروابط الواردة بالمشاركات والموضوعات والمجالات المحيلة الصحيحة.
  • WHERE: يطبق هذا الجزء من الاستعلام عوامل تصفية لتضييق مجموعة النتائج:
    • يحد السجلات لتشمل فقط تلك التي تنتمي إلى نمط ‘regular’، أي سلاسل المنتدى العادية (على عكس الرسائل الخاصة أو الأنواع الخاصة الأخرى).
    • يقوم بتصفية التواريخ لتشمل فقط الروابط التي تم إنشاؤها ضمن الفترة المحددة (:start_date إلى :end_date).
    • يطبق تصفية الفئة بناءً على المعلمة :category_id المقدمة. إذا كانت :category_id فارغة، يتم النظر في جميع الفئات. إذا كانت :include_subcategories صحيحة و :category_id ليست فارغة، فإنه يشمل أيضًا الفئات الفرعية للفئة المحددة.
  • GROUP BY: يقوم الاستعلام بتجميع البيانات حسب اسم المجال (ind.name)، مما يضمن تقسيم العد لكل مجال محيل مميز.
  • ORDER BY and LIMIT: يتم فرز مجموعة البيانات النهائية بترتيب تنازلي بناءً على عدد النقرات، وبالتالي إعطاء الأولوية للمجالات ذات أعلى حركة الزيارات المحالة. يحد شرط LIMIT :limit عدد النتائج لجعل التقرير موجزًا ومركّزًا على أهم المجالات.

نتائج مثال

domain clicks topics
www.examplesite1.com 1234 123
www.examplesite2.com 56 67
www.examplesite3.com 40 95
… … …
3 إعجابات
2

أحب قراءة كل واحد من هذه حقًا.

عمل رائع!

إعجاب واحد (1)
3

هل لديكم أي أفكار لماذا قد يعطي هذا أرقامًا صغيرة جدًا؟ في منتداي، فيسبوك ليس قريبًا على الإطلاق، مما يعني أن الآلاف مفقودون وبعض المواقع غير موجودة على الإطلاق. هل تخفي الهواتف المصدر أو شيء من هذا القبيل؟