Introduction:
- This document describes the enhanced/derived Device and Extended User Profile schemas, design of how the profile data is captured and how it can be used across various systems.
Design Flow:
Schema Design for both User and Device Profile
Computation Table:
Field | Type | Profile | Description | Source | Release | |
|---|---|---|---|---|---|---|
| 1 | device_id | String | Device | Unique id of device | From first source which inserts a record | |
| 2 | total_ts | Double | Device | Total Time spent on device | Device profile updater data product | |
| 3 | total_launches | Long | Device | Number app/portal launches from the device | Device profile updater data product | |
| 4 | avg_ts | Double | Device | Average time spent on device | Device profile updater data product | |
| 5 | first_access | Timestamp | Device | Timestamp on which device was first accessed | Device profile updater data product | |
| 6 | last_access | Timestamp | Device | Timestamp on which device was last accessed | Device profile updater data product | |
| 7 | state | String | Device | State from which device is accessed | Device Register API | |
| 8 | state_code | String | Device | Device state code | Device Register API | |
| 9 | state_code_custom | String | Device | Device custom state code | Device Register API | |
| 10 | state_custom | String | Device | Device custom state name | Device Register API | |
| 11 | city | String | Device | City from which device is accessed | Device Register API | |
| 12 | country | String | Device | Country from which device is accessed | Device Register API | |
| 13 | country_code | String | Device | Device country code | Device Register API | |
| 14 | district_custom | String | Device | Device custom district name | Device Register API | |
| 15 | device_spec | Map<String,String> | Device | Device Specification | Device Register API | |
| 16 | uaspec | Map<String,String> | Device | Device user agent data | Device Register API | |
| 17 | fcm_token | String | Device | FCM token for app devices | Device Register API | 2.1.0 |
| 18 | producer_id | String | Device | Producer id for which device belongs to(App/Portal) | Device Register API | 2.1.0 |
| 19 | producer_ver | String | Device | Version of producer | Device profile updater data product | |
| 20 | user_ids | List<String> | Device | List of unique users accessed from the device | Device profile updater data product | |
| 21 | user_count | Long | Device | Unique users accessed from the device | Device profile updater data product | |
| 22 | last_updated_by | String | Device | Source from which record was last updated | Source which is updating the record | |
| 23 | sources | List<String> | Device | List of sources from which record gets updated | From all the sources | |
| 24 | user_id | String | User | Unique id for user | From first source which inserts a record | 2.2.0 |
| 25 | user_type | String | User | Type of user(Anonymous/SelfSignedIn/ValidatedUser) | User_Org Cassandra via Samza job | 2.2.0 |
| 26 | user_role | String | User | Role of user(Student/Teacher) | AUDIT events via Samza job | 2.2.0 |
| 27 | sign_in_type | String | User | Type of sign-in(GoogleAuth/SingleSignIn) | User_Org Cassandra via Samza job | 2.2.0 |
| 28 | user_state | String | User | State name of the User | User_Org Cassandra via Samza job | |
| 29 | user_district | String | User | District name of the User | User_Org Cassandra via Samza job | |
| 30 | user_block | String | User | Block name of the User | User_Org Cassandra via Samza job | |
| 31 | first_access | Timestamp | User | Timestamp on which user was first seen | User Profile Updater data product | |
| 32 | last_access | Timestamp | User | Timestamp on which user was last seen | User Profile Updater data product | |
| 33 | last_device_accessed | String | User | Device id on which user was last seen | User Profile Updater data product | |
| 34 | device_ids | List<String> | User | List of unique devices on which user was seen | User Profile Updater data product | |
| 35 | last_updated_by | String | User | Source from which record was last updated | Source which is updating the record | |
| 36 | sources | List<String> | User | List of sources from which record gets updated | From all the sources | |
| 37 | last_content_played | String | User | Last content played by user | User Profile Updater data product | |
| 38 | total_ts | Double | User | Total time spent by user | User Profile Updater data product |
Questions:
- Possibility of switch in user_role for APP users, Is it ok to have multiple values for user_role in the telemetry for same user_id?
- Can we enhance the existing user table or create new one in analytics database.