BearND has uploaded a new change for review.
https://gerrit.wikimedia.org/r/156735
Change subject: Add edit funnel sql for apps
......................................................................
Add edit funnel sql for apps
Similar to the previous app edits reports but one graph per OS
with the main event_actions on the same graph.
Am going to remove the previous ones once they get approved.
Still want to keep them around for comparison sake.
Change-Id: I2603c917cfc3c1379888f2c6e4c65c65d305c5f1
---
A datasources/app-edit-funnel-android.json
A datasources/app-edit-funnel-ios.json
A datasources/app-edit-funnel-total.json
A graphs/app-edit-funnel-android.json
A graphs/app-edit-funnel-ios.json
A graphs/app-edit-funnel-total.json
A mobile/app-edit-funnel-android.sql
A mobile/app-edit-funnel-ios.sql
A mobile/app-edit-funnel-total.sql
9 files changed, 671 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-mobile-data
refs/changes/35/156735/1
diff --git a/datasources/app-edit-funnel-android.json
b/datasources/app-edit-funnel-android.json
new file mode 100644
index 0000000..d6f87f0
--- /dev/null
+++ b/datasources/app-edit-funnel-android.json
@@ -0,0 +1,37 @@
+{
+ "name": "App Edits - Android (per day)",
+ "timespan": {
+ "start": "2014/06/25",
+ "step": "1d"
+ },
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/app-edit-funnel-android.csv";,
+ "notes": "data excludes test wiki",
+ "format": "csv",
+ "slug": null,
+ "shortName": "app-edit-funnel-android",
+ "type": "timeseries",
+ "id": "app-edit-funnel-android",
+ "columns": [
+ {
+ "type": "date",
+ "label": "date"
+ },
+ {
+ "type": "int",
+ "label": "Starts"
+ },
+ {
+ "type": "int",
+ "label": "Previews"
+ },
+ {
+ "type": "int",
+ "label": "Save attempts"
+ },
+ {
+ "type": "int",
+ "label": "Saved"
+ }
+ ],
+ "desc": ""
+}
diff --git a/datasources/app-edit-funnel-ios.json
b/datasources/app-edit-funnel-ios.json
new file mode 100644
index 0000000..fb6baad
--- /dev/null
+++ b/datasources/app-edit-funnel-ios.json
@@ -0,0 +1,37 @@
+{
+ "name": "App Edits - iOS (per day)",
+ "timespan": {
+ "start": "2014/06/25",
+ "step": "1d"
+ },
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/app-edit-funnel-ios.csv";,
+ "notes": "data excludes test wiki",
+ "format": "csv",
+ "slug": null,
+ "shortName": "app-edit-funnel-ios",
+ "type": "timeseries",
+ "id": "app-edit-funnel-ios",
+ "columns": [
+ {
+ "type": "date",
+ "label": "date"
+ },
+ {
+ "type": "int",
+ "label": "Starts"
+ },
+ {
+ "type": "int",
+ "label": "Previews"
+ },
+ {
+ "type": "int",
+ "label": "Save attempts"
+ },
+ {
+ "type": "int",
+ "label": "Saved"
+ }
+ ],
+ "desc": ""
+}
diff --git a/datasources/app-edit-funnel-total.json
b/datasources/app-edit-funnel-total.json
new file mode 100644
index 0000000..8f687ed
--- /dev/null
+++ b/datasources/app-edit-funnel-total.json
@@ -0,0 +1,37 @@
+{
+ "name": "App Edits - all apps (per day)",
+ "timespan": {
+ "start": "2014/06/25",
+ "step": "1d"
+ },
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/app-edit-funnel-total.csv";,
+ "notes": "data excludes test wiki",
+ "format": "csv",
+ "slug": null,
+ "shortName": "app-edit-funnel-total",
+ "type": "timeseries",
+ "id": "app-edit-funnel-total",
+ "columns": [
+ {
+ "type": "date",
+ "label": "date"
+ },
+ {
+ "type": "int",
+ "label": "Starts"
+ },
+ {
+ "type": "int",
+ "label": "Previews"
+ },
+ {
+ "type": "int",
+ "label": "Save attempts"
+ },
+ {
+ "type": "int",
+ "label": "Saved"
+ }
+ ],
+ "desc": ""
+}
diff --git a/graphs/app-edit-funnel-android.json
b/graphs/app-edit-funnel-android.json
new file mode 100644
index 0000000..dd3ca90
--- /dev/null
+++ b/graphs/app-edit-funnel-android.json
@@ -0,0 +1,173 @@
+{
+ "graph_version": "0.6.0",
+ "name": "App Edits - Android (per day)",
+ "notes": "",
+ "slug": "app-edit-funnel-android",
+ "shortName": "",
+ "root": {
+ "scaling": "linear",
+ "nodeType": "canvas",
+ "minWidth": 750,
+ "minHeight": 500,
+ "height": 500,
+ "disabled": false,
+ "width": "auto",
+ "children": [
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "x",
+ "orient": "bottom"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "y",
+ "orient": "left"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "x"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "y"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "zoom-brush",
+ "options": {
+ "allowY": true,
+ "allowX": true
+ }
+ },
+ {
+ "nodeType": "callout",
+ "target": "latest",
+ "disabled": false,
+ "steps": [
+ "1y",
+ "1M"
+ ],
+ "metricRef": 0,
+ "options": {
+ "deltaPercent": true,
+ "dateFormat": "MMM YYYY",
+ "colorDelta": true
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "legend",
+ "options": {
+ "valueFormat": ",.2s",
+ "dateFormat": "MMM YYYY"
+ },
+ "label": "Aug 2012"
+ },
+ {
+ "disabled": false,
+ "nodeType": "line-group",
+ "options": {
+ "palette": "wmf_projects",
+ "scale": "log",
+ "stroke": {
+ "opacity": 1,
+ "width": 2
+ },
+ "dateFormat": "MMM YYYY"
+ },
+ "children": [
+ {
+ "disabled": false,
+ "index": 0,
+ "metric": {
+ "source_id": "app-edit-funnel-android",
+ "type": "int",
+ "source_col": 1
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Starts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 1,
+ "metric": {
+ "source_id": "app-edit-funnel-android",
+ "type": "int",
+ "source_col": 2
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Previews",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 2,
+ "metric": {
+ "source_id": "app-edit-funnel-android",
+ "type": "int",
+ "source_col": 3
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Save attempts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 3,
+ "metric": {
+ "source_id": "app-edit-funnel-android",
+ "type": "int",
+ "source_col": 4
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Saved",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "id": "app-edit-funnel-android",
+ "desc": ""
+}
diff --git a/graphs/app-edit-funnel-ios.json b/graphs/app-edit-funnel-ios.json
new file mode 100644
index 0000000..07fe1c9
--- /dev/null
+++ b/graphs/app-edit-funnel-ios.json
@@ -0,0 +1,173 @@
+{
+ "graph_version": "0.6.0",
+ "name": "App Edits - iOS (per day)",
+ "notes": "",
+ "slug": "app-edit-funnel-ios",
+ "shortName": "",
+ "root": {
+ "scaling": "linear",
+ "nodeType": "canvas",
+ "minWidth": 750,
+ "minHeight": 500,
+ "height": 500,
+ "disabled": false,
+ "width": "auto",
+ "children": [
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "x",
+ "orient": "bottom"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "y",
+ "orient": "left"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "x"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "y"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "zoom-brush",
+ "options": {
+ "allowY": true,
+ "allowX": true
+ }
+ },
+ {
+ "nodeType": "callout",
+ "target": "latest",
+ "disabled": false,
+ "steps": [
+ "1y",
+ "1M"
+ ],
+ "metricRef": 0,
+ "options": {
+ "deltaPercent": true,
+ "dateFormat": "MMM YYYY",
+ "colorDelta": true
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "legend",
+ "options": {
+ "valueFormat": ",.2s",
+ "dateFormat": "MMM YYYY"
+ },
+ "label": "Aug 2012"
+ },
+ {
+ "disabled": false,
+ "nodeType": "line-group",
+ "options": {
+ "palette": "wmf_projects",
+ "scale": "log",
+ "stroke": {
+ "opacity": 1,
+ "width": 2
+ },
+ "dateFormat": "MMM YYYY"
+ },
+ "children": [
+ {
+ "disabled": false,
+ "index": 0,
+ "metric": {
+ "source_id": "app-edit-funnel-ios",
+ "type": "int",
+ "source_col": 1
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Starts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 1,
+ "metric": {
+ "source_id": "app-edit-funnel-ios",
+ "type": "int",
+ "source_col": 2
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Previews",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 2,
+ "metric": {
+ "source_id": "app-edit-funnel-ios",
+ "type": "int",
+ "source_col": 3
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Save attempts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 3,
+ "metric": {
+ "source_id": "app-edit-funnel-ios",
+ "type": "int",
+ "source_col": 4
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Saved",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "id": "app-edit-funnel-ios",
+ "desc": ""
+}
diff --git a/graphs/app-edit-funnel-total.json
b/graphs/app-edit-funnel-total.json
new file mode 100644
index 0000000..ea47a37
--- /dev/null
+++ b/graphs/app-edit-funnel-total.json
@@ -0,0 +1,173 @@
+{
+ "graph_version": "0.6.0",
+ "name": "App Edits - all apps (per day)",
+ "notes": "",
+ "slug": "app-edit-funnel-total",
+ "shortName": "",
+ "root": {
+ "scaling": "linear",
+ "nodeType": "canvas",
+ "minWidth": 750,
+ "minHeight": 500,
+ "height": 500,
+ "disabled": false,
+ "width": "auto",
+ "children": [
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "x",
+ "orient": "bottom"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "axis",
+ "options": {
+ "tickFormat": "MMM YY",
+ "dimension": "y",
+ "orient": "left"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "x"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "grid",
+ "options": {
+ "ticks": 10,
+ "dimension": "y"
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "zoom-brush",
+ "options": {
+ "allowY": true,
+ "allowX": true
+ }
+ },
+ {
+ "nodeType": "callout",
+ "target": "latest",
+ "disabled": false,
+ "steps": [
+ "1y",
+ "1M"
+ ],
+ "metricRef": 0,
+ "options": {
+ "deltaPercent": true,
+ "dateFormat": "MMM YYYY",
+ "colorDelta": true
+ }
+ },
+ {
+ "disabled": false,
+ "nodeType": "legend",
+ "options": {
+ "valueFormat": ",.2s",
+ "dateFormat": "MMM YYYY"
+ },
+ "label": "Aug 2012"
+ },
+ {
+ "disabled": false,
+ "nodeType": "line-group",
+ "options": {
+ "palette": "wmf_projects",
+ "scale": "log",
+ "stroke": {
+ "opacity": 1,
+ "width": 2
+ },
+ "dateFormat": "MMM YYYY"
+ },
+ "children": [
+ {
+ "disabled": false,
+ "index": 0,
+ "metric": {
+ "source_id": "app-edit-funnel-total",
+ "type": "int",
+ "source_col": 1
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Starts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 1,
+ "metric": {
+ "source_id": "app-edit-funnel-total",
+ "type": "int",
+ "source_col": 2
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Previews",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 2,
+ "metric": {
+ "source_id": "app-edit-funnel-total",
+ "type": "int",
+ "source_col": 3
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Save attempts",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 3,
+ "metric": {
+ "source_id": "app-edit-funnel-total",
+ "type": "int",
+ "source_col": 4
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Saved",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "id": "app-edit-funnel-total",
+ "desc": ""
+}
diff --git a/mobile/app-edit-funnel-android.sql
b/mobile/app-edit-funnel-android.sql
new file mode 100644
index 0000000..ef1984c
--- /dev/null
+++ b/mobile/app-edit-funnel-android.sql
@@ -0,0 +1,14 @@
+-- derived from cancelled-uploads.sql
+SELECT SUM( IF( event_action = 'start', 1, 0) ) AS start,
+ SUM( IF( event_action = 'preview', 1, 0) ) AS preview,
+ SUM( IF( event_action = 'saveAttempt', 1, 0) ) AS saveAttempt,
+ SUM( IF( event_action = 'saved', 1, 0) ) AS saved
+
+FROM {{ tables.edits_app }}
+
+WHERE userAgent LIKE '%Android%'
+ AND wiki != 'testwiki'
+ AND timestamp >= '{from_timestamp}'
+ AND timestamp < '{to_timestamp}'
+
+GROUP BY DATE( timestamp )
diff --git a/mobile/app-edit-funnel-ios.sql b/mobile/app-edit-funnel-ios.sql
new file mode 100644
index 0000000..6a7ada9
--- /dev/null
+++ b/mobile/app-edit-funnel-ios.sql
@@ -0,0 +1,14 @@
+-- derived from cancelled-uploads.sql
+SELECT SUM( IF( event_action = 'start', 1, 0) ) AS start,
+ SUM( IF( event_action = 'preview', 1, 0) ) AS preview,
+ SUM( IF( event_action = 'saveAttempt', 1, 0) ) AS saveAttempt,
+ SUM( IF( event_action = 'saved', 1, 0) ) AS saved
+
+FROM {{ tables.edits_app }}
+
+WHERE userAgent LIKE '%Darwin%' OR userAgent LIKE '%iPhone OS%'
+ AND wiki != 'testwiki'
+ AND timestamp >= '{from_timestamp}'
+ AND timestamp < '{to_timestamp}'
+
+GROUP BY DATE( timestamp )
diff --git a/mobile/app-edit-funnel-total.sql b/mobile/app-edit-funnel-total.sql
new file mode 100644
index 0000000..36de3f6
--- /dev/null
+++ b/mobile/app-edit-funnel-total.sql
@@ -0,0 +1,13 @@
+-- derived from cancelled-uploads.sql
+SELECT SUM( IF( event_action = 'start', 1, 0) ) AS start,
+ SUM( IF( event_action = 'preview', 1, 0) ) AS preview,
+ SUM( IF( event_action = 'saveAttempt', 1, 0) ) AS saveAttempt,
+ SUM( IF( event_action = 'saved', 1, 0) ) AS saved
+
+FROM {{ tables.edits_app }}
+
+WHERE wiki != 'testwiki'
+ AND timestamp >= '{from_timestamp}'
+ AND timestamp < '{to_timestamp}'
+
+GROUP BY DATE( timestamp )
--
To view, visit https://gerrit.wikimedia.org/r/156735
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I2603c917cfc3c1379888f2c6e4c65c65d305c5f1
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-mobile-data
Gerrit-Branch: master
Gerrit-Owner: BearND <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
