Problem statement
If you use the Discourse Gamification Plugin to award users points for contributing to your community, you may have come across a need to adjust point values based on emerging trends. In the SailPoint Developer Community, we use the Gamification Plugin to power our Ambassador program. Users who make valuable contributions to our community earn points which in turn are used to determine the level of perks they receive. As our community has grown and the number of contributions has drastically increased, we determined that the original values we assigned for certain types of contributions needed to be adjusted. Adjusting point values works well if you never do a recalculation of your points, but we often have to recalculate points when we do user merges or when we use the external gamification API to award points for past contributions. If you modify gamification point values and do a recalculation, then previous contributions that were scored using the old point values will now be scored using the new point values. This is a problem if you want the new point values to be assigned to new contributions while leaving the old contributions at the same value. In this how-to, I will discuss a solution I created that will ensure your users’ previous points will not be altered when you change the point values and run a recalculation.
Using the Data Explorer to calculate current and proposed points
Import the SQL query
The first step in this process is to import the following SQL query into your Data Explorer plugin. This SQL query was modified from the original SQL query to include pagination, add additional columns for user_name and name, and to sort by the user_id. This query will produce a table of user scores for a given time range and using the given score values.
-- [params]
-- date :start_date
-- date :end_date
-- int :day_visited_score_value = 0
-- int :time_read_score_value = 0
-- int :posts_read_score_value = 0
-- int :posts_created_score_value = 6
-- int :topics_created_score_value = 0
-- int :likes_received_score_value = 3
-- int :likes_given_score_value = 0
-- int :solutions_score_value = 60
-- int :flag_created_score_value = 6
-- int :user_invited_score_value = 0
-- int :limit = 1000
-- int :page = 0
WITH visits AS (
SELECT
uv.user_id,
COUNT(*) AS user_visits,
COUNT(*) * :day_visited_score_value AS visits_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
GROUP BY uv.user_id
),
time_read AS (
SELECT
uv.user_id,
SUM(uv.time_read) /3600 AS time_read,
SUM(uv.time_read) /3600 * :time_read_score_value AS time_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.time_read >= 60
GROUP BY uv.user_id
),
posts_read AS (
SELECT
uv.user_id,
SUM(uv.posts_read) AS posts_read,
SUM(uv.posts_read) /100 * :posts_read_score_value AS posts_read_score
FROM user_visits uv
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND uv.posts_read >= 5
GROUP BY uv.user_id
),
posts_created AS (
SELECT
p.user_id,
COUNT(*) AS posts_created,
COUNT(*) * :posts_created_score_value AS posts_created_score
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND p.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
topics_created AS (
SELECT
t.user_id,
COUNT(*) AS topics_created,
COUNT(*) * :topics_created_score_value AS topics_created_score
FROM topics t
WHERE t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND t.created_at::date BETWEEN :start_date AND :end_date
GROUP BY t.user_id
),
likes_received AS (
SELECT
p.user_id,
COUNT(*) AS likes_received,
COUNT(*) * :likes_received_score_value AS likes_received_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
likes_given AS (
SELECT
pa.user_id AS user_id,
COUNT(*) AS likes_given,
COUNT(*) * :likes_given_score_value AS likes_given_score
FROM post_actions pa
INNER JOIN posts p ON p.id = pa.post_id
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.wiki IS FALSE
AND post_action_type_id = 2
AND pa.created_at::date BETWEEN :start_date AND :end_date
GROUP BY pa.user_id
),
solutions AS (
SELECT
p.user_id,
COUNT(tcf.topic_id) AS solutions,
COUNT(tcf.topic_id) * :solutions_score_value AS solutions_score
FROM topic_custom_fields tcf
INNER JOIN topics t ON tcf.topic_id = t.id
INNER JOIN posts p ON p.id = tcf.value::INTEGER
WHERE p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND tcf.name = 'accepted_answer_post_id'
AND t.archetype <> 'private_message'
AND p.user_id <> t.user_id
AND tcf.updated_at::date BETWEEN :start_date AND :end_date
GROUP BY p.user_id
),
flags AS (
SELECT
r.created_by_id AS user_id,
COUNT(*) AS flags,
COUNT(*) * :flag_created_score_value AS flags_score
FROM reviewables r
WHERE created_at::date BETWEEN :start_date AND :end_date
AND status = 1
GROUP BY user_id
),
invites AS (
SELECT
inv.invited_by_id AS user_id,
SUM(inv.redemption_count) AS invites,
(SUM(inv.redemption_count) * :user_invited_score_value)::int AS invites_score
FROM invites inv
WHERE inv.created_at::date BETWEEN :start_date AND :end_date
AND inv.redemption_count > 0
GROUP BY inv.invited_by_id
)
SELECT
u.id AS user_id,
u.username AS username,
u.name AS name,
(
COALESCE(v.visits_score,0) +
COALESCE(tr.time_read_score,0) +
COALESCE(pr.posts_read_score,0) +
COALESCE(pc.posts_created_score,0) +
COALESCE(tc.topics_created_score,0) +
COALESCE(lr.likes_received_score,0) +
COALESCE(lg.likes_given_score,0) +
COALESCE(s.solutions_score,0) +
COALESCE(f.flags_score,0) +
COALESCE(i.invites_score,0)
) AS "Total Cheers",
COALESCE(v.user_visits,0) || ' (' || COALESCE(v.visits_score,0) || ')' AS "Visits (cheers)",
COALESCE(tr.time_read,0) || 'hrs' || ' (' || COALESCE(tr.time_read_score,0) || ')' AS "Time Read (cheers)",
COALESCE(pr.posts_read,0) || ' (' || COALESCE(pr.posts_read_score,0) || ')' AS "Posts Read (cheers)",
COALESCE(pc.posts_created,0) || ' (' || COALESCE(pc.posts_created_score,0) || ')' AS "Posts Created (cheers)",
COALESCE(tc.topics_created,0) || ' (' || COALESCE(tc.topics_created_score,0) || ')'AS "Topics Created (cheers)",
COALESCE(lr.likes_received,0) || ' (' || COALESCE(lr.likes_received_score,0) || ')' AS "Likes Received (cheers)",
COALESCE(lg.likes_given,0) || ' (' || COALESCE(lg.likes_given_score,0) || ')' AS "Likes Given (cheers)",
COALESCE(s.solutions,0) || ' (' || COALESCE(s.solutions_score,0) || ')'AS "Solutions (cheers)",
COALESCE(f.flags,0) || ' (' || COALESCE(f.flags_score,0) || ')' AS "Agreed Flags (cheers)",
COALESCE(i.invites,0) || ' (' || COALESCE(i.invites_score,0) || ')' AS "Invites Redeemed (cheers)"
FROM users u
LEFT JOIN visits v ON v.user_id = u.id
LEFT JOIN posts_read pr USING (user_id)
LEFT JOIN time_read tr USING (user_id)
LEFT JOIN flags f USING (user_id)
LEFT JOIN posts_created pc USING (user_id)
LEFT JOIN topics_created tc USING (user_id)
LEFT JOIN likes_given lg USING (user_id)
LEFT JOIN likes_received lr USING (user_id)
LEFT JOIN solutions s USING (user_id)
LEFT JOIN invites i USING (user_id)
WHERE u.id > 0
AND u.id NOT IN (SELECT user_id FROM group_users WHERE group_id = 3)
ORDER BY user_id ASC
OFFSET :page * :limit
LIMIT :limit
Test the query using your current and proposed point values
Once you have the SQL query imported, you should test it using a timeframe that reflects the period in which you don’t want points to be changed. For us, that meant the beginning of our forum (2020-01-01) to the day before we wanted to make the update to our point values (2024-05-15). Update the score values to reflect your current point values, and test the query to make sure the values look correct.
Next, keep the same timeframe but change the point values to what you want them to be. Take note of the difference in values for the same users. In this example, Neil had a total of 1104 points (AKA cheers) using the current point values, and his points increased to 1245 using the new point values. We need a method to calculate the difference in the before and after points and adjust the user’s points so that they don’t see an increase or decrease in points after the new point values are implemented.
Using the external gamification API to adjust the points
Using Neil as an example, his current points are 1104 and his points after the proposed change will be 1245. To make sure he doesn’t see an increase or decrease in points, we need to calculate the difference of these two values and then assign the difference to his score. The difference is calculated as currentValue - newValue, which in Neil’s case would be 1104 - 1245 = -141. This means that Neil needs to be assigned -141 points. We can use the external gamification API to assign these points to his user account so they reflect in his leaderboard score. The API call we need to make is as follows:
curl --location 'https://my.discourse.com/admin/plugins/gamification/score_events' \
--header 'Api-Key: <your key>' \
--header 'Api-Username: <your username>' \
--header 'Content-Type: application/json' \
--data '{
"user_id": "101",
"date": "2024-05-15",
"points": "-141",
"description": "Gamification point adjustment"
}'
Executing this API call will adjust Neil’s total score so that the proposed point values will only affect new score-able events, while any difference in the old score-able events will be canceled out by the adjustment. Now we just need to apply this process to every user in the community.
Automating the point adjustments
To automate this process, I have created a Python script that leverages Discourse APIs to run the SQL queries, calculate the difference, and assign the adjustments to each user. The script also outputs a CSV file with a log of all the users who received an adjustment. The comments in the script do a pretty good job describing the values you need to change and how it works. If there are any questions about how to get this working, please leave a comment below.
Requirements
You will need a recent version of Python 3. I use Python 3.9.6. You will also need to install the requests package from PyPi.
python3 -m pip install requests
Script
import requests
import secrets
from datetime import date
from datetime import timedelta
import time
import csv
import json
# The API credentials are store in a secret file. How you supply the secrets are up to you.
api_key = secrets.api_key
api_username = secrets.api_username
host = 'https://developer.sailpoint.com/discuss' # The hostname of your Discourse instance
# A link to a topic in your forum describing the current and proposed point changes. This is a good thing to have
# so you can look back on it to understand what changed, as well as make the changes transparent to your users.
point_adjustment_link = 'https://developer.sailpoint.com/discuss/t/update-to-ambassador-point-values-may-15th-2024/54178'
# The name you want to give your CSV file. The start_date and end_date will be appended when it is created.
csv_name = 'sailpoint_developer_community_point_adjustment'
max_requests_per_minute = 200 # Max requests per 1 minute period. Adjust this to be at or below your Discourse API rate limit.
start_date = '2020-01-01' # The forum started after this date, so this will ensure that all points are considered.
# This date cannot be today, or the query will error out. This formula will use yesterday's date. You can modify this
# to be further in the past if desired.
end_date = (date.today() - timedelta(days = 1)).strftime('%Y-%m-%d')
# The ID of your SQL query. You can find this by clicking on your query in the Data Explorer plugin and then looking for
# the "id" param in the URL. For example, my ID is 66 as shown in this URL:
# https://developer.sailpoint.com/discuss/admin/plugins/explorer?id=66
query_id = '66'
# These are the current point values for your Gamification score settings. These four were applicable to our forum
# but you can add the other score-able point types if you need to.
current_likes_received_score_value = '3'
current_solutions_score_value = '60'
current_posts_created_score_value = '6'
current_flag_created_score_value = '6'
# These are the proposed point values that will be applied to all new points.
new_likes_received_score_value = '6'
new_solutions_score_value = '60'
new_posts_created_score_value = '3'
new_flag_created_score_value = '6'
# This function runs the SQL query to get the point values for all users. It will automatically paginate until there are no more records.
def getPointValues(query_id, start_date, end_date, limit, likes_received_score_value, solutions_score_value, posts_created_score_value, flag_created_score_value):
rows = []
page = 0
headers = {
'Api-Key': api_key,
'Api-Username': api_username,
'Content-Type': 'application/x-www-form-urlencoded'
}
payload = f'params={{"start_date":"{start_date}","end_date":"{end_date}","limit":"{str(limit)}","page":"{str(page)}","likes_received_score_value":"{likes_received_score_value}","solutions_score_value":"{solutions_score_value}","posts_created_score_value":"{posts_created_score_value}","flag_created_score_value":"{flag_created_score_value}"}}'
r = requests.post(f'{host}/admin/plugins/explorer/queries/{query_id}/run', headers=headers, data=payload).json()
rows += r['rows']
# Paginate until we reach last page
while len(r['rows']) == limit:
page += 1
payload = f'params={{"start_date":"{start_date}","end_date":"{end_date}","limit":"{str(limit)}","page":"{str(page)}","likes_received_score_value":"{likes_received_score_value}","solutions_score_value":"{solutions_score_value}","posts_created_score_value":"{posts_created_score_value}","flag_created_score_value":"{flag_created_score_value}"}}'
r = requests.post(f'{host}/admin/plugins/explorer/queries/{query_id}/run', headers=headers, data=payload).json()
rows += r['rows']
return rows
# Create the array of point adjustments that need to be made. If a user has 0 points, or there is no difference in points, they
# will be filtered out so we don't make unnecessary API calls. This array will include all of the information necessary to make
# the external gamification API call as well as populate the CSV file.
def calculatePointAdjustments(old_values, new_values):
point_adjustments = []
for row in old_values:
user_id = row[0]
username = row[1]
name = row[2]
current_cheers = row[3]
for i in range(len(new_values)):
new_cheers = new_values[i][3]
if new_values[i][0] == user_id:
# Only calculate the diff if there is a difference in points.
if current_cheers != new_cheers:
point_adjustments.append({
'user_id': user_id,
'username': username,
'name': name,
'current_points': current_cheers,
'new_points': new_cheers,
'difference': current_cheers - new_cheers,
'external_gamification_point_id': -1
})
new_values.pop(i) # Remove the element from the list to speed up processing
break
else:
new_values.pop(i) # Remove the element from the list to speed up processing
break
return point_adjustments
# Save the point adjustment array to a CSV file for your record keeping
def exportCSV(differences):
fields = ['User ID', 'Username', 'Name', 'Current Points', 'New Points', 'Difference', 'External Gamification Point ID']
rows = []
for diff in differences:
rows.append([diff['user_id'], diff['username'], diff['name'], diff['current_points'], diff['new_points'], diff['difference'], diff['external_gamification_point_id']])
with open(f'{csv_name}_{start_date}_to_{end_date}.csv', 'w') as f:
csv_writer = csv.writer(f)
csv_writer.writerow(fields)
csv_writer.writerows(rows)
# Execute the gamification API to assign the difference in points for each user. This function will intentionally slow down
# in order to stay within your rate limit. In the event a rate limit is reached, this function will continue to wait until
# the rate limit is expired and it can continue. If at any time there is an error in the API call, then the succesffully completed
# point adjustments will be saved to a CSV file so you know which ones are complete and where you need to resume.
def assignPointAdjustments(point_adjustments):
assigned_point_adjustments = []
# Self rate limit to avoid affecting other integrations
sleep_time = 60 / max_requests_per_minute # Number of seconds to wait between each call to stay within the max requests limit
endpoint = f'{host}/admin/plugins/gamification/score_events'
headers = {
'Api-Key': api_key,
'Api-Username': api_username,
'Content-Type': 'application/json'
}
for adjustment in point_adjustments:
payload = json.dumps({
"user_id": str(adjustment['user_id']),
"date": end_date,
"points": str(adjustment['difference']),
"description": f'Adjusting points based on new gamification point values. Please see {point_adjustment_link}.'
})
start = time.time() # Keep track of execution time. Since API calls take several milliseconds, don't count this time towards the max request sleep time.
try:
r = requests.post(endpoint, headers=headers, data=payload)
except Exception as e:
# In case of failure, save the assigned adjustments to a CSV file so we know which ones are already complete
exportCSV(assigned_point_adjustments)
raise
# If the max requests is below 300, then we shouldn't hit a rate limit. If we happen to hit the rate limit, handle it appropriately.
while r.status_code == 429:
wait_time = r.json()["extras"]["wait_seconds"] + 1
print(f'Hit rate limit. Sleeping for {wait_time} seconds')
time.sleep(wait_time)
try:
r = requests.post(endpoint, headers=headers, data=payload)
adjustment['external_gamification_point_id'] = r.json()["id"]
except Exception as e:
# In case of failure, save the assigned adjustments to a CSV file so we know which ones are already complete
exportCSV(assigned_point_adjustments)
raise
if r.status_code != 200:
exportCSV(assigned_point_adjustments)
print(f'The last request returned a {r.status_code} error with the following error message\n{r.text}')
print('Aborting adjustments and writing the successful adjustments to file')
raise Exception("HTTP Error")
end = time.time()
if end - start < sleep_time:
# Only sleep if the request time was less than the max request sleep time
time.sleep(sleep_time - (end - start))
adjustment['external_gamification_point_id'] = r.json()["id"]
print(f'Assigned {adjustment["username"]} {adjustment["difference"]} points')
assigned_point_adjustments.append(adjustment)
return assigned_point_adjustments
limit = 1000 # This is the max limit for SQL queries.
current_values = getPointValues(query_id, start_date, end_date, limit, current_likes_received_score_value, current_solutions_score_value, current_posts_created_score_value, current_flag_created_score_value)
new_values = getPointValues(query_id, start_date, end_date, limit, new_likes_received_score_value, new_solutions_score_value, new_posts_created_score_value, new_flag_created_score_value)
point_adjustments = calculatePointAdjustments(current_values, new_values)
assigned_point_adjustments = assignPointAdjustments(point_adjustments)
exportCSV(assigned_point_adjustments)
The steps to run the point adjustment
Now that we have all of the pieces in place, you will need to follow these steps to execute the point adjustment process. It is recommended that you test this process in a test instance of your Discourse environment first so you can work out any issues.
- Update the variables in the Python script to your desired values.
- Change the point values in the Gamification settings to the new values.
- Recalculate the scores for “All Time”.
- Run the Python script and wait for it to complete. Depending on how many users are in your system and what your rate limit is, this can take several minutes to complete. We had 2,200 users that needed an adjustment. At a rate limit of 200 requests/minute, it took about 15 minutes to complete.
- Once complete, recalculate the scores for “All Time” again, just to be safe.
- Save the CSV file in a safe place for your records, in case you need to roll back any changes.
The point recalculation can take a few hours to complete. You will need to be patient in order to see the adjustments take effect. Once the recalculation is complete, you now have successfully adjusted your users’ points, and points going forwarded will be calculated from the new point values.
Making more adjustments in the future
If at any time you need to make adjustments to point values, you can follow the same process. The only difference is that you need to set the start_date to the end_date of when you last ran the point adjustment. For example, the first time I run this point adjustment I set the end_date to 2024-05-15. This made sure that all points earned between the beginning of my forum and 2024-05-15 were adjusted to preserve the old values. Let’s assume that one year passes and I want to change the point values again. Now I need to set the start_date to 2024-05-15 and the end_date to 2025-05-15. This will ensure that the point adjustment only applies to that period and doesn’t override anything from the first time you ran the point adjustment.