Month over month top traffic sources report?

Community Building Data & reporting
,

AI-generated summary

User nachocardozop seeks to generate a month-over-month top traffic sources report, showing how sources changed each month in a single graph or table.

nachocardozop shared a screenshot of their current data, which only shows totals for a full 5-month period.

olivia provided a SQL query to help nachocardozop achieve this, using a WITH clause to extract month, domain, and visit count, and a LAG function to calculate the previous month’s visit count.

JammyDodger asked if olivia had tested the query, and later mentioned that the date_trunc function might not be working as intended.

JammyDodger shared an alternative version of the Top Traffic Sources report in SQL, which uses WITH clauses to count clicks and topics, and joins these results to display the top domains by clicks and topics.

1

Hello,

I’m trying to generate a month-over-month top traffic sources report. It would be cool to see how our sources changed each month, all within one graph/table. Is there any way to get this data?

Currently I can only get the totals for the full 5 month period that I am interested in:

image
image1231×691 36.5 KB

Thanks,

Nacho

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

Hi Just came back from my dinner and updated the query; you could please have a try, if you have any progress, please let me know :smiley:. My discourse server is a newly created one so there aren’t sufficient data to query, I created some dummy data in my SQL Server, then transferred it to PGSQL.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

Just to check, have you tested this one out this time? :slight_smile:

I may have some bad news… :frowning: I don’t think the date_trunc is working as you what it to:

image
image1402×330 10.1 KB

7

In case it’s useful to anyone attempting this adaptation, here’s a version of the Top Traffic Sources report 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