تقرير أهم مصادر الزيارات شهرًا بشهر؟

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

مرحباً،

أحاول إنشاء تقرير شهري عن أهم مصادر الزيارات. سيكون من الرائع رؤية كيف تغيرت مصادرنا كل شهر، كل ذلك ضمن رسم بياني/جدول واحد. هل هناك أي طريقة للحصول على هذه البيانات؟

حاليًا، لا يمكنني الحصول إلا على الإجماليات للفترة الكاملة المكونة من 5 أشهر التي أهتم بها:

image
image1231×691 36.5 KB

شكراً،

ناتشو

إعجاب واحد (1)
2 
WITH traffic_sources AS (
  SELECT
    EXTRACT(MONTH FROM user_visits.visited_at) AS month,
    incoming_referers.incoming_domain_id,
    COUNT(*) AS visit_count,
    LAG(COUNT(*)) OVER (PARTITION BY incoming_referers.incoming_domain_id ORDER BY EXTRACT(MONTH FROM user_visits.visited_at)) AS prev_month_visit_count
  FROM  user_visits
  JOIN  incoming_referers
  ON  user_visits.user_id = incoming_referers.id
  WHERE user_visits.visited_at IS NOT NULL
  GROUP BY EXTRACT(MONTH FROM user_visits.visited_at), incoming_referers.incoming_domain_id
)
SELECT
  month,
  incoming_domain_id,
  visit_count,
  prev_month_visit_count,
  visit_count - COALESCE(prev_month_visit_count, 0) AS change
FROM traffic_sources
ORDER BY month, incoming_domain_id

مرحباً، لقد عدت للتو من العشاء وقمت بتحديث الاستعلام؛ يمكنك تجربته، وإذا أحرزت أي تقدم، فيرجى إخباري :smiley:. خادم المناقشة الخاص بي هو خادم تم إنشاؤه حديثًا لذا لا توجد بيانات كافية للاستعلام، لقد أنشأت بعض البيانات الوهمية في SQL Server الخاص بي، ثم نقلتها إلى PGSQL.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

فقط للتحقق، هل اختبرت هذا هذه المرة؟ :slight_smile:

قد يكون لدي بعض الأخبار السيئة… :pensive_face: لا أعتقد أن date_trunc يعمل كما تريد:

image
image1402×330 10.1 KB

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

في حال كان ذلك مفيدًا لأي شخص يحاول إجراء هذا التكييف، فإليك نسخة من تقرير أهم مصادر الزيارات بصيغة SQL:


-- [params]
-- date :start_date = 04/05/2023
-- date :end_date = 05/06/2023

WITH count_links AS (

SELECT COUNT(*) AS clicks,
       ind.name AS domain
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 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 > :start_date
  AND il.created_at < :end_date
GROUP BY ind.name
ORDER BY clicks DESC
),

count_topics AS (

SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
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 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 > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
)

SELECT cl.domain AS "Domain",
       cl.clicks AS "Clicks",
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10
إعجاب واحد (1)