This task is to track the impact of the work we are doing towards encouraging more email verifications.
I don't know how feasible it is to get this data into Logstash, because Logstash is more suited to event-type things, not tracking totals over time. We do already have useful information in the database that would allow us to get a snapshot of these metrics any time, and some limited information about what they might have looked like in the past (but finding a way to regularly snapshot them is probably better).
In the globaluser table, we have the following for every user:
We also capture the timestamp of when the link in the confirmation email expires (from which we can derive the timestamp when the email was sent), but unfortunately that is in the local user table (as user_email_token_expires), so it's a little harder (but not totally impossible) to find out how long ago a given user's last confirmation email was sent.
What I think would be easy to track in Logstash and could be helpful:
Thanks for looking into this. I tried to get a hold of the totals on superset but realized they scrub gu_email(makes sense) so I can only get count and timestamp of confirmed emails but not the unconfirmed ones.
What I think would be easy to track in Logstash and could be helpful:
- How many email confirmation emails are sent (I think we'll also need this for T415886)
- How many emails get confirmed (although the ratio between this metric and the previous one might be misleading, if there's a delay)
- How much time elapses between us sending a (user-initiated) confirmation email and the user confirming their email. (This is something we should be able to compute and log to logstash at the time the user confirms their email.)
This sounds fine to me. I'll update the task.
If you have access to the stat boxes, you can get it from there:
catrope@catrope-ThinkPad-T16-Gen-1$ ssh stat1008.eqiad.wmnet catrope@stat1008:~$ analytics-mysql centralauth mysql:research@dbstore1008.eqiad.wmnet [centralauth]> select if(gu_email is null or gu_email = '', 'no', 'yes') as hasemail, count(*) from globaluser group by hasemail; +----------+----------+ | hasemail | count(*) | +----------+----------+ | no | 19885382 | | yes | 62393788 | +----------+----------+ 2 rows in set (24.526 sec)
This sounds fine to me. I'll update the task.
Great, thanks! @mmartorana will work on this next week.
Thanks. Just to clarify timing: I’m still finishing my current sprint work and may not be able to start this next week.
Happy to pick it up once that’s done, or we can re prioritize if this is urgent.
Change #1245420 had a related patch set uploaded (by Mmartorana; author: Mmartorana):
[mediawiki/core@master] ConfirmEmail: Log confirmation email send events
Change #1245420 merged by jenkins-bot:
[mediawiki/core@master] ConfirmEmail: Log confirmation email send events
Weekly stats update:
+----------+----------+ | hasemail | count(*) | +----------+----------+ | no | 20275953 | | yes | 62528040 | +----------+----------+ +--------------+----------+ | email_status | cnt | +--------------+----------+ | confirmed | 23306047 | | unconfirmed | 39221560 | +--------------+----------+
Change #1247141 had a related patch set uploaded (by Mmartorana; author: Mmartorana):
[mediawiki/core@master] User: Log email confirmation completion
Change #1247141 merged by jenkins-bot:
[mediawiki/core@master] User: Log email confirmation completion
Change #1247641 had a related patch set uploaded (by Mmartorana; author: Mmartorana):
[mediawiki/core@master] Confirmemail: Log delay between email sent and confirmation
Change #1247651 had a related patch set uploaded (by Mmartorana; author: Mmartorana):
[operations/mediawiki-config@master] Enable confirmemail logstash channel
Change #1247641 merged by jenkins-bot:
[mediawiki/core@master] Confirmemail: Log delay between email sent and confirmation
Change #1248075 had a related patch set uploaded (by Mmartorana; author: Mmartorana):
[mediawiki/core@wmf/1.46.0-wmf.18] Confirmemail: Log delay between email sent and confirmation
Change #1247651 merged by jenkins-bot:
[operations/mediawiki-config@master] Enable confirmemail logstash channel
Change #1248075 merged by jenkins-bot:
[mediawiki/core@wmf/1.46.0-wmf.18] Confirmemail: Log delay between email sent and confirmation
Mentioned in SAL (#wikimedia-operations) [2026-03-09T13:28:42Z] <phuedx@deploy2002> Started scap sync-world: Backport for [[gerrit:1248075|Confirmemail: Log delay between email sent and confirmation (T415902)]], [[gerrit:1247651|Enable confirmemail logstash channel (T415902)]]
Mentioned in SAL (#wikimedia-operations) [2026-03-09T13:30:30Z] <phuedx@deploy2002> mmartorana, phuedx: Backport for [[gerrit:1248075|Confirmemail: Log delay between email sent and confirmation (T415902)]], [[gerrit:1247651|Enable confirmemail logstash channel (T415902)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.
Mentioned in SAL (#wikimedia-operations) [2026-03-09T13:39:58Z] <phuedx@deploy2002> Finished scap sync-world: Backport for [[gerrit:1248075|Confirmemail: Log delay between email sent and confirmation (T415902)]], [[gerrit:1247651|Enable confirmemail logstash channel (T415902)]] (duration: 11m 16s)
Weekly stats update:
+----------+----------+ | hasemail | count(*) | +----------+----------+ | no | 20494532 | | yes | 62605988 | +----------+----------+ +--------------+----------+ | email_status | cnt | +--------------+----------+ | confirmed | 23350536 | | unconfirmed | 39254938 | +--------------+----------+