Find all users who used "login with facebook"

Community Building Data & reporting
,

AI-generated summary

In the Data & reporting category, darix inquired about identifying users who utilize “login with Facebook” to assess the impact of discontinuing the feature. Johani referenced existing discussions on the topic, while sam suggested adding a built-in Data Explorer report for authentication methods. david agreed, noting future capabilities to track “last used date”. michebs provided three essential SQL queries to address the request:

  1. List of Facebook users: Retrieves active, non-staged users with Facebook accounts, displaying their names, emails, and profile images.
  2. List of all external logins: Generates a comprehensive list of users across multiple providers (Discord, Facebook, GitHub, Google, Twitter) with their associated details.
  3. Count per method: Aggregates the total number of active users per authentication provider, showing counts such as 500 for Google, 400 for GitHub, and 100 for Facebook.

ToddZ thanked the community for the queries, noting that the second query specifically resolved a pending question regarding OAuth user identification.

1

for reasons we want to discontinue that and would like to know the impact it would have on our user base. is there an easy way to find that out?

2

There’s some discussion about this here

3

@david once your work consolidating is done, can you add a quick built-in report to data explorer that counts users per auth method. I think it can help make decisions in some cases.

4

Definitely :+1:. Once the new stuff is done we will also be able to report on “last used date”, which should be useful for deciding which methods are important.

9

These Data Explorer queries can help in these cases.

1ª List of users per login with Facebook

WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    user_id,
    info->>'name' name_user,
    info->>'email' email_user,
    info->>'image' image_user
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)
    AND provider_name = 'facebook'
provider_name user_id name email image
facebook 1 User1 user1@gmail.com https://graph.facebook.com/.../photo1.jpg
facebook 2 User2 user2@yahoo.com https://graph.facebook.com/.../photo2.jpg

2ª List of users per external login method

  • discord
  • facebook
  • github
  • google_oauth2
  • twitter
WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    user_id,
    info->>'name' AS name,
    info->>'email' AS email,
    info->>'image' AS image
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)
provider_name user_id name email image
google_oauth2 1 User1 user1@gmail.com https://lh3.googleusercontent.com/.../p1.jpg
facebook 2 User2 user2@yahoo.com https://graph.facebook.com/.../p2.jpg
twitter 3 User3 user3@gmail.com http://pbs.twimg.com/profile_img/.../p1.jpg

3ª Number of users per external login method

WITH target_user_ids AS (
    SELECT id
    FROM users
    WHERE staged = false
        AND active = true
        AND last_seen_at IS NOT NULL)

SELECT 
    provider_name,
    COUNT(user_id) AS qtd
FROM user_associated_accounts ua
WHERE user_id IN (SELECT id FROM target_user_ids)
GROUP BY provider_name
UNION
SELECT 
    'github', 
    COUNT(user_id)
FROM   github_user_infos
WHERE user_id IN (SELECT id FROM target_user_ids)
ORDER BY provider_name
provider_name qtd
discord 10
facebook 100
github 400
google_oauth2 500
twitter 200
12

Thank you for posting these queries! 2ª just answered a question I had.

(Keyword boost: list oauth users, find oauth users, report of oauth users)