这是“热门流量来源”仪表板报告的 SQL 版本。
此仪表板报告提供了对指定日期范围内链接到 Discourse 网站帖子的热门外部来源的分析，还可以选择特定类别或其子类别。
通过识别哪些外部域链接到其社区，管理员可以评估营销工作的有效性，调整其内容策略以吸引更广泛的受众，探索与参与网站的协作机会，优化 SEO，并管理可能由不受欢迎的流量来源引起的任何潜在危机。
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true
SELECT
ind.name AS domain,
COUNT(*) AS clicks,
COUNT(DISTINCT p.topic_id) AS topics
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 categories c ON c.id = t.category_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::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
OR t.category_id = :category_id
OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit
参数
- 日期参数：
- 查询接受两个日期参数
:start_date和:end_date，它们定义了报告的日期范围。两个日期参数都接受YYYY-MM-DD格式的日期。
- 查询接受两个日期参数
- 类别参数：
:category_id：一个整数参数，可以设置为特定类别的 ID，以缩小分析范围到该类别内的帖子。如果设置为 null 或未提供，则考虑所有类别的帖子。:include_subcategories：一个布尔参数，用于控制是否包含指定:category_id的子类别中的帖子。如果设置为 true，报告将包括指向指定类别及其子类别中帖子的链接；如果设置为 false，则只考虑指定类别。
- 结果限制：
:limit：一个整数参数，将查询返回的行数限制为基于点击次数的前 N 个结果。查询中提供的默认值为 10。
SQL 查询说明
- SELECT：报告的结构旨在选择三条信息：
ind.name AS domain：此列将显示已将用户引荐到论坛的每个域的名称。COUNT(*) AS clicks：此列显示来自每个域的总入站链接点击次数。COUNT(DISTINCT p.topic_id) AS topics：此列表示通过这些入站链接访问的唯一主题的数量。
- FROM AND JOIN：查询聚合了来自多个表的数据：
incoming_links il：这是存储入站链接数据的表。posts p：与论坛帖子相关的表。topics t：包含主题数据的表。categories c：包含类别信息的表。incoming_referers ir：存储引荐链接信息的表。incoming_domains ind：目录化链接到论坛的域的表。INNER JOINs确保我们只考虑未被删除的帖子和主题，并将入站链接数据链接到正确的帖子、主题和引荐域。
- WHERE：此部分查询应用过滤器来缩小结果集：
- 它将记录限制为仅属于“regular”类型的记录，即普通论坛主题（而不是私人消息或其他特殊类型）。
- 它将日期过滤到仅包括在指定期间（
:start_date到:end_date）内创建的链接。 - 它根据提供的
:category_id参数应用类别过滤。如果:category_id为 null，则考虑所有类别。如果:include_subcategories为 true 且:category_id不为 null，则还包括指定类别的子类别。
- GROUP BY：查询按域名称 (
ind.name) 对数据进行分组，确保计数按每个不同的引荐域进行分区。 - ORDER BY and LIMIT：最终数据集按点击次数降序排序，从而优先处理具有最高引荐流量的域。
LIMIT :limit子句将结果数量限制为使报告简洁并专注于热门域。
示例结果
| domain | Clicks | Topics |
|---|---|---|
www.examplesite1.com |
1234 | 123 |
www.examplesite2.com |
56 | 67 |
www.examplesite3.com |
40 | 95 |
| … | … | … |