PM / nuture after tag added

Feature
1

In looking at patterns of user behavior, we’ve recently starting paying attention to when users answer themselves. When a user responds to his own OP in a thread, is it a #bump or a #self-solve? I’ve written a report to

Find threads where users respond to themselves. 
-- Goal show threads where users respond to themselves

WITH

SonarSourcers AS (
    SELECT u.id AS user_id
    FROM groups g
    INNER JOIN group_users gu ON g.id=gu.group_id
    INNER JOIN users u ON u.id = gu.user_id
    WHERE g.name='sonarsourcers'
),

tagged_topics AS (
    SELECT tt.topic_id
    FROM topic_tags tt
    JOIN tags t on t.id=tt.tag_id
    WHERE name in ('me-too', 'bump', 'self-solve')
),


-- find 'regular' topics created by normal users
topic_user AS (
    SELECT id as topic_id, user_id, created_at
    FROM topics
    LEFT JOIN SonarSourcers ss USING(user_id)
    LEFT JOIN tagged_topics tt on topics.id = tt.topic_id
    WHERE ss.user_id IS NULL  -- omit topics started by SonarSourcers
        AND tt.topic_id IS NULL -- omit topics tagged me-too, bump or self-solve
        AND visible = TRUE
        AND archived = FALSE
        AND archetype='regular'
        AND deleted_at IS NULL
        AND created_at::DATE > '2023-07-01'
),

-- find first non-OP reply to user topics
min_response AS (
    SELECT p.topic_id, tu.created_at, MIN(post_number) as post_number
    FROM posts p
    JOIN topic_user tu USING(topic_id)
    WHERE p.post_type = 1
--        AND p.user_id = tu.user_id
        AND p.post_number > 1
        AND p.hidden = false
        AND p.deleted_at IS NULL
    GROUP BY topic_id, tu.created_at
)

SELECT p.topic_id, p.user_id, mr.created_at::DATE as thread_date
FROM posts p
JOIN min_response mr ON p.topic_id = mr.topic_id AND p.post_number=mr.post_number
LEFT JOIN SonarSourcers ss ON p.user_id=ss.user_id
LEFT JOIN user_badges ub on p.id = ub.post_id and ub.badge_id=110
WHERE ss.user_id IS NULL -- eliminate topics where SonarSourcer is first to respond
    AND ub.user_id IS NULL -- eliminate topics where a badge has already been granted
ORDER BY mr.created_at DESC

We go through the results regularly to categorize threads by adding a #bump or #self-solve tag (which removes the thread from the report).

And now we’d like to build on that by sending a “nurture” PM to users who self-solve to say something like

Congrats on working through your issue and thanks for sharing your findings. Your colleagues in the community will find certainly it useful in the future. And if you’d like to further lend your expertise, here’s how you can get started…

Now, to the question: We can find the relevant users, based on the tags in their threads. And we can certainly send these messages manually (and are, so far).

But is there any automation we can put in place around

  • selecting the users (yes, we can write another report for that…)
  • sending the message
2 Likes