Anyone... ?
On Thu, Jan 30, 2014 at 1:50 PM, Jon Robson <jdlrobson(a)gmail.com> wrote:
Note in any reports we could also leverage
MobileWebEditing_6637866 to
show the most edited articles in that same month.
Top ten edited articles for month of January:
Title | Edit count
National_Museum,_New_Delhi 118
2014_Formula_One_season 86
Royal_Rumble_(2014) 76
Vera_Atkins 56
List_of_My_Little_Pony:_Friendship_Is_Magic_characters 52
Timber_(song) 52
Jamia_Millia_Islamia 49
Poliklinik_Cheras 45
Roy_Campbell,_Jr. 45
Air_gun 44
Using SQL:
select page_title, count from (
select count(*) as count, page_namespace, page_title
from MobileWebEditing_6637866 as Editing
JOIN enwiki.page
ON page_id = event_pageID
where
event_action = 'success' and
timestamp >= '2014010100000' and
timestamp <= '2014013100000' and
page_namespace = 0 and
wiki = 'enwiki'
group by page_title )
as MostActive
order by MostActive.count desc
limit 10
On Thu, Jan 30, 2014 at 1:32 PM, Jon Robson <jrobson(a)wikimedia.org> wrote:
IN case you don't follow mobile-l - if you
don't make sure you do now.
---------- Forwarded message ----------
From: Jon Robson <jdlrobson(a)gmail.com>
Date: Thu, Jan 30, 2014 at 1:32 PM
Subject: MobileWebBetaWatchlist
To: mobile-l <mobile-l(a)lists.wikimedia.org>
This schema has been around since the dawn of time. It collects data
around the watch star activity.
Currently we don't use it for anything.
I think we should do something about this.
1) Create an automation script using SQL [A] that generates regular
reports showing the top ten watch articles on mobile on wikis for each
month and publish that somewhere. See Graph [C] to get an idea of what
this looks like for January.
2) We may want to do the above but in addition to this stop logging
clicks on the watchstar when logged out. The graph generated for
January [D] from this data is very different, quicker to run and much
more work safe.
3) Kill the schema altogether
I would suggest options 2 or 3. Interested in the wider communities opinion.
(Note 2/3 could take the form of mails to the mobile-l mailing list or
outputted to some server on the labs)
[A] SQL query:
select page_namespace, page_title, count from (
select count(*) as count, page_namespace, page_title
from MobileBetaWatchlist_5281061 as Watchstar
JOIN enwiki.page
ON page_id = event_articleID
where
event_username != 'Selenium user' and
timestamp >= '2014010100000' and
timestamp <= '2014013100000' and
wiki = 'enwiki'
group by page_title )
as MostActive
order by MostActive.count desc
limit 10
[B] Only include logged in users
select page_namespace, page_title, count from (
select count(*) as count, page_namespace, page_title
from MobileBetaWatchlist_5281061 as Watchstar
JOIN enwiki.page
ON page_id = event_articleID
where
event_anon = 0 and
timestamp >= '2014010100000' and
timestamp <= '2014013100000' and
wiki = 'enwiki'
group by page_title )
as MostActive
order by MostActive.count desc
limit 10
[C] List of most watched articles for January including anon clicks
0 Main_Page 47619
12 Searching 3827
0 XXX 2205
6 Xxx_movie.jpg 2054
0 Pornography 1973
0 Facebook 1906
6 Facebook_(login,_signup_page).jpg 1619
6 Elf_Yourself_screenshot.jpg 1566
0 XXX_(film) 1498
0 Android_rooting 1368
[D] List of most watched articles for January excluding anon clicks
0 Main_Page 4079
0 The_Fosters_(2013_TV_series) 186
0 Editor_&_Publisher 99
0 Juan_Mata 85
12 Searching 63
0 Bailee_Madison 55
6 Elf_Yourself_screenshot.jpg 49
0 Android_rooting 48
100 Current_events 47
0 Psychopath_(disambiguation) 44
On Thu, Jan 30, 2014 at 1:32 PM, Jon Robson <jdlrobson(a)gmail.com> wrote:
This schema has been around since the dawn of
time. It collects data
around the watch star activity.
Currently we don't use it for anything.
I think we should do something about this.
1) Create an automation script using SQL [A] that generates regular
reports showing the top ten watch articles on mobile on wikis for each
month and publish that somewhere. See Graph [C] to get an idea of what
this looks like for January.
2) We may want to do the above but in addition to this stop logging
clicks on the watchstar when logged out. The graph generated for
January [D] from this data is very different, quicker to run and much
more work safe.
3) Kill the schema altogether
I would suggest options 2 or 3. Interested in the wider communities opinion.
(Note 2/3 could take the form of mails to the mobile-l mailing list or
outputted to some server on the labs)
[A] SQL query:
select page_namespace, page_title, count from (
select count(*) as count, page_namespace, page_title
from MobileBetaWatchlist_5281061 as Watchstar
JOIN enwiki.page
ON page_id = event_articleID
where
event_username != 'Selenium user' and
timestamp >= '2014010100000' and
timestamp <= '2014013100000' and
wiki = 'enwiki'
group by page_title )
as MostActive
order by MostActive.count desc
limit 10
[B] Only include logged in users
select page_namespace, page_title, count from (
select count(*) as count, page_namespace, page_title
from MobileBetaWatchlist_5281061 as Watchstar
JOIN enwiki.page
ON page_id = event_articleID
where
event_anon = 0 and
timestamp >= '2014010100000' and
timestamp <= '2014013100000' and
wiki = 'enwiki'
group by page_title )
as MostActive
order by MostActive.count desc
limit 10
[C] List of most watched articles for January including anon clicks
0 Main_Page 47619
12 Searching 3827
0 XXX 2205
6 Xxx_movie.jpg 2054
0 Pornography 1973
0 Facebook 1906
6 Facebook_(login,_signup_page).jpg 1619
6 Elf_Yourself_screenshot.jpg 1566
0 XXX_(film) 1498
0 Android_rooting 1368
[D] List of most watched articles for January excluding anon clicks
0 Main_Page 4079
0 The_Fosters_(2013_TV_series) 186
0 Editor_&_Publisher 99
0 Juan_Mata 85
12 Searching 63
0 Bailee_Madison 55
6 Elf_Yourself_screenshot.jpg 49
0 Android_rooting 48
100 Current_events 47
0 Psychopath_(disambiguation) 44
--
Jon Robson
*
http://jonrobson.me.uk
*
https://www.facebook.com/jonrobson
* @rakugojon