How to distinguish audits from real reviews in SEDE data?

Question

I was trying to look at some statistics on review outcomes using SEDE, but hit upon a snag: apparently, the SEDE ReviewTasks table also includes audit tasks, and there doesn't seem to be any way to reliably tell them apart from real reviews.

If there is some way to tell which review tasks are audits in SEDE, please let me know how to do it. If not, could some way to do it (e.g. an IsAudit column on the ReviewTasks table) please be added?

(Before anybody asks, no, I don't think revealing this information via SEDE should be a problem, since there already exist much easier ways to automatically detect review audits if anybody wants to do that.)

---

Ps. Also, while I'm at it, is there any way to tell from SEDE whether e.g. a reopen review was triggered by an edit to the question or by an actual reopen vote? And if not, could that information also be added?

Answer 1

You have a high chance on finding audits by looking for those ReviewTasks that are completed by a single ReviewTaskResult AND have no corresponding posthistory event around that same time.

This query does that for audits in the close vote queue:

select top 100 
       'site://review/close/' 
     + cast(rt.id as nvarchar) 
     + '| ' 
     + p.title
     as [close audits for post]
from reviewtasks rt
inner join posts p on p.id = rt.postid
inner join ReviewTaskResults rr on rr.ReviewTaskId = rt.id
left outer join posthistory ph on ph.postid = rt.postid 
                              and ph.posthistorytypeid in (10,11) -- close / (leave) open
                              and ph.creationdate between rt.creationdate 
                                                      and dateadd(d, 1, rt.deletiondate)
where rt.reviewtasktypeid = 2 -- close votes
and rt.creationdate = rt.deletiondate
and rt.completedbyReviewTaskId is not null
and ph.id is null -- no actual posthistory event around the deletiontime
group by rt.id
    , p.title
    , rt.creationdate
having count(*) = 1
order by rt.creationdate desc

It still can miss some audits or indicate reviews as audits while they are not as you can't 100% pinpoint if a completed review actually did cause an posthistory event to be recorded (due to the creationdate and deletiondate sanitation of the reviewtask and the anonymity of the reviewtaskresult ) or if the posthistory events are caused due to other reasons.

In the grand scheme of things I assume this deviation is acceptable. I leave it to you to adapt the query to work for the reopen queue.

Answer 2

For the specific case of "good post" audits in the reopen queue, I did manage to find a partial work-around.

According to this answer by Shog9, such "known-good" audits are always generated using posts that have never been closed (or even had close votes cast on them, if I'm reading the answer right), whereas a real reopen review obviously requires the post to have been closed before it could become eligible for reopening. So simply requiring the post to have been closed at least once before the review should rule out most if not all "good" audits.

There do seem to be a couple of gotchas, though:

1. The history of deleted questions is not available on SEDE, so if a question is closed, sent to reopen review and then later deleted, the close event will not be visible on SEDE. That said, it seems pretty safe to assume that any reopen reviews on deleted questions are probably not known-good audits.

2. The timestamps for review tasks on SEDE only include the date, not the time of day, whereas post history timestamps are accurate to the second. Since it's quite common for reopen reviews to be triggered very soon after a question is closed (most commonly by an edit to the question), it's quite common for the (accurate) close event timestamp to appear to be up to 24 hours later than the (rounded) reopen review task timestamp. For a reliable comparison, both timestamps need to be rounded down to the previous midnight.

Still, I did manage to implement this filtering, and it seems to be working. And the difference it makes is pretty striking:

All reopen review outcomes on SO:

Same graph, excluding "known-good" audits:

Of course, the second graph does have some intrinsic bias, since it still includes "known-bad" audits which presumably inflate the "Leave Closed" outcome count somewhat. It would be interesting to see the same results with both kinds of audits excluded, but I still haven't managed to find a way to do that. Still, just seeing that over 60% of all the "Reopen" outcomes in the past 6 months have actually been audits is pretty remarkable.

---

Update: I came up with a hack for heuristically detecting "known-bad" audits: on the assumption that people usually don't just randomly vote to reopen long ago closed questions without at least editing or commenting on them, I decided to exclude any review tasks where the question had no post history events or comments within the week preceding the review. I think this should do a half-decent job of filtering out most audits, although obviously there will be some false positives and false negatives.

(In particular, since the review task timestamps only have one-day granularity, a reviewer commenting on an audit question could often be enough to make the query think it wasn't an audit. I have not done any sampling to see how widespread such errors might be.)

I also excluded reviews on questions that had been deleted before the supposed review took place, but this alone seemed to make no noticeable difference. Apparently, deleted questions are not normally used for "known-bad" reopen review audits.

Surprisingly, excluding the known-bad audits eliminated most of the seasonal spikes noticeable in the graphs above:

Reopen review outcomes on SO, (hopefully) excluding most audits:

I'm not sure why reopen review audit rates should have peaked every 10 months or so between 2013 and 2016, when the filtered audit-less reopen review rates show no such cycle, but that's what it looks like based on the graphs. Weird. Of course, it could be some error in my queries, but if so, I can't figure what could be causing it.