Метрики страницы пользователя

[srinivas.chilukuri]

@SaraDev
Можешь предоставить SQL-запрос для метрик, доступных по адресу https://meta.discourse.org/u?cards=no&order=post_count?
Смотри изображение ниже:

метрики

• полученные лайки
• отправленные лайки
• просмотренные темы
• прочитанные сообщения
• дни посещений
• решения
• благодарности

---

Поскольку мы используем модераторов категорий, мы модифицируем запрос для модераторов для любой заданной группы.

[SaraDev (Sara Devlaeminck)]

Привет @srinivas.chilukuri,

Статистику страницы пользователя /u можно получить через Data Explorer, используя таблицу directory_items.

Метрики страницы каталога пользователей

-- [params]
-- int :period
-- Варианты периода:
-- 1. все время
-- 2. ежегодно
-- 3. ежемесячно
-- 4. еженедельно
-- 5. ежедневно
-- 6. ежеквартально

SELECT 
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM 
    directory_items di
WHERE 
    di.period_type = :period
ORDER BY 
    di.user_id

Вместо обычных параметров start_date и end_date данные из этой таблицы можно фильтровать с помощью поля period_type, где следующие значения соответствуют различным временным периодам, доступным на странице каталога:

• 1: все время
• 2: ежегодно
• 3: ежемесячно
• 4: еженедельно
• 5: ежедневно
• 6: ежеквартально

Пример результатов для этого отчета будет выглядеть так:

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

[srinivas.chilukuri]

@SaraDev
Мне нужны даты начала и окончания. Есть ли обходной путь для получения указанных метрик с датами начала и окончания?

• user
• likes_received
• likes_given
• topics_viewed
• topic_count
• post_count
• days_visited
• posts_read
• solutions
• cheers

Примечание: я получаю метрики для небольшой подвыборки из общего числа пользователей

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Мне нужны дата начала и дата окончания. Есть ли обходной путь для получения указанных метрик с указанием дат начала и окончания?

• пользователь
• полученные лайки
• отправленные лайки
• просмотренные темы
• количество тем
• количество сообщений
• дни посещений
• прочитанные сообщения
• решения
• благодарности

Если вы хотите просмотреть эти метрики для пользователей вашего сайта и отфильтровать их по конкретным датам начала и окончания, вам потребуется запрос, который извлекает данные для каждой метрики в отдельном CTE, а затем объединяет результаты в финальном операторе SELECT.

Вот как это может выглядеть:

Метрики пользователей

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT 
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.user_id
),
likes_given AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
user_metrics AS (
    SELECT 
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM 
        user_stats us
    WHERE 
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        us.user_id
),
solutions AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 15 
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
cheers AS (
    SELECT 
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM 
        gamification_scores gs
    WHERE 
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY 
        gs.user_id
)

SELECT 
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM 
    users u
LEFT JOIN 
    likes_received lr ON u.id = lr.user_id
LEFT JOIN 
    likes_given lg ON u.id = lg.user_id
LEFT JOIN 
    user_metrics um ON u.id = um.user_id
LEFT JOIN 
    solutions sol ON u.id = sol.user_id
LEFT JOIN 
    cheers ch ON u.id = ch.user_id
ORDER BY 
    u.id

Объяснение запроса:

1. Параметры:

• :start_date и :end_date — это параметры, определяющие диапазон дат для запрашиваемых данных.

2. Общие табличные выражения (CTE):

• likes_received: Подсчитывает количество полученных каждым пользователем лайков (action_type = 2) в указанном диапазоне дат.
• likes_given: Подсчитывает количество отправленных каждым пользователем лайков (action_type = 1) в указанном диапазоне дат.
• user_metrics: Агрегирует статистику пользователей, такую как просмотренные темы, прочитанные сообщения и дни посещений, для пользователей, сделавших свой первый пост в указанном диапазоне дат.
• solutions: Подсчитывает количество решений, предоставленных каждым пользователем (action_type = 15), в указанном диапазоне дат.
• cheers: Суммирует баллы геймификации для каждого пользователя в указанном диапазоне дат.

3. Финальный выбор:

• Основной запрос выбирает метрики вовлеченности пользователей для каждого пользователя, включая полученные лайки, отправленные лайки, просмотренные темы, прочитанные сообщения, дни посещений, предоставленные решения и полученные благодарности.
• Используется LEFT JOIN, чтобы включить всех пользователей, даже если у них нет активности в некоторых категориях, заполняя отсутствующие значения нулями с помощью COALESCE.

Пример результатов

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10
—	—	—	—	—	—	—	—

[srinivas.chilukuri]

@SaraDev

@SaraDev

Метрики пользователей

Мы использовали запрос, приведенный в посте выше, и у нас возникли следующие вопросы:

1. Является ли поле user_metrics из таблицы user_stats правильным источником для этой информации? Учитывая, что user_stats — это статическая таблица, обобщающая метрики пользователя с момента его присоединения к Discourse, она может не подходить для фильтрации метрик в определенном временном диапазоне (например, с определенной даты по другую дату).
2. Сравнение временных рядов (T/S C/O)
   Для заданного набора пользователей мы сравнили доступные данные за период времени на странице пользователя и обнаружили значительные расхождения.

Основные расхождения:

• topics_entered
• posts_read_count
• days_visited

Не могли бы вы уточнить, есть ли лучший способ получения метрик пользователей с учетом временных ограничений?

[Jagster (Jakke Flemming)]

PG::UndefinedColumn: ОШИБКА: столбец uv.topic_id не существует
СТРОКА 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Под...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Является ли поле user_metrics из таблицы user_stats правильным источником для этой информации? Учитывая, что user_stats — это статичная таблица, суммирующая показатели пользователя с момента его регистрации в Discourse, она может не подходить для фильтрации метрик в определённом временном диапазоне (например, с даты начала по дату окончания).

Вы правы: таблица user_stats является статичной и суммирует показатели пользователя за всё время с момента его регистрации в Discourse.

Вместо этого для фильтрации метрик по дате, таких как posts_read_count и days_visited, следует использовать таблицу базы данных user_visits для данных о постах. Для фильтрации метрики topics_entered по дате также нужно использовать таблицу topic_views.

@srinivas.chilukuri

Для заданного набора пользователей мы сравнили доступные данные за определённый период на странице пользователя и заметили существенные расхождения.

Расхождения, которые вы обнаружили, вызваны использованием таблицы user_stats вместо других таблиц, таких как user_visits и topic_views, для фильтрации этих статистических данных по дате.

Чтобы исправить это, мы можем обновить запрос, используя указанные таблицы базы данных:

Вот обновлённая версия запроса:

Метрики на странице пользователя

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT 
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.user_id
),
likes_given AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
user_metrics AS (
    SELECT 
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM 
        topic_views tv
    WHERE 
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY 
        tv.user_id
),
days_and_posts AS (
    SELECT 
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM 
        user_visits uv
    WHERE 
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY 
        uv.user_id
),
solutions AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 15 
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
cheers AS (
    SELECT 
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM 
        gamification_scores gs
    WHERE 
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY 
        gs.user_id
)

SELECT 
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM 
    users u
LEFT JOIN 
    likes_received lr ON u.id = lr.user_id
LEFT JOIN 
    likes_given lg ON u.id = lg.user_id
LEFT JOIN 
    user_metrics um ON u.id = um.user_id
LEFT JOIN 
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN 
    solutions sol ON u.id = sol.user_id
LEFT JOIN 
    cheers ch ON u.id = ch.user_id
ORDER BY 
    u.id

Обратите внимание, что при использовании этого метода данные о прочитанных постах (posts_read) в таблице user_visits имеют одно важное отличие: они не учитывают собственные посты пользователя, тогда как данные из таблицы user_stats включают и посты, написанные самим пользователем. Поэтому вы всё ещё можете заметить расхождения между этими двумя показателями в данном запросе и на странице пользователя.