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

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