How to find how long a user has viewed a topic/who has posted new topics/country of users

data & reporting
1

Hello,

I would like to know:

  1. How long do users stay on the page of specific topic? Can I get the information from post_timings table? or should I take it from other source?
  2. Where can I find the data of users who have posted new topic?-
    image
    image1179×105 18.8 KB
  3. Where can I get the country of the user?

Thanks in advance

1 Like
2

I assume you have Data Explorer installed.

It looks like you can get it from post_timings:

select topic_id,
       count(user_id) users,
       round(sum(msecs/(60*1000.0)),2)/count(user_id) avg_minutes
from post_timings
group by topic_id
order by avg(msecs) desc

I should note that only includes people who registered. You might want to use Google Analytics to get all readers.

I’m not sure what data you are looking for. Here’s how to find each user’s first post:

select p.id post_id
from posts p
where p.id = (select min(id) from posts where user_id = p.user_id)

Add and post_number = 1 if you want to find users who started by posting a new topic rather than starting by replying to an existing topic. Not sure if either of these are helpful.

People can enter their location, which might even be recognizable as a country:

select user_id, location
from user_profiles

In my experience, people don’t usually bother. So Google Analytics might be more useful.

There’s also ip_address in the users table, which can be used to look up location data.

1 Like
3

Thank you @jericson ! this is helpful