Research:Daily unique media creators

Daily unique media creators

Specification

A ${\text{daily unique media creator}}(D,n)$ is a user who completed at least

n

media creations on date

D

.

WMF Standard

$n$ = 1 media creation

Measures

Editing population size

Status

completed

SQL

SET @date = "20140101";
SET @n = 1;

SELECT COUNT(*)
FROM (
    SELECT
        upload.log_user,
        COUNT(*) AS media_creations
    FROM (
        SELECT *
        FROM logging
        WHERE
            log_type = "upload" AND
            log_action = "upload" AND
            log_timestamp BETWEEN @date AND
                DATE_FORMAT(DATE_ADD(@date, INTERVAL 1 DAY), "%Y%m%d%H%i%S")
    ) AS upload
    LEFT JOIN logging old_upload ON
        old_upload.log_type = "upload" AND
        old_upload.log_action = "upload" AND
        old_upload.log_timestamp < @date AND
        upload.log_namespace = old_upload.log_namespace AND
        upload.log_title = old_upload.log_title
    WHERE old_upload.log_id IS NULL
    GROUP BY 1
) media_creators
WHERE media_creations >= @n;

Daily unique media creators is a standardized metric used to measure the number unique media uploaders in a given day (re-uploads/overwrites excluded). It's used as a proxy for overall Contribution.

Discussion

Identifying media creations

Since 2004, the logging table has maintained a history of "uploads" to MediaWiki instances. Using this table, we identify media creations as the first instance of log_type = "upload" AND log_action = "upload" for a given filename (log_title).

Time lag

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.

Analysis

Discussion

Notes