問題の定義
Discourse Gamification Plugin を使用して、コミュニティへの貢献に対してユーザーにポイントを付与している場合、新たなトレンドに応じてポイント値を調整する必要があることに気づくかもしれません。SailPoint Developer Community では、Gamification Plugin を活用して Ambassador プログラム を運営しています。コミュニティに価値ある貢献を行ったユーザーはポイントを獲得し、それに応じて受けられる特典のレベルが決定されます。コミュニティが成長し、貢献数が劇的に増加したため、特定の種類の貢献に対して当初割り当てていたポイント値を調整する必要があると判断しました。
ポイント値の調整は、ポイントを再計算しない限り問題ありませんが、ユーザーのマージを行う場合や、外部の Gamification API を使用して過去の貢献に対してポイントを付与する場合は、頻繁にポイントを再計算する必要があります。Gamification のポイント値を変更して再計算を行うと、旧ポイント値で評価された過去の貢献が、新ポイント値で再評価されてしまいます。これは、新ポイント値を新しい貢献にのみ適用し、古い貢献は元の値のままにしたい場合に問題となります。本記事では、ポイント値を変更して再計算を実行しても、ユーザーの過去のポイントが変更されないようにする解決策について説明します。
Data Explorer を使用した現在および提案されたポイントの計算
SQL クエリのインポート
このプロセスの最初のステップは、以下の SQL クエリを Data Explorer プラグインにインポートすることです。この SQL クエリは、元の SQL クエリ を基に、ページネーション機能の追加、user_name と name の追加列の追加、user_id によるソートを行うよう修正されています。このクエリは、指定された期間とスコア値を使用して、ユーザーのスコア表を生成します。
-- [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(st.topic_id) AS solutions,
COUNT(st.topic_id) * :solutions_score_value AS solutions_score
FROM discourse_solved_solved_topics st
INNER JOIN topics t ON st.topic_id = t.id
INNER JOIN posts p ON p.id = st.answer_post_id
WHERE p.deleted_at IS NULL
AND t.deleted_at IS NULL
AND t.archetype <> 'private_message'
AND p.user_id <> t.user_id
AND st.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
現在および提案されたポイント値を使用したクエリのテスト
SQL クエリをインポートしたら、変更したくない期間を反映するタイムフレームを使用してテストしてください。私たちの場合、それはフォーラムの開始（2020-01-01）からポイント値の更新を行う前日（2024-05-15）までの期間でした。スコア値を現在のポイント値に合わせて更新し、クエリを実行して値が正しいか確認してください。
次に、同じタイムフレームを維持しつつ、ポイント値を変更したい値に変更します。同じユーザーにおける値の違いに注意してください。この例では、Neil は現在のポイント値を使用すると合計 1104 ポイント（別名 cheers）でしたが、新しいポイント値を使用すると 1245 ポイントに増加しました。新旧のポイントの差を計算し、新しいポイント値が適用された後でもユーザーのポイントが増減しないように調整する方法が必要です。
外部 Gamification API を使用したポイントの調整
Neil を例に取ると、現在のポイントは 1104、提案された変更後のポイントは 1245 です。Neil が増減を見せないようにするには、これらの 2 つの値の差を計算し、その差を彼のスコアに割り当てる必要があります。差は currentValue - newValue として計算され、Neil の場合は 1104 - 1245 = -141 となります。つまり、Neil には -141 ポイントを割り当てる必要があります。これらのポイントをユーザーアカウントに割り当ててリーダーボードのスコアに反映させるには、外部 Gamification API を使用できます。必要な API 呼び出しは以下の通りです。
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"
}'
この API 呼び出しを実行すると、Neil の合計スコアが調整され、提案されたポイント値は新しいスコア対象イベントにのみ影響し、古いスコア対象イベントの差は調整によって相殺されます。あとはこのプロセスをコミュニティのすべてのユーザーに適用するだけです。
ポイント調整の自動化
このプロセスを自動化するために、Discourse API を活用して SQL クエリを実行し、差を計算して各ユーザーに調整を割り当てる Python スクリプトを作成しました。スクリプトは、調整を受けたすべてのユーザーのログを含む CSV ファイルも出力します。スクリプト内のコメントは、変更が必要な値とその動作についてよく説明しています。動作に関するご質問は、以下のコメント欄にご記入ください。
要件
最新の Python 3 バージョンが必要です。私は Python 3.9.6 を使用しています。また、PyPi から requests パッケージをインストールする必要があります。
python3 -m pip install requests
スクリプト
import requests
import secrets
from datetime import date
from datetime import timedelta
import time
import csv
import json
# API 認証情報は秘密ファイルに保存されます。秘密情報をどのように供給するかはあなた次第です。
api_key = secrets.api_key
api_username = secrets.api_username
host = 'https://developer.sailpoint.com/discuss' # Discourse インスタンスのホスト名
# フォーラム内のトピックへのリンク（現在の提案されたポイント変更について説明したもの）。これにより、変更内容を振り返って理解でき、ユーザーに対して変更を透明化できます。
point_adjustment_link = 'https://developer.sailpoint.com/discuss/t/update-to-ambassador-point-values-may-15th-2024/54178'
# CSV ファイルに付ける名前。作成時に start_date と end_date が追加されます。
csv_name = 'sailpoint_developer_community_point_adjustment'
max_requests_per_minute = 200 # 1 分あたりの最大リクエスト数。Discourse API のレート制限以下になるように調整してください。
start_date = '2020-01-01' # フォーラムはこの日付後に開始されたため、すべてのポイントが考慮されます。
# この日付は今日にしてはいけません。クエリがエラーになります。この式は昨日の日付を使用します。必要に応じて過去の日付に変更可能です。
end_date = (date.today() - timedelta(days = 1)).strftime('%Y-%m-%d')
# SQL クエリの ID です。Data Explorer プラグインでクエリをクリックし、URL の "id" パラメータを確認することで取得できます。例えば、私の ID は 66 です（以下の URL 参照）：
# https://developer.sailpoint.com/discuss/admin/plugins/explorer?id=66
query_id = '66'
# これらは Gamification スコア設定の現在のポイント値です。これら 4 つは私たちのフォーラムに適用されましたが、必要に応じて他のスコア対象ポイントタイプを追加できます。
current_likes_received_score_value = '3'
current_solutions_score_value = '60'
current_posts_created_score_value = '6'
current_flag_created_score_value = '6'
# これらは、すべての新しいポイントに適用される提案されたポイント値です。
new_likes_received_score_value = '6'
new_solutions_score_value = '60'
new_posts_created_score_value = '3'
new_flag_created_score_value = '6'
# この関数は SQL クエリを実行して、全ユーザーのポイント値を取得します。レコードがなくなるまで自動的にページネーションを行います。
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']
# 最後のページに到達するまでページネーション
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
# 行う必要があるポイント調整の配列を作成します。ユーザーのポイントが 0 の場合、またはポイントに差がない場合は除外され、不要な API 呼び出しを防ぎます。この配列には、外部 Gamification API 呼び出しを実行し、CSV ファイルを埋めるために必要な情報がすべて含まれます。
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:
# ポイントに差がある場合のみ差を計算します。
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) # 処理を高速化するためにリストから要素を削除
break
else:
new_values.pop(i) # 処理を高速化するためにリストから要素を削除
break
return point_adjustments
# ポイント調整配列を CSV ファイルに保存して記録を残します
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)
# Gamification API を実行して、各ユーザーのポイントの差を割り当てます。この関数は、レート制限内に収まるように意図的に遅延します。レート制限に達した場合、制限が解除されるまで待機して続行します。API 呼び出しでエラーが発生した場合、正常に完了したポイント調整を CSV ファイルに保存し、完了したものと再開する場所を確認できるようにします。
def assignPointAdjustments(point_adjustments):
assigned_point_adjustments = []
# 他のインテグレーションに影響を与えないよう自己レート制限
sleep_time = 60 / max_requests_per_minute # 最大リクエスト制限内に収まるように各呼び出し間で待機する秒数
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() # 実行時間を追跡。API 呼び出しには数ミリ秒かかるため、この時間は最大リクエスト待機時間には含めないようにします。
try:
r = requests.post(endpoint, headers=headers, data=payload)
except Exception as e:
# 失敗した場合、割り当てられた調整を CSV ファイルに保存し、すでに完了しているものを確認できるようにします
exportCSV(assigned_point_adjustments)
raise
# 最大リクエスト数が 300 未満であれば、レート制限に達する可能性は低いです。もし達した場合、適切に処理します。
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:
# 失敗した場合、割り当てられた調整を CSV ファイルに保存し、すでに完了しているものを確認できるようにします
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:
# リクエスト時間が最大リクエスト待機時間より短い場合のみ待機
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 # SQL クエリの最大リミットです。
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)
ポイント調整を実行する手順
すべての要素が整ったので、ポイント調整プロセスを実行するために以下の手順に従ってください。Discourse 環境のテストインスタンスでこのプロセスを最初にテストし、問題点を解決することを強くお勧めします。
- Python スクリプト内の変数を希望の値に更新します。
- Gamification 設定内のポイント値を新しい値に変更します。
- 「全期間」のスコアを再計算します。
- Python スクリプトを実行し、完了するまで待ちます。システム内のユーザー数やレート制限に応じて、完了までに数分かかる場合があります。私たちは調整が必要なユーザーが 2,200 人おり、レート制限が 200 リクエスト/分の場合、完了までに約 15 分かかりました。
- 完了したら、念のため「全期間」のスコアを再度再計算します。
- 変更をロールバックする必要がある場合に備えて、CSV ファイルを安全な場所に保存して記録を残します。
ポイントの再計算には数時間かかる場合があります。調整が反映されるまで根気強く待機してください。再計算が完了すると、ユーザーのポイントが正常に調整され、今後ポイントは新しいポイント値に基づいて計算されます。
将来的にさらなる調整を行う場合
ポイント値の調整を行う必要がある場合は、同じ手順に従ってください。唯一の違いは、start_date を最後にポイント調整を実行した際の end_date に設定する必要があることです。例えば、最初にこのポイント調整を実行した際、end_date を 2024-05-15 に設定しました。これにより、フォーラム開始から 2024-05-15 までの間に獲得されたすべてのポイントが調整され、古い値が維持されました。1 年後に再度ポイント値を変更したいと仮定します。この場合、start_date を 2024-05-15 に、end_date を 2025-05-15 に設定する必要があります。これにより、ポイント調整はその期間にのみ適用され、最初の調整からの変更が上書きされないように保証されます。