Data explorer challenge: Generate list of members by location

Data & reporting
1

On our site, we populate the location field via SSO with the country where the member is based. I had a request this week from a moderator who wanted to alert members in Pakistan about a new resource published specific to Pakistan. He wanted to do this by mentioning all of them by name in a post.

I looked into it and found that I don’t know an easy way to do this via the UI. Location isn’t even provided in user exports which surprised me. Am I missing something?

Data explorer seems the low hanging fruit home for solving this problem. Has anyone created a data explorer query to search the user list by location and spit out a list of usernames who share that location?

A better solution for the longer term to this specific problem might be a new feature to be able to mention users by country somehow, e.g. !pakistan or some such.

1 Like
2

The issue with the location field as it is is that is free text entry - so whilst some people could have put in Pakistan others could have just put in India for example. I would love to be able to use a localised mention system or at least some visibility on where members are from but whilst its free text and not validated or from a drop down its going to prove difficult.

1 Like
3

Thanks for the thoughts. On my site it’s not a freetext field, actually - it’s handled via SSO externally and is always countries. I’m sure many sites do this. In any case this query doesn’t have to look for a perfect match.

Maybe another approach would be to use the user list, which currently only allows filtering by username. An additional filter by profile option would be interesting - e.g. show me everyone who indicates Pakistan in their profile bio or location. And then an admin link to export directly from there. :wink:

All of this would be moot if profile details were included in user export - let me check again to see if I have it right that location is not actually exported. That really surprised me.

5 Likes
4

Did you get the country filter working? I would be very interested in seeing it. My members really like to group per country

2 Likes
6

Maybe I don’t understand the question but, if I do, then

SELECT u.username
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    WHERE location = 'Pakistan'

or, equally,

SELECT username from user_profiles, users 
where user_id = id and location = 'Pakistan'

Note that the Data Explorer does not accept double quotes as delimiters.

7

Nice! Thanks for the (very belated) offer of a query to solve my problem. Maybe it will be helpful to others. I no longer work at Namati where we were trying to target a post to members in Pakistan. The way we solved this type of problem in the end was by creating an insight dashboard in WordPress, which we had set up to accompany Discourse using the WP Discourse wordpress plugin and SSO. In the dashboard we created a view allowing us to filter by location and many other criteria besides.

We could then export that list to a spreadsheet and grab the usernames or email addresses to use in various ways in Discourse. My preferred way was to send a PM to each user directly to let them know about an opportunity specific to them.