Is the info Top Referred Topics/ Top Traffic Sources stored in a table in the database?

data & reporting
1

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!

1 Like
2

You can find them with the data explorer plugin.

2 Likes
3

Thanks. Good call. Let me go take a look at the tables/ fields to see how they are stored.

1 Like
5

The data explorer 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?

8

Here is a Data Explorer 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.

--[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
5 Likes
9

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.

--[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
7 Likes
13

Thanks Simon, that was really useful (and required no changes 2 years later :+1: :sweat_smile: ). Should be one of the standard detailed reports imho.

I often think: “goodness, who is doing all those referals??”

2 Likes