# Month over month top traffic sources report?
**URL:** https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports
**Created:** [June 1, 2023, 6:18pm UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933 "2023-06-01T18:18:23Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@nachocardozop](https://meta.discourse.org/u/nachocardozop)
#### Post date: [June 1, 2023, 6:18pm UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933/1 "2023-06-01T18:18:24Z")
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:
Thanks,
Nacho
---
### Author:  [@olivia](https://meta.discourse.org/u/olivia)
#### Post date: [June 2, 2023, 5:31am UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933/2 "2023-06-02T05:31:50Z")
```plaintext
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 😃. 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.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 2, 2023, 6:43am UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933/5 "2023-06-02T06:43:46Z")
Just to check, have you tested this one out this time? 🙂
I may have some bad news… ☹ I don’t think the `date_trunc` is working as you what it to:
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [June 4, 2023, 5:23am UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933/7 "2023-06-04T05:23:58Z")
In case it’s useful to anyone attempting this adaptation, here’s a version of the Top Traffic Sources report In SQL:
```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
```