Data Explorer - % of active members by month

Community Building Data & reporting

AI-generated summary

The discussion is about creating a Data Explorer query to calculate the percentage of active community members by month. Konrad_Sopala defines an active member as someone who has liked or replied to at least one thread in a given month. michebs provides a SQL query that takes into account the cumulative number of users up to each month and calculates the percentage of active users based on this number. The query joins user actions with the cumulative user count and filters for action types 1 and 5 (likes and replies). Konrad_Sopala confirms that the solution makes sense and thanks michebs for their help.

1

Hey there to everyone playing and exploring Data Explorer plugin!

Thanks for helping with all the previous questions @michebs . I’ve got one more regarding data explorer queries.

Has someone ever created a query that fetches % of active users by month?

Active means they either liked or replied in some threads in that month, it doesn’t matter how many times but it needs to be at least 1. % is calculated based on the number of all members of the community. So let’s say we have two people who engaged in a month. One liked some post and the other one replied - so it’s two and we divide that by the sum. of all the members we have in our community.

2

I hope it helps.
It is worth mentioning that the total number of users varies over time, so what is being considered in the query is the cumulative number of users up to the month and year that the action was performed.

WITH tt_users_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

total_users AS (
    SELECT
        year, 
        month, 
        SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
    FROM tt_users_by_month ORDER BY year, month
)

SELECT 
    date_part('year', ua.created_at) AS year, 
    date_part('month', ua.created_at) AS month,
    TRUNC(COUNT(DISTINCT user_id)::decimal/tu.total*100,2) AS "%"
FROM 
    user_actions ua
INNER JOIN total_users tu ON (date_part('year', ua.created_at) = tu.year AND date_part('month', ua.created_at) = tu.month)
WHERE action_type IN (1,5)
	GROUP BY date_part('year', created_at), date_part('month', created_at), total
3

Yep totally makes sense! Thanks!