Anyone... ?
On Thu, Jan 30, 2014 at 1:50 PM, Jon Robson <[email protected]> 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 <[email protected]> wrote: >> IN case you don't follow mobile-l - if you don't make sure you do now. >> >> ---------- Forwarded message ---------- >> From: Jon Robson <[email protected]> >> Date: Thu, Jan 30, 2014 at 1:32 PM >> Subject: MobileWebBetaWatchlist >> To: mobile-l <[email protected]> >> >> >> 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 <[email protected]> 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 -- Jon Robson * http://jonrobson.me.uk * https://www.facebook.com/jonrobson * @rakugojon _______________________________________________ Mobile-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mobile-l
