Требования к сложному отчету о пользовательской активности

Создание сообщества Данные и отчетность
1

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

  • Мне также нужно включать сообщения, отправленные по электронной почте и на которые отвечали по почте (без дублирования подсчёта, если те же сообщения были прочитаны или отправлены при использовании форума онлайн). Текущие отчёты, похоже, исключают сообщения через email.

  • Мне нужно фильтровать эти результаты по определённому пользовательскому полю профиля (уникальный номер членства).

  • Мне нужно исключать результаты для пользователей с номерами членства, которые не входят в назначенный числовой диапазон.

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

  • Весовые коэффициенты (множители) для каждой из этих переменных активности должны быть настраиваемыми и задаваться администратором. Результаты должны округляться вниз до ближайших 5 баллов и ограничиваться заданным максимальным количеством баллов, устанавливаемым администратором.

  • Приятным дополнением было бы наличие разбивки по категориям тем или тегам для каждого пользователя.

  • В идеале этот отчёт должен автоматически формироваться и отправляться мне по электронной почте в назначенное время каждый год (это было бы вишенкой на торте).

Насколько всё это реализуемо?

Требуется ли для этого создание нового кастомного плагина или это возможно с помощью какого-нибудь сложного SQL-запроса в текущем плагине Data Explorer?

Или более разумным решением будет поиск относительно простой опции экспорта «всё», а затем обработка остального в Excel?

2

Здесь, скорее всего, лучше всего подойдет Data Explorer. Возможно, вам придется создавать разные запросы для каждой задачи (например, разбивка по пользователям будет отдельным запросом).

Единственное, что Data Explorer не может сделать, — это функция «автоматическая генерация и отправка мне по электронной почте». Если это требование обязательно, вы можете реализовать его, вызывая API Data Explorer из другой системы.

3

Спасибо, Дэвид.
Похоже, мне придётся разобраться в SQL-запросах.

Может ли один запрос принимать в качестве входных данных отфильтрованные результаты другого запроса? Также можно ли обязательно включить взаимодействие на основе списка рассылки? Хранится ли это как-то иначе? Я беспокоился, что оно было исключено из стандартных отчётов о активности по какой-то неочевидной, но непреодолимой причине.

4

Нет, вам придется копировать/вставлять фрагменты запроса вручную.

Таблица posts в базе данных содержит булево поле via_email, поэтому да, вы можете определить, какие сообщения были созданы через электронную почту :+1:

Однако в Discourse нет трекеров в рассылках, которые он отправляет, поэтому не будет возможности определить, было ли уведомление по электронной почте «прочитано» или нет.

5

Обрабатывается ли встроенная функциональность отчётов о активности пользователей через SQL-запросы, которые я могу скопировать откуда-либо и доработать, чтобы не тратить недели на изобретение велосипеда?

6

Вы смотрите на отчёт «Ежедневные активные пользователи»? Он генерируется по этой логике, которая использует ActiveRecord (то есть без сырого SQL). Но даже так, эта логика может стать полезной отправной точкой.

7

Я стремлюсь получить отчёт о активности по каждому пользователю в формате CSV за период с одной даты по другую. Конечная цель — присвоить каждому пользователю оценку в виде баллов активности за год (или иной период) на основе полученных/прочитанных сообщений онлайн или по электронной почте, отправленных сообщений онлайн или по электронной почте, а также с учётом того, что сообщения с лайками получают больше баллов. В качестве основы я рассматриваю отчёт, который отображается первым при переходе по пути Администратор/Пользователи, поскольку он уже выполняет многое из того, что мне нужно.

image
image868×321 44.3 KB

8

Логика каталога пользователей находится здесь: discourse/app/models/directory_item.rb at 1e66e4602f408d7b2d1105b6c228542dce7f4b38 · discourse/discourse · GitHub

9

Хм, похоже, это непростая задача для реализации через SQL (по крайней мере, для меня), так как мне придётся одновременно изучать достаточно SQL, чтобы сделать это с нуля, если не окажется ничего достаточно похожего, что можно было бы скопировать напрямую.
Последний раз я занимался программированием много лет назад в школе, когда признание в знании BASIC ещё не было таким неловким.

10

Да, я думаю, что для реализации этого потребуется разумное знание SQL. Если у вас есть бюджет на эту работу, вы можете найти кого-то, кто сможет помочь, в канале Marketplace.

11

Спасибо! У меня нет бюджета (форум приносит мало пожертвований, едва покрывающих базовые расходы на хостинг), но, похоже, мне всё равно придётся пойти по этому пути.

12

@Paul_King

Этот запрос может помочь.

https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167u=grayden_shand

13

Большое спасибо! Я попробовал, но получаю ошибку синтаксиса

PG::SyntaxError: ERROR: syntax error at or near “WITH”
LINE 13: WITH date_range AS (

(извините, я отправил вам личное сообщение в той теме, прежде чем понял, что вы и оригинальный автор — один и тот же человек!)

14

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

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

  • start_date: 2021-07-01
  • end_date: 2021-07-30
  • coverage: all
15

Привет, я попробовал несколько вариантов, но все они выдали тот же результат — например,

image
image1428×491 21.7 KB

16

Извините, мне не удалось воспроизвести ошибку.

Можете ли вы вставить запрос здесь так, как он у вас есть?

17 
SELECT 1-- охват: 'week', 'all' или 'date'
-- [параметры]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
18

Ах, я понял.

SELECT 1 в начале не является частью запроса и является причиной вашей проблемы. Это заполнитель, который появляется при создании нового запроса в Data Explorer. Удалите его, и всё должно заработать.

-- coverage: 'week', 'all', или 'date'
-- [params]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END
19

Спасибо @Grayden_Shand

Ошибка исчезла.

Если я могу немного еще вас побеспокоить, включают ли подсчеты, сгенерированные этим запросом, сообщения, отправленные по электронной почте, и ответы по электронной почте для пользователей в режиме списка рассылки? Если нет, как их можно включить?

Также, как можно добавить значение пользовательского поля профиля рядом с именем пользователя?

Есть ли у вас какие-либо советы о том, как определить имя нужного поля и реализовать это?

20

Да, должны. Как отметил Дэвид, таблица posts содержит булево поле via_email. В текущем запросе это поле игнорируется, и подсчитываются все посты, независимо от того, были ли они отправлены по электронной почте или нет.

Существует таблица с именем user_custom_fields. Чтобы включить конкретное пользовательское поле, необходимо выполнить соединение (JOIN) с этой таблицей.

Я бы, вероятно, сделал это в подзапросе post_summary.

Например:

...
), posts_summary AS (
SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received, cf.value as LABEL_FOR_CUSTOM_FIELD
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and cf.name == "YOUR CUSTOM FIELD NAME") 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
...

Я добавил столбец в предложение SELECT и новое предложение JOIN для таблицы user_custom_fields.

Обратите внимание, что вам нужно будет заменить "YOUR CUSTOM FIELD NAME" и LABEL_FOR_CUSTOM_FIELD на соответствующие значения.

Затем вам также потребуется обновить столбцы, которые вы выбираете в окончательном запросе.

...
SELECT ps.id, ps.username, ps.created_at, ps.LABEL_FOR_CUSTOM_FIELD, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
..

Вот как я бы подошёл к решению этой задачи.

Удачи!