Research:Daily unique registered editors

From Meta, a Wikimedia project coordination wiki
Daily unique registered editors
Specification
A is a user who is not a flagged bot and completed at least edits on date .
WMF Standard
  • = 1 edits
Status
completed
SQL
SET @date = "20140101";
SET @n = 1;

SELECT 
    COUNT(*) 
FROM (
    SELECT
        rev_user,
        SUM(revisions) AS revisions
    FROM (
        SELECT
            rev_user,
            COUNT(*) AS revisions
        FROM revision
        WHERE
            rev_timestamp BETWEEN @date AND
                DATE_FORMAT(DATE_ADD(@date, INTERVAL 1 DAY), "%Y%m%d%H%i%S") AND
            rev_user > 0 
        GROUP BY 1
        UNION ALL
        SELECT
            ar_user AS rev_user,
            COUNT(*) AS revisions
        FROM archive
        WHERE
            ar_timestamp BETWEEN @date AND
                DATE_FORMAT(DATE_ADD(@date, INTERVAL 1 DAY), "%Y%m%d%H%i%S") AND
            ar_user > 0
        GROUP BY 1
    ) AS user_revisions
    GROUP BY 1
) AS editors
LEFT JOIN user_groups ON
    ug_user = rev_user AND
    ug_group = "bot"
WHERE ug_group IS NULL
AND revisions >= @n;

Daily unique registered editors is a standardized metric used to measure the number of registered users who edit a wiki in a given day. It's used as a proxy for editing population size.

Discussion[edit]

Filtering bot accounts[edit]

Bot accounts are filtered using the bot flag strategy. This allows for straightforward and efficient bot filtering, but it is also possible that unflagged bots will be counted. Given the proportion of bot editors, this should have minuscule effect on the overall count.

Time lag[edit]

As this is a daily metric, a full 24 hours must elapse after the beginning of the date (UTC) in order to calculate an uncensored value.

Edits on deleted pages[edit]

This metric includes edits on existing pages as well as pages that have been or will later be deleted. This allows us to define a metric as stateless, in other words historical values will not change in the future depending on the status of a page (existing/deleted/moved) at the time the metric is computed. Deletion-related activity is tracked via a separate set of metrics.

Analysis[edit]

German Wikipedia[edit]

Unique editors (daily) are plotted over time for the German Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (dewiki). Unique editors (daily) are plotted over time for the German Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

English Wikipedia[edit]

Unique editors (daily) are plotted over time for the English Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (enwiki). Unique editors (daily) are plotted over time for the English Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

Spanish Wikipedia[edit]

Unique editors (daily) are plotted over time for the Spanish Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (eswiki). Unique editors (daily) are plotted over time for the Spanish Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

French Wikipedia[edit]

Unique editors (daily) are plotted over time for the French Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (frwiki). Unique editors (daily) are plotted over time for the French Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

Italian Wikipedia[edit]

Unique editors (daily) are plotted over time for the Italian Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (itwiki). Unique editors (daily) are plotted over time for the Italian Wikipedia with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

Wikidata[edit]

Unique editors (daily) are plotted over time for Wikidata with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).
Daily unique editors (wikidatawiki). Unique editors (daily) are plotted over time for Wikidata with different filters in place and with different thresholds min # of edits to be counted (n=1 or 5).

Discussion[edit]

Notes[edit]