Is it possible to get a report on post approvals?

Feature
1

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.

3 Likes
2

Interesting, why has it come to this?

3

I think you’ll need to use the data explorer plugin.

4

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. :upside_down_face:

4 Likes
5

I could have written this. We have set so that all posts by TL0 users are approved by staff.

2 Likes
6

These are good things for @eviltrout to read as he is working on a big project centralizing all approval and flag handling paths.

1 Like
7

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.)

8 Likes
8

@eviltrout How would I construct queries like this in the new schema?

9

I’d prefer not to type up how to do it until the new schema is merged, but it is possible to do all these reports.

5 Likes
10

Looks like the new schema is merged! Can you suggest how to generate reports like these in the new review queue?

11

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.

3 Likes
12

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
4 Likes