Page MenuHomePhabricator
Paste P83599

Distribution of incoming mobile visits between mobile_pageview and mobile_redirect (Sep-Oct 2025)
ActivePublic

Authored by Krinkle on Oct 5 2025, 10:21 AM.
# Context: https://www.mediawiki.org/wiki/Requests_for_comment/Mobile_domain_sunsetting/2025_Announcement
# Context: https://phabricator.wikimedia.org/T405429
#
# These SQL queries were run in Hadoop.
# -------
# 2025-09-27: Rolled out wikis
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
CASE WHEN is_redirect_to_pageview THEN 'mobile_redirect' ELSE 'mobile_pageview' END AS _bucket,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month=9
AND agent_type='user'
AND referer_class!='internal'
AND uri_host RLIKE '(^www\.mediawiki\.org|^(ca|he|it|fa)\.wikipedia\.org|^meta\.wikimedia\.org|\.(wikinews|wikibooks|wikiquote|wikivoyage|wikiversity)\.org)$'
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND user_agent RLIKE '(?i)(android|mobi)') OR (is_pageview AND access_method='mobile web'))
GROUP BY year,month,day,is_pageview,is_redirect_to_pageview
ORDER BY _date ASC, _bucket ASC;
_date _bucket _count
2025-09-01 mobile_redirect 9173124
2025-09-02 mobile_redirect 9139723
2025-09-03 mobile_redirect 8591564
2025-09-04 mobile_redirect 9007455
2025-09-05 mobile_pageview 20249
2025-09-05 mobile_redirect 9236422
2025-09-06 mobile_pageview 58671
2025-09-06 mobile_redirect 9956883
2025-09-07 mobile_pageview 60916
2025-09-07 mobile_redirect 10808248
2025-09-08 mobile_pageview 52414
2025-09-08 mobile_redirect 8637792
2025-09-09 mobile_pageview 39944
2025-09-09 mobile_redirect 9013440
2025-09-10 mobile_pageview 42360
2025-09-10 mobile_redirect 9264013
2025-09-11 mobile_pageview 44976
2025-09-11 mobile_redirect 8463800
2025-09-12 mobile_pageview 51543
2025-09-12 mobile_redirect 8813351
2025-09-13 mobile_pageview 36480
2025-09-13 mobile_redirect 9018222
2025-09-14 mobile_pageview 41452
2025-09-14 mobile_redirect 9851504
2025-09-15 mobile_pageview 35700
2025-09-15 mobile_redirect 8299137
2025-09-16 mobile_pageview 29582
2025-09-16 mobile_redirect 8415992
2025-09-17 mobile_pageview 2594172
2025-09-17 mobile_redirect 5755458
2025-09-18 mobile_pageview 6950199
2025-09-18 mobile_redirect 1973708
2025-09-19 mobile_pageview 8992180
2025-09-19 mobile_redirect 302325
2025-09-20 mobile_pageview 9654453
2025-09-20 mobile_redirect 517064
2025-09-21 mobile_pageview 10353964
2025-09-21 mobile_redirect 498543
2025-09-22 mobile_pageview 8782507
2025-09-22 mobile_redirect 436490
2025-09-23 mobile_pageview 8892142
2025-09-23 mobile_redirect 454979
2025-09-24 mobile_pageview 9405849
2025-09-24 mobile_redirect 199306
2025-09-25 mobile_pageview 9181829
2025-09-25 mobile_redirect 104708
2025-09-26 mobile_pageview 9384746
2025-09-26 mobile_redirect 126200
# 2025-09-27: All wikis
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
CASE WHEN is_redirect_to_pageview THEN 'mobile_redirect' ELSE 'mobile_pageview' END AS _bucket,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month=9
AND agent_type='user'
AND referer_class!='internal'
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND user_agent RLIKE '(?i)(android|mobi)') OR (is_pageview AND access_method='mobile web'))
GROUP BY year,month,day,is_pageview,is_redirect_to_pageview
ORDER BY _date ASC, _bucket ASC;
_date _bucket _count
2025-09-01 mobile_redirect 142201266
2025-09-02 mobile_redirect 138331858
2025-09-03 mobile_pageview 592
2025-09-03 mobile_redirect 135349366
2025-09-04 mobile_pageview 2099
2025-09-04 mobile_redirect 136241581
2025-09-05 mobile_pageview 28675
2025-09-05 mobile_redirect 142230435
2025-09-06 mobile_pageview 74057
2025-09-06 mobile_redirect 156415641
2025-09-07 mobile_pageview 63517
2025-09-07 mobile_redirect 166823456
2025-09-08 mobile_pageview 55108
2025-09-08 mobile_redirect 140957942
2025-09-09 mobile_pageview 48609
2025-09-09 mobile_redirect 140381094
2025-09-10 mobile_pageview 53205
2025-09-10 mobile_redirect 149529379
2025-09-11 mobile_pageview 48522
2025-09-11 mobile_redirect 140852499
2025-09-12 mobile_pageview 62666
2025-09-12 mobile_redirect 140701802
2025-09-13 mobile_pageview 38824
2025-09-13 mobile_redirect 152986136
2025-09-14 mobile_pageview 53612
2025-09-14 mobile_redirect 168662255
2025-09-15 mobile_pageview 45451
2025-09-15 mobile_redirect 142692572
2025-09-16 mobile_pageview 35286
2025-09-16 mobile_redirect 141959762
2025-09-17 mobile_pageview 2598732
2025-09-17 mobile_redirect 135176660
2025-09-18 mobile_pageview 6953730
2025-09-18 mobile_redirect 128125946
2025-09-19 mobile_pageview 8996338
2025-09-19 mobile_redirect 127717003
2025-09-20 mobile_pageview 9659733
2025-09-20 mobile_redirect 143640675
2025-09-21 mobile_pageview 10358343
2025-09-21 mobile_redirect 153239971
2025-09-22 mobile_pageview 8786518
2025-09-22 mobile_redirect 131176161
2025-09-23 mobile_pageview 8898484
2025-09-23 mobile_redirect 130905838
2025-09-24 mobile_pageview 9412157
2025-09-24 mobile_redirect 127066627
2025-09-25 mobile_pageview 9186344
2025-09-25 mobile_redirect 125437159
2025-09-26 mobile_pageview 9388601
2025-09-26 mobile_redirect 127979199
# 2025-10-05: Peek at 1% gap
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
http_status,
uri_path,
uri_query,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month=10 AND day=3 AND hour=13
AND agent_type='user'
AND referer_class!='internal'
AND (uri_host NOT RLIKE '\.wikipedia\.org$' OR uri_host RLIKE '^(test|ca|he|it|fa|id|fr|de|es|ru|ja)\.wikipedia\.org$')
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND user_agent RLIKE '(?i)(android|mobi)'))
GROUP BY year,month,day,http_status,uri_path,uri_query
ORDER BY _count DESC LIMIT 10;
_date http_status uri_path _count
2025-10-03 301 /w/index.php 10463
2025-10-03 302 /w/index.php 7480
2025-10-03 301 /wiki/Special:FilePath/… 4442
2025-10-03 302 /wiki/Special:FilePath/… 4402
2025-10-03 301 /wiki/Special:Redirect/file/… 4334
2025-10-03 302 /wiki 1062
2025-10-03 301 /wiki/ 426
2025-10-03 302 /wiki/Sp%C3%A9cial:Page_au_hasard/… 372
2025-10-03 302 /wiki/Spezial:Suche 216
2025-10-03 302 /wiki/Special:FilePath/… 173
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
http_status,
uri_path,
uri_query,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month=10 AND day=3 AND hour=13
AND agent_type='user'
AND referer_class!='internal'
AND (uri_host NOT RLIKE '\.wikipedia\.org$' OR uri_host RLIKE '^(test|ca|he|it|fa|id|fr|de|es|ru|ja)\.wikipedia\.org$')
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND http_status=301
AND ((is_redirect_to_pageview AND user_agent RLIKE '(?i)(android|mobi)'))
GROUP BY year,month,day,http_status,uri_path,uri_query
ORDER BY _count DESC LIMIT 10;
_date http_status uri_path uri_query _count
2025-10-03 301 /wiki/Special:FilePath/….jpg 4442
2025-10-03 301 /wiki/Special:Redirect/file/….jpg 4334
2025-10-03 301 /wiki/ 426
2025-10-03 301 /w/index.php ?title=Special:Redirect/file/…&width=512 167
2025-10-03 301 /w/index.php ?title=Special:Redirect/file/…&width=512 166
2025-10-03 301 /w/index.php ?title=Special:Redirect/file/…&width=512 161
2025-10-03 301 /w/index.php ?title=Special:Redirect/file/…&width=512 157
2025-10-03 301 /w/index.php ?title=Special:Redirect/file/…&width=512 156
2025-10-03 301 /wiki/Special:FilePath/… 154
2025-10-03 301 /wiki/Special:Redirect/file/… 153
# 2025-10-05: Conclusion
#
# The mobile redirect uses HTTP 302.
# Most other unrelated redirects use HTTP 301.
# Improve precision of mobile_redirect by specifying http_status=302.
# 2025-10-05: Rolled out wikis
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
CASE WHEN is_redirect_to_pageview THEN 'mobile_redirect' ELSE 'mobile_pageview' END AS _bucket,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month IN (9,10)
AND agent_type='user'
AND referer_class!='internal'
AND (uri_host NOT RLIKE '\.wikipedia\.org$' OR uri_host RLIKE '^(test|ca|he|it|fa|id|fr|de|es|ru|ja)\.wikipedia\.org$')
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND http_status=302 AND user_agent RLIKE '(?i)(android|mobi)') OR (is_pageview AND access_method='mobile web'))
GROUP BY year,month,day,is_pageview,is_redirect_to_pageview
ORDER BY _date ASC, _bucket ASC;
_date _bucket _count
2025-09-01 mobile_pageview 0
2025-09-01 mobile_redirect 50007198
2025-09-02 mobile_pageview 0
2025-09-02 mobile_redirect 50978862
2025-09-03 mobile_pageview 592
2025-09-03 mobile_redirect 50339706
2025-09-04 mobile_pageview 2099
2025-09-04 mobile_redirect 50699173
2025-09-05 mobile_pageview 28675
2025-09-05 mobile_redirect 52437116
2025-09-06 mobile_pageview 74057
2025-09-06 mobile_redirect 56710633
2025-09-07 mobile_pageview 63517
2025-09-07 mobile_redirect 61351028
2025-09-08 mobile_pageview 55108
2025-09-08 mobile_redirect 52305542
2025-09-09 mobile_pageview 48609
2025-09-09 mobile_redirect 52572270
2025-09-10 mobile_pageview 53205
2025-09-10 mobile_redirect 53142562
2025-09-11 mobile_pageview 48522
2025-09-11 mobile_redirect 49953436
2025-09-12 mobile_pageview 62666
2025-09-12 mobile_redirect 51547219
2025-09-13 mobile_pageview 38824
2025-09-13 mobile_redirect 55939112
2025-09-14 mobile_pageview 53612
2025-09-14 mobile_redirect 62053590
2025-09-15 mobile_pageview 45451
2025-09-15 mobile_redirect 53009488
2025-09-16 mobile_pageview 35286
2025-09-16 mobile_redirect 52151035
2025-09-17 mobile_pageview 2598732
2025-09-17 mobile_redirect 48139608
2025-09-18 mobile_pageview 6953730
2025-09-18 mobile_redirect 43003417
2025-09-19 mobile_pageview 8996338
2025-09-19 mobile_redirect 41823739
2025-09-20 mobile_pageview 9659733
2025-09-20 mobile_redirect 47489785
2025-09-21 mobile_pageview 10358343
2025-09-21 mobile_redirect 50943161
2025-09-22 mobile_pageview 8786518
2025-09-22 mobile_redirect 44075770
2025-09-23 mobile_pageview 8898484
2025-09-23 mobile_redirect 43889562
2025-09-24 mobile_pageview 9412157
2025-09-24 mobile_redirect 41964920
2025-09-25 mobile_pageview 9186344
2025-09-25 mobile_redirect 40925030
2025-09-26 mobile_pageview 9388601
2025-09-26 mobile_redirect 41853222
2025-09-27 mobile_pageview 10370209
2025-09-27 mobile_redirect 46427274
2025-09-28 mobile_pageview 10918932
2025-09-28 mobile_redirect 49736983
2025-09-29 mobile_pageview 9023536
2025-09-29 mobile_redirect 41840771
2025-09-30 mobile_pageview 9538445
2025-09-30 mobile_redirect 45207316
2025-10-01 mobile_pageview 20965250
2025-10-01 mobile_redirect 29135997
2025-10-02 mobile_pageview 53103960
2025-10-02 mobile_redirect 403510
2025-10-03 mobile_pageview 58628102
2025-10-03 mobile_redirect 457181
2025-10-04 mobile_pageview 66923504
2025-10-04 mobile_redirect 475863
# 2025-10-05: All wikis
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
CASE WHEN is_redirect_to_pageview THEN 'mobile_redirect' ELSE 'mobile_pageview' END AS _bucket,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month IN (9,10)
AND agent_type='user'
AND referer_class!='internal'
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND http_status=302 AND user_agent RLIKE '(?i)(android|mobi)') OR (is_pageview AND access_method='mobile web'))
GROUP BY year,month,day,is_pageview,is_redirect_to_pageview
ORDER BY _date ASC, _bucket ASC;
_date _bucket _count
2025-09-01 mobile_pageview 0
2025-09-01 mobile_redirect 142201266
2025-09-02 mobile_pageview 0
2025-09-02 mobile_redirect 138331858
2025-09-03 mobile_pageview 592
2025-09-03 mobile_redirect 135349366
2025-09-04 mobile_pageview 2099
2025-09-04 mobile_redirect 136241581
2025-09-05 mobile_pageview 28675
2025-09-05 mobile_redirect 142230435
2025-09-06 mobile_pageview 74057
2025-09-06 mobile_redirect 156415641
2025-09-07 mobile_pageview 63517
2025-09-07 mobile_redirect 166823456
2025-09-08 mobile_pageview 55108
2025-09-08 mobile_redirect 140957942
2025-09-09 mobile_pageview 48609
2025-09-09 mobile_redirect 140381094
2025-09-10 mobile_pageview 53205
2025-09-10 mobile_redirect 149529379
2025-09-11 mobile_pageview 48522
2025-09-11 mobile_redirect 140852499
2025-09-12 mobile_pageview 62666
2025-09-12 mobile_redirect 140701802
2025-09-13 mobile_pageview 38824
2025-09-13 mobile_redirect 152986136
2025-09-14 mobile_pageview 53612
2025-09-14 mobile_redirect 168662255
2025-09-15 mobile_pageview 45451
2025-09-15 mobile_redirect 142692572
2025-09-16 mobile_pageview 35286
2025-09-16 mobile_redirect 141959762
2025-09-17 mobile_pageview 2598732
2025-09-17 mobile_redirect 135176660
2025-09-18 mobile_pageview 6953730
2025-09-18 mobile_redirect 128125946
2025-09-19 mobile_pageview 8996338
2025-09-19 mobile_redirect 127717003
2025-09-20 mobile_pageview 9659733
2025-09-20 mobile_redirect 143640675
2025-09-21 mobile_pageview 10358343
2025-09-21 mobile_redirect 153239971
2025-09-22 mobile_pageview 8786518
2025-09-22 mobile_redirect 131176161
2025-09-23 mobile_pageview 8898484
2025-09-23 mobile_redirect 130905838
2025-09-24 mobile_pageview 9412157
2025-09-24 mobile_redirect 127066627
2025-09-25 mobile_pageview 9186344
2025-09-25 mobile_redirect 125437159
2025-09-26 mobile_pageview 9388601
2025-09-26 mobile_redirect 127979199
2025-09-27 mobile_pageview 10370209
2025-09-27 mobile_redirect 141927995
2025-09-28 mobile_pageview 10918932
2025-09-28 mobile_redirect 150869590
2025-09-29 mobile_pageview 9023536
2025-09-29 mobile_redirect 127450209
2025-09-30 mobile_pageview 9538445
2025-09-30 mobile_redirect 129220034
2025-10-01 mobile_pageview 20965250
2025-10-01 mobile_redirect 113647139
2025-10-02 mobile_pageview 53103960
2025-10-02 mobile_redirect 86318318
2025-10-03 mobile_pageview 58628102
2025-10-03 mobile_redirect 89191441
2025-10-04 mobile_pageview 66923504
2025-10-04 mobile_redirect 100123633
## 2025-10-17: All wikis
SELECT
CONCAT(year,'-',LPAD(month, 2, '0'),'-',LPAD(day, 2, '0')) AS _date,
CASE WHEN is_redirect_to_pageview THEN 'mobile_redirect' ELSE 'mobile_pageview' END AS _bucket,
COUNT(*) AS _count
FROM wmf.pageview_actor
WHERE year=2025 AND month IN (9,10)
AND agent_type='user'
AND referer_class!='internal'
-- From puppet/varnish/text-frontend.vcl@cluster_fe_recv_pre_purge
AND uri_host NOT RLIKE '(?i)^([a-z0-9-]+\\.)?m\\.'
AND uri_path NOT LIKE '/wiki/Special:CentralAutoLogin%'
AND ((is_redirect_to_pageview AND http_status=302 AND user_agent RLIKE '(?i)(android|mobi)') OR (is_pageview AND access_method='mobile web'))
GROUP BY year,month,day,is_pageview,is_redirect_to_pageview
ORDER BY _date ASC, _bucket ASC;
_date _bucket _count
2025-09-01 mobile_pageview 0
2025-09-01 mobile_redirect 141929530
2025-09-02 mobile_pageview 0
2025-09-02 mobile_redirect 138151884
2025-09-03 mobile_pageview 592
2025-09-03 mobile_redirect 135180220
2025-09-04 mobile_pageview 2099
2025-09-04 mobile_redirect 136100754
2025-09-05 mobile_pageview 28675
2025-09-05 mobile_redirect 142067873
2025-09-06 mobile_pageview 74057
2025-09-06 mobile_redirect 156175115
2025-09-07 mobile_pageview 63517
2025-09-07 mobile_redirect 166757311
2025-09-08 mobile_pageview 55108
2025-09-08 mobile_redirect 140870387
2025-09-09 mobile_pageview 48609
2025-09-09 mobile_redirect 140143857
2025-09-10 mobile_pageview 53205
2025-09-10 mobile_redirect 149415413
2025-09-11 mobile_pageview 48522
2025-09-11 mobile_redirect 140756711
2025-09-12 mobile_pageview 62666
2025-09-12 mobile_redirect 140548385
2025-09-13 mobile_pageview 38824
2025-09-13 mobile_redirect 152925866
2025-09-14 mobile_pageview 53612
2025-09-14 mobile_redirect 168534707
2025-09-15 mobile_pageview 45451
2025-09-15 mobile_redirect 142541192
2025-09-16 mobile_pageview 35286
2025-09-16 mobile_redirect 141829938
2025-09-17 mobile_pageview 2598732
2025-09-17 mobile_redirect 135069733
2025-09-18 mobile_pageview 6953730
2025-09-18 mobile_redirect 128058862
2025-09-19 mobile_pageview 8996338
2025-09-19 mobile_redirect 127646565
2025-09-20 mobile_pageview 9659733
2025-09-20 mobile_redirect 143458373
2025-09-21 mobile_pageview 10358343
2025-09-21 mobile_redirect 153075920
2025-09-22 mobile_pageview 8786518
2025-09-22 mobile_redirect 131034179
2025-09-23 mobile_pageview 8898484
2025-09-23 mobile_redirect 130712725
2025-09-24 mobile_pageview 9412157
2025-09-24 mobile_redirect 126890640
2025-09-25 mobile_pageview 9186344
2025-09-25 mobile_redirect 125294365
2025-09-26 mobile_pageview 9388601
2025-09-26 mobile_redirect 127824209
2025-09-27 mobile_pageview 10370209
2025-09-27 mobile_redirect 141806666
2025-09-28 mobile_pageview 10918932
2025-09-28 mobile_redirect 150712327
2025-09-29 mobile_pageview 9023536
2025-09-29 mobile_redirect 127325453
2025-09-30 mobile_pageview 9538445
2025-09-30 mobile_redirect 129026069
2025-10-01 mobile_pageview 20965250
2025-10-01 mobile_redirect 113260370
2025-10-02 mobile_pageview 53103960
2025-10-02 mobile_redirect 85708646
2025-10-03 mobile_pageview 58628102
2025-10-03 mobile_redirect 88543851
2025-10-04 mobile_pageview 66923504
2025-10-04 mobile_redirect 99485223
2025-10-05 mobile_pageview 71222564
2025-10-05 mobile_redirect 120155856
2025-10-06 mobile_pageview 59746786
2025-10-06 mobile_redirect 90516350
2025-10-07 mobile_pageview 61050960
2025-10-07 mobile_redirect 85450289
2025-10-08 mobile_pageview 104218975
2025-10-08 mobile_redirect 45751838
2025-10-09 mobile_pageview 160468576
2025-10-09 mobile_redirect 835578
2025-10-10 mobile_pageview 164913169
2025-10-10 mobile_redirect 764845
2025-10-11 mobile_pageview 187084755
2025-10-11 mobile_redirect 1001807
2025-10-12 mobile_pageview 200101133
2025-10-12 mobile_redirect 1070922
2025-10-13 mobile_pageview 172549988
2025-10-13 mobile_redirect 825597
2025-10-14 mobile_pageview 164342926
2025-10-14 mobile_redirect 680235
2025-10-15 mobile_pageview 160092080
2025-10-15 mobile_redirect 794455
2025-10-16 mobile_pageview 157896562
2025-10-16 mobile_redirect 719036
2025-10-17 mobile_pageview 149828912
2025-10-17 mobile_redirect 698595