Research:Daily media created

Daily media created

Specification

${\text{daily media created}}(D)$ is a count of media creations on date

D

.

WMF Standard

no parameters

Measures

Contribution

Status

completed

SQL

SET @date = "20140101";

SELECT
    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;

Daily media created is a standardized metric used to measure the number of media files that uploaded 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