Looking at https://meta.wikimedia.org/wiki/Schema, here are my observations:

• Edit https://meta.wikimedia.org/wiki/Schema:Edit "Logs generic events related to editing activity. All events are logged client-side, except that init and saveSuccess events for the wikitext editor are logged server-side."
  • Sampling rate is currently 6.25%. We need 100% to effectively correlate this data with the lower levels of traffic on the wikis we are targeting. Created T206543 to track this
• PrefUpdate https://meta.wikimedia.org/wiki/Schema:PrefUpdate is helpful for understanding when user's prefs have changed.
• Thanks. AFAICT, there is nothing instrumenting "thanks" – user giving thanks or receiving thanks. We could (1) reconstruct this data from the mediawiki DB, (2) add it to our PageViews schema, or (3) create a new "Thanks" schema for tracking giver/receiver, and we could tie that data into everything else we are analyzing
• Revert count. We discussed creating a "Revert/Undo" schema to log when a user reverts or undoes another user's edits.
• Email provided / email verification. We could add these to the PageView schema or we could create a new schema to track when users add an email to their account, and also when they verify their email.
• Block status. Might be useful to add the user's block status in the PageViews schema or to create a new schema around when users are blocked.
• API action requests. This schema exists https://meta.wikimedia.org/wiki/Schema:APIRequest but is inactive.
  • We could consider creating a new schema that hooks into APIAfterExecute, and logs user ID and action, plus whatever other "health metrics" we end up using in PageViews schema (like "editCount")
• Guided Tour https://meta.wikimedia.org/wiki/Schema:GuidedTour, tracks user ID so we should include this in analysis
• Getting Started https://meta.wikimedia.org/wiki/Schema:GettingStarted exists on Korean but not Czech wiki, so not sure we should include this now.
• Echo schemas https://meta.wikimedia.org/wiki/Schema:Echo most of these include user ID and should be included in analysis
• Edit conflict https://meta.wikimedia.org/wiki/Schema:EditConflict includes user ID, we can include it
• NewEditorEdit https://meta.wikimedia.org/wiki/Schema:NewEditorEdit -- includes 30 days of data so we could filter this down.
  • Inactive. We'd have to add code to re-implement this and I'm not sure if we need this exact schema
• PageCreation https://meta.wikimedia.org/wiki/Schema:PageCreation
  • Inactive. Do we want to reactivate?
• PageDeletion https://meta.wikimedia.org/wiki/Schema:PageDeletion
  • Inactive. Do we want to reactivate?
• EditConflict https://meta.wikimedia.org/wiki/Schema:EditConflict used, has user ID so we can incorporate the data

I'm probably missing something, so @nettrom_WMF please review the list closely and let me know your thoughts as well.

@kostajh -- thank you for doing such a thorough job. This gives me confidence that we're making to sure to use whatever existing code and data could be relevant, instead of reinventing the wheel. In general, please see the evolving specifications on T205758 to evaluate which of the existing schemas can help us. One thought I had while reading your list:

Regarding "Thanks", I feel like this must be stored in the Mediawiki database, because this page exists in public: https://en.wikipedia.org/wiki/Special:Log/thanks

@kostajh – Kudos from me too, excellent job on going through these!

I looked through the list of schemas and did my best to cross-reference them with the questions we're looking to answer, and I could not find any new candidates to be added.

Here are some comments on the list above:

• PrefUpdate and email: I don't think this captures whether a user adds/updates their email address, as that setting seems to be handled differently in the MediaWiki code, and I did some exploration of the logged data and couldn't find anything there either. When it comes to email verification, that's available in the user table as it stores the timestamp of the verification. So as suggested, we might want to have a schema that logs when a user adds or updates their email address.
• Thanks: that's stored in the logging table with log_type = 'thanks' and log_action = 'thank' (hence the Special:Log page @MMiller_WMF mentioned)
• Revert/undo: The SHA1 checksum in the revision table allows us to identify identity reverts after the fact. As far as I understand, those are most reverts, so I'm not sure if it's worth the effort to do more. I noticed that the "undo" link has a specific URL, so that could allow us to identify other types of reverts.
• Block status: this can be inferred from the logging table as blocks are logged there, but maybe it's useful for us to also capture this in the schema? It's the kind of transient status that I'm unsure about the cost of calculating, I haven't worked with the blocking data.
• PageCreation: these have been logged using an EventBus schema since mid-2017, so that is available for all wikis. I don't know where that schema is documented, but it logs a whole bunch of fields. See the mediawiki_page_create_3 table in the log database for the most recent data.
• PageDeletion: page deletions are also logged in the logging table. Not sure if we want more information than what is captured there?

I can't think of anything else. Thanks again for a great review, an excellent starting point @kostajh!

The final step on this task is to list the existing schemas we intend to use in our analysis. I think @nettrom_WMF should list them, and then we'll resolve this task and move on to T206802.

From what I can tell, the schemas that we intend to use (apart from the one we're developing) are:

• Echo
• EchoInteraction
• Edit
• GettingStartedRedirectImpression
• GuidedTourButtonClick
• GuidedTourExited
• GuidedTourExternalLinkActivation
• GuidedTourGuiderHidden
• GuidedTourGuiderImpression
• GuidedTourInternalLinkActivation
• PrefUpdate
• ServerSideAccountCreation

We also intend to use the page creation data that is captured through the EventBus and stored in the Data Lake (the mediawiki_page_create table in the event database).