Gilles has submitted this change and it was merged.
Change subject: Record optout stats for users with 1+ edits and 100+ edits
..
Record optout stats for users with 1+ edits and 100+ edits
These have been referred to at various places so let's track them.
Don't put them in the tsv file for now, the dashboard will be
crowded already and this can be changed easily later if needed,
but there is no way to get the data later if we don't do it now.
Change-Id: Ic80e0c2bef97b28d51f69a74a809ef6c82dd0424
---
M optout/init.sql
M optout/template.sql
2 files changed, 12 insertions(+), 3 deletions(-)
Approvals:
Gilles: Verified; Looks good to me, approved
jenkins-bot: Verified
diff --git a/optout/init.sql b/optout/init.sql
index 467cd74..38f0240 100644
--- a/optout/init.sql
+++ b/optout/init.sql
@@ -4,10 +4,14 @@
day DATE,
wikidb VARCHAR(16) COMMENT 'wiki dbname (e.g. commonswiki)',
all_touched INT COMMENT 'number of active users per user_touched
(user_touched now() - 30 days)',
+all_edited INT COMMENT 'number of users who have edited in the last 30
days',
all_active INT COMMENT 'number of active users per standard definition (at
least 5 edits in last 30 days)',
+all_very_active INT COMMENT 'number of very active users per standard
definition (at least 100 edits in the last 30 days)',
optout_total INT COMMENT 'total number of opted-out users',
optout_touched INT COMMENT 'opted-out users who are active per
user_touched',
-optout_active INT COMMENT 'opted-out users who are active per standard
definition'
+optout_edited INT COMMENT 'opted-out users who have edited in the last 30
days',
+optout_active INT COMMENT 'opted-out users who are active per standard
definition',
+optout_very_active INT COMMENT 'opted-out users who are very active per
standard definition'
);
CREATE UNIQUE INDEX mediaviewer_optout_day_wikidb ON mediaviewer_optout (day,
wikidb);
CREATE INDEX mediaviewer_optout_wikidb ON mediaviewer_optout (wikidb);
diff --git a/optout/template.sql b/optout/template.sql
index 9a462ee..c0d4520 100644
--- a/optout/template.sql
+++ b/optout/template.sql
@@ -2,15 +2,20 @@
-- put today's numbers into a temporary table
REPLACE INTO
-staging.mediaviewer_optout (day, wikidb, all_touched, all_active,
optout_total, optout_touched, optout_active)
+staging.mediaviewer_optout (day, wikidb, all_touched, all_edited,
all_active, all_very_active,
+optout_total, optout_touched, optout_edited, optout_active,
optout_very_active)
SELECT
CURDATE() day,
'%wikidb%' wikidb,
SUM(1) all_touched,
+SUM(edits_in_last_30_days 0) all_edited,
SUM(edits_in_last_30_days = 5) all_active,
+SUM(edits_in_last_30_days = 100) all_very_active,
(SELECT COUNT(*) FROM user_properties WHERE up_property =
'multimediaviewer-enable') optout_total,
SUM(up_value IS NOT NULL) optout_touched,
-SUM(edits_in_last_30_days = 5 AND up_value IS NOT NULL) optout_active
+SUM(edits_in_last_30_days 0 AND up_value IS NOT NULL) optout_edited,
+SUM(edits_in_last_30_days = 5 AND up_value IS NOT NULL) optout_active,
+SUM(edits_in_last_30_days = 100 AND up_value IS NOT NULL)
optout_very_active
FROM
user
LEFT JOIN user_properties ON user_id = up_user AND up_property =
'multimediaviewer-enable'
--
To view, visit https://gerrit.wikimedia.org/r/148842
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: Ic80e0c2bef97b28d51f69a74a809ef6c82dd0424
Gerrit-PatchSet: 2
Gerrit-Project: analytics/multimedia
Gerrit-Branch: master
Gerrit-Owner: Gergő Tisza gti...@wikimedia.org
Gerrit-Reviewer: Gilles gdu...@wikimedia.org
Gerrit-Reviewer: Springle sprin...@wikimedia.org
Gerrit-Reviewer: jenkins-bot
___
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
