Rapporto sulle principali fonti di traffico mese su mese?

Dati e reportistica
,
1

Ciao,

Sto cercando di generare un report sulle principali fonti di traffico mese su mese. Sarebbe bello vedere come le nostre fonti sono cambiate ogni mese, tutto all’interno di un unico grafico/tabella. C’è un modo per ottenere questi dati?

Attualmente posso solo ottenere i totali per l’intero periodo di 5 mesi che mi interessa:

image
image1231×691 36.5 KB

Grazie,

Nacho

1 Mi Piace
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

Ciao, sono appena tornato dalla cena e ho aggiornato la query; potresti provare, se hai progressi, fammelo sapere :smiley:. Il mio server discourse è stato creato di recente, quindi non ci sono dati sufficienti per eseguire query, ho creato alcuni dati fittizi nel mio SQL Server, quindi li ho trasferiti a PGSQL.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

Solo per verificare, l’hai testato questa volta? :slight_smile:

Potrei avere una brutta notizia… :disappointed_face: Non credo che date_trunc stia funzionando come vorresti:

image
image1402×330 10.1 KB

1 Mi Piace
7

Nel caso fosse utile a qualcuno che tenta questo adattamento, ecco una versione del report 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 "Dominio",
       cl.clicks AS "Click",
       ct.topics AS "Argomenti"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10
1 Mi Piace