AndrewTavis_WMDE added a comment.
As far as totals for this task are concerned, @Manuel, what I'm getting is
the following:
**Population for the following is**:
All views of `www.wikidata.org` and `m.wikidata.org` inclusively between
01/7/2023 and 31/7/2023 that are further `agent_type != 'spider'` and mobile
users with `user_agent_map.os_family = 'Android'`, `iOS` or `KaiOS`.
WITH pageview_actor_os_families AS (
SELECT
user_agent_map.os_family AS os_family
FROM
wmf.pageview_actor
WHERE
year = 2023
AND month = 7
AND '2023-07-01' <= dt
AND dt < '2023-08-01'
AND uri_host IN ('www.wikidata.org', 'm.wikidata.org')
AND is_pageview = True
AND agent_type = 'user'
)
SELECT
COUNT(*) AS total_views
FROM
pageview_actor_os_families
WHERE
os_family = 'Android'
OR os_family = 'iOS'
OR os_family = 'KaiOS'
... gives us `699,968`. For edits I need some help filtering out bots as we
don't have the option of going from webrequests as mentioned above and I'm
having a hard time figuring out how to access MediaWiki tables directly for
`user_groups`. With that being said, I currently have the following that we
just need to get the bots out of:
WITH cu_changes_os_families AS (
SELECT
ua_parser(cuc_agent) AS user_agent_map
FROM
wmf_raw.mediawiki_private_cu_changes
WHERE
wiki_db = 'wikidatawiki'
AND '20230701' <= cuc_timestamp
AND cuc_timestamp < '20230801'
)
SELECT
COUNT(*) AS total_edits
FROM
cu_changes_os_families
WHERE
user_agent_map.os_family = 'Android'
OR user_agent_map.os_family = 'iOS'
OR user_agent_map.os_family = 'KaiOS'
... gives us `221,073`. Let me know if you have any thoughts about how to
make the connection to the MediaWiki tables or if not also where we could get
bot status from! :)
TASK DETAIL
https://phabricator.wikimedia.org/T336361
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: AndrewTavis_WMDE
Cc: JAllemandou, AndrewTavis_WMDE, Michael, Manuel, Aklapper,
Danny_Benjafield_WMDE, Astuthiodit_1, karapayneWMDE, Invadibot, maantietaja,
ItamarWMDE, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden,
LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, Mbch331
_______________________________________________
Wikidata-bugs mailing list -- [email protected]
To unsubscribe send an email to [email protected]
