thanks @Charlotte - nothing in terms of targeting, but it would be good to see if there are patterns in usage of Notifications itself (event logging from T201943). That is:

• How many people turned on polling out of all those who saw Notifications?
• Interactions with different notification types - how many Milestone, Welcome and Thank notifications were sent vs viewed?
• Was there a particular notification type that shifted contribution/edit rate more than others?

Finally, tangentially it might be interesting if possible to see if notifications also lead to greater usage of the app in general (eg people opening the app to view notifications staying in-app afterwards).

Working on acquiring data for this and it turned out to be much, much harder and more involved than I anticipated. The analytics data we get from the app just has the notification ID, so I'm in the process of getting the Echo extension tables into Hive (that's the part that's causing problems & delays) so that I can get editing activity for users who got the notifications on Android.

I've come across a potential block and I would like some clarification. For context:

• MobileWikiAppNotificationInteraction EventLogging schema includes the IDs of the notification and the wiki as fetched/received by the Android app
• Because I'm trying to get editing activity (via the edit data in Data Lake) for app users who received notifications on their Android device, I had to sqoop data from the [[ https://www.mediawiki.org/wiki/Extension:Echo/echo_event_table | echo_event ]] and [[ https://www.mediawiki.org/wiki/Extension:Echo/echo_notification_table | echo_notification ]] tables (which contain Echo extension data) from MariaDB into Hadoop
  • A joined version of this data now exists as bearloga.echo in Hadoop/Hive
  • Since the echo_notifications table is not documented (every field has a "?" in the MW page) I had to make some guesses, such as the notification_event field being the key which corresponds to the event_id key in echo_event table
  • The notification_event and event_id keys have been confirmed by @Dbrant to be what is recorded by the app as [[ https://github.com/wikimedia/apps-android-wikipedia/blob/72d1fc8708b651b7d9ff99e0662b56231b0f8d2d/app/src/main/java/org/wikipedia/notifications/Notification.java#L32-L54 | the id in the notification code ]]

When double checking some sub-queries, I found something that's confusing me. The following Hive query (to look at the data on a small scale):

WITH android_notifications AS (
  -- This subquery refines Android app notification EL data
  SELECT
    event.app_install_id,
    CASE event.notification_type
         WHEN 'edit-thank' THEN 'thanks'
         WHEN 'thank-you-edit' THEN 'milestone'
         WHEN 'welcome' THEN 'welcome'
    END AS notification_type,
    event.notification_id,
    CASE event.action_rank
         WHEN 0 THEN 'mark as read and archive'
         WHEN 1 THEN 'primary'
         WHEN 2 THEN 'secondary (1)'
         WHEN 3 THEN 'secondary (2)'
    END AS notification_action,
    event.action_icon,
    event.client_dt
  FROM event.mobilewikiappnotificationinteraction
  WHERE revision = 18325732
    AND year = 2018 AND month = 11 AND day = 1
    AND event.notification_wiki = 'enwiki'
  LIMIT 10
)
SELECT
  client_dt AS date1, -- timestamp of interaction with notification in app/mobile OS
  notification_timestamp, -- timestamp of notification initially triggered by echo event
  event_id,
  notification_user AS user_id,
  app_install_id,
  notification_type,
  notification_action
FROM android_notifications
LEFT JOIN bearloga.echo ON (
  android_notifications.notification_id = echo.event_id
  AND echo.wiki_db = 'enwiki'
)
LIMIT 1000

yields the following results:

and the first thing you might notice is the difference in timestamps of the notification and the time the user interacted with it on their Android device. I'm not entirely sure who to ask about the Echo extension tables so I'm going with my best educated guess of @kaldari and @Catrope (please correct me if I'm wrong and you know who I should actually ask): am I correct in my understanding of the tables and their keys?

Dmitry told me that "[he knows] that the id is supposed to be the primary key of the notification in the actual database," so I just want to make sure that the reason for the differences in timestamps is because users are interacting with old notifications (e.g. from 2017) that they are just now seeing (e.g. in Nov 2018) on their devices (because maybe they don't edit on desktop/mobile web) and NOT because I've made a mistake with joining on the wrong keys.

Also if either of you maintain that extension (or know who is), can you please update (or ping someone to update) the descriptions of the fields in [[ https://www.mediawiki.org/wiki/Extension:Echo/echo_notification_table | echo_notification table ]] because currently none of them are described so I have to infer based on the column names in the schema.

Okay, Roan confirmed for me that's the correct interpretation.

Apologies for the lack of documentation in echo.sql. This patch adds documentation for all tables and fields.

Thank you, @Catrope!!!