Monatlicher Bericht über die wichtigsten Traffic-Quellen?

Daten & Berichterstattung
,
1

Hallo,

ich versuche, einen monatlichen Bericht über die wichtigsten Traffic-Quellen zu erstellen. Es wäre toll zu sehen, wie sich unsere Quellen jeden Monat geändert haben, alles in einem Diagramm/einer Tabelle. Gibt es eine Möglichkeit, diese Daten zu erhalten?

Derzeit kann ich nur die Gesamtsummen für den gesamten 5-monatigen Zeitraum erhalten, an dem ich interessiert bin:

image
image1231×691 36.5 KB

Danke,

Nacho

1 „Gefällt mir“
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

Hallo, ich bin gerade vom Abendessen zurückgekommen und habe die Abfrage aktualisiert. Du könntest es bitte ausprobieren, wenn du Fortschritte machst, lass es mich bitte wissen :smiley:. Mein Discourse-Server ist ein neu erstellter, daher gibt es nicht genügend Daten zum Abfragen. Ich habe einige Dummy-Daten in meinem SQL Server erstellt und sie dann nach PGSQL übertragen.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

Nur zur Überprüfung, haben Sie diesmal getestet? :slight_smile:

Ich habe vielleicht schlechte Nachrichten… :frowning: Ich glaube nicht, dass date_trunc so funktioniert, wie Sie es möchten:

image
image1402×330 10.1 KB

1 „Gefällt mir“
7

Falls es für jemanden nützlich ist, der diese Anpassung versucht, hier ist eine Version des Berichts „Top Traffic Sources“ in 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 „Gefällt mir“