Find all users who used "login with facebook"

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?

There’s some discussion about this here

5 Likes

@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.

5 Likes

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.

6 Likes

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)


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
6 Likes