Query to support low friction signup at an in-person event via mailto and staged users

data & reporting
1

This query returns all users (including their emails) who PM / email into a group inbox, excluding the members of the group.

The use case is for a low friction way to enable people at a physical event to create a staged user for my site.

I’ve found that at an in-person event, many potential users struggle to complete the sign-up process (which includes typing their email address (correctly), username choosing, and password generation as a minimum. It also requires validation of email and subsequent signing in. This is a big ask when standing around chatting to people / listening to a speaker / eating food / etc!

This can be lessened somewhat with OAuth2, but there is still some stuff to do and the less technically savvy seem to struggle with it too.

My Solution

  1. Create a semi-private group with a catchy email-in address (i.e. join@mycommunity.url)

  2. Present a QR code which encodes a mailto: link to that address

  3. Put the good folk who will manage the subsequent joining up of these people in the group

  4. Add the following query to Data Explorer, and give access to the group:

-- [params]
-- string :group_name

WITH group_members AS (
    SELECT gu.user_id 
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name
),

non_group_users AS (
    SELECT u.id, u.username, ue.email, u.created_at
    FROM users u
    JOIN user_emails ue ON ue.user_id = u.id
    WHERE NOT EXISTS (
        SELECT 1 
        FROM group_members gm 
        WHERE u.id = gm.user_id
    ) AND ue.primary = TRUE
),

group_messages AS (
    SELECT tg.topic_id 
    FROM topic_allowed_groups tg
    JOIN groups g ON g.id = tg.group_id
    WHERE g.name = :group_name
),

group_message_participants AS (
    SELECT gm.topic_id, tau.user_id AS tau_user_id FROM group_messages gm
    JOIN topic_allowed_users tau ON tau.topic_id = gm.topic_id
    JOIN non_group_users ngu ON ngu.id = tau.user_id
)

SELECT ngu.username, ngu.email, ngu.created_at FROM group_message_participants gmp
JOIN non_group_users ngu ON ngu.id = gmp.tau_user_id
GROUP BY ngu.username, ngu.email, ngu.created_at
ORDER BY ngu.username

There is a bit of chasing up to do afterwards, but at least you capture their email addresses reliably in a way that only requires two or three clicks / touches of the user!

3 Likes