The discussion revolves around extracting user data from a Discourse platform. Maayan_Mizrahi asks how to determine how long users stay on a topic, identify users who have posted new topics, and find users’ countries. jericson suggests using the post_timings table for time spent on topics, though notes it only includes registered users and recommends Google Analytics for broader data. For identifying users who posted new topics, he provides a SQL query to find users’ first posts. Regarding country information, he mentions that users may enter their location in their profile, which can be retrieved via user_profiles, and alternatively, ip_address in the users table can be used for geolocation. Maayan_Mizrahi thanks jericson for the helpful insights.
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.
Thank you @jericson ! this is helpful