Global Editor Metrics - Data Pipeline - edit_per_editor_per_page_daily
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	Ottomata
	Oct 16 2025, 7:50 PM

Description

In order to compute per editor pageview metrics, we need a daily updated Data Lake table that allows us to lookup pages edited by a user on or before a date.

See parent T405039: Global Editor Metrics - Data Pipeline description for more detail and options.

This table can be backfilled using mediawiki_history (after T365648) and computed ongoing daily from mediawiki_content_history_v1 (via T406515).

Done is

Hive table exists that given a user_central_id and a date, can lookup list of wiki_id,page_id pairs that the user has edited on or before that date.
Hive table is updated daily.

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Add HQL for edit_per_editor_per_page_daily and pageview_per_editor_per_page_daily	analytics/refinery	master	+724 -0

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Open	None	T382442 [Epic] iOS Wikipedia Year in Review 2025 (V3)
Resolved	HNordeenWMF	T388060 [Sub-Epic] [WE 3.2.9] On-device statistics Improvements for Year in Review 2025
Open	None	T341649 Provide an easy way for MediaWiki to fetch aggregate data from the data lake
Open	None	T388455 [Spike] Full-year editing stats for Year in Review
Open	Ottomata	T403660 WE3.3.7 Year in Review and Activity Tab Services - Global Editor Metrics
Open	amastilovic	T405039 Global Editor Metrics - Data Pipeline
Open	xcollazo	T406515 Add user_central_id to mediawiki_content_history_v1 (and mediawiki_content_current_v1)
Open	amastilovic	T365648 Add user_central_id to mediawiki_history and mediawiki_history_reduced Hive tables
Open	None	T407559 Global Editor Metrics - Data Pipeline - edit_per_editor_per_page_daily

Event Timeline

Ottomata created this task.Oct 16 2025, 7:50 PM

Ottomata updated the task description. (Show Details)

Ottomata added a parent task: T406515: Add user_central_id to mediawiki_content_history_v1 (and mediawiki_content_current_v1).

Ottomata added a parent task: T365648: Add user_central_id to mediawiki_history and mediawiki_history_reduced Hive tables.

Ottomata removed a subscriber: Eevans.

Ottomata updated the task description. (Show Details)Oct 16 2025, 7:53 PM

Ottomata updated the task description. (Show Details)

Ottomata added a subscriber: xcollazo.

Change #1196892 had a related patch set uploaded (by Ottomata; author: Ottomata):

[analytics/refinery@master] Add HQL for user_edited_pages_daily

https://gerrit.wikimedia.org/r/1196892

gerritbot added a project: Patch-For-Review.Oct 17 2025, 11:41 AM

@mforns let me know if this ticket and Add HQL for user_edited_pages_daily (1196892) is making sense to you!

Ottomata added a subscriber: JAllemandou.Oct 17 2025, 3:07 PM

amastilovic mentioned this in T405039: Global Editor Metrics - Data Pipeline.Oct 17 2025, 4:44 PM

TIL about wmf_contributors.editor_month table, which is a Movement Insights owned Iceberg table that stores monthly edit counts per user (thank you Datahub!).

Should we do also do Iceberg in wmf_contributors for user_edited_pages instead of Hive? Would the data model change? Hm.

Ottomata updated the task description. (Show Details)Oct 20 2025, 5:45 PM

Ottomata renamed this task from Global Editor Metrics - Data Pipeline - user_edited_pages to Global Editor Metrics - Data Pipeline - edit_per_user_per_page_daily.Oct 27 2025, 7:48 PM

Ottomata renamed this task from Global Editor Metrics - Data Pipeline - edit_per_user_per_page_daily to Global Editor Metrics - Data Pipeline - edit_per_editor_per_page_daily.Oct 27 2025, 8:27 PM

Change #1196892 merged by Aleksandar Mastilovic:

[analytics/refinery@master] Add HQL for edit_per_editor_per_page_daily and pageview_per_editor_per_page_daily