MobileWebBetaWatchlist - Mobile-l

30 Jan 2014

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