Data explorer challenge: Generate list of members by location

Community Building Data & reporting

AI-generated summary

The discussion revolves around generating a list of members by location using the Data Explorer, prompted by a request to notify users in Pakistan. tobiasEigen notes that the location field is populated via SSO and isn’t available in user exports, making it difficult to target users by country. mikechristopher highlights the challenge of free-text entries in the location field, which complicates filtering. tobiasEigen clarifies that their site uses validated country data via SSO and suggests improvements like filtering by profile content or exporting profile details. DonH provides a SQL query to retrieve users by location, while ToddZ expands it to include email and locale. tobiasEigen shares that they ultimately used a WordPress dashboard with SSO integration to filter and export users by location, bypassing the need for a direct Data Explorer solution.

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.

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.

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.

4

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

6

질문을 제대로 이해하지 못했을 수도 있지만, 만약 이해했다면 다음과 같습니다.

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

또는 동일하게,

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

데이터 탐색기(Data Explorer)는 이중 인용 부호를 구분자로 허용하지 않는다는 점에 유의하세요.

7

좋네요! 제 문제를 해결해 줄 쿼리를 제안해 주셔서 감사드립니다. (아주 늦게까지 기다렸네요.) 다른 분들에게도 도움이 될 수 있을 것 같습니다. 저는 이제 나마티(Namati)에서 일하지 않습니다. 그곳에서는 파키스탄 회원들에게 특정 게시물을 표적으로 보내려 하고 있었습니다. 결국 이 유형의 문제를 해결한 방법은 워드프레스에 인사이트 대시보드를 만든 것이었습니다. 워드프레스는 WP Discourse 워드프레스 플러그인과 SSO를 사용하여 디스커스를 보완하도록 설정해 두었죠. 대시보드에서 위치를 비롯한 여러 기준으로 필터링할 수 있는 뷰를 만들었습니다.

그렇게 내보낸 목록을 스프레드시트로 가져와 사용자 이름이나 이메일 주소를 추출한 뒤, 디스커스에서 다양한 방식으로 활용할 수 있었습니다. 제가 선호하는 방법은 각 사용자에게 직접 개인 메시지를 보내 해당 사용자에게 특화된 기회를 알리는 것이었습니다.

8

Don의 쿼리를 조금 확장하여 위치 정보가 입력된 모든 사용자의 사용자 이름, 위치, 이메일 목록을 생성해 보았습니다. 일부 상황에서 흥미롭거나 유용할 수 있습니다:

SELECT u.username, u.locale, up.location, ue.email
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    LEFT JOIN user_emails ue ON u.id = ue.user_id
    -- 전체 사용자 목록을 보려면 WHERE 절을 주석 처리하세요
    WHERE up.location <> ''
    ORDER BY up.location