Dashboard Report - Reactions

data & reporting
, , ,
1

This is an SQL version of the Dashboard Report for Reactions.

This report provides a daily count of all likes and reactions to posts across a site over a specified date range.

This report helps provide a snapshot of user engagement within a Discourse community by measuring the frequency of various reaction emojis and likes on posts. By analyzing the usage of different reaction emojis, administrators can gain insights into how often users interact with content, users’ emotional responses to posts, and identify both popular and underused reaction emojis.

:information_source: This report requires the Discourse Reactions plugin to be enabled on your site. The reactions available on the report will depend on which specific reactions have been enabled with the discourse_reactions_enabled_reactions site setting.

-- [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 Query Explanation

Parameters

  • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.

Query Structure

  • Inner Query (Reactions): The query starts with a subquery that selects the date (day), the type of reaction (reaction_value), and the count of reactions (reactions_count) from the discourse_reactions_reactions table joined with discourse_reactions_reaction_users on the reaction_id. This join ensures we are accounting for user-specific reactions.
  • Aggregation by Reaction Types: The selected data is then grouped by day and reaction_value, restricting the range to the selected start and end dates. This is used to calculate the total count of each type of reaction for each day in the specified date range.
  • Sorting Reaction Totals: For each type of reaction, the query uses a CASE statement to aggregate the number of times the specific reaction is used and casts the result as an integer for a clean count.
    • You may need to adjust the reaction_value = '...' in this section depending on which reactions are enabled on your site.
  • Likes Count Subquery: A separate subquery is used to calculate the total number of likes (likes_count) for each day using the post_actions table where post_action_type_id corresponds to likes.
  • Combining Data: The outer query then combines the counting of likes with the reaction counts by joining on the day field.
  • Final Selection: The outermost SELECT generates the final output with the day, the number of likes (likes_count), and counts of each reaction type. If there’s no data for likes on a specific day, the COALESCE function ensures a zero is displayed instead of NULL.
  • Ordering the Results: The results are ordered by date (r.day) to create a time series of engagement on the site.

Columns

  • day: The date on which the reactions and likes were counted.
  • likes_count: Total number of likes for each day.
  • Each reaction type (laughing, cry, exploding_head, etc.): Individual columns showing the total count of each reaction type per day.

Example Results

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
1 Like
2

And heads up for us not-so-dev types… those reactions are hard coded there and what are in use here. That means too that if offered reactions has changed at some point this shows wrong older times.

I have feeling this is counting wrong if default has changed from :heart: to :+1: for example.

3

@JammyDodger fixed a query I was using that accounts for this, I believe:

2 Likes