Jump to content

This is a read-only backup copy of Wikitech. The live site can be found at wikitech.wikimedia.org

Data Platform/Data Lake/Edits/Editor counts per page

From Wikitech

The wmf_contributors.editor_counts_per_page dataset holds distinct visible editor counts for every (wiki_id, page_id) across all Wikimedia wikis — broken down into total, permanent (registered human), bot, logged-out (anonymous), and temporary-account editors. It is built from the Incremental MediaWiki History table by a daily pipeline, stored as an Iceberg table in the Data Lake, and served per page from a Cassandra-backed Data Gateway endpoint.

User documentation

For each page on each wiki, the dataset stores the number of distinct editors who have made at least one publicly visible revision to that page, split by editor type:

  • Total — all distinct visible editors (humans and bots).
  • Permanent — distinct visible registered (permanent-account) human editors.
  • Bot — distinct visible editors classified as bots.
  • Logged-out — distinct visible anonymous (IP-based) human editors.
  • Temporary — distinct visible temporary-account human editors.

A "distinct editor" is counted once per page regardless of how many revisions they made. Editors are identified by a stable key (central user id when available, otherwise a local user id, otherwise an anonymous identity) so the same registered user is not double-counted across renames.

Schema

The Data Lake table ( wmf_contributors.editor_counts_per_page ) and the per-page API expose the same columns. One row per (wiki_id, page_id) .

column data type description
wiki_id string Wiki database code (e.g. enwiki , cawiki ). Part of the primary key.
page_id bigint MediaWiki page identifier within the wiki. Part of the primary key.
page_is_deleted boolean true when the page is deleted in MediaWiki History. false when page is not deleted. null when no deletion information is available.
editor_total_count int Distinct visible editors on this page (humans and bots), excluding cross-wiki editors.
editor_permanent_count int Distinct visible permanent (registered) human editors, excluding cross-wiki editors.
editor_bot_count int Distinct visible bot editors, excluding cross-wiki editors.
editor_logged_out_count int Distinct visible anonymous (IP-based) human editors, excluding cross-wiki editors.
editor_temporary_count int Distinct visible temporary-account human editors, excluding cross-wiki editors.
loaded_at timestamp Load watermark — when this row was written by the pipeline (the pipeline run's data_interval_end on incremental runs, current UTC on a full refresh). Not the underlying MediaWiki History update time.

Access

  • Attribution API : The Attribution API is the preferred access method for MediaWiki clients, allowing lookups by wiki_id and page_id .
  • Data Lake (Iceberg): WMF staff can also use the wmf_contributors.editor_counts_per_page table in dbt models, Jupyter notebooks and Superset dashboards. Also queryable directly from Presto / Spark for ad-hoc analysis. See examples below.

For other use cases, such as accessing this data from within a MediaWiki extension or exposing it through a different API, please contact the MediaWiki Interfaces and/or Data Engineering teams.

Examples

Attribution API

See more usage in mw:Attribution_API documentation. This API is in development and will incorporate editor counts soon. Learn more in the diff post .

Example API call with curl: 

curl 'https://en.wikipedia.org/w/rest.php/attribution/v0-beta/pages/Earth/signals?expand=trust_and_relevance' | jq

The output looks like the following as of beta v0. We are interested in the trust_and_relevance.contributor_counts . This will be updated to include the unique editor counts by permanent, bot, temp, and anonymous accounts. 

{
  "essential": {
    "link": "https://en.wikipedia.org/w/index.php?title=Earth&wprov=afsw1",
    "default_brand_marks": [
      {
        "name": "Default logo",
        "url": "https://en.wikipedia.org/static/images/project-logos/enwiki-25.png",
        "type": "logo"
      }...
    ],
    "source_wiki": {
      "site_name": "English Wikipedia",
      "project_family": "wikipedia",
      "site_id": "enwiki",
      "site_language": "en",
      "page_language": "en"
    },
    "title": "Earth",
    "license": {
      "title": "CC BY-SA 4.0",
      "url": "https://creativecommons.org/licenses/by-sa/4.0/deed.en"
    }
  },
  "trust_and_relevance": {
    "last_updated": "2026-07-02T17:36:24Z",
    "contributor_counts": null,
    "page_views": 244510,
    "reference_count": 310,
    "trending": {
      "top": {
        "read": false,
        "edited": false,
        "read_and_edited": false
      },
      "relative": {
        "read": false,
        "edited": false,
        "read_and_edited": false
      }
    }
  }
}

TBD: API response will be updated after Attribution API incorporates the editor counts.

dbt models

Sample dbt model that lists all pages with more than 100 unique editors. The editor counts table is accessed by ref('editor_counts_per_page') . 

{{ config(materialized='table') }}

SELECT
    wiki_id,
    page_id,
    editor_total_count,
    editor_permanent_count,
    editor_bot_count,
    editor_logged_out_count,
    editor_temporary_count
FROM {{ ref('editor_counts_per_page') }}
WHERE wiki_id = 'enwiki'
  AND editor_total_count > 100

SQL

We can use PySpark/Spark or Presto from the terminal in a stat machine; execute python or scala code in stat machines; run SQL directly in Superset ; or code in Jupyter Notebooks to run SQL or equivalent code to access this dataset.

Counts for a single page 

SELECT
    editor_total_count,
    editor_permanent_count,
    editor_bot_count,
    editor_logged_out_count,
    editor_temporary_count
FROM wmf_contributors.editor_counts_per_page
WHERE wiki_id = 'cawiki'
  AND page_id = 148945;

Top 20 pages on a wiki by distinct editors 

SELECT
    page_id,
    editor_total_count
FROM wmf_contributors.editor_counts_per_page
WHERE wiki_id = 'enwiki'
ORDER BY editor_total_count DESC
LIMIT 20;

Details

  • Visible revisions only. Counts include only editors with at least one publicly visible (non-suppressed, non-deleted) revision. A visibility change on an old revision can therefore change a page's counts.
  • Cross-wiki editors are excluded from all counts, until MediaWiki History exposes reliable user-status for them ( phab:T428018 ).
  • loaded_at is a load timestamp, not a data-freshness timestamp. It marks when the pipeline wrote the row, not when the underlying edit happened or when MediaWiki History last changed. Data freshness can be determined from when the latest airflow job ran.
  • New-wiki completeness gap. A wiki created mid-month can miss events until the next monthly namespace snapshot.
  • ~1 day producer latency. Data of a day is collected and processed at the start of the next day. So we get updated data of day D at the start of day D+1 .
  • ~2-3 days latency on first of month. The editor counts pipeline depends on incremental MWH, which in turn depends on the monthly MWH. The monthly MWH take 2-3 days to land. The 1st of the month of incremental MWH waits on this load before loading the data for the 1st of the month. Hence editor counts pipeline also waits ~2-3 days, after which the data from 1st of the month is loaded, followed by subsequent days.
  • Editor is bot. An editor is considered a bot if the editor belongs to a user group bot (bot by group) or has the term "bot" in the name (bot by name).
  • Bot as of event-time. We use event_user_is_bot_by_historical (bot status as of event time), as current bot status is not available in incremental MWH. This means if someone was deemed bot later on (or vice versa), we won't know that until that user makes a new change in that page. If we receive an update for that user, we update the bot field. We are able to take on the latest value of bot-ness (if available). More details here: phab:T426316#11979835 .
  • page_is_deleted is not comprehensive and may contain NULL values. MWH could not identify the status of those pages, hence they are NULL.

Technical documentation

The dataset is produced by a chain of three dbt models and one Airflow DAG, then loaded into Cassandra for per-page serving. A Data Gateway API acts as an interface for the Cassandra table to be used for serving into applications. 

incremental MediaWiki History  (wmf_mediawiki.mediawiki_history_incremental_v1)
        │
        ▼
stg_mwh__revision_editor       (view)         one row per visible revision, with editor identity/type
        │
        ▼
int_editors_per_page           (Iceberg)      one row per (wiki_id, page_id, editor_key)
        │
        ▼
editor_counts_per_page         (Iceberg)      one row per (wiki_id, page_id) with the count columns
        │
        ▼ (post-hook)
Cassandra  linked_artifacts.editor_counts_per_page  ──►  Data Gateway API

The original design for this pipeline can be found in a publicly-viewable Google document , kept for historical purposes.

Source: incremental MediaWiki History

The chain reads the incremental MediaWiki History table wmf_mediawiki.mediawiki_history_incremental_v1 rather than the monthly wmf.mediawiki_history snapshot. This gives daily freshness from a single table. Every touched row carries a row_update_dt watermark — re-stamped on inserts and on revert / visibility / page-deletion back-patches — so a single column captures every change the model cares about, including a visibility flip on an old revision.

dbt Models

wmf_contributors.stg_mwh__revision_editor (view)
Selects visible revisions from incremental MWH and derives each revision's editor identity ( editor_key ) and type flags (anonymous / temporary / permanent / bot / cross-wiki / visible). Incremental runs filter on a closed-right row_update_dt window (data_interval_start, data_interval_end] , with a 1st-of-month −1s reach-back to fold in the monthly reconcile. See phab:T428503#12003010 .
wmf_contributors.int_editors_per_page (Iceberg incremental table)
Aggregates staging to one row per (wiki_id, page_id, editor_key) . Lists all unique editors of a page with the latest-revision identity/type flags and a page-level editor_is_visible / page_is_deleted . Cross-wiki editors are retained here.
wmf_contributors.editor_counts_per_page (Iceberg incremental table)
Computes the distinct editor counts over visible, non-cross-wiki editors, one row per (wiki_id, page_id) . Incremental runs recount only pages touched in the current int_editors_per_page load, then MERGE into Iceberg. A post-hook loads the run's rows to Cassandra.

Incremental design and read window

The watermark is the Airflow logical_date interval itself — there is no stored stateful watermark, so retries, catchup , and backfills are reproducible because the read set is a pure function of the interval.

  • Normal day: row_update_dt ∈ (data_interval_start, data_interval_end] → reads the run's own day (fresh).
  • 1st of month: the lower bound reaches back one second ( >= data_interval_start − 1s ) to also capture the monthly reconcile, which is stamped at the last second of the prior month.
  • Touched-key re-aggregation: the model finds (wiki_id, page_id, editor_key) touched in the window, then re-aggregates all revisions for those keys (not an append), so a changed revision correctly re-derives the page's counts.

Orchestration

One Airflow DAG, editor_counts_per_page_daily_dag.py :

  • Schedule: @daily , max_active_runs=1 (the incremental-merge model must run one at a time).
  • Dependency: an ExternalTaskSensor on the incremental MWH daily writer (dataset iceberg_wmf_mediawiki_mediawiki_history_incremental_v1 ) at the same logical date. The 1st-of-month run is transitively gated on the prior month's reconcile through that writer, so no separate monthly job and no manual full refresh are needed.
  • SLA: 4 days — the 1st-of-month run is full-refresh-sized and can wait ~3 days on the monthly reconcile; with max_active_runs=1 the next runs queue behind it. The job lives in its own DAG (not the shared dbt_daily ) so this wait does not block other teams.
  • Load step: on success the dbt post-hook writes the run's rows to Cassandra ( aqs.linked_artifacts.editor_counts_per_page ). The Cassandra data is accessible through a Data Gateway endpoint. See usage below.
  • A manual --full-refresh rebuilds the whole table and re-pushes all rows.

Usage

See more usage examples in Data_Platform/Data_Lake/Edits/Editor_counts_per_page#Examples .

Data Gateway

Data Gateway is a wrapper on the Cassandra table and allows single-page lookups by (wiki_id, page_id) , backed by Cassandra . See the Data Gateway documentation for the endpoint contract. This is only to be used by internal APIs and not for direct access by external applications or APIs. 

$ curl https://data-gateway.discovery.wmnet:30443/public/linked_artifacts/editor_counts_per_page/cawiki/148945 | jq
{
  "rows": [
    {
      "wiki_id": "cawiki",
      "page_id": 148945,
      "editor_bot_count": 0,
      "editor_logged_out_count": 0,
      "editor_permanent_count": 2,
      "editor_temporary_count": 0,
      "editor_total_count": 2,
      "loaded_at": "2026-06-25 23:29:52.000Z",
      "page_is_deleted": false
    }
  ]
}

Operational notes

  • 1st-of-month run is full-refresh-sized — it re-aggregates the entire re-inserted snapshot and re-pushes the whole table to Cassandra.
  • Computes all touched pages — row_update_dt is re-stamped on any field change with back-patches. All such pages get re-aggregated irrespective of what changed.

Changes and known problems

Date from Task Details
2026-06-25 task T426316 Initial data set.
Retrieved from " https://wikitech.wikimedia.org/w/index.php?title=Data_Platform/Data_Lake/Edits/Editor_counts_per_page&oldid=2434626 "