User Page Metrics

@SaraDev
can u provide the sql query for metrics available at https://meta.discourse.org/u?cards=no&order=post_count
refer image below

metrics

  • like received
  • like given
  • topics viewed
  • posts read
  • days visited
  • solutions
  • cheers

we are utilizing category moderators hence we are modifying the moderator query to any given group

1 Like

Hi @srinivas.chilukuri,

The /u user page statistics can be retrieved via the Data Explorer by using the directory_items table.

User Directory Page Metrics

-- [params]
-- int :period
-- Period Options:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT 
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM 
    directory_items di
WHERE 
    di.period_type = :period
ORDER BY 
    di.user_id

Instead of the typical start_date and end_date parameters, data from this table can be filtered using the period_type field, where the following values correspond to the different time periods available on the directory page:

  • 1: all time
  • 2: yearly
  • 3: monthly
  • 4: weekly
  • 5: daily
  • 6: quarterly

Example Results for this report would look like:

user likes_received likes_given topics_viewed topic_count post_count days_visited posts_read solutions cheers
Username1 4 17 250 69 116 480 217 10 844100
Username2 2 5 47 0 2 43 59 1 112305
Username3 0 4 2 0 0 2 7 0 3100
3 Likes

@SaraDev
I require the start and end date . is there a workaround for getting the given metrics with start and end date

  • user
  • likes_received
  • likes_given
  • topics_viewed
  • topic_count
  • post_count
  • days_visited
  • posts_read
  • solutions
  • cheers

Note : I am getting metrics for a small subset of total users

If you want to view those metrics for users on your site and filter by specific start and end dates, you would need a query that pulls the data for each metric in a separate CTE, and then combine the results together in a final SELECT statement.

Here’s what that would look like:

User Metrics

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT 
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.user_id
),
likes_given AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
user_metrics AS (
    SELECT 
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM 
        user_stats us
    WHERE 
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        us.user_id
),
solutions AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 15 
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
cheers AS (
    SELECT 
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM 
        gamification_scores gs
    WHERE 
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY 
        gs.user_id
)

SELECT 
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM 
    users u
LEFT JOIN 
    likes_received lr ON u.id = lr.user_id
LEFT JOIN 
    likes_given lg ON u.id = lg.user_id
LEFT JOIN 
    user_metrics um ON u.id = um.user_id
LEFT JOIN 
    solutions sol ON u.id = sol.user_id
LEFT JOIN 
    cheers ch ON u.id = ch.user_id
ORDER BY 
    u.id

Query Explanation:

  1. Parameters:
  • :start_date and :end_date are parameters that define the date range for the data being queried.
  1. Common Table Expressions (CTEs):
  • likes_received: Counts the number of likes received by each user (action_type = 2) within the specified date range.
  • likes_given: Counts the number of likes given by each user (action_type = 1) within the specified date range.
  • user_metrics: Aggregates user statistics such as topics viewed, posts read, and days visited for users who made their first post within the specified date range.
  • solutions: Counts the number of solutions provided by each user (action_type = 15) within the specified date range.
  • cheers: Sums up the gamification scores for each user within the specified date range.
  1. Final Selection:
  • The main query selects user engagement metrics for each user, including likes received, likes given, topics viewed, posts read, days visited, solutions provided, and cheers received.
  • It uses LEFT JOIN to ensure all users are included, even if they have no activity in some categories, filling in with zeros using COALESCE.

Example Results

user_id likes_received likes_given topics_viewed posts_read days_visited solutions cheers
1 10 5 20 100 15 2 30
2 0 3 5 20 5 0 10
3 Likes

@SaraDev

We used the query provided in the post above and have the following questions:

  1. Is user_metrics from the user_stats table the correct source for this information? Given that user_stats is a static table summarizing a user’s metrics since they joined Discourse, it may not be ideal for filtering metrics within a specific time range (e.g., from a start date to an end date).
  2. Time-Series Comparison (T/S C/O)
    For a given set of users, we compared the available time-period data on the user page and noticed significant discrepancies.

Key Discrepancies:

  • topics_entered
  • posts_read_count
  • days_visited

Could you clarify if there’s a better way to retrieve time-bound user metrics?

2 Likes
PG::UndefinedColumn: ERROR:  column uv.topic_id does not exist
LINE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

You are correct that the user_stats table is a static table summarizing a user’s lifetime metrics since they joined Discourse.

Instead, for filter metrics by date like posts_read_count and days_visited, we would want to use the user_visits database table for posts. We would also want to use the topic_views table for filtering the topics_entered metrics by date.

The discrepancies you observed stem from using the user_stats table instead of other tables like user_visits and topic_views to filter those statistics by date.

To address this, we can update the query to use those database tables instead:

Here’s an updated version of the query:

User Page Metrics

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT 
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.user_id
),
likes_given AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
user_metrics AS (
    SELECT 
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM 
        topic_views tv
    WHERE 
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY 
        tv.user_id
),
days_and_posts AS (
    SELECT 
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM 
        user_visits uv
    WHERE 
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY 
        uv.user_id
),
solutions AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 15 
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
cheers AS (
    SELECT 
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM 
        gamification_scores gs
    WHERE 
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY 
        gs.user_id
)

SELECT 
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM 
    users u
LEFT JOIN 
    likes_received lr ON u.id = lr.user_id
LEFT JOIN 
    likes_given lg ON u.id = lg.user_id
LEFT JOIN 
    user_metrics um ON u.id = um.user_id
LEFT JOIN 
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN 
    solutions sol ON u.id = sol.user_id
LEFT JOIN 
    cheers ch ON u.id = ch.user_id
ORDER BY 
    u.id

Note that with this method, the posts_read data in the user_visits table has one important distinction - it does not count a user’s own posts, while the data from the user_stats table does include self-authored posts, so you may still find a difference between these two statistics in this query and on the User Page.

1 Like