Unable to identify openedx_user_id for some MITx Online users

[rachellougee]

Description/Context

There are some MITx Online app users that we can't match with open edx users by username in #781

select app_users.username,app_users.email, app_users.is_active, app_users.created_on
from  "ol_data_lake_production"."ol_warehouse_production_raw"."raw__mitxonline__app__postgres__users_user" as app_users
left join "ol_data_lake_production"."ol_warehouse_production_raw"."raw__mitxonline__openedx__mysql__auth_user" as openedx_users  on app_users.username = openedx_users.username
where openedx_users.id is null
order by app_users.created_on 

The mismatched username between the app and openedx seems to be either one of these cases

• usernames contain non-ASCII characters (e.g. ë)
  • e.g https://mitxonline.mit.edu/admin/users/user/11212/change/ and https://courses.mitxonline.mit.edu/admin/auth/user/584/change/
• mitxonline username contains an underscore, but the open edx username contains a dot
  • e.g. https://courses.mitxonline.mit.edu/admin/auth/user/279/change/ vs https://mitxonline.mit.edu/admin/users/user/281/change/
• No open edx user record
  • e.g. https://mitxonline.mit.edu/admin/users/user/60986/change/

List of users can be found https://docs.google.com/spreadsheets/d/1QaJqTHK_Qugsuu2puG6MFKT1XvijYQJR/edit#gid=1932674965

Looking into how we should handle these cases

Expected Behavior

int__mitxonline__users.openedx_user_id should be populated

Current Behavior

some null int__mitxonline__users.openedx_user_id

[pdpinch]

@rachellougee, please update this list, and we'll try to get it fixed once and for all.

[rachellougee]

@pdpinch I've updated the spreadsheet with two lists:

• 54 app users can't be matched on either username or email https://docs.google.com/spreadsheets/d/1QaJqTHK_Qugsuu2puG6MFKT1XvijYQJR/edit#gid=1908621716
  • While generating this list, I noted that there could be two app users linked to the same open edx user based on either username or email. e.g. https://mitxonline.mit.edu/admin/users/user/17078/change/, https://mitxonline.mit.edu/admin/users/user/9560/change/, https://courses.mitxonline.mit.edu/admin/auth/user/9491/change/
• 97 app users can't be matched on username https://docs.google.com/spreadsheets/d/1QaJqTHK_Qugsuu2puG6MFKT1XvijYQJR/edit#gid=1608772514

I checked a couple of these users. One user has never logged and there no account on open edx. The other one seems to be retired user who email is obscured on open edx. Not sure how we should handle these retired user cases

[pdpinch]

Can you clarify the field labels to make it more clear if they contain mitxonline username and emails or open edX usernames and emails? I'm not sure what I'm looking at.

[rachellougee]

Can you clarify the field labels to make it more clear if they contain mitxonline username and emails or open edX usernames and emails? I'm not sure what I'm looking at.

These fields are all from MITx online app, they don't contain any open edx field. Sorry for the confusion

[rachellougee]

There are 57 MITx Online app users with blank openedx_user_id https://bi.odl.mit.edu/queries/1345.

• 9 of them are active users
• 18 of them have enrollments but no certificates.
  • 15 are retired users. For those, I couldn't find any reference in open edx's usersocialauth
  • 3 users seem to be no record created in open edx . e.g. https://mitxonline.mit.edu/admin/users/user/41841/change/

In data platform, we mostly need their openedx_user_id for their course activities in open edx, but since they don't have account in open edx, then no course activities I assume. For these retired users, there are probably course activities for them, but now we can't find match in open edx due to username or email are hashed.

@pdpinch FY, I created https://github.com/mitodl/hq/issues/3613 for similar issue in xPro.

[rachellougee]

Filtering out inactive users for this report since there is not much we can do about retired users. So that still leaves us 9 users that don't have open edx accounts https://bi.odl.mit.edu/queries/1345. 3 of them have enrollments so there could be issue syncing their data at the time when they register. https://mitxonline.mit.edu/admin/users/user/76254/change/ looks like this user last login is the same date as joined on.