Manuel added a comment.
I have moved this comment from T336361#9134815 <https://phabricator.wikimedia.org/T336361#9134815>, In T336361#9134815 <https://phabricator.wikimedia.org/T336361#9134815>, @AndrewTavis_WMDE wrote: > As far as totals for this task are concerned, @Manuel, what I'm getting is: > > **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`. > > **Population for the following is**: > > All edits of `wiki_db = 'wikidatawiki'` inclusively between 01/7/2023 and 31/7/2023 that are further mobile users with `user_agent_map.os_family = 'Android'`, `iOS` or `KaiOS`. > > 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 month = '2023-07' > 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/T344052 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Manuel Cc: AndrewTavis_WMDE, Aklapper, Manuel, 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 -- wikidata-bugs@lists.wikimedia.org To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org
