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

Создание сообщества Данные и отчетность
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

В Data Explorer это было удалено, а мне нужно запускать более длительные запросы, чем в дашборде. Я искал возможность скопировать или импортировать запрос, но безрезультатно. Где-нибудь он доступен?

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

Спасибо, Саймон, это было очень полезно (и не потребовало изменений спустя 2 года :+1: :sweat_smile: ). На мой взгляд, это должно быть одним из стандартных подробных отчётов.

Я часто думаю: «Боже, кто же делает все эти рефералы??»