...
- 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.
#diagram#Design Flow
Schema Design for both User and Device Profile
...
| Code Block | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
#Schema of Device Profile
TABLE device_db.device_profile (
device_id text,
avg_ts double,
city text,
country text,
country_code text,
device_spec map<text, text>,
district_custom text,
first_access timestamp,
last_access timestamp,
state text,
state_code text,
state_code_custom text,
state_custom text,
total_launches bigint,
total_ts double,
uaspec map<text, text>,
updated_date timestamp,
last_updated_by text,
products_list list<text>,
user_count bigint,
producer_id text,
producer_ver text,
fcm_token text,
PRIMARY KEY (device_id)
);
|
Computation Table:
Field | Type | Profile | Description | Source | Release |
|---|---|---|---|---|---|
device_id | String | Device | Unique id of device | ||
total_ts | Double | Device | Total Time spent on device | Device profile updater data product | |
total_launches | Long | Device | Number app/portal launches from the device | Device profile updater data product | |
avg_ts | Double | Device | Average time spent on device | Device profile updater data product | |
first_access | Timestamp | Device | Timestamp on which device was first accessed | Device profile updater data product | |
last_access | Timestamp | Device | Timestamp on which device was last accessed | Device profile updater data product | |
state | String | Device | State from which device is accessed | Device Register API | |
state_code | String | Device | Device state code | Device Register API | |
state_code_custom | String | Device | Device custom state code | Device Register API | |
state_custom | String | Device | Device custom state name | Device Register API | |
city | String | Device | City from which device is accessed | Device Register API | |
country | String | Device | Country from which device is accessed | Device Register API | |
country_code | String | Device | Device country code | Device Register API | |
district_custom | String | Device | Device custom district name | Device Register API | |
device_spec | Map<String,String> | Device | Device Specification | Device Register API | |
uaspec | Map<String,String> | Device | Device user agent data | Device Register API | |
fcm_token | String | Device | FCM token for app devices | Device Register API | |
producer_id | String | Device | Producer id for which device belongs to(App/Portal) | Device Register API | |
producer_ver | String | Device | Version of producer | Device profile updater data product | |
user_ids | List<String> | User | List of unique users accessed from the device | Device profile updater data product | |
user_count | Long | Device | Unique users accessed from the device | Device profile updater data product | |
last_updated_by | String | Device | Source from which record was last updated | Source which is updating the record | |
products_list | List<String> | Device | List of sources from which record gets updated | From all the sources | |
user_id | String | User | Unique id for user | ||
user_type | String | User | Type of user(Anonymous/SelfSignedIn/ValidatedUser) | Samza job | |
user_role | String | User | Role of user(Student/Teacher) | Samza job | |
sign_in_type | String | User | Type of sign-in(GoogleAuth/SingleSignIn) | Samza job | |
user_state | String | User | State name of the User | ||
user_district | String | User | District name of the User | ||
user_block | String | User | Block name of the User | ||
first_access | Timestamp | User | Timestamp on which user was first seen | User Profile Updater data product | |
last_access | Timestamp | User | Timestamp on which user was last seen | User Profile Updater data product | |
last_device_accessed | String | User | Device id on which user was last seen | User Profile Updater data product | |
device_ids | List<String> | User | List of unique devices on which user was seen | User Profile Updater data product | |
last_updated_by | String | User | Source from which record was last updated | Source which is updating the record | |
products_list | List<String> | User | List of sources from which record gets updated | From all the sources | |
last_content_played | String | User | Last content played by user | User Profile Updater data product | |
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.