Page Comparison

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:

Image RemovedImage Added

Schema Design for

...

User and Device Profile

Code Block

language	sql
theme	RDark
borderStyle	solid
linenumbers	true

#Schema of User Profile

TABLE user_db.user_profile (
   user_id text,
   user_type text,
   user_role text,
   sign_in_type text,
   user_state text,
   user_district text,
   user_block text,
   first_access timestamp,
   last_access timestamp,
   device_ids list<text>,
   last_device_accessed text,
   last_updated_by text,
   products_list list<text>,
   last_content_played text,
   total_ts bigint,
   updated_date timestamp,
   PRIMARY KEY (userid)
);

Code Block

language	sql
theme	RDark
borderStyle	solid
linenumbers	true

#Schema of Device Profile

TABLE device_db.device_profile (
    device_id text,
    total_ts double,
    total_launches bigint,
    avg_ts double,
    first_access timestamp,
    last_access timestamp,
    device_spec map<text, text>,
    uaspec map<text, text>,
    country text,
    country_code text,
    state text,
    state_code text,
    state_code_custom text,
    state_custom text,
    city text,
    district_custom text,
    last_updated_by text,
    products_list list<text>,
    user_ids list<text>,
    user_count bigint,
    producer_id text,
    producer_ver text,
    fcm_token text,
    updated_date timestamp,
   PRIMARY KEY (device_id)
);

Computation Table:

...

Field

...

Type

...

Profile

...

Description

...

Computation Table:

Device Profile -

	Field	Type	Description	Source	Release
1	device_id	String

Device

	Unique id of device	From first source which inserts a record	1.11
2	total_ts	Double

Device

Total Time spent on device till date	Device profile updater data product	1.11
3	total_launches	Long

Device

	Number app/portal launches from the device	Device profile updater data product	1.11
4	avg_ts	Double

Device

Average time spent on device	Device profile updater data product	1.11
5	first_access	Timestamp

Device

	Timestamp on which device was first accessed	Device profile updater data product	1.11
6	last_access	Timestamp

Device

	Timestamp on which device was last accessed	Device profile updater data product	1.11
7	state	String

Device

State from which device is accessed	Device Register API	1.12
8	state_code	String	Device

Device

state code	Device Register API	1.14
9	state_code_custom	String

Device

	Device custom state code	Device Register API	1.14
10	state_custom	String	Device

Device

custom state name	Device Register API	1.14
11	city	String

Device

City from which device is accessed	Device Register API	1.12
12	country	String

Device

	Country from which device is accessed	Device Register API	1.14
13	country_code	String	Device

Device

country code	Device Register API	1.14
14	district_custom	String

Device

	Device custom district name	Device Register API	1.14
15	device_spec	Map<String,String>	Device

Device

Specification	Device Register API	1.12
16	uaspec	Map<String,String>	Device

Device

user agent data	Device Register API	1.12
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	location_updated_date	Timestamp	last location details updated date	Device Register API	TBD
20	producer_ver	String

Device

Version of producer

Device profile updater data product

20

TBD
21	user_ids	List<String>

Device

List of unique users accessed from the device

Device profile updater data product

TBD

21

22

user_count

Long

Device

Unique users accessed from the device

Device profile updater data product

TBD

22

23

last_updated_by

String

Device

Source from which record was last updated

Source which is updating the record

23products_list

TBD
24	sources	List<String>

Device

	List of sources from which record gets updated	From all the sources	TBD
25	updated_date	Timestamp	Last updated date by the

sources24

source

From all sources

1.12

User Profile -

	Field	Type	Description	Source	Release
1	user_id	String

User

Unique id

for

of the user

From first source which inserts a record

25

2.2.0
2	user_type	String

User

Type of user(Anonymous/SelfSignedIn/ValidatedUser)

User_Org Cassandra via Samza job

26

2.2.0
3	user_role	String

User

Role of user(Student/Teacher)

AUDIT events via Samza job

27

	2.2.0
4	sign_in_type	String

User

Type of sign-in(GoogleAuth/SingleSignon/

SingleSignIn)

User_Org Cassandra via Samza job

28user_stateStringUser

Login)	AUDIT events via Samza job	2.2.0
5	board	List<String>	Board the user has selected	INTERACT event via Samza job	2.3.0
6	subject	List<String>	Subject the user has selected	INTERACT event via Samza job	2.3.0
7	medium	List<String>	Medium the user has selected	INTERACT event via Samza job	2.3.0
8	grade	List<String>	Grade the user has selected	INTERACT event via Samza job	2.3.0
9	language	String	User preferred language	INTERACT event via Samza job	2.3.0
10	profile_state	String	State name of the User	User_Org Cassandra via Samza job	TBD

29

11

user

profile_district

String

User

District name of the User

User_Org Cassandra via Samza job

TBD

30

12

user

profile_block

String

User

Block name of the User

User_Org Cassandra via Samza job

TBD

31

13

first_access

Timestamp

User

Timestamp on which user was first seen

User Profile Updater data product

TBD

32

14

last_access

Timestamp

User

Timestamp on which user was last seen

User Profile Updater data product

33

TBD
15	last_device_accessed	String

User

Device id on which user was last seen

User Profile Updater data product

TBD

34

16	device_ids

List<String>

User

List

Map<String,String>

list of unique devices

on which user was seen

with device_id and producer_id

User Profile Updater data product

TBD

35

17

last_updated_by

String

User

Source from which record was last updated

Source which is updating the record

36products_list

TBD
18	sources	List<String>

User

List of sources from which record gets updated

From all the sources

TBD

37

19

last_content_played

String

User

Last content played by user

User Profile Updater data product

38

TBD
20	total_ts	Double

User

Total

overall time spent till date by user	User Profile Updater data product	TBD
21	updated_date	Timestamp	Last updated date by the source	From all sources	2.2.0
22	qr_scans	Integer	Total scans done by the user	via data product	?
23	...

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.

Versions Compared

Old Version 6

New Version Current

Key

Introduction:

Design Flow:

Schema Design for

User and Device Profile

Computation Table:

Computation Table:

Device Profile -

User Profile -

Questions: