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

Community Building Data & reporting

AI 生成的摘要

The discussion revolves around accessing detailed data on top referred topics and traffic sources beyond the dashboard’s top 10 limit. ckshen asks if this information is stored in a database table, and pfaffman suggests using the Data Explorer plugin. simon provides two SQL queries for retrieving the top referred topics and traffic sources, including parameters for date ranges and adjustable limits. anon48433008 notes that the Data Explorer feature has been removed and seeks access to the queries, which simon then shares. merefield praises the queries for their usefulness and suggests they should be standard reports.

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!

2

You can find them with the data explorer plugin.

3

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

5

数据探索器已移除了该功能，而我希望运行比仪表板更长时间的查询。我搜索了相关查询以便复制/导入，但一无所获。请问该功能是否在其他地方可用？

8

这是一个 Data Explorer 查询，用于返回指定时间段内被引用次数最多的主题。我很快会添加一个关于主要流量来源的查询。

该查询返回 Discourse 主题列表，以及从外部来源点击每个主题链接的次数。查询要求您提供 start_date 和 end_date 参数，格式为 ‘yyyy-mm-dd’，例如 2020-01-08。结果按点击次数降序排列。将返回该时间段内被引用次数最多的前 100 个主题。如果您需要更多结果，请调整查询中的 LIMIT 值。

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

对于流量来源，请尝试运行此查询。

该查询将返回流量来源的域名、在指定时间段内来自该来源的点击次数，以及从该来源链接到的不同主题的数量。结果按点击数降序排列。运行此查询需要设置 start_date 和 end_date 参数。日期格式应为 ‘yyyy-mm-dd’，例如 2020-01-09。

该查询默认限制返回结果数量为 100。如需更改，请调整查询中的 LIMIT 值。

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

谢谢 Simon，这确实很有用（而且两年后仍然无需更改 :+1: :sweat_smile:）。依我看，这应该是标准的详细报告之一。
我常常在想：“天哪，到底是谁在做所有这些推荐？？”