Inconsistent counts of global account registrations in analytics datasets
Closed, InvalidPublic
Actions

Assigned To

None

Authored By

	nshahquinn-wmf
	Jun 13 2026, 2:37 AM

Project Tags

Referenced Files

None

Subscribers

Description

mediawiki_history (MWH), mediawiki_user_history (MWUH), and serversideaccountcreation (SSAC) record marginally different numbers of global account registrations. Here are the counts for May 2026:

table	global registration count
`mediawiki_history`	246,238
`mediawiki_user_history`	249,822
`serversideaccountcreation`	248,273

It seems reasonable that event loss could cause SSAC to end up with slightly fewer rows, but it's much more surprising that MWH and MWUH differ, since they come from the same history reconstruction process.

Queries

`mediawiki_history`

SELECT
    COUNT(DISTINCT event_user_central_id) AS registration_count
FROM wmf.mediawiki_history
LEFT JOIN canonical_data.wikis w
ON wiki_db = database_code
WHERE
    event_entity = 'user'
    AND event_type = 'create'
    AND NOT event_user_is_created_by_system
    -- Filter out wikis which are not connected to CentralAuth
    -- and which require separate registration
    AND NOT w.editability = 'private'
    AND event_timestamp BETWEEN '2026-05' AND '2026-06'
    AND snapshot = '2026-05'

`mediawiki_user_history`

SELECT
    COUNT(DISTINCT user_central_id) AS registration_count
FROM wmf.mediawiki_user_history
LEFT JOIN canonical_data.wikis w
ON wiki_db = database_code
WHERE
    caused_by_event_type = 'create'
    AND NOT created_by_system
    -- Filter out wikis which are not connected to CentralAuth
    -- and which require separate registration
    AND NOT w.editability = 'private'
    AND start_timestamp BETWEEN '2026-05' AND '2026-06'
    AND snapshot = '2026-05'

`serversideaccountcreation`

SELECT
    COUNT(*) AS registration_count
FROM event_sanitized.serversideaccountcreation
LEFT JOIN canonical_data.wikis w
ON wiki = database_code
WHERE
    -- Filter out wikis which are not connected to CentralAuth
    -- and which require separate registration
    NOT w.editability = 'private'
    AND MAKE_DATE(year, month, day) >= DATE '2026-05-01'
    AND MAKE_DATE(year, month, day) < DATE '2026-06-01'

Related Objects

Mentioned In: T429288: event_sanitized.serversideaccountcreation reports users that actually don't exist
T414996: [MI 3] Monitor and investigate movement trends
Mentioned Here: T423952: Create mediawiki.user_change event stream
T422732: Convert wmf_contributors.editor_month from an Airflow job to a Dbt job, adding user central ID and mobile content edit count

Event Timeline

nshahquinn-wmf created this task.Jun 13 2026, 2:37 AM

Restricted Application added a project: Data-Engineering. · View Herald TranscriptJun 13 2026, 2:37 AM

Restricted Application added a subscriber: • Aklapper. · View Herald Transcript

@JAllemandou maybe you have ideas about the difference between MWH and MWUH? Am I missing anything in my queries?

@Milimetric FYI since we discussed the possibility for eventloss in SSAC—if we take mediawiki_user_history as the source of truth, then SSAC loses 0.6% of the events.

FYI, I don't consider this is a urgent issue. My main desire here is to document the oddity, figure out if others know or suspect the reason, and collect ideas for future work.

Related: I've drafted a Dbt model for global registrations (as part of T422732). Anyone interested in this should definitely review it!

nshahquinn-wmf mentioned this in T414996: [MI 3] Monitor and investigate movement trends.Jun 15 2026, 5:11 AM

APizzata-WMF added a project: DPE-MediaWiki-Incremental-History.Jun 16 2026, 9:51 AM

APizzata-WMF subscribed.

maybe you have ideas about the difference between MWH and MWUH? Am I missing anything in my queries?

Hey @nshahquinn-wmf the two queries are counting different entities. In mediawiki_history the event_user_central_id is the actor performing the action, whereas the user_central_id is the actual created account. The latter column matches the user_central_id column in the mediawiki_user_history table.

Therefore the query should be:

spark.sql("""
SELECT COUNT(DISTINCT user_central_id) AS registration_count
FROM wmf.mediawiki_history
LEFT JOIN canonical_data.wikis w ON wiki_db = database_code
WHERE event_entity = 'user'
  AND event_type   = 'create'
  AND NOT created_by_system
    -- Filter out wikis which are not connected to CentralAuth
    -- and which require separate registration
  AND NOT w.editability = 'private'
  AND event_timestamp BETWEEN '2026-05' AND '2026-06'
  AND snapshot = '2026-05';
    """).show()

+------------------+
|registration_count|
+------------------+
|            249822|
+------------------+

Which matches the mediawiki_user_history result.

FYI: T423952: Create mediawiki.user_change event stream. event.mediawiki_user_change_dev0 has data now.

In T429061#12024164, @APizzata-WMF wrote:

Hey @nshahquinn-wmf the two queries are counting different entities. In mediawiki_history the event_user_central_id is the actor performing the action, whereas the user_central_id is the actual created account. The latter column matches the user_central_id column in the mediawiki_user_history table.

Ahh, of course! Thank you very, very much @APizzata-WMF! 🏆

I'll close this; the remaining divergence between SSAC and MWH/MWUH is less concerning and less important as we'll probably migrate many uses to mediawiki.user_change over time.

Ottomata mentioned this in T429288: event_sanitized.serversideaccountcreation reports users that actually don't exist.Wed, Jun 17, 5:31 PM

Inconsistent counts of global account registrations in analytics datasetsClosed, InvalidPublicActions