We’ve configured our forum to require approval for posts from new users. We approve posts pretty fast, I think, but I wanted to check the data to see how we’re doing.
Is there an admin report on how many posts we’ve approved, how long posts have to wait in the queue, which moderators approve/reject the most posts, etc.?
I couldn’t find anything obvious on the Dashboard.
Well, it started when we had a troll circumventing a ban. (I think he literally posted something like, “Hah, suckers, you can’t ban me, I can just create a new account.”) He switched IPs and everything.
So we turned on new-post approval for a while, thinking it would be temporary. But it turned out to be not that onerous, and it gave us an opportunity to fix problems before they start, so we just left it on.
It came in especially handy when the moderator of another forum offered karma points to anyone who successfully trolled us.
For the record, here are the queries I’ve come up with using the data explorer plugin. (I’d appreciate code review on these.)
Approvals and rejections by moderator in the last 30 days:
SELECT count(q.id), username, state
FROM queued_posts q
JOIN users u on q.approved_by_id = u.id
OR q.rejected_by_id = u.id
WHERE queue='default'
AND q.created_at > now() - interval '30 days'
GROUP BY username, state
ORDER BY username
Average time to approval:
SELECT sum(approved_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 2
AND created_at > now() - interval '30 days'
Average time to rejection:
SELECT sum(rejected_at - created_at)/count(id)
FROM queued_posts
WHERE queue='default' and state = 3
AND created_at > now() - interval '30 days'
For us, we had a 37 minute time to approval and a 56 minute time to rejection in the last 30 days. (I suspect that rejection time may be longer because sometimes when moderators see a questionable post, they may decide to pass the buck and leave it in the queue for another moderator.)
It looks like you are comfortable with SQL. All reviewable items are in the reviewables table, with a type column that differentiates which type of thing it is. The status column indicates the current state (pending / approved / rejected / ignored / deleted.)
If you want to know when something happened to a reviewable, that information is in the reviewable_histories table.
Approvals and rejections by moderator in the last 30 days:
SELECT username, r.status, count(r.id)
FROM reviewables r
JOIN (
SELECT max(h.id) as history_id, reviewable_id
FROM reviewable_histories h
GROUP BY reviewable_id
) latest
ON r.id = latest.reviewable_id
JOIN reviewable_histories h
ON h.id = latest.history_id
JOIN users u
ON h.created_by_id = u.id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY username, r.status
ORDER BY username, r.status
Average time to approval/rejection:
SELECT sum(h.created_at - r.created_at)/count(r.id), r.status
FROM reviewables r
JOIN (
SELECT max(h.id) as history_id, reviewable_id
FROM reviewable_histories h
GROUP BY reviewable_id
) latest
ON r.id = latest.reviewable_id
JOIN reviewable_histories h
ON h.id = latest.history_id
WHERE r.type = 'ReviewableQueuedPost'
AND r.created_at > now() - interval '30 days'
GROUP BY r.status
ORDER BY r.status