Jforrester has uploaded a new change for review.
https://gerrit.wikimedia.org/r/177943
Change subject: Provide some initial raw graphs for basic numbers
......................................................................
Provide some initial raw graphs for basic numbers
Change-Id: I3a272388c06bed0531c9767f8367dcbbb0e0fc8c
---
M dashboards/reportcard.json
A edit/aborts.sql
A edit/aborts_rates.sql
M edit/config.yaml
A edit/saves.sql
A edit/saves_rates.sql
A edit/saves_rates_comparative_attempts.sql
A edit/saves_rates_comparative_success_attempts.sql
A edit/saves_rates_comparative_successes.sql
9 files changed, 370 insertions(+), 1 deletion(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-edit-data
refs/changes/43/177943/1
diff --git a/dashboards/reportcard.json b/dashboards/reportcard.json
index 750e03c..72168a3 100644
--- a/dashboards/reportcard.json
+++ b/dashboards/reportcard.json
@@ -6,6 +6,13 @@
{
"name": "Core",
"graph_ids": [
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/saves_rates_comparative_attempts.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/saves_rates_comparative_success_attempts.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/saves_rates_comparative_successes.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/aborts.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/aborts_rates.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/saves.csv";,
+
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/saves_rates.csv";,
"http://datasets.wikimedia.org/limn-public-data/edit/datafiles/action.csv";
]
}
diff --git a/edit/aborts.sql b/edit/aborts.sql
new file mode 100644
index 0000000..3e04717
--- /dev/null
+++ b/edit/aborts.sql
@@ -0,0 +1,41 @@
+SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'init', 1, 0 ) )
AS "Starts",
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'abort', 1, 0 ) )
AS "Aborts",
+ SUM( IF( `event_action.abort.type` = 'preinit', 1, 0 ) )
AS "Aborts - Abandoned starts",
+ SUM( IF( `event_action.abort.type` = 'nochange', 1, 0 ) )
AS "Aborts - Bounces",
+ SUM( IF( `event_action.abort.type` = 'switchwith', 1, 0 ) )
AS "Aborts - Switches with changes",
+ SUM( IF( `event_action.abort.type` = 'switchwithout', 1, 0 ) )
AS "Aborts - Switches without changes",
+ SUM( IF( `event_action.abort.type` = 'abandon', 1, 0 ) )
AS "Aborts - Abandoned edits",
+ SUM( IF( `event_action.abort.type` = 'abandonMidsave', 1, 0 ) )
AS "Aborts - Abandoned saves"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ `event_action.abort.type`,
+ timestamp
+
+ FROM Edit_10604157
+
+ WHERE event_action = 'abort' OR event_action = 'ready'
OR event_action = 'init'
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ `event_action.abort.type`,
+ timestamp
+
+ FROM Edit_10676603
+
+ WHERE event_action = 'abort' OR event_action = 'ready'
OR event_action = 'init'
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+;
diff --git a/edit/aborts_rates.sql b/edit/aborts_rates.sql
new file mode 100644
index 0000000..62d8bee
--- /dev/null
+++ b/edit/aborts_rates.sql
@@ -0,0 +1,53 @@
+SELECT
+ day,
+# ( Starts - Loads - `Aborts - Abandoned starts` ) / Starts AS
"Aborts - Missing abandoned starts rate",
+ `Aborts - Abandoned starts` / `Starts` AS
"Aborts - Abandoned start rate",
+ `Aborts - Bounces` / `Loads`
AS "Aborts - Bounces rate",
+ `Aborts - Switches with changes` / `Loads` AS
"Aborts - Switches with changes rate",
+ `Aborts - Switches without changes` / `Loads` AS "Aborts -
Switches without changes rate",
+ `Aborts - Abandoned edits` / `Loads` AS
"Aborts - Abandoned edits rate",
+ `Aborts - Abandoned saves` / `Loads` AS
"Aborts - Abandoned saves rate"
+
+ FROM
+ ( SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'init', 1, 0 ) )
AS "Starts",
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'abort', 1, 0 ) )
AS "Aborts",
+ SUM( IF( `event_action.abort.type` = 'preinit', 1, 0 )
) AS "Aborts - Abandoned starts",
+ SUM( IF( `event_action.abort.type` = 'nochange', 1, 0 )
) AS "Aborts - Bounces",
+ SUM( IF( `event_action.abort.type` = 'switchwith', 1, 0
) ) AS "Aborts - Switches with changes",
+ SUM( IF( `event_action.abort.type` = 'switchwithout',
1, 0 ) ) AS "Aborts - Switches without changes",
+ SUM( IF( `event_action.abort.type` = 'abandon', 1, 0 )
) AS "Aborts - Abandoned edits",
+ SUM( IF( `event_action.abort.type` = 'abandonMidsave',
1, 0 ) ) AS "Aborts - Abandoned saves"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ `event_action.abort.type`,
+ timestamp
+
+ FROM Edit_10604157
+
+ WHERE event_action = 'abort' OR
event_action = 'ready' OR event_action = 'init'
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ `event_action.abort.type`,
+ timestamp
+
+ FROM Edit_10676603
+
+ WHERE event_action = 'abort' OR
event_action = 'ready' OR event_action = 'init'
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+ ) as internalQuery
+;
diff --git a/edit/config.yaml b/edit/config.yaml
index 4632df3..6c1bd45 100644
--- a/edit/config.yaml
+++ b/edit/config.yaml
@@ -15,5 +15,36 @@
# the key in the graph section is the name of the sql file and generated file
graphs:
action:
- title: "Editing by Action"
+ title: "Count of edit actions - All wikis"
+ frequency: hourly
+
+ aborts:
+ title: "Count of edit aborts - All wikis"
+ frequency: hourly
+
+ aborts_rates:
+ title: "Rate of edit aborts - All wikis"
+ frequency: hourly
+
+ saves:
+ title: "Count of saves - All wikis"
+ frequency: hourly
+
+ saves_rates:
+ title: "Rate of saves - All wikis"
+ frequency: hourly
+
+ saves_rates_comparative_attempts:
+ # Attempted saves against Loads rate
+ title: "How many editor loads try to save? - Comparative for select
Wikipedias"
+ frequency: hourly
+
+ saves_rates_comparative_success_attempts:
+ # Successful saves against Attempted saves rate
+ title: "How many tries to save work? - Comparative for select
Wikipedias"
+ frequency: hourly
+
+ saves_rates_comparative_successes:
+ # Successful saves against Loads rate
+ title: "How many editor loads end up with saves? - Comparative for
select Wikipedias"
frequency: hourly
diff --git a/edit/saves.sql b/edit/saves.sql
new file mode 100644
index 0000000..e0e7994
--- /dev/null
+++ b/edit/saves.sql
@@ -0,0 +1,31 @@
+SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'init', 1, 0 ) )
AS "Starts",
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'saveAttempt', 1, 0 ) )
AS "Attempted saves",
+ SUM( IF( event_action = 'saveFailure', 1, 0 ) )
AS "Failed saves",
+ SUM( IF( event_action = 'saveSuccess', 1, 0 ) )
AS "Successful saves"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ timestamp
+
+ FROM Edit_10604157
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ timestamp
+
+ FROM Edit_10676603
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+;
diff --git a/edit/saves_rates.sql b/edit/saves_rates.sql
new file mode 100644
index 0000000..10389e6
--- /dev/null
+++ b/edit/saves_rates.sql
@@ -0,0 +1,41 @@
+SELECT
+ day,
+ `Loads` / `Starts`
AS "Successful loads rate",
+ `Attempted saves` / `Loads`
AS "Attempted saves against Loads rate",
+ `Failed saves` / `Attempted saves`
AS "Failed saves against Attempted saves rate",
+ `Successful saves` / `Attempted saves` AS
"Successful saves against Attempted saves rate",
+ `Successful saves` / `Loads`
AS "Successful saves against Loads rate"
+
+ FROM
+ ( SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'init', 1, 0 ) )
AS "Starts",
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'saveAttempt', 1, 0 ) )
AS "Attempted saves",
+ SUM( IF( event_action = 'saveFailure', 1, 0 ) )
AS "Failed saves",
+ SUM( IF( event_action = 'saveSuccess', 1, 0 ) )
AS "Successful saves"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ timestamp
+
+ FROM Edit_10604157
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ timestamp
+
+ FROM Edit_10676603
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+ ) as internalQuery
+;
diff --git a/edit/saves_rates_comparative_attempts.sql
b/edit/saves_rates_comparative_attempts.sql
new file mode 100644
index 0000000..a3f5875
--- /dev/null
+++ b/edit/saves_rates_comparative_attempts.sql
@@ -0,0 +1,55 @@
+SELECT
+ day,
+ # Attempted saves against Loads rate
+ `Attempted saves` / `Loads`
AS "All wikis",
+ `Attempted saves - dewiki` / `Loads - dewiki`
AS "dewiki",
+ `Attempted saves - enwiki` / `Loads - enwiki`
AS "enwiki",
+ `Attempted saves - eswiki` / `Loads - eswiki`
AS "eswiki",
+ `Attempted saves - frwiki` / `Loads - frwiki`
AS "frwiki",
+ `Attempted saves - ptwiki` / `Loads - plwiki`
AS "plwiki",
+ `Attempted saves - plwiki` / `Loads - ptwiki`
AS "ptwiki"
+
+ FROM
+ ( SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'ready' AND wiki = 'dewiki', 1,
0 ) ) AS "Loads - dewiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'enwiki', 1,
0 ) ) AS "Loads - enwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'eswiki', 1,
0 ) ) AS "Loads - eswiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'frwiki', 1,
0 ) ) AS "Loads - frwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'plwiki', 1,
0 ) ) AS "Loads - plwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'ptwiki', 1,
0 ) ) AS "Loads - ptwiki",
+ SUM( IF( event_action = 'saveAttempt', 1, 0 ) )
AS "Attempted saves",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'dewiki', 1, 0 ) ) AS "Attempted saves - dewiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'enwiki', 1, 0 ) ) AS "Attempted saves - enwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'eswiki', 1, 0 ) ) AS "Attempted saves - eswiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'frwiki', 1, 0 ) ) AS "Attempted saves - frwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'plwiki', 1, 0 ) ) AS "Attempted saves - plwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'ptwiki', 1, 0 ) ) AS "Attempted saves - ptwiki"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10604157
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10676603
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+ ) as internalQuery
+;
diff --git a/edit/saves_rates_comparative_success_attempts.sql
b/edit/saves_rates_comparative_success_attempts.sql
new file mode 100644
index 0000000..a469447
--- /dev/null
+++ b/edit/saves_rates_comparative_success_attempts.sql
@@ -0,0 +1,55 @@
+SELECT
+ day,
+ # Successful saves against Attempted saves rate
+ `Successful saves` / `Attempted saves`
AS "All wikis",
+ `Successful saves - dewiki` / `Attempted saves - dewiki` AS
"dewiki",
+ `Successful saves - enwiki` / `Attempted saves - enwiki` AS
"enwiki",
+ `Successful saves - eswiki` / `Attempted saves - eswiki` AS
"eswiki",
+ `Successful saves - frwiki` / `Attempted saves - frwiki` AS
"frwiki",
+ `Successful saves - plwiki` / `Attempted saves - plwiki` AS
"plwiki",
+ `Successful saves - ptwiki` / `Attempted saves - ptwiki` AS
"ptwiki"
+
+ FROM
+ ( SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'saveAttempt', 1, 0 ) )
AS "Attempted saves",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'dewiki', 1, 0 ) ) AS "Attempted saves - dewiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'enwiki', 1, 0 ) ) AS "Attempted saves - enwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'eswiki', 1, 0 ) ) AS "Attempted saves - eswiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'frwiki', 1, 0 ) ) AS "Attempted saves - frwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'plwiki', 1, 0 ) ) AS "Attempted saves - plwiki",
+ SUM( IF( event_action = 'saveAttempt' AND wiki =
'ptwiki', 1, 0 ) ) AS "Attempted saves - ptwiki",
+ SUM( IF( event_action = 'saveSuccess', 1, 0 ) )
AS "Successful saves",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'dewiki', 1, 0 ) ) AS "Successful saves - dewiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'enwiki', 1, 0 ) ) AS "Successful saves - enwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'eswiki', 1, 0 ) ) AS "Successful saves - eswiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'frwiki', 1, 0 ) ) AS "Successful saves - frwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'plwiki', 1, 0 ) ) AS "Successful saves - plwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'ptwiki', 1, 0 ) ) AS "Successful saves - ptwiki"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10604157
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10676603
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+ ) as internalQuery
+;
diff --git a/edit/saves_rates_comparative_successes.sql
b/edit/saves_rates_comparative_successes.sql
new file mode 100644
index 0000000..d4c96cf
--- /dev/null
+++ b/edit/saves_rates_comparative_successes.sql
@@ -0,0 +1,55 @@
+SELECT
+ day,
+ # "Successful saves against Loads rate"
+ `Successful saves` / `Loads`
AS "All wikis",
+ `Successful saves - dewiki` / `Loads - dewiki`
AS "dewiki",
+ `Successful saves - enwiki` / `Loads - enwiki`
AS "enwiki",
+ `Successful saves - eswiki` / `Loads - eswiki`
AS "eswiki",
+ `Successful saves - frwiki` / `Loads - frwiki`
AS "frwiki",
+ `Successful saves - plwiki` / `Loads - plwiki`
AS "plwiki",
+ `Successful saves - ptwiki` / `Loads - ptwiki`
AS "ptwiki"
+
+ FROM
+ ( SELECT
+
+ DATE( timestamp )
AS day,
+ SUM( IF( event_action = 'ready', 1, 0 ) )
AS "Loads",
+ SUM( IF( event_action = 'ready' AND wiki = 'dewiki', 1,
0 ) ) AS "Loads - dewiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'enwiki', 1,
0 ) ) AS "Loads - enwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'eswiki', 1,
0 ) ) AS "Loads - eswiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'frwiki', 1,
0 ) ) AS "Loads - frwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'plwiki', 1,
0 ) ) AS "Loads - plwiki",
+ SUM( IF( event_action = 'ready' AND wiki = 'ptwiki', 1,
0 ) ) AS "Loads - ptwiki",
+ SUM( IF( event_action = 'saveSuccess', 1, 0 ) )
AS "Successful saves",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'dewiki', 1, 0 ) ) AS "Successful saves - dewiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'enwiki', 1, 0 ) ) AS "Successful saves - enwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'eswiki', 1, 0 ) ) AS "Successful saves - eswiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'frwiki', 1, 0 ) ) AS "Successful saves - frwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'plwiki', 1, 0 ) ) AS "Successful saves - plwiki",
+ SUM( IF( event_action = 'saveSuccess' AND wiki =
'ptwiki', 1, 0 ) ) AS "Successful saves - ptwiki"
+
+ FROM
+ ( SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10604157
+
+ UNION ALL
+
+ SELECT
+
+ event_action,
+ wiki,
+ timestamp
+
+ FROM Edit_10676603
+
+ ) AS valid_edit_schema_revisions
+
+ GROUP BY day
+ ORDER BY day
+ ) as internalQuery
+;
--
To view, visit https://gerrit.wikimedia.org/r/177943
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I3a272388c06bed0531c9767f8367dcbbb0e0fc8c
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-edit-data
Gerrit-Branch: master
Gerrit-Owner: Jforrester <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
