Extension:UniversalLanguageSelector/EventLogging queries
Some data from 2013 is available at http://etherpad.wikimedia.org/p/czjQFgQLJr
Schema available at meta:Schema:UniversalLanguageSelector
Number of samples
editselect count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
Top 10 wikis with most logged events
editselect webHost, count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
group by webHost
order by count( webHost ) DESC
limit 10
Logged event types
editselect event_action, count( event_action )u
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
group by event_action
order by count( event_action ) DESC
Most used input methods
editselect `event_inputMethod`, count(event_inputMethod)
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
webHost like '%.org'
group by event_inputMethod
order by count( event_inputMethod ) DESC
limit 20
Most used fonts
editselect `event_interfaceFont`, count(event_interfaceFont)
from `UniversalLanguageSelector_7327441`
where event_action = 'font-change' AND
webHost like '%.org'
group by event_interfaceFont
order by count( event_interfaceFont ) DESC
limit 20
Most used input methods per wiki
editselect `webHost`, `event_inputMethod`, count(event_inputMethod) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
event_inputMethod <> 'system' AND
webHost like '%.org'
group by webHost, event_inputMethod
order by count( event_inputMethod ) DESC
limit 20
All wikis, sorted by wiki
editselect `webHost`, `event_inputMethod`, count(event_inputMethod) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'ime-change' AND
event_inputMethod <> 'system' AND
webHost like '%.org'
group by webHost, event_inputMethod
order by webHost ASC, count DESC;
Most used fonts per wiki
editselect `webHost`, `event_interfaceFont`, count(event_interfaceFont) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'font-change' AND
webHost like '%.org'
group by webHost, event_interfaceFont
order by count( event_interfaceFont ) DESC
limit 20
Top 25 language search missses
editWARNING! FOR PRIVACY REASONS THIS DATA CANNOT BE MADE AVAILABLE TO PEOPLE THAT HAVE NOT SIGNED AN NDA WITH WIKIMEDIA FOUNDATION BEFORE SANITIZING IT!
select event_context, count( event_context )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org' AND
event_action = 'no-search-results'
group by event_context
order by count( event_context ) DESC, event_context
limit 25
On which hosts was the language not found most frequently
editselect event_context, webHost, count(event_context)
from `UniversalLanguageSelector_7327441`
where webHost like '%.org' AND
event_action = 'no-search-results' AND
event_context = 'XXXX'
group by webHost
order by count(event_context)
Event counts in a single wiki
editOrdered by count, descending
select event_action, count( event_action )
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org'
group by event_action
order by count( event_action ) DESC
Event counts by user in a single wiki
editOrdered by count, descending
select event_action, count( event_action ), event_token, count(event_token)
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org'
group by event_action, event_token
order by count( event_action ) DESC
Event counts by projects in a single language
editselect webHost, count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost LIKE 'gu.%'
group by webHost
order by count( webHost ) DESC
Input method switches by a single user in a single wiki
editselect `event_inputMethod`, count(event_inputMethod)
from `UniversalLanguageSelector_7327441`
where webHost = 'te.wiktionary.org' AND
event_token = 'xxxxx'
group by event_inputMethod
Most often changed UI languages per wiki
editselect `webHost`, `event_interfaceLanguage`, count(event_interfaceLanguage) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'language-change' AND
webHost like '%.org'
group by webHost, event_interfaceLanguage
order by count( event_interfaceLanguage ) DESC
limit 20
Most often changed UI languages per wiki - all wikis, sorted by wiki
editselect `webHost`, `event_interfaceLanguage`, count(event_interfaceLanguage) as count
from `UniversalLanguageSelector_7327441`
where event_action = 'language-change' AND
webHost like '%.org'
group by webHost, event_interfaceLanguage
order by webHost ASC, count DESC;
Language selection methods
editSELECT event_languageSelectionMethod, COUNT(event_languageSelectionMethod) count
FROM
(
SELECT event_languageSelectionMethod
FROM `UniversalLanguageSelector_5729800`
WHERE event_action = 'language-change' AND
webHost LIKE '%.org'
UNION ALL
SELECT event_languageSelectionMethod
FROM `UniversalLanguageSelector_7327441`
WHERE event_action = 'language-change' AND
webHost LIKE '%.org'
) unitedTables
GROUP BY event_languageSelectionMethod
ORDER BY count DESC;