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
_______________________________________________
Mobile-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mobile-l
