Pull Event RSVP list via Data Explorer

Data & reporting
,
1

We are trying to boost our event attendance by finding users who have engaged with similar existing content. With the help of the AI bot, we were able to concoct the following query.

-- [params]
-- text :keyword
-- int :min_engagements
-- null string :tag_name

WITH keyword_posts AS (
    SELECT
        p.id AS post_id
    FROM
        posts p
    JOIN
        topics t ON t.id = p.topic_id
    LEFT JOIN
        topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN
        tags tg ON tg.id = tt.tag_id
    WHERE
        p.raw ILIKE '%' || :keyword || '%'
        AND (:tag_name is null OR tg.name = :tag_name)
),
user_engagement AS (
    SELECT
        ua.user_id,
        COUNT(DISTINCT ua.target_post_id) AS engaged_posts_count
    FROM
        user_actions ua
    JOIN
        keyword_posts kp ON kp.post_id = ua.target_post_id
    WHERE
        ua.action_type IN (1, 4, 5, 6)
    AND ua.user_id NOT IN (
        SELECT gu.user_id
        FROM group_users gu
        JOIN groups g ON g.id = gu.group_id
        WHERE g.name = 'developer_relations'
    )
    GROUP BY
        ua.user_id
)
SELECT
    ue.user_id,
    ue.engaged_posts_count
FROM
    user_engagement ue
WHERE
    ue.engaged_posts_count >= :min_engagements
ORDER BY
    ue.engaged_posts_count DESC

We’re giving users time to discover and RSVP to our events organically, but as we approach the event we want to send out more targeted invitations. To do this, we need to cross-reference the existing RSVP list and exclude them from the targeted user list. Is there a way to access this RSVP list via data-explorer?

The only way I have found to access this manually is by using the Export option on an Event object:
CleanShot 2024-08-23 at 14.11.28@2x

The problem is that this generates a zipped file that is PM’d to me, which I assume is not visible to Data Explorer.

2

I think you’re going to want to use the discourse_post_event_events and discourse_post_event_invitees tables for this.

3

I was able to plug away at the following with the bot, but it is returning 0 results (even though I can see 30+ people have responded. The query works if I remove the WHERE statement.

-- [params]
-- text :event_url = 'https://developer.sailpoint.com/discuss/t/deploying-the-beyondtrust-password-safe-connector-for-identityiq-with-michel-bluteau/68228'

SELECT 
    users.username AS "Username",
    invitees.status AS "RSVP Status",
    invitees.updated_at AS "Last Updated"
FROM 
    discourse_post_event_events AS events
JOIN 
    discourse_post_event_invitees AS invitees
ON 
    events.id = invitees.post_id
JOIN 
    users
ON 
    invitees.user_id = users.id
WHERE 
    events.url = :event_url
4

How about something like this:

-- [params]
-- text :keyword
-- int :min_engagements
-- null string :tag_name
-- post_id :event_post

WITH keyword_posts AS (
    
    SELECT 
        p.id AS post_id
    FROM posts p
      JOIN topics t ON t.id = p.topic_id
      LEFT JOIN topic_tags tt ON tt.topic_id = t.id
      LEFT JOIN tags tg ON tg.id = tt.tag_id
    WHERE p.raw ILIKE '%' || :keyword || '%'
      AND (:tag_name is null OR tg.name = :tag_name)
),

user_engagement AS (

    SELECT
        ua.user_id,
        COUNT(DISTINCT ua.target_post_id) AS engaged_posts_count
    FROM user_actions ua
    JOIN keyword_posts kp ON kp.post_id = ua.target_post_id
    WHERE ua.action_type IN (1, 4, 5, 6)
    AND ua.user_id NOT IN (SELECT user_id FROM group_users WHERE group_id = 41)
    GROUP BY ua.user_id
)

SELECT
    ue.user_id,
    ue.engaged_posts_count
FROM user_engagement ue
WHERE ue.engaged_posts_count >= :min_engagements
  AND ue.user_id NOT IN (SELECT user_id FROM discourse_post_event_invitees WHERE post_id = :event_post AND status = 0)
ORDER BY ue.engaged_posts_count DESC

For the event_url parameter it accepts the topic url but with the post number on the end so https://developer.sailpoint.com/discuss/t/deploying-the-beyondtrust-password-safe-connector-for-identityiq-with-michel-bluteau/68228/1 (rather than the topic version that doesn’t have the /1 on the end)

1 Like