Matthias Mullie has uploaded a new change for review.
https://gerrit.wikimedia.org/r/249394
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/echo_distribution_of_response_time.sql
A ee/echo_distribution_of_unread_notifications.sql
A ee/echo_monthly_production_and_consumption_of_notifications.sql
4 files changed, 110 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-ee-data
refs/changes/94/249394/1
diff --git a/ee/config.yaml b/ee/config.yaml
new file mode 100644
index 0000000..134182a
--- /dev/null
+++ b/ee/config.yaml
@@ -0,0 +1,36 @@
+databases:
+ mediawiki:
+ host: "analytics-store.eqiad.wmnet"
+ port: 3306
+ creds_file: /a/.my.cnf.research
+ db: enwiki # just a default, queries already specify it
+
+defaults:
+ db: mediawiki
+
+graphs: # must be here, or else generate.py will crash
+
+reportupdater-output: /a/limn-public-data/ee/datasets
+
+reportupdater-reports:
+ echo_monthly_production_and_consumption_of_notifications:
+ frequency: months
+ granularity: months
+ timeboxed: true
+ starts: 2015-11-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
+ echo_distribution_of_unread_notifications:
+ frequency: months
+ granularity: months
+ timeboxed: true
+ starts: 2015-11-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
+ echo_distribution_of_response_time:
+ frequency: days
+ granularity: days
+ timeboxed: true
+ starts: 2015-11-01
+ explode_by:
+ wiki_db: enwiki, dewiki, eswiki, frwiki, hewiki, huwiki, itwiki,
nlwiki, plwiki, ptwiki, svwiki
diff --git a/ee/echo_distribution_of_response_time.sql
b/ee/echo_distribution_of_response_time.sql
new file mode 100644
index 0000000..3c7f87f
--- /dev/null
+++ b/ee/echo_distribution_of_response_time.sql
@@ -0,0 +1,20 @@
+SELECT
+ SUBSTRING(notification_timestamp, 1, 6) 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 {wiki_db}.echo_notification AS bundle ON
bundle.notification_bundle_display_hash =
notification.notification_bundle_display_hash AND
bundle.notification_bundle_display_hash != "" AND
bundle.notification_bundle_base = 1
+) AS temp
+GROUP BY date
+ORDER BY date ASC;
diff --git a/ee/echo_distribution_of_unread_notifications.sql
b/ee/echo_distribution_of_unread_notifications.sql
new file mode 100644
index 0000000..115bc31
--- /dev/null
+++ b/ee/echo_distribution_of_unread_notifications.sql
@@ -0,0 +1,20 @@
+SELECT
+ SUBSTRING(notification_timestamp, 1, 6) AS date,
+ SUM(unread_diff > 2592000) AS 30_plus,
+ SUM(notification_read_timestamp IS NOT NULL AND unread_diff <= 2592000)
AS read_under_30,
+ SUM(notification_read_timestamp IS NULL AND unread_diff <= 2592000) AS
recent_unread,
+ SUM(unread_diff > 2592000) / COUNT(*) * 100 AS percentage
+FROM
+(
+ SELECT
+ notification.notification_event,
+ notification.notification_timestamp,
+ # timestamp it was read, or null if it hasn't been read yet
+ IFNULL(bundle.notification_read_timestamp,
notification.notification_read_timestamp) AS notification_read_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 {wiki_db}.echo_notification AS bundle ON
bundle.notification_bundle_display_hash =
notification.notification_bundle_display_hash AND
bundle.notification_bundle_display_hash != "" AND
bundle.notification_bundle_base = 1
+) AS temp
+GROUP BY date
+ORDER BY date ASC;
diff --git a/ee/echo_monthly_production_and_consumption_of_notifications.sql
b/ee/echo_monthly_production_and_consumption_of_notifications.sql
new file mode 100644
index 0000000..0040132
--- /dev/null
+++ b/ee/echo_monthly_production_and_consumption_of_notifications.sql
@@ -0,0 +1,34 @@
+SELECT date, SUM(notifications_sent) AS notifications_sent,
SUM(notifications_read) AS notifications_read
+FROM
+(
+ # amount of sent notifications per month
+ SELECT CONCAT(SUBSTRING(notification_timestamp, 1, 4), "-",
SUBSTRING(notification_timestamp, 5, 2)) AS date, COUNT(*) AS
notifications_sent, 0 AS notifications_read
+ FROM {wiki_db}.echo_notification
+ GROUP BY SUBSTRING(notification_timestamp, 1, 6)
+
+ 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 CONCAT(SUBSTRING(notification_read_timestamp, 1, 4), "-",
SUBSTRING(notification_read_timestamp, 5, 2)) AS date, 0 AS notifications_sent,
SUM(count) AS notifications_read
+ FROM
+ (
+ # bundled notifications
+ SELECT MAX(notification_read_timestamp) AS
notification_read_timestamp, COUNT(*) AS count
+ FROM {wiki_db}.echo_notification
+ GROUP BY notification_bundle_display_hash
+
+ UNION
+
+ # unbundled notifications
+ SELECT notification_read_timestamp, 1 AS count
+ FROM {wiki_db}.echo_notification
+ WHERE notification_bundle_display_hash = ''
+ ) AS temp
+ WHERE notification_read_timestamp IS NOT NULL
+ GROUP BY SUBSTRING(notification_read_timestamp, 1, 6)
+) AS temp
+GROUP BY date
+ORDER BY date ASC;
--
To view, visit https://gerrit.wikimedia.org/r/249394
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I03fda4c6623d7493e7400640a3dad1f01d9b962b
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-ee-data
Gerrit-Branch: master
Gerrit-Owner: Matthias Mullie <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
