Extension:UniversalLanguageSelector/EventLogging queries

Some data is available at http://etherpad.wikimedia.org/p/czjQFgQLJr

Schema available at: https://meta.wikimedia.org/wiki/Schema:UniversalLanguageSelector

Number of samples

select count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'

Top 10 wikis with most logged events

select webHost, count( webHost )
from `UniversalLanguageSelector_7327441`
where webHost like '%.org'
group by webHost
order by count( webHost ) DESC
limit 10

Logged event types

select 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

select `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

select `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

select `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

select `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

select `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

WARNING! 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

select 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

Ordered 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

Ordered 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

select 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

select `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

select `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

select `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

SELECT 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;