Research:Rolling re-activated editor

Standard metric

Rolling re-activated editor

Specification

A ${\text{rolling re-activated editor}}(T,u,n)$ is a user who completed less than $n$ edits between $T-2u$ and $T-u$ and completed $n$ edits (but was not a R:newly registered user) between $T-u$ and $T$.

WMF Standard

• $n$ = 5 edits
• $u$ = 30 days

Measures

Editor re-activation

Status

SQL

SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20140102"; /* February 1st, 2014 before midnight */
 
/* Create a temp table of the current month's active editors who are not new */
CREATE TEMPORARY TABLE staging.current_active_non_new
SELECT
    rev_user
FROM (
    SELECT
        rev_user
    FROM (
        SELECT
            rev_user
        FROM revision
        WHERE rev_timestamp BETWEEN
            DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
        UNION ALL
        SELECT
            ar_user AS rev_user
        FROM archive
        WHERE ar_timestamp BETWEEN
            DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
    ) AS split_revisions
    GROUP BY rev_user
    HAVING COUNT(*) >= @n
) AS active_editors
LEFT JOIN logging nru ON
    log_type = "newusers" AND
    log_action = "create" AND
    log_user = rev_user
WHERE 
    nru.log_id IS NULL OR 
    nru.log_timestamp <= DATE_FORMAT(
        DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S");

/* Create a temp table of the last month's active editors */
CREATE TEMPORARY TABLE staging.last_active
SELECT
    rev_user
FROM (
    SELECT
        rev_user
    FROM revision
    WHERE rev_timestamp BETWEEN
        DATE_FORMAT(DATE_SUB(@T, INTERVAL @u*2 DAY), "%Y%m%d%H%i%S") AND
        DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S")
    UNION ALL
    SELECT
        ar_user AS rev_user
    FROM archive
    WHERE ar_timestamp BETWEEN
        DATE_FORMAT(DATE_SUB(@T, INTERVAL @u*2 DAY), "%Y%m%d%H%i%S") AND
        DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S")
) AS split_revisions
GROUP BY rev_user
HAVING COUNT(*) >= @n;

SELECT
    user_id,
    user_name,
    user_registration
FROM staging.current_active_non_new
INNER JOIN user ON rev_user = user_id
WHERE 
    rev_user NOT IN (SELECT rev_user FROM staging.last_active) AND
    rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = "bot");

Rolling re-activated editors are non-bot editors who were not recently active in the previous time period, but are active in the current time period.

Discussion

The n edits threshold

The edits necessary to cross this threshold, the fewer editors will meet the criteria. Historically, "active editors" were considered to be editors who made 5 edits to specific types of pages over the course of a calendar month. So for historical purposes, we recommend $n=5$.

The u activity unit

The wider this unit is set, the more casual editors will be considered "active". Historically, "active editors" were considered during calendar months. So for historical purposes, we recommend $u=30$.

Time lag

This metric has a built-in time lag of $u$ for the time period in question.

Bot filtering

Bots are filtered using the bot flag method.