Milimetric has submitted this change and it was merged.
Change subject: Measure the user responsiveness to notifications over time
......................................................................
Measure the user responsiveness to notifications over time
Bug: T108208
Change-Id: I03fda4c6623d7493e7400640a3dad1f01d9b962b
---
A ee/config.yaml
A ee/distribution_of_response_time.sql
A ee/distribution_of_unread_notifications.sql
A ee/monthly_production_and_consumption_of_notifications.sql
4 files changed, 116 insertions(+), 0 deletions(-)
Approvals:
Milimetric: Verified; Looks good to me, approved
diff --git a/ee/config.yaml b/ee/config.yaml
new file mode 100644
index 0000000..e089e28
--- /dev/null
+++ b/ee/config.yaml
@@ -0,0 +1,39 @@
+databases:
+ x1:
+ host: "x1-analytics-slave.eqiad.wmnet"
+ port: 3306
+ creds_file: /a/.my.cnf.research
+ db: enwiki # just a default, queries already specify it
+
+defaults:
+ db: x1
+
+graphs: # must be here, or else generate.py will crash
+
+reportupdater-output: /a/limn-public-data/metrics/echo
+
+reportupdater-reports:
+ monthly_production_and_consumption_of_notifications:
+ frequency: months
+ granularity: months
+ lag: 2678400 # 31 days in seconds
+ timeboxed: true
+ starts: 2013-09-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
+ distribution_of_unread_notifications:
+ frequency: months
+ granularity: months
+ lag: 2678400 # 31 days in seconds
+ timeboxed: true
+ starts: 2013-09-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
+ distribution_of_response_time:
+ frequency: months
+ granularity: months
+ lag: 2678400 # 31 days in seconds
+ timeboxed: true
+ starts: 2013-09-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
diff --git a/ee/distribution_of_response_time.sql
b/ee/distribution_of_response_time.sql
new file mode 100644
index 0000000..3433c89
--- /dev/null
+++ b/ee/distribution_of_response_time.sql
@@ -0,0 +1,26 @@
+SELECT
+ DATE('{from_timestamp}') AS date,
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff < 172801)
AS 0_to_2, # <= 2 days
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff BETWEEN
172800 AND 432001) AS 2_to_5, # 2 < x <= 5 days
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff BETWEEN
432000 AND 864001) AS 5_to_10, # 5 < x <= 10 days
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff BETWEEN
864000 AND 1728001) AS 10_to_20, # 10 < x <= 20 days
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff BETWEEN
1728000 AND 2592001) AS 20_to_30, # 20 < x <= 30 days
+ SUM(unread_diff > 2592000) AS 30_plus # > 30 days
+FROM
+(
+ SELECT
+ notification.notification_event,
+ notification.notification_timestamp,
+ IFNULL(bundle.notification_read_timestamp,
notification.notification_read_timestamp) AS notification_read_timestamp,
+ COALESCE(bundle.notification_read_timestamp,
notification.notification_read_timestamp, DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')) -
notification.notification_timestamp AS unread_diff
+ FROM {wiki_db}.echo_notification AS notification
+ LEFT JOIN
+ (
+ SELECT
+ notification_read_timestamp,
+ notification_bundle_display_hash
+ FROM {wiki_db}.echo_notification
+ WHERE notification_bundle_base = 1
+ ) bundle ON notification.notification_bundle_display_hash =
bundle.notification_bundle_display_hash AND
notification.notification_bundle_display_hash != ''
+ WHERE notification.notification_timestamp BETWEEN '{from_timestamp}'
AND '{to_timestamp}'
+) AS temp;
diff --git a/ee/distribution_of_unread_notifications.sql
b/ee/distribution_of_unread_notifications.sql
new file mode 100644
index 0000000..f168cbc
--- /dev/null
+++ b/ee/distribution_of_unread_notifications.sql
@@ -0,0 +1,21 @@
+SELECT
+ DATE('{from_timestamp}') AS date,
+ SUM(unread_diff > 2592000) / COUNT(*) * 100 AS percentage
+FROM
+(
+ SELECT
+ notification.notification_event,
+ notification.notification_timestamp,
+ # diff in seconds between notification time & read time (or
now, if it hasn't been read yet)
+ COALESCE(bundle.notification_read_timestamp,
notification.notification_read_timestamp, DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')) -
notification.notification_timestamp AS unread_diff
+ FROM {wiki_db}.echo_notification AS notification
+ LEFT JOIN
+ (
+ SELECT
+ notification_read_timestamp,
+ notification_bundle_display_hash
+ FROM {wiki_db}.echo_notification
+ WHERE notification_bundle_base = 1
+ ) bundle ON notification.notification_bundle_display_hash =
bundle.notification_bundle_display_hash AND
notification.notification_bundle_display_hash != ''
+ WHERE notification.notification_timestamp BETWEEN '{from_timestamp}'
AND '{to_timestamp}'
+) AS temp;
diff --git a/ee/monthly_production_and_consumption_of_notifications.sql
b/ee/monthly_production_and_consumption_of_notifications.sql
new file mode 100644
index 0000000..9bed52f
--- /dev/null
+++ b/ee/monthly_production_and_consumption_of_notifications.sql
@@ -0,0 +1,30 @@
+SELECT DATE('{from_timestamp}') AS date, SUM(notifications_sent) AS
notifications_sent, SUM(notifications_read) AS notifications_read
+FROM
+(
+ # amount of sent notifications per month
+ SELECT
+ COUNT(*) AS notifications_sent,
+ 0 AS notifications_read
+ FROM {wiki_db}.echo_notification
+ WHERE notification_timestamp BETWEEN '{from_timestamp}' AND
'{to_timestamp}'
+
+ UNION
+
+ # amount of read notifications per month is harder: notifications
+ # are bundled and only one of the gets the read timestamp, so we'll
+ # have to group them based on how they're bundled & get that timestamp,
+ # then have another query to fetch the unbundled notifications
+ SELECT
+ 0 AS notifications_sent,
+ COUNT(*) AS notifications_read
+ FROM {wiki_db}.echo_notification AS notification
+ LEFT JOIN
+ (
+ SELECT
+ notification_read_timestamp,
+ notification_bundle_display_hash
+ FROM {wiki_db}.echo_notification
+ WHERE notification_bundle_base = 1
+ ) bundle ON notification.notification_bundle_display_hash =
bundle.notification_bundle_display_hash AND
notification.notification_bundle_display_hash != ''
+ WHERE notification.notification_read_timestamp BETWEEN
'{from_timestamp}' AND '{to_timestamp}'
+) AS temp;
--
To view, visit https://gerrit.wikimedia.org/r/249394
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I03fda4c6623d7493e7400640a3dad1f01d9b962b
Gerrit-PatchSet: 5
Gerrit-Project: analytics/limn-ee-data
Gerrit-Branch: master
Gerrit-Owner: Matthias Mullie <[email protected]>
Gerrit-Reviewer: Matthias Mullie <[email protected]>
Gerrit-Reviewer: Mforns <[email protected]>
Gerrit-Reviewer: Milimetric <[email protected]>
Gerrit-Reviewer: Springle <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
