Отчет по панели управления - Реакции

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

Это SQL-версия отчета по реакциям для панели управления.

Этот отчет предоставляет ежедневный подсчет всех лайков и реакций на сообщения на сайте за указанный период времени.

Этот отчет помогает получить представление о вовлеченности пользователей в сообществе Discourse, измеряя частоту использования различных эмодзи-реакций и лайков на сообщения. Анализируя использование различных эмодзи-реакций, администраторы могут получить представление о том, как часто пользователи взаимодействуют с контентом, каковы их эмоциональные реакции на сообщения, а также выявить как популярные, так и малоиспользуемые эмодзи-реакции.

:information_source: Для работы этого отчета на вашем сайте должен быть включен плагин Discourse Reactions. Реакции, отображаемые в отчете, зависят от того, какие именно реакции включены в настройке сайта discourse_reactions_enabled_reactions.

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-17
 
SELECT
  r.day,
  COALESCE(l.likes_count, 0) as likes_count,
  sum(case when reaction_value = 'laughing' then reactions_count else 0 end)::int as laughing,
  sum(case when reaction_value = 'cry' then reactions_count else 0 end)::int as cry,
  sum(case when reaction_value = 'exploding_head' then reactions_count else 0 end)::int as exploding_head,
  sum(case when reaction_value = 'clap' then reactions_count else 0 end)::int as clap,
  sum(case when reaction_value = 'confetti_ball' then reactions_count else 0 end)::int as confetti_ball,
  sum(case when reaction_value = 'hugs' then reactions_count else 0 end)::int as hugs,
  sum(case when reaction_value = 'chefs_kiss' then reactions_count else 0 end)::int as chefs_kiss,
  sum(case when reaction_value = '100' then reactions_count else 0 end)::int as one_hundred,
  sum(case when reaction_value = '+1' then reactions_count else 0 end)::int as plus_one,
  sum(case when reaction_value = 'rocket' then reactions_count else 0 end)::int as rocket,
  sum(case when reaction_value = 'star_struck' then reactions_count else 0 end)::int as star_struck,
  sum(case when reaction_value = 'eyes' then reactions_count else 0 end)::int as eyes,
  sum(case when reaction_value = 'discourse' then reactions_count else 0 end)::int as discourse
FROM (
  SELECT
    date_trunc('day', drru.created_at)::date as day,
    drr.reaction_value,
    count(drru.id) as reactions_count
  FROM discourse_reactions_reactions as drr
  LEFT OUTER JOIN discourse_reactions_reaction_users as drru on drr.id = drru.reaction_id
  WHERE drr.reaction_users_count IS NOT NULL
  AND drru.created_at::date >= :start_date::date AND drru.created_at::date <= :end_date::date
  GROUP BY drr.reaction_value, day
) r
LEFT JOIN (
  SELECT
    count(pa.id) as likes_count,
    date_trunc('day', pa.created_at)::date as day
  FROM post_actions as pa
  WHERE pa.post_action_type_id = 2 
  AND pa.created_at::date >= :start_date::date AND pa.created_at::date <= :end_date::date 
  GROUP BY day
) l ON r.day = l.day
GROUP BY r.day, l.likes_count
ORDER BY r.day

Пояснение к SQL-запросу

Параметры

  • Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат YYYY-MM-DD.

Структура запроса

  • Внутренний запрос (Реакции): Запрос начинается с подзапроса, который выбирает дату (day), тип реакции (reaction_value) и количество реакций (reactions_count) из таблицы discourse_reactions_reactions, объединенной с таблицей discourse_reactions_reaction_users по полю reaction_id. Это соединение обеспечивает учет реакций, привязанных к конкретным пользователям.
  • Агрегация по типам реакций: Выбранные данные затем группируются по day и reaction_value, ограничивая диапазон выбранными датами начала и конца. Это используется для подсчета общего количества каждого типа реакции за каждый день в указанном диапазоне.
  • Сортировка итогов по реакциям: Для каждого типа реакции запрос использует оператор CASE для агрегации количества использований конкретной реакции и приводит результат к типу целого числа для получения чистого подсчета.
    • Возможно, вам потребуется изменить reaction_value = '...' в этом разделе в зависимости от того, какие реакции включены на вашем сайте.
  • Подзапрос подсчета лайков: Отдельный подзапрос используется для подсчета общего количества лайков (likes_count) за каждый день с использованием таблицы post_actions, где post_action_type_id соответствует лайкам.
  • Объединение данных: Внешний запрос затем объединяет подсчет лайков с подсчетом реакций, выполняя соединение по полю day.
  • Финальный выбор: Внешний оператор SELECT формирует итоговый вывод, содержащий day, количество лайков (likes_count) и количество каждого типа реакции. Если для конкретного дня нет данных о лайках, функция COALESCE обеспечивает отображение нуля вместо NULL.
  • Сортировка результатов: Результаты сортируются по дате (r.day) для создания временного ряда вовлеченности на сайте.

Столбцы

  • day: Дата, за которую подсчитывались реакции и лайки.
  • likes_count: Общее количество лайков за каждый день.
  • Каждый тип реакции (laughing, cry, exploding_head и т. д.): Отдельные столбцы, показывающие общее количество каждого типа реакции за день.

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

day likes_count laughing cry exploding_head clap confetti_ball hugs chefs_kiss one_hundred plus_one rocket star_struck eyes discourse
2023-12-16 13 0 3 0 3 1 0 0 0 5 2 2 1 0
2023-12-17 17 1 0 2 2 0 0 0 0 4 4 1 2 0
2023-12-18 46 0 1 0 6 0 1 3 0 27 3 4 5 0
2

И небольшое предупреждение для нас, не-разработчиков… эти реакции «зашиты» в код и используются именно так, как есть. Это также означает, что если доступный набор реакций когда-либо изменится, здесь будут отображаться неверные данные за прошлые периоды.

У меня есть ощущение, что подсчёт идёт неправильно, если, например, реакция по умолчанию была изменена с :heart: на :+1:.

3

@JammyDodger исправил запрос, который я использовал и который учитывает это, насколько я понимаю: