top_pages_per_editor returns implausible data for metawiki:Steward requests/Global permissions
Open, Needs TriagePublic
Actions

Assigned To

Authored By

	Urbanecm_WMF
	Wed, Aug 5, 4:30 PM

Project Tags

Referenced Files

	F97274868: image.png
	Wed, Aug 5, 4:30 PM

Subscribers

Aklapper

Ottomata

Urbanecm_WMF

Description

NOTE: This is relevant to DE1.3.2 Impact + Dashboard, owned by Growth-Team, which intends to use this API.

https://wikimedia.org/api/rest_v1/metrics/pageviews/v3/top_pages_per_editor/34722510/monthly/20260101/20260201 claims that:

{
    "context": {
        "endpoint": "pageviews/v3/top_pages_per_editor",
        "user_central_id": 34722510,
        "granularity": "monthly",
        "start": "2026-01-01T00:00:00.000Z",
        "end": "2026-02-01T00:00:00.000Z"
    },
    "items": [
            {
                "timestamp": "2026-01-01T00:00:00.000Z",
                "top_k": 10,
                "rank_items": [
                    {
                        "rank": 1,
                        "wiki_id": "metawiki",
                        "page_id": 135805,
                        "view_count": 75149
                    },
                    // [...]
                ]
        }
    ]
}

I interpret this as "in January 2026, metawiki's page 135805 was visited 75149 times". However, I was unable to find this number when cross-checking with pageviews dataset itself.

According to the query, the pageviews are computed for the calendar month represented by the timestamp key. For this case, this should mean January 2026.

Pageviews tool

I checked the Pageviews tool, which says:

putting the number of January 2026 pageviews at Pageviews: 26,826 (significantly longer than the 75k figure given by the other tool).

`wmf.pageview_hourly`

spark-sql (default)> select sum(view_count)
                   >   from wmf.pageview_hourly
                   >   where year=2026 and month=1
                   >   and project = 'meta.wikimedia'
                   >   and page_id = 135805
                   > and agent_type = 'user';
sum(view_count)
29051
Time taken: 84.412 seconds, Fetched 1 row(s)
spark-sql (default)>

this puts the number of pageviews at 29051 (slightly higher than the Pageviews tool, but attributable to query looking for something slightly different).

Intermediary tables

The wmf_readership.pageview_per_editor_per_page_daily and wmf_readership.pageviews_top_pages_per_editor intermediary tables have the wrong numbers:

spark-sql (default)> 
                   > 
                   > 
                   > 
                   > select sum(view_count) from wmf_readership.pageview_per_editor_per_page_daily
                   >   where user_central_id = 34722510
                   >   and wiki_id = 'metawiki'
                   >   and page_id = 135805
                   >   and day >= DATE '2026-01-01' and day < DATE '2026-02-01'
                   > ;
sum(view_count)
75149
Time taken: 33.627 seconds, Fetched 1 row(s)
spark-sql (default)> select view_count from wmf_readership.pageviews_top_pages_per_editor
                   >   where user_central_id = 34722510
                   >   and wiki_id = 'metawiki'
                   >   and page_id = 135805
                   >   and granularity = 'monthly'
                   >   and dt = TIMESTAMP '2026-01-01 00:00:00.000'
                   > ;
view_count
75149
Time taken: 10.656 seconds, Fetched 1 row(s)
spark-sql (default)>

What is the cause for the discrepancy? Are we double-counting certain pageviews? Or are pageviews defined differently in this specific endpoint?

Related Objects

Mentioned Here: T434217: wmf_contributors.edit_per_editor_per_page_daily claims `Martin Urbanec` is a system user

Event Timeline

Urbanecm_WMF created this task.Wed, Aug 5, 4:30 PM

Restricted Application added a project: Growth-Team. · View Herald TranscriptWed, Aug 5, 4:31 PM

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

Discussed via Slack as well (WMF internal link).

Urbanecm_WMF moved this task from Inbox to Tracking on the Growth-Team board.Wed, Aug 5, 4:32 PM

Urbanecm_WMF moved this task from Backlog to External on the DE1.3.2 Impact + Dashboard board.

Milimetric assigned this task to Ahoelzl.Wed, Aug 5, 4:51 PM

Milimetric moved this task from Incoming (new tickets) to Q1 FS26/27 July 1st - September 30th on the Data-Engineering board.

Milimetric edited projects, added: Data-Engineering (Q1 FS26/27 July 1st - September 30th); removed: Data-Engineering.

wmf_readership.pageview_per_editor_per_page_daily is where to look. It If this number is wrong, all the other downstream ones will be.

It is loaded by load_pageview_per_editor_per_page_daily.hql, which sources its data from pageview_hourly. pageview_hourly is the canonical source here.

Pageviews tool
[...]
putting the number of January 2026 pageviews at Pageviews: 26,826
[...]
[pageview_hourly] puts the number of pageviews at 29051 (slightly higher than the Pageviews tool, but attributable to query looking for something slightly different).

BTW, the pageviews tools is fetching from the /pageviews/aggregate endpoint, which IIRC aggregates by page_title instead of page_id. Not all views are correctly tagged with page_id, so I'd guess this is the source of discrepancy.

I noticed something interesting. wmf_readership.pageview_per_editor_per_page_daily has duplicated rows (more than one entry for the same wiki, user and page):

spark-sql (default)> SELECT day, COUNT(*) AS row_count, SUM(view_count)
                   > FROM wmf_readership.pageview_per_editor_per_page_daily
                   > WHERE user_central_id = 34722510 AND wiki_id = 'metawiki' AND page_id = 135805
                   > AND day >= DATE '2026-01-01' AND day < DATE '2026-02-01'
                   > GROUP BY day
                   > HAVING COUNT(*) > 1
                   > ORDER BY day;
day     row_count       sum(view_count)
2026-01-01      3       1224
2026-01-02      3       1800
2026-01-03      3       3651
2026-01-04      3       7878
2026-01-05      3       13155
2026-01-06      3       12546
2026-01-07      3       11646
2026-01-08      3       10962
2026-01-09      3       4290
2026-01-10      3       684
2026-01-11      3       864
Time taken: 29.8 seconds, Fetched 11 row(s)
spark-sql (default)>

Those rows contain exactly the same data:

spark-sql (default)> SELECT * FROM wmf_readership.pageview_per_editor_per_page_daily
                   > WHERE user_central_id = 34722510 AND wiki_id = 'metawiki' AND page_id = 135805 AND day = DATE '2026-01-01';
day     user_central_id user_id user_name       user_is_bot     user_is_system  wiki_id wiki    pageview_project        page_id view_count
2026-01-01      34722510        7034294 Martin Urbanec  false   true    metawiki        meta.wikimedia.org      meta.wikimedia  135805  408
2026-01-01      34722510        7034294 Martin Urbanec  false   true    metawiki        meta.wikimedia.org      meta.wikimedia  135805  408
2026-01-01      34722510        7034294 Martin Urbanec  false   true    metawiki        meta.wikimedia.org      meta.wikimedia  135805  408
Time taken: 20.309 seconds, Fetched 3 row(s)
spark-sql (default)>

It also claims I am a system user (filled T434217 for that bug instead).

Removing the duplicate records produces a much more reasonable result:

spark-sql (default)> SELECT SUM(view_count)
                   > FROM (
                   > SELECT day, MAX(view_count) AS view_count
                   > FROM wmf_readership.pageview_per_editor_per_page_daily
                   > WHERE user_central_id = 34722510 AND wiki_id = 'metawiki' AND page_id = 135805
                   > AND day >= DATE '2026-01-01' AND day < DATE '2026-02-01'
                   > GROUP BY day);
sum(view_count)
29349
Time taken: 47.833 seconds, Fetched 1 row(s)
spark-sql (default)>

29349 is still not exactly what I got from pageviews_hourly, but it's much closer at least.

Why are the rows duplicated?

The duplication starts in wmf_contributors.edit_per_editor_per_page_daily:

spark-sql (default)> SELECT day, COUNT(*)
                   > FROM wmf_contributors.edit_per_editor_per_page_daily
                   > WHERE user_central_id = 34722510 AND wiki_id = 'metawiki' AND page_id = 135805
                   > GROUP BY day HAVING COUNT(*) > 1 ORDER BY day;
day     count(1)
2025-02-14      3
2025-02-15      3
2025-08-07      3
Time taken: 30.694 seconds, Fetched 3 row(s)
spark-sql (default)>

Those rows are from 2025, but the API looks at all pages the user has ever edited.

In the query, latest_user_data is not filtering those duplicates down. As a result, JOINing latest_user_data and pageviews_daily contains those duplicates as well. The API is apparently summing multiple rows for the same day+user+page+wiki.

Urbanecm_WMF added a project: OKR-Work (DE1 FY2026-27).Thu, Aug 6, 4:33 PM

top_pages_per_editor returns implausible data for metawiki:Steward requests/Global permissionsOpen, Needs TriagePublicActions