Milimetric has submitted this change and it was merged.
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(-)
Approvals:
Milimetric: Verified; Looks good to me, approved
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..9375773
--- /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..6453e2f
--- /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..4cf48fe
--- /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..6fe014e
--- /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..b514bf6
--- /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..ba86e53
--- /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..6728ad0
--- /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: merged
Gerrit-Change-Id: I3a272388c06bed0531c9767f8367dcbbb0e0fc8c
Gerrit-PatchSet: 2
Gerrit-Project: analytics/limn-edit-data
Gerrit-Branch: master
Gerrit-Owner: Jforrester <[email protected]>
Gerrit-Reviewer: Catrope <[email protected]>
Gerrit-Reviewer: Milimetric <[email protected]>
Gerrit-Reviewer: Nuria <[email protected]>
Gerrit-Reviewer: OliverKeyes <[email protected]>
Gerrit-Reviewer: Springle <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
