Milimetric has uploaded a new change for review.
https://gerrit.wikimedia.org/r/192944
Change subject: [WIP] Analyze edit success rate by user type
......................................................................
[WIP] Analyze edit success rate by user type
Bug: T89729
Change-Id: Ibf4274d0050aa932d7f601213d307a89cd004c9a
---
R edit/funnel_failure_rates_by_type.sql
R edit/funnel_save_rates_low_noise.sql
A edit/funnel_user_types.sql
M static/adhoc.html
M static/funnel-prototype.js
M static/util.js
6 files changed, 119 insertions(+), 25 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-edit-data
refs/changes/44/192944/1
diff --git a/edit/failure_rates_by_type.sql
b/edit/funnel_failure_rates_by_type.sql
similarity index 100%
rename from edit/failure_rates_by_type.sql
rename to edit/funnel_failure_rates_by_type.sql
diff --git a/edit/save_rates_low_noise.sql
b/edit/funnel_save_rates_low_noise.sql
similarity index 100%
rename from edit/save_rates_low_noise.sql
rename to edit/funnel_save_rates_low_noise.sql
diff --git a/edit/funnel_user_types.sql b/edit/funnel_user_types.sql
new file mode 100644
index 0000000..529a22a
--- /dev/null
+++ b/edit/funnel_user_types.sql
@@ -0,0 +1,64 @@
+set @from_time = '{from_timestamp}';
+set @to_time = '{to_timestamp}';
+set @wiki = '{wiki}';
+
+set @from_time = '20141203000000';
+set @to_time = '20150201000000';
+set @wiki = null;
+
+ select day,
+ sum(if(success, repeated, 0)) / sum(repeated) as 'total',
+ sum(if(success and usertype = 'anonymous', repeated, 0)) /
sum(repeated) as 'anonymous',
+ sum(if(success and usertype = 'veteran', repeated, 0)) / sum(repeated)
as 'veteran',
+ sum(if(success and usertype = 'very-active', repeated, 0)) /
sum(repeated) as 'very-active',
+ sum(if(success and usertype = 'active', repeated, 0)) / sum(repeated)
as 'active',
+ sum(if(success and usertype = 'novice', repeated, 0)) / sum(repeated)
as 'novice',
+ sum(if(success and usertype = 'first-edit', repeated, 0)) /
sum(repeated) as 'first-edit'
+ from (select date(timestamp) as day,
+ if(actions like '%saveSuccess%', 1, 0) as success,
+ usertype,
+ count(*) as repeated
+ from (select timestamp,
+ event_editingSessionId,
+ wiki,
+ group_concat(event_action order by timestamp,
action_order.ord separator '-') as actions,
+ case
+ # NOTE: user.class is never "bot" in the current
data
+ when `event_user.class` = 'IP' then 'anonymous'
+ when `event_user.editCount` >= 1000 then 'veteran'
+ when `event_user.editCount` >= 100 then
'very-active'
+ when `event_user.editCount` >= 5 then 'active'
+ when `event_user.editCount` >= 1 then 'novice'
+ when `event_user.editCount` = 0 then 'first-edit'
+ else 'unknown-user-type'
+ end as usertype
+
+ from staging.milimetric_edit
+ inner join
+ (select 'init' as action, 0 as ord
+ union all
+ select 'ready' as action, 1 as ord
+ union all
+ select 'saveIntent' as action, 2 as ord
+ union all
+ select 'saveAttempt' as action, 3 as ord
+ union all
+ select 'saveFailure' as action, 4 as ord
+ union all
+ select 'saveSuccess' as action, 5 as ord
+ union all
+ select 'abort' as action, 6 as ord
+ ) action_order on action =
staging.milimetric_edit.event_action
+ where event_editor = 'visualeditor'
+ and timestamp between @from_time and @to_time
+ and (@wiki is null or wiki = @wiki)
+ group by clientIp, event_editingSessionId
+ # this purposefully ignores sessions that start before
"from_timestamp",
+ # as well as some other sessions that legitimately do not
have an "init"
+ # as well as sessions that have an "init" after "ready"
+ having actions like 'init%'
+ ) sessions
+ group by day, success, usertype
+ ) successes
+ group by day
+;
diff --git a/static/adhoc.html b/static/adhoc.html
index c563511..df717ed 100644
--- a/static/adhoc.html
+++ b/static/adhoc.html
@@ -2,7 +2,7 @@
<html>
<head>
<meta charset='utf-8'>
- <title>Visual Editor by Day by Wiki</title>
+ <title>Editing Analysis</title>
<script src='//cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js'></script>
<script
src='//cdnjs.cloudflare.com/ajax/libs/knockout/3.2.0/knockout-min.js'></script>
@@ -28,6 +28,10 @@
<div id='timeseriesLegend'></div>
<div id='timeline'></div>
+ <hr style='clear: both; visibility: hidden;'/>
+
+ <table class='data'></table>
+
<script type='text/javascript' src='util.js'></script>
<script type='text/javascript'>
var color = d3.scale.category10(),
@@ -41,6 +45,32 @@
var header = d3.tsv.parseRows(text),
rows = header.splice(1);
+ // show as a table for reference
+ var table = d3.select('table.data')
+ .style('margin-top', '40px')
+ .style('clear', 'both');
+ table.append('thead')
+ .style('background-color', '#112233')
+ .style('color', '#ffffff')
+ .selectAll('tr').data(header)
+ .enter().append('tr')
+ .selectAll('th').data(function(d) { return d; })
+ .enter().append('th')
+ .style('padding', '4px')
+ .text(function (d) { return d; });
+
+ table.append('tbody')
+ .selectAll('tr').data(rows)
+ .enter().append('tr')
+ .selectAll('td').data(function(d) { return d; })
+ .enter().append('td')
+ .text(function (d) { return d; })
+ .style('padding', '4px')
+ .style('background-color', function (d, i) {
+ return i % 2 == 0 ? '#dadada' : '#ffffff';
+ });
+
+
var series = header[0].slice(1).map(function (col, i) {
return {
name: col,
diff --git a/static/funnel-prototype.js b/static/funnel-prototype.js
index f8c7f5e..809e77f 100644
--- a/static/funnel-prototype.js
+++ b/static/funnel-prototype.js
@@ -1,29 +1,5 @@
var ALL = '* All *';
-if (!Array.prototype.find) {
- Array.prototype.find = function(predicate) {
- if (this == null) {
- throw new TypeError('Array.prototype.find called on null or
undefined');
- }
- if (typeof predicate !== 'function') {
- throw new TypeError('predicate must be a function');
- }
- var list = Object(this);
- var length = list.length >>> 0;
- var thisArg = arguments[1];
- var value;
-
- for (var i = 0; i < length; i++) {
- value = list[i];
- if (predicate.call(thisArg, value, i, list)) {
- return value;
- }
- }
- return undefined;
- };
-}
-function t(){ return (new Date()).getTime(); }
-
// Mapping of step names to colors.
var colors = {
'init': '#5687d1',
diff --git a/static/util.js b/static/util.js
index 4bc4df2..70ea8d8 100644
--- a/static/util.js
+++ b/static/util.js
@@ -43,3 +43,27 @@
return graph;
}
+
+if (!Array.prototype.find) {
+ Array.prototype.find = function(predicate) {
+ if (this == null) {
+ throw new TypeError('Array.prototype.find called on null or
undefined');
+ }
+ if (typeof predicate !== 'function') {
+ throw new TypeError('predicate must be a function');
+ }
+ var list = Object(this);
+ var length = list.length >>> 0;
+ var thisArg = arguments[1];
+ var value;
+
+ for (var i = 0; i < length; i++) {
+ value = list[i];
+ if (predicate.call(thisArg, value, i, list)) {
+ return value;
+ }
+ }
+ return undefined;
+ };
+}
+function t(){ return (new Date()).getTime(); }
--
To view, visit https://gerrit.wikimedia.org/r/192944
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ibf4274d0050aa932d7f601213d307a89cd004c9a
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-edit-data
Gerrit-Branch: master
Gerrit-Owner: Milimetric <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
