The discussion revolves around tracking post editing and revision activity on a website. bombledmonk wants to measure the number of edits made by users and the level of contribution to topical posts. codinghorror suggests using a Data Explorer query to extract this data. simon provides two SQL queries to retrieve the number of revisions per post and the number of unique users who have edited a post. However, bombledmonk is unable to find the Data Explorer feature, which simon confirms is not installed on the forum and offers to add it.
This is probably an edge case, but I’m curious to understand how much Editing/Revision is going on on my site. There are stats for Topics Viewed, Posts Viewed, Read Time, etc, but I’m looking also for how many edits a person has done. Is there any way to extract this somehow?
The reason why i’m curious is that there’s people publishing and editing content on the website and there may be multiple people revising the same Topical post, but there’s not really a good way to metric when multiple people have contributed, nor their level of contribution. The people primarily revising content are essentially invisible.
There is information about revisions in the post_revisions table. You can run these as Data Explorer queries The first will give the number of revisions per post, the second gives the number of unique users who have edited a post.
SELECT
pr.post_id,
count(pr.post_id) AS revisions
FROM post_revisions pr
GROUP BY pr.post_id
ORDER BY revisions DESC
LIMIT 50
SELECT
pr.post_id,
COUNT(DISTINCT pr.user_id) AS reviser_count
FROM post_revisions pr
GROUP BY pr.post_id
ORDER BY reviser_count DESC
LIMIT 50
Kind of a silly question, but I can’t seem to find the data explorer anymore. I could swear it was enabled on our instance previously, but for the life of me I can’t find it.