This topic describes my experience and gives all you need to run a complex one-month long contest, which will give boost to your forum overall, and especially focus on developing a few selected categories.
It is working so good that I feel the need to share this experience with you.
I run 4 contests before this one, so I got some experience and knowledge about what to do and what not to. I will go through all this in detail below.
You should adapt the points formulas depending on your community size and activity. The formulas described below work very well for our community, which can be characterized by the following statistics:
- 1000 registered users
- 100 authorized users visiting the forum daily
- 200-400 new public messages daily
- 2-5 new public topics daily
- 120k views monthly in Google Analytics, 40% direct traffic, 60% search engine traffic
Step 1. Choose categories to boost
Our goat keepers community needed some more attention to a few categories related to cheeses. So we chose 3 categories for this contest.
I do not recommend choosing more than 3 — even with just 3 categories, people cannot easily figure out which ones are best to spend time on in order to win the contest. So I would say 2 or 3 is best. one is not fun — people love choice.
Step 2. Develop rules and formulas for points
The huge SQL query you will find below reflects our rules. But you should always adapt for your audience habits and community size.
Length. One month is enough. Too long — and people will lose their focus. Too short — and you will get few participants.
Points. We use the following formula:
- 1 like = 1 point
- 5 views = 1 point
- 1 topic created = 2 points
Calculation is made only for topics, messages and views recorded during the contest period.
Messages that qualify for points must be made on topics created within the contest period.
Views are not as valuable as likes — because you can exploit it by posting and repeatedly open your topics every day (and ask your friends to do the same). Still, we need views to let people spread the world with the links to your topics. So far works very well. However, in the future contests I would switch to 6 views = 1 topic to balance between the importance of likes vs views better.
Who can participate
We care about our users a lot. We don’t want newcomers to win valuable prizes. We also don’t want for people to sign up for the contest only, grab prizes and then disappear. So we only allow the following users to participate:
- was a writer in last 2 months
- had gained trust level 2 before the contest started
You can easily adapt the criteria in SQL query below.
Whos likes are counted
Never count likes from everyone — people will create lots of fake accounts and exploit your contest. We failed this way once and had to disqualify likes from people registered for liking only. We did it manually and it was rigmarole.
This time we are only counting likes from people who had written at least once in 2 months period before the contest started. Another important requirement is that those likers must had gained level 1 before the contest started.
This criteria is included in the SQL query.
How likes are counted
In previous contests, we used to count all likes, but people exploited it quickly. This time we only choose one message per user per topic with most likes received, and only count those likes. I can already see it works great. People do not try to write many short replies. Instead, they try to create attractive and engaging comments, at least one per topic, and try to gain as much likes as possible by increasing the quality of content. I’m extremely happy with this criterion.
Additional points
More ways to gain points is always fun. We developed 3 additional sources of points.
-
10 points for Popularity badge
You can use any other badge — just make sure it can be granted multiple times.
It is important to make it impossible to win a contest with badges only, so it will only consider up to 3 badges granted during the contest. -
100 points for a Diary Keeper badge.
This is a badge that is very difficult to achieve. You have to write a diary about something related to cheese making or goat keeping for 15 days, totalling 3000+ words.
Once we see a new diary topic, we add adiarytag to it. Then, the badge is granted on auto when the criteria met. If there is at least one such a badge granted during the contest, the SQL query will add +100 points. These additional points can be granted once only per user. -
25 points for purchasing access to our online magazine. The magazine is basically a hidden category. Only a certain group has access to the category. Once someone makes a payment, their nickname is added to the group. This way the system can find out whether to grant 25 points or not. It is also important that these points can only be granted once per user, so that it is impossible to buy subscription once every day for future months and “buy more points” in this way.
Tasks
Besides points, there are 5 tasks. These can be done at any time during the contest — even in the latest contest day. However, you should only choose winners among those completed all of the tasks.
Develop your tasks in a way that will help your forum grow and build up the desired atmosphere and activity level in your community.
Here are out tasks
-
Task 1. Create a topic in a particular category. Minimum length required is 500 words.
-
Task 2. Write something in a particular topic, min 150 words length.
Because our contest is a New Year contest, the topic is with people’s New Year congrats and wishes. Put in some rules.
This task is pretty easy. You need such a one to make people “tick this task” in their mind and say “easy peasy” as soon as they read about it. This engages big time. You cannot just do all 5 tasks difficult ones.
We also added a requirement to make a New Year photo with a goat, and with “New Year Contest [FORUM NAME]” written on paper and seen in the photo. -
Task 3. Video transcription. Videos are great source of unique information, Google will like it. The task is to create a topic with a link to a video and a transcription of the video. The video must be at least 5m length. The topic must be created in a particular category. It can be anything that fits into the category
-
Build a topic from any materials available online. Min 400 words length. It must be created in a particular category and be in a specific area. For this task to work, we go through all new topics and put a specific tag when the requirement is met. This way the system recognizes when the criteria is met and considers task completed.
In our case, the tag isseasoning-in-cheese. -
Write a story in a particular topic, min 400 words length.
This is similar to (1), but a bit more difficult. Good formatting is required in this topic as well. We link to our Tutorial series, so that people can learn about formatting a bit while in contest.
In this case, I wanted to build up the positive atmosphere, so I created a topic “community — our charity and helping people around us”. The task is to create a story about you or your friend doing something good, helping people etc.
Disqualifying topics from contest
When a topic is “just copied” from the Internet with no modification and expansion, we disqualify it by adding a tag named topic-in-development. This tells to the user that the topic needs to be improved and will not be considered for points until the tag is removed.
Step 3. Write down your rules
Make a hidden topic 1 week before the contest starts. Read it and rewrite it every day. This is the only way to make the rules clear and easy to read. If you don’t do so, you risk getting a lot of confusion — because the rules are not easy ones.
I spent 1 week writing out the rules, one hour every evening. It also took me a few hours of going through tutorials for journalists about how to write in a clear and readable way. Anyway, this is fun and you will learn a lot.
Give the rules to your friends and ask them to criticize them, and note every bit that is unclear.
If that helps, you can translate our rules from Russian with using Google translator or whatever, and see if you can adapt the translation. here is the message with rules.
Step 4. Create an SQL query.
Below is a complex and long SQL query that calculates all the criteria. Our goal is to display a table like this, so that every participant can check their score in detail:
The first 5 columns show ticks for tasks 1 to 5.
The next 3 columns show points for topics, likes and views respectively.
The next 3 columns show points for additional ways to gain them, as outlined above.
The last column shows the points total.
Note that the table is ordered by username, not by points. There are a few reasons why. Firstly, it is easier to find your username when it is always ordered the same way. Secondly, one will spend more seconds on the page because they will need to scroll (good for Google Analytics that is then shown to marketers). Finally, if one sees the best scores immediately at the top, it can be discouraging.
You can see this table in action by the following link:
It is built in WordPress with using my Twig Anything plugin. The principles are explained in a separate tutorial topic:
Another important thing is that the table is cached for 3 hours — so the numbers in the table will only update once every 3 hours. This is NOT for performance reasons, but rather to add excitement. Firstly, people will open the table more often “waiting for an update”. Secondly, they will be able to focus on the contest itself rather than looking at their score after every single action. In other words, I tried to prevent the table from being a distraction.
SQL Query for Discourse Data Explorer
WITH
/* Find out who wrote in the forum at least once in October and November */
writers_oct_nov AS (
SELECT DISTINCT (p.user_id) as user_id
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE p.created_at BETWEEN '2016-10-01'::date AND '2016-11-30'::date
AND t.deleted_at IS NULL
AND t.visible = TRUE
AND t.archived = FALSE
AND t.archetype = 'regular'
),
/* Whos likes are counted when calculating points for likes: must had been a writer in last 2 months and had gained level 1 before the contest started */
accepted_likers AS (
SELECT ub.user_id as user_id
FROM user_badges ub
INNER JOIN users u ON u.id = ub.user_id
INNER JOIN writers_oct_nov wr ON wr.user_id = ub.user_id
WHERE badge_id = 1
AND granted_at < '2016-12-01'::date
AND u.active = TRUE
AND u.suspended_at IS NULL
AND u.silenced = FALSE
AND u.staged = FALSE
),
/* Count likes by post from qualified likers only */
likes_by_post as (
SELECT p.topic_id as topic_id,
p.user_id as user_id,
p.id as post_id,
COUNT(pa.id) as likes
FROM post_actions pa
LEFT JOIN accepted_likers al ON al.user_id = pa.user_id
LEFT JOIN posts p ON p.id = pa.post_id
LEFT JOIN topics t ON t.id = p.topic_id
LEFT OUTER JOIN topic_tags tt
ON tt.topic_id = t.id AND tt.tag_id = 21 /* topic-in-development tag */
WHERE post_action_type_id = 2 /*Action to like a post */
AND pa.deleted_at IS NULL
AND p.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest period */
AND t.category_id IN(
28 /* id of contest category 1 */,
55 /* id of contest category 2 */,
14 /* id of contest category 3 */
)
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest period */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND tt.tag_id IS NULL
GROUP BY p.id
),
/* Choose one message per topic per user with most likes */
best_likes_by_topic as (
SELECT user_id as user_id,
topic_id as topic_id,
MAX(likes) as likes
FROM likes_by_post
GROUP BY user_id, topic_id
),
/* Who can participate in contest? */
participants AS (
SELECT ub.user_id as user_id,
u.username as username
FROM user_badges ub
INNER JOIN users u ON u.id = ub.user_id
INNER JOIN writers_oct_nov wr ON wr.user_id = ub.user_id /* was a writer in last 2 months */
WHERE badge_id = 2
AND granted_at < '2016-12-01'::date /* had gained trust level 2 before contest started */
AND u.active = TRUE
AND u.suspended_at IS NULL
AND u.silenced = FALSE
AND u.staged = FALSE
AND u.username NOT IN ('system', 'meg', 'Brovarchanka') /* exclude administrators */
),
/* TASK1 - read about it in detail above */
task1 AS (
SELECT p.user_id as user_id,
p.topic_id as topic_id,
tt.tag_id as topic_tag_id,
p.word_count as post_word_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
LEFT OUTER JOIN topic_tags tt
ON tt.topic_id = p.topic_id
AND ( tt.tag_id = 21 /* tag topic-in-development */
OR tt.tag_id = 43 /* tag seasoning-in-cheese - exclude it as it is required for another task */
)
WHERE p.post_number = 1
AND t.category_id = 28 /* only cheese-making category accepted for task 1 */
AND t.archetype = 'regular'
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND p.word_count >= 400 /* at least 400 words */
AND tt.tag_id IS NULL
),
/* TASK 2 */
task2 as (
SELECT p.user_id, p.id as post_id
FROM posts p
WHERE p.topic_id = 5582 /* topic ID for task 2 */
AND p.word_count >= 150 /* at least 150 words */
AND (
position('<img' in p.cooked) > 0
OR position('[img' in p.cooked) > 0
)
),
/* TASK 3 */
task3 as (
SELECT p.user_id as user_id,
p.topic_id as topic_id
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
LEFT OUTER JOIN topic_tags tt
ON tt.topic_id = p.topic_id AND tt.tag_id = 44 /* seek for a specific tag */
WHERE t.category_id = 28 /* only cheese-making category accepted */
AND t.archetype = 'regular'
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND tt.tag_id IS NOT NULL
),
/* TASK 4 */
task4 as (
SELECT p.user_id as user_id,
p.topic_id as topic_id,
p.word_count as post_word_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
LEFT OUTER JOIN topic_tags tt
ON tt.topic_id = p.topic_id AND tt.tag_id = 43 /* seek for a particular tag */
WHERE p.post_number = 1
AND t.category_id = 28 /* only cheese-making category accepted*/
AND t.archetype = 'regular'
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND p.word_count >= 400 /* at least 400 words */
AND tt.tag_id IS NOT NULL
),
/* TASK5 */
task5 as (
SELECT p.user_id, p.id as post_id
FROM posts p
WHERE p.topic_id = 5583 /* message in a particular topic, use your own ID */
AND p.word_count >= 250 /* at least 250 words */
),
points_for_topics as (
SELECT
t.user_id as user_id,
COUNT(t.id) * 2 as points /* every new topic adds 2 points */
FROM topics t
LEFT OUTER JOIN topic_tags tt
ON tt.topic_id = t.id AND tt.tag_id = 21 /* exclude topics with tag topic-in-development */
WHERE t.category_id IN(
28 /* accepted contest category 1 */,
55 /* accepted contest category 2 */,
14 /* accepted contest category 3 */
)
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND tt.tag_id IS NULL
GROUP BY t.user_id
),
points_for_views as (
SELECT
t.user_id as user_id,
floor(COUNT(tw.topic_id) / 5) as points /* every 5 views add 1 point */
FROM topic_views tw
LEFT OUTER JOIN topics t ON t.id = tw.topic_id
LEFT OUTER JOIN topic_tags tt
ON (tt.topic_id = t.id AND tt.tag_id = 21) /* exclude topics with tag topic-in-development */
WHERE t.category_id IN (
28 /* accepted contest category 1 */,
55 /* accepted contest category 2 */,
14 /* accepted contest category 3 */
)
AND t.created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND tw.viewed_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
AND t.closed = FALSE
AND t.archived = FALSE
AND t.visible = TRUE
AND t.deleted_at IS NULL
AND tt.tag_id IS NULL
GROUP BY t.user_id
),
/* SUM POINTS FOR LIKES RECEIVED, PER USER */
points_for_likes as (
SELECT user_id,
SUM(likes) as points /* 1 like adds 1 point, just add */
FROM best_likes_by_topic
GROUP BY user_id
),
/* Number of BADGE1 badges per user for additional points */
popularity_investment_badges as (
SELECT user_id,
COUNT(id) as num_badges
FROM user_badges ub
WHERE ub.badge_id = 21 /* for which badge ID additional points are given? use your ID */
AND granted_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
GROUP BY user_id
),
/* Additional points for BADGE1 per user */
points_for_popularity as (
SELECT user_id,
(CASE WHEN num_badges > 3 THEN 3 ELSE num_badges END) * 10 as points /* up to 3 badges accepted, 10 points per badge*/
FROM popularity_investment_badges
),
/* Number of BADGE2 badges per user for additional points */
diary_badges as (
SELECT user_id,
COUNT(id) as num_badges
FROM user_badges ub
WHERE ub.badge_id = 112 /* for which badge ID additional points are given? use your ID */
AND granted_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
GROUP BY user_id
),
/* Additional points for BADGE2 per user */
points_for_diary as (
SELECT user_id,
(CASE WHEN num_badges > 1 THEN 1 ELSE num_badges END) * 100 as points /* no more than 1 badge accepted, +100 points if badge found */
FROM diary_badges
),
/* Magazine subscriptions per user in the contest period */
magazine_subscriptions as (
SELECT user_id,
COUNT(group_id) as num
FROM group_users
WHERE group_id = 49 /* group ID granted to user when they subscribe to the journal */
AND created_at BETWEEN '2016-12-01'::date AND '2016-12-31'::date /* contest dates */
GROUP BY user_id, group_id
),
/* Points for magazine subscriptions per user */
points_for_magazine as (
SELECT user_id,
(CASE WHEN num > 1 THEN 1 ELSE num END) * 25 as points /* no more than 1 subscription accepted, +25 points if there was a subscription */
FROM magazine_subscriptions
),
/* All points and task completion status in a single table */
points_calculated as (
SELECT p.user_id as user_id,
u.username as username,
MIN(task1.topic_id) as task1_topic_id,
MIN(task2.post_id) as task2_post_id,
MIN(task3.topic_id) as tas3_topic_id,
MIN(task4.topic_id) as task4_topic_id,
MIN(task5.post_id) as task5_post_id,
MIN(p_t.points) as points_for_topics,
MIN(p_w.points) as points_for_views,
MIN(p_l.points) as points_for_likes,
MIN(p_p.points) as points_for_popularity,
MIN(p_d.points) as points_for_diary,
MIN(p_m.points) as points_for_magazine
FROM participants p
LEFT JOIN users u ON u.id = p.user_id
LEFT OUTER JOIN task1 ON task1.user_id = p.user_id
LEFT OUTER JOIN task2 ON task2.user_id = p.user_id
LEFT OUTER JOIN task3 ON task3.user_id = p.user_id
LEFT OUTER JOIN task4 ON task4.user_id = p.user_id
LEFT OUTER JOIN task5 ON task5.user_id = p.user_id
LEFT OUTER JOIN points_for_topics p_t ON p_t.user_id = p.user_id
LEFT OUTER JOIN points_for_views p_w ON p_w.user_id = p.user_id
LEFT OUTER JOIN points_for_likes p_l ON p_l.user_id = p.user_id
LEFT OUTER JOIN points_for_popularity p_p
ON p_p.user_id = p.user_id
LEFT OUTER JOIN points_for_diary p_d ON p_d.user_id = p.user_id
LEFT OUTER JOIN points_for_magazine p_m ON p_m.user_id = p.user_id
GROUP BY p.user_id, u.username
)
/* Same as above, but adds POINTS TOTAL */
SELECT pc.*,
( COALESCE(points_for_topics, 0)
+ COALESCE(points_for_views, 0)
+ COALESCE(points_for_likes, 0)
+ COALESCE(points_for_popularity, 0)
+ COALESCE(points_for_diary, 0)
+ COALESCE(points_for_magazine, 0)
) as points_total
/* FINAL OUTPUT - order by username */
FROM points_calculated pc
ORDER BY pc.username
Step 5. Enjoy
Be kind and make the rules work for everyone the same way. No priorities. Help everyone to understand your rules. Allow them to edit their topics and improve. Make everyone happy. That is what it is all about.
I can only show statistics for 2 days as we started on 1st December.
Topics created:
Messages written:
Likes given:
Private messages:
If you like the idea, I can also be hired to help you setup all you need to run a similar contest with your Discourse setup.
