What's the difference between "User Visits" and "Active Users"


#21

This one gives you people that posted within the timeframe – it would need to be edited if you want to include likes.

-- [params]
-- date :date_from
-- date :date_to
-- int  :min_posts = 1

WITH user_activity AS (
    SELECT p.user_id, count (p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date BETWEEN :date_from::date AND :date_to::date
        AND t.deleted_at IS NULL
        AND t.visible = TRUE
        AND p.deleted_at IS NULL
        
    GROUP BY p.user_id
)
SELECT COUNT(user_id)
FROM user_activity
WHERE posts_count >= :min_posts

What cool data explorer queries have you come up with?
(Alex Armstrong) #22

Thank you so much, Sarah. This is really helpful.

I did some archaeological work in my statistics archive and discovered that last year I had used a somewhat different version of this query to get the numbers for “Monthy Active Users”. (Also provided by you :heart: )

The old code has a section like this:

    WHERE p.created_at::date BETWEEN :date_from::date AND :date_to::date
        AND t.deleted_at IS NULL
        AND t.visible = TRUE
        AND t.closed = FALSE
        AND t.archived = FALSE
        AND t.archetype = 'regular'
        AND p.deleted_at IS NULL

The new code is missing the following lines:

        AND t.closed = FALSE
        AND t.archived = FALSE
        AND t.archetype = 'regular'

If I understand this correctly, removing these lines will include closed and archived topics in the count. The archetype line is more puzzling.

I think that removing the archetype line means that private messages are included in the count. Is this the case?

And another question: does this count take into account both topics and posts, or only the latter?


#23

Yes.

It counts all posts (including OPs).


(Henrik Lenberg) #24

The “User Visits” number for “Today” seems to be counting unique users that have visited. This is good! However, the “Last 7 Days” number seems to be the sum of unique daily users the last seven days, which to me makes no sense at all. IMHO user visits have to be unique users visiting the site one or more times during a certain time frame. Otherwise it’s better to look at page views or something like that.


(Ruth Cheesley) #25

Just adding a voice, this would be a very handy metric to have on the dashboard to track, and some clarity on the terms as expressed above is really helpful, especially when coming from other platforms where the same words mean quite different things.

It’s a common metric to be reported on and often a performance indicator (% of active members) so would be great if it was there on the dashboard.


#26

Hi Ruth,
You can see details of the upcoming dashboard here. Note that it might be a few iterations before everything you see there is included.


(Mtan) #27

Are unique visits part of the new dashboard definition? Checking as this is already 8 months old. Also looking for an easy way to get unique visits / month