Dashboard Report - Daily Engaged Users

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Daily Engaged Users.

The SQL report will count the number of unique users that have liked or posted on a site each day.

--[params]
-- date :start_date
-- date :end_date

SELECT 
  ua.created_at::date AS day,
  COUNT(DISTINCT ua.user_id) AS engaged_users
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY ua.created_at::date ASC

Breakdown of the Query:

  • Date Parameters: The query accepts two date parameters, :start_date and :end_date, which allow you to specify the range for the report.
  • Selection: It selects the date of the action (ua.created_at::date) and counts the distinct user IDs (COUNT(DISTINCT ua.user_id)) who performed the actions, aliasing this count as engaged_users.
  • Data Source: The query pulls from the user_actions table (ua) and joins it with the topics table (t) to focus on actions related to topics.
  • Action Types: It filters the actions to include only those with types 1, 4, 5, and 12. These action types correspond to the following specific activities:
    • 1: Like
    • 4: New Topic
    • 15: Reply
    • 12: New Private Message
  • Grouping and Ordering: The results are grouped by the date of the action and ordered in ascending order, giving you a chronological view of the activity.

Example Results

day engaged_users
2023-01-01 136
2023-01-02 124
2023-01-03 187
2023-01-04 287
2023-01-05 110
2023-01-06 95
2023-01-07 73
5 Likes
2

This one is quite interesting when you see it laid out. It would be good to maybe have a version that included Reacting and Voting (polls, topic votes, post votes - maybe even post voting comments). :thinking:

2 Likes