Gamification of our activism

Project details

Project description (max 200 words): Gamification is used to motivate and engage members by creating playful experiences. To put it as simply as possible: Gamification (in any context!) uses an understanding of human psychology to elicit emotions and positive experiences, providing people with an incentive to participate.
Your personal background within the context of the project: Member of several open-source communities that use gamification to motivate people, such as the Fedora Project.
Estimated timeline in terms of time boxed deliverables: We should keep it simple, not creating too many rewards, but make it worthwhile to own them and keep it special. I suggest we introduce 5 custom badges for the TZM Community.
Resources needed or already arranged: SQL knowledge and creativity.

As people may know already, I introduced 2 custom badges this week.

I think the Project Hero badge could use some work. I think someone would need to have created 5 #activism:projects and closed these as well. Finalized projects count more than just starting ideas :slight_smile:

I’ll finetune the SQL query a bit more once I have more time. But people are free to help out and improve these queries already. These queries are not site specific. You can create them on any Discourse website (as an admin). There are only a few minor adjustments needed for topic/user/category ID and such.

Below is the current query. What’s missing is a check for solved topics and then conclude with a count of 5.

SELECT DISTINCT ON (t.user_id) t.user_id, t.created_at granted_at
FROM topics t
WHERE t.category_id = 28

Feel free to comment here with feedback, ideas and worked out examples! :slight_smile:

Another idea might be that someone else solved 5 #activism:support topics, inspired by the Helping Hand badge of the Fedora community.

Tagging the @it-team to make use of our resources (skills and time) :slight_smile:

I can write SQL.
But I really don’t like to.
I’m much more of a NoSQL lover, especially MongoDB with it’s powerful aggregations, etc…

So sorry I can’t help you. I also am not a completionist type who likes to get all the badges.

I’m the start lots of project ideas type and see what sticks.

1 Like

It’s not for all, but I think it’s nice to be surprised sometimes with a badge. I also don’t hunt for them in the Fedora community, but it’s nice to get one for activities :slight_smile: It’s sort of cheap recognition. But not like the video below :stuck_out_tongue:

So we have 3 now. A 4th and 5th one could be about finalizing a #co-initiatives:approved and helping with the #co-initiatives:reviews

I guess that concludes the goal of this project. I’ll check the database when I have more time for this and create the queries. In the meantime, people can provide feedback with improved ideas or totally different ones.

This query below finds someone who had 10 solved issues. Inspired by the Helping Hand badge in the Fedora community.

SELECT id user_id, current_timestamp granted_at
FROM users
WHERE id  IN (
       SELECT p1.user_id
       FROM post_custom_fields pc
       JOIN badge_posts p1 ON p1.id = pc.post_id
       JOIN topics t1 ON p1.topic_id = t1.id
       WHERE p1.user_id <> t1.user_id AND
                    name = 'is_accepted_answer' AND
            p1.user_id IN (
                   SELECT user_id
                   FROM posts
                   WHERE :backfill OR  p1.id IN (:post_ids)
            )
        GROUP BY p1.user_id
        HAVING COUNT(*) > 9
)
1 Like

Also created a badge for completing a co-initiative. I think I’ll stop there. There is of course still the review contributor badge. But it’s hard to distinguish if someone really contributed, or maybe just posted BS.

So, I consider this project, Kees closed :nerd_face:

Whoops, I was experimenting with queries and now the New User of the Month is purged except for me in there (I guess because I izz admin?). Dunno. I believe I saw @kublermdk, @BoQsc, @sabtu, @Bahusson and @Jay131278 in there. I’ll add you guys to it manually. I’ll stop messing with the badges now as well :nerd_face:

1 Like

I see badges as a great way to quickly spot someone more active or with similar intents. They are great.

1 Like

Although I’m super busy, I couldn’t help myself. I also created a query for the co-initiative review process. The way I differentiate between valuable and not so valuable comments is when these comments are liked by people with TL3.

SELECT distinct p.user_id, p.created_at granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND 
      post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
       ) AND 
       pa.user_id IN (
           SELECT gu.user_id
           FROM group_users gu
           WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'trust_level_3' ) 
       )
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'reviews'
) 

Here is the co-initiative reviewer badge.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Added a last one: First report badge on TZM Community Forum