# Is the info Top Referred Topics/ Top Traffic Sources stored in a table in the database?
**URL:** https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [November 2, 2017, 4:07pm UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301 "2017-11-02T16:07:09Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@ckshen](https://meta.discourse.org/u/ckshen)
#### Post date: [November 2, 2017, 4:07pm UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/1 "2017-11-02T16:07:09Z")
Quick question for the community- I see in the dashboard we have a top 10 for Top referred topics and traffic sources. Is this information stored in a table in a database? What I am most interested in is the records outside the top 10. Thanks for any tips!
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [November 2, 2017, 7:22pm UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/2 "2017-11-02T19:22:52Z")
You can find them with the [data explorer](https://meta.discourse.org/t/32566?silent=true) plugin.
---
### Author:  [@ckshen](https://meta.discourse.org/u/ckshen)
#### Post date: [November 3, 2017, 4:16pm UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/3 "2017-11-03T16:16:54Z")
Thanks. Good call. Let me go take a look at the tables/ fields to see how they are stored.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [December 21, 2019, 12:28am UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/4 "2019-12-21T00:28:50Z")
---
### Author:  [@anon48433008](https://meta.discourse.org/u/anon48433008)
#### Post date: [December 21, 2019, 12:59am UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/5 "2019-12-21T00:59:18Z")
The [data explorer](https://meta.discourse.org/t/32566?silent=true) had this removed and I wanted to run longer quires than the dashboard, I’ve searched for the query to copy/import with no luck, is it available anywhere?
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [January 10, 2020, 2:34am UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/8 "2020-01-10T02:34:23Z")
Here is a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query that returns the top referred topics for a given time period. I’ll add a query for the top traffic sources soon.
The query returns a list of Discourse topics, and the number of times links to each topic have been clicked from an external source. The query requires you to supply start\_date and end\_date parameters in the form ‘yyyy-mm-dd’, for example `2020-01-08`. Results are ordered by click count in descending order. The top 100 referred topics for the time period are returned. If you need more results than that, adjust the query’s `LIMIT` value.
```sql
--[params]
-- date :start_date
-- date :end_date
SELECT
t.id AS topic_id,
COUNT(p.id) AS external_click_count
FROM incoming_links il
JOIN posts p
ON p.id = il.post_id
JOIN topics t
ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date::date AND :end_date::date
GROUP BY p.id, t.id
ORDER BY external_click_count DESC
LIMIT 100
```
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [January 11, 2020, 4:18am UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/9 "2020-01-11T04:18:50Z")
For traffic sources, give this query a try.
It returns the traffic source domain name, the number of clicks from the traffic source within the time period, and the number of distinct topics that were linked to from the traffic source. Results are ordered by click count in descending order. The query requires you to set `start_date` and `end_date` parameters. Dates should be in the form ‘yyyy-mm-dd’, for example `2020-01-09`.
The query limits the number of results to 100. If you need to change that, adjust the query’s `LIMIT` value.
```sql
--[params]
-- date :start_date
-- date :end_date
WITH links AS (
SELECT
ind.name,
t.id AS topic_id
FROM incoming_links il
JOIN posts p
ON p.id = il.post_id
JOIN topics t
ON t.id = p.topic_id
JOIN incoming_referers ir
ON ir.id = il.incoming_referer_id
JOIN incoming_domains ind
ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date::date AND :end_date::date
)
SELECT
name,
COUNT(name) AS clicks,
COUNT(DISTINCT topic_id) AS topics
FROM links
GROUP BY name
ORDER BY clicks DESC
LIMIT 100
```
---
### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [March 16, 2022, 9:45pm UTC](https://meta.discourse.org/t/is-the-info-top-referred-topics-top-traffic-sources-stored-in-a-table-in-the-database/73301/13 "2022-03-16T21:45:32Z")
Thanks Simon, that was _really_ useful (and required no changes 2 years later 👍 😅 ). Should be one of the standard detailed reports imho.
I often think: “goodness, who is doing all those referals??”