How are you identifying up-and-coming top contributors?
It’s pretty easy to pull up a list of accounts and how often they’re contributing and deduce a list of top contributors, but is there any way to identify accounts that are trending in a direction towards becoming a top contributor?
I’d love to better “nurture” those accounts - e.g. simple actions such as recognizing their contributions with reactions/replying with a “Love this response” etc.
FYI you can use Claude (I’m using 3.5 Sonnet) to create quite complex queries. I created a query that gives users a score based upon very specific criteria I gave it. It took a lot of back forth to get it where I wanted, but in the end I’m thrilled with the results, the hardest part is figuring out the criteria to use.
I tried OpenAI and Gemini for this as well, and Claude was the undeniable winner.
Our first run at this was the leaderboard, but it seems to weight pure volume (think “Is it fixed yet? Is it fixed yet? Is it fixed yet? Hello?”) versus quality. Okay, yes “quality” is hard to algo, altho there are some obvious tweaks to be made.
What we’ve started doing to identify truly helpful users is to
look for threads where another user is the first to answer
-- Goal: Find topics where first non-OP reply is a non-SonarSourcer
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 = 'me-too'
),
-- 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 with me-too
AND user_id > 0 -- omit DiscoBot's 'Welcome' PM/tutorial threads
AND visible = TRUE
AND archived = FALSE
AND archetype='regular'
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
Once we identify those threads, we evaluate the user response and then either award the responding user a “Users helping Users” badge or tag the thread with a (invisible to non-staff) “me-too” tag. (BTW, this user-response report has the happy side effect of spotting incidents in our cloud service quickly. )
From there it gets easy to further reward helpful users, and identify them for further nurtures.
I guess the hardest part is defining what a top contributor is - and attributes that measure qualitative rather than quantitative data.
e.g. is a member who replies 50 times and 90% of their replies marked as solutions more of a top contributor who contributes 100 times but only 10% of replies marked as solutions?
At the moment, top contributors are fairly easy to identify, as they’re the ones we see the most around the community. But I’m guessing we need more concrete criteria that we can use to identify those close, or on a path towards.
Yeah. The leaderboard is a great way to see the more active users. For example, most forums’ top posters occupy the top 10 spots. These spots are mostly Regulars and Leaders, due to their high activity and hence climbing high and getting promoted.
I think you’re hitting the nail on the head here. This is mostly a qualitative rather than a quantitative thing.
I’ve had really useful contributors with a few posts per month and I’ve had useless ones with a few dozen per week. The leaderboard is a great starting place to separate the wheat from the chaff, but in the end it’ll be your judgement as a community manager