Research:Newly registered user
/* specify a datetime range */
SET @start_date = '20131001000000';
SET @end_date = '20131101000000';
SELECT
`log_user` AS user_id,
REPLACE(`log_title`, '_', ' ') AS user_name,
`log_timestamp` AS user_registration
FROM
enwiki.logging
WHERE
`log_type` = 'newusers' /* exclude proxy registrations */
AND
`log_action` = 'create' /* only include self-created users, exclude attached and proxy-registered users */
AND
`log_timestamp` BETWEEN @start_date AND @end_date;
Newly registered user is a standardized user class used to measure number of new users signing up on a given wiki project for the first time. It's used as a proxy for user acquisition.
Discussion
[edit]The proposed definition on what counts as a newly registered user makes a number of assumptions and exceptions:
- Users who originally register an account on project A and visit for the first time project B become "attached" to project B. When they do, a record is created in project B. As such, these users are not genuinely "new" and should not be counted in this class.[1]
- User accounts can be created by proxy by existing users. These accounts should be excluded from the definition of newly registered users.
- Newly registered users include all self-registered accounts, regardless of the version of a project (desktop or mobile) the user visited when signing up.
- Users may have their status changed after registration to reflect that they are bots. These users are included in the newly registered user definition (unless they are proxy-registered) because a change to bot status only occurs after registration.
- Users who register a legitimate alternate account are included in the newly registered user definition (unless they are proxy-registered) because a change to flag a username as an alternate account only occurs after registration.
Historical definition
[edit]There is no historical definition of a newly registered user provided by the Wikimedia Foundation. New account registrations do not form part of data historically collected and exposed via WikiStats, which refers instead to the notion of a New Wikipedian (a notion based on editor activity, not account registration). However, new account registrations are one of the key metrics that the Wikimedia Foundation has been using as part of the editor engagement program and specifically growth experiments focused on new user acquisition.
Data sources
[edit]Newly registered users are logged globally via Schema:ServerSideAccountCreation or locally on a per-project basis via MediaWiki's logging table. Note that the user table is not a reliable source of new registration data, as it includes both attached and proxy-registered users.
Sample queries
[edit]Newly registered users (local)
EventLogging
|
|---|
SELECT
`event_userId` AS user_id,
`event_userName` AS user_name,
`timestamp` AS user_registration
FROM
log.ServerSideAccountCreation_5487345 /* schema revision reference */
WHERE
`event_isSelfMade` = 1 /* exclude proxy registrations */
AND
`wiki` = 'enwiki' /* specify the project. Omit for global registrations */
AND
`timestamp` >= '20131001000000' /* specify a datetime range */
AND
`timestamp` < '20131101000000' /* specify a datetime range */
|
logging table
|
|---|
SELECT
`log_user` AS user_id,
`log_timestamp` AS user_registration
FROM
enwiki.logging
WHERE
`log_type` = 'newusers' /* exclude proxy registrations */
AND
`log_action` = 'create' /* only include self-created users, exclude attached and proxy-registered users */
AND
`log_timestamp` >= '20131001000000' /* specify a datetime range */
AND
`log_timestamp` < '20131101000000' /* specify a datetime range */
|
Newly registered users (global)
EventLogging
|
|---|
SELECT
`event_userId` AS user_id,
`event_userName` AS user_name,
`timestamp` AS user_registration
FROM
log.ServerSideAccountCreation_5487345 /* schema revision reference */
WHERE
`event_isSelfMade` = 1 /* exclude proxy registrations */
AND
`timestamp` >= '20131001000000' /* specify a datetime range */
AND
`timestamp` < '20131101000000' /* specify a datetime range */
|
Notes
[edit]- Newly registered users, attached users and proxy-registered users can be selected in the MediaWiki logging table by specifying log_action='create', log_action='autocreate' and log_action='create2' respectively.
- The logging table only logs user registrations since 2005-09-07 22:16:49. EventLogging logs registrations via ServerSideAccountCreation since 2013-02-04 19:29:31. The log version currently in production (5487345) logs registration data since 2013-06-13 23:58:29. New user registration data may be inaccurate prior to 2005. At that time, MediaWiki would sometimes record the date of a user's first edit as their registration date. The globaluser table (holding global registrations across wikis) only includes data since 2008-03-13 04:16:56.
Usage
[edit]See the following research reports focused on user acquisition for examples of usage of this user class:
The notion of a newly registered users is also used in new account registration dashboards.
Analysis
[edit]The following plots represent daily new registered users between 2007 and 2013 (based on the above definition and obtained via MediaWiki's logging tables) and their decomposition into a seasonal, trend and residual component. Time series were normalized for leap years by removing February 29 data.
Long-term trends in newly registered users show substantial differences across languages: the German and English Wikipedia show an upward trend as of 2013 after years of decline Spanish is seeing a strong positive trend in 2013. French has seen almost no variation in acquisitions for years and shows some slight sign of growth as of 2013. Russian new registrations plateaued in 2010 and have remained constant since then. Growth observed in 2013 is primarily driven by mobile user acquisition.
- ↑ There are also several bugs and other sources of unexpected registrations, see bugzilla:16864 and phabricator:T74469.