Yuvipanda has uploaded a new change for review.
https://gerrit.wikimedia.org/r/60027
Change subject: Add graph of deleted uploads
......................................................................
Add graph of deleted uploads
Change-Id: I3866df427836a760d459faad91bafab91a81f399
---
M dashboards/reportcard.json
A datasources/deleted-uploads.json
A graphs/deleted-uploads.json
M mobile/config.yaml
A mobile/deleted-uploads.py
5 files changed, 284 insertions(+), 1 deletion(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-mobile-data
refs/changes/27/60027/1
diff --git a/dashboards/reportcard.json b/dashboards/reportcard.json
index 5726dd3..9b3b36e 100644
--- a/dashboards/reportcard.json
+++ b/dashboards/reportcard.json
@@ -9,9 +9,10 @@
"unique-uploaders",
"30-day-uploads",
"successful-uploads",
+ "deleted-uploads",
+ "no-cats",
"successful-logins",
"error-uploads",
- "no-cats",
"share-attempts"
]
},
diff --git a/datasources/deleted-uploads.json b/datasources/deleted-uploads.json
new file mode 100644
index 0000000..e1e5bfe
--- /dev/null
+++ b/datasources/deleted-uploads.json
@@ -0,0 +1,34 @@
+{
+ "name": "Deleted Uploads per day",
+ "timespan": {
+ "start": "2013/02/18",
+ "step": "1d",
+ "end": "2013/03/19"
+ },
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/deleted-uploads.csv";,
+ "notes": "",
+ "format": "csv",
+ "slug": null,
+ "shortName": "deleted-uploads",
+ "type": "timeseries",
+ "id": "deleted-uploads",
+ "columns": [
+ {
+ "type": "date",
+ "label": "date"
+ },
+ {
+ "type": "int",
+ "label": "Android"
+ },
+ {
+ "type": "int",
+ "label": "iOS"
+ },
+ {
+ "type": "int",
+ "label": "Web"
+ }
+ ],
+ "desc": ""
+}
diff --git a/graphs/deleted-uploads.json b/graphs/deleted-uploads.json
new file mode 100644
index 0000000..b85801a
--- /dev/null
+++ b/graphs/deleted-uploads.json
@@ -0,0 +1,155 @@
+{
+ "graph_version": "0.6.0",
+ "name": "Uploads deleted every day",
+ "notes": "",
+ "slug": "deleted-uploads",
+ "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": 1,
+ "metric": {
+ "source_id": "deleted-uploads",
+ "type": "int",
+ "source_col": 2
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Android",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 2,
+ "metric": {
+ "source_id": "deleted-uploads",
+ "type": "int",
+ "source_col": 3
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "iOS",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
+ {
+ "disabled": false,
+ "index": 3,
+ "metric": {
+ "source_id": "deleted-uploads",
+ "type": "int",
+ "source_col": 4
+ },
+ "nodeType": "line",
+ "options": {
+ "stroke": {
+ "width": 2
+ },
+ "label": "Web",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "id": "deleted-uploads",
+ "desc": ""
+}
diff --git a/mobile/config.yaml b/mobile/config.yaml
index e61f167..2df8f08 100644
--- a/mobile/config.yaml
+++ b/mobile/config.yaml
@@ -43,6 +43,8 @@
title: All time numbers
no-cats: # Only puppies
title: Percentage of files with a category added
+ deleted-uploads:
+ title: Mobile uploads deleted per day
performance-rendering:
title: Rendering time (ms)
diff --git a/mobile/deleted-uploads.py b/mobile/deleted-uploads.py
new file mode 100644
index 0000000..264d191
--- /dev/null
+++ b/mobile/deleted-uploads.py
@@ -0,0 +1,91 @@
+mobile_uploads_android_sql = u"""
+SELECT event_filename
+FROM {{tables.upload_attempts}}
+WHERE event_platform LIKE 'Android%'
+ AND event_result="success"
+ AND wiki="commonswiki"
+"""
+
+mobile_uploads_ios_sql = u"""
+SELECT event_filename
+FROM {{tables.upload_attempts}}
+WHERE event_platform LIKE 'iOS%'
+ AND event_result="success"
+ AND wiki="commonswiki"
+"""
+
+mobile_uploads_web_sql = u"""
+SELECT log_title
+FROM logging INNER JOIN tag_summary ON log_id = ts_log_id
+WHERE tag_summary.ts_tags = 'mobile edit'
+ AND log_action = 'upload'"""
+
+deleted_file_template = u"""
+SELECT
+ Date,
+ (SELECT COUNT(*) FROM logging WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ DATE(log_timestamp) = Date AND
+ log_action = 'delete'
+ ) AS Android,
+
+ (SELECT COUNT(*) FROM logging WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ DATE(log_timestamp) = Date AND
+ log_action = 'delete'
+ ) AS iOS,
+
+ (SELECT COUNT(*) FROM logging WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ DATE(log_timestamp) = Date AND
+ log_action = 'delete'
+ ) AS Web
+
+-- http://stackoverflow.com/a/6871220/365238
+FROM (
+ SELECT DATE_FORMAT(
+ ADDDATE(CURDATE() - INTERVAL {{ intervals.running_average }} - 1 DAY,
@num:=@num+1),
+ '%%%%Y-%%%%m-%%%%d'
+ ) AS Date
+ FROM logging, (SELECT @num:=-1) num LIMIT {{ intervals.running_average }}
+) AS Month;
+"""
+
+headers = ["date", "Android", "iOS", "Web"]
+
+def titles_for(dg, sql, src_db="el"):
+ titles = []
+ cur = dg.get_connection(src_db).cursor()
+
+ cur.execute(dg.render(sql))
+
+ for row in cur:
+ titles.append(row[0].replace("File:", ""))
+
+ print len(titles)
+ return titles
+
+def execute(dg):
+
+ results = []
+
+ android_titles = titles_for(dg, mobile_uploads_android_sql)
+ iOS_titles = titles_for(dg, mobile_uploads_ios_sql)
+ web_titles = titles_for(dg, mobile_uploads_web_sql, "commons")
+
+ deletes_sql = deleted_file_template % (
+ ('%s,' * len(android_titles))[:-1],
+ ('%s,' * len(iOS_titles))[:-1],
+ ('%s,' * len(web_titles))[:-1]
+ )
+ print deletes_sql
+
+ commons_cur = dg.get_connection('commons').cursor()
+ commons_cur.execute(dg.render(deletes_sql), android_titles + iOS_titles +
web_titles)
+
+ results = commons_cur.fetchall()
+
+ return (headers, results)
--
To view, visit https://gerrit.wikimedia.org/r/60027
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I3866df427836a760d459faad91bafab91a81f399
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-mobile-data
Gerrit-Branch: master
Gerrit-Owner: Yuvipanda <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
