Rapport mensuel sur les principales sources de trafic ?

Données et rapports
,
1

Bonjour,

J’essaie de générer un rapport sur les principales sources de trafic de mois en mois. Ce serait bien de voir comment nos sources ont changé chaque mois, le tout dans un seul graphique/tableau. Y a-t-il un moyen d’obtenir ces données ?

Actuellement, je ne peux obtenir que les totaux pour la période complète de 5 mois qui m’intéresse :

image
image1231×691 36.5 KB

Merci,

Nacho

1 « J'aime »
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

Salut Je reviens de mon dîner et j’ai mis à jour la requête ; vous pourriez essayer, si vous avez des progrès, faites-le moi savoir :smiley:. Mon serveur de discourse est nouvellement créé, il n’y a donc pas assez de données à interroger, j’ai créé des données factices dans mon SQL Server, puis je les ai transférées vers PGSQL.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

Juste pour vérifier, l’avez-vous testé cette fois-ci ? :slight_smile:

J’ai peut-être une mauvaise nouvelle… :pensive_face: Je ne pense pas que date_trunc fonctionne comme vous le souhaitiez :

image
image1402×330 10.1 KB

1 « J'aime »
7

Au cas où cela serait utile à quelqu’un qui tente cette adaptation, voici une version du rapport Top Traffic Sources en 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 "Domaine",
       cl.clicks AS "Clics",
       ct.topics AS "Sujets"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10
1 « J'aime »