Data Tables and Types

Data Tables

Currently, all data in Sensors Analytics is mapped to two tables: events and users. You can complete all queries by using these two tables in SQL. We also support mapping all sessions created by customers to tables named Sessions_${Session_name}. The following listed fields are special fields. Other attributes with "$" and not listed are sensors data's pre-set attributes. The meanings of these attributes can be referenced in the document Preset Events and Attributes. Attributes without "$" are custom properties. The meanings of these attributes need to be confirmed with the corresponding buried point personnel.

Event Table(events)

The event table contains detailed information for all events (excluding virtual events). Each row in this table represents an event tracked. Fields in the event table can be divided into two categories: special fields and the properties of the actual events.

Field	Description	Example
event	The event name.	BuyGold
user_id	The internal ID assigned by Sensors Analytics to this user, associated with the id field in the user table.	1234
distinct_id	The original ID of the user, passed in during tracking and may be an anonymous ID or login ID.	wahaha
date	The date when the event occurred. This is a special field and does not need to be uploaded when sending data.	2015-09-21
time	The exact time when the event occurred.	2015-09-21 11:11:11
$receive_time	The specific timestamp when the event was received by the server. This field can be displayed in custom queries. In the frontend analysis module, $receive_time cannot be used to analyze data because it is not bound to any event by default.	1570230586048

It should be noted that the user_id field in the event table is not the distinct_id passed in when track is called, but the internal ID assigned to the user by Sensors Analytics. The specific mechanism can be found in the 2021-10-19_10-11-23_.identify users v1.17.

User table (users)

Each row of the user table represents a User. Similar to the event table, the fields in the user table are divided into special fields and other Profile fields of the User. The description of special fields is as follows:

Field	Description	Example
id	The internal ID assigned to the user by Sensors Analytics, associated with the user_id field in the events table	1234567
first_id	The user's anonymous ID, associated with the distinct_id of the pre-login behavior in the events table. It should be noted that if the value of first_id for a user is equal to second_id, it means that the user is not successfully associated with an anonymous ID, which is equivalent to "unknown".	0c476090a0b2940a
second_id	The login ID of the user, associated with the distinct_id of the post-login behavior in the events table	wahaha
$update_time	The timestamp of the most recent update to the user table information for the user	1570230586048
$device_id_list	When the multiple-to-one association mechanism is enabled, a list of anonymous IDs associated with the login ID and the timestamp of the association is recorded.	1570230586048:0c476090a0b2940a；1570230591000:65A71299-7139-4B4C-9B71-23A0AC9AAF7D

Items table

Field name	Description	Example
$item_type	The type of the item table	apple
$item_id	The id of the item	123
$is_valid	Whether the item is valid or not, default is true	1
$receive_time	The receive time of the item	1575604527772
$update_time	The update time of the item, default is the write time	1575604527772

Session table

Each Session table corresponds to a Session configuration, named as Sessions_${Session_name}.

The Session table is an extension of the events table, in addition to the fields contained in the events table, it also includes Session attributes and special fields related to Session, the naming rule of Session attributes is the original attribute name plus suffix $Session, which represents the attributes of the initial event in Session. The explanations of the special fields are as follows:

Field	Description	Example
$Session_id	The unique id that identifies a Session	2036149433405577601
$Session_position	Indicates the index of an event in a session, starting with 0 and increasing sequentially. In version 1.14 and earlier, the index of the last event in the session was -1; if there was only one event in the session, the index value is -2. In version 1.15 and later, there are no special -1 and -2 index values.	0
$Session_event_duration	Session duration, indicates the time interval between two adjacent events in a session, in seconds, and the duration of the last event in the session is null.	354
$Session_duration	The time interval between the last event triggered in the session and the time when the first event was triggered in the session, in seconds.	234
$Session_depth	Session depth, indicates the number of events triggered in the session.	4
$event_id$Session	The event ID of the first event triggered in the session.	97

Because the Session table has a large calculation load, time annotations must be added for use, such as:

SELECT event, user_id, distinct_id, date FROM Sessions_default/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/

Note: Since Session table queries are time-consuming, in order to improve query efficiency, select * queries are not supported at present, and specific field names must be selected for querying.

User Segmentation/Tag Table

These tables are storage tables for segmentation/tag results in the system, and the users stored in the table are filtered out for this segmentation/tag. The table naming rules are different for different versions, as shown in the table below:

System version	Type	Table name rule	Example
<=1.13	User segmentation	segmenter_${segmenter_name}	segmenter_abc
>=1.14	User segmentation	user_group_${user_group_name}	user_group_abc
>=1.14	Tag	user_tag_${user_tag_name}	user_tag_abc

About the specific fields in the table, the explanation is as follows:

Field	Description	Example
user_id	User id	-9220214159525537212
distinct_id	associated with distinct_id in event table.	3f840957485db9a9
values	User Group/Tag Values.	1
base_time	Base time for user group/tag calculation. Added in version 1.14 and later.	1547015611000

The base_time is stored in milliseconds, so when querying, users can convert the date to milliseconds for query using the unix_timestamp_ms function as shown in the example below:

SELECT * FROM user_group_fenqun9 WHERE base_time=unix_timestamp_ms('2019-01-17 00:00:00')

Data Types

For query performance reasons, the custom query functionality has different processing methods for different data types. Some data types also have usage restrictions, which are explained below:

Number

Numerical type, which does not differentiate between floating-point and integer values. The output format is automatically converted based on whether there are decimal places.

String

String type.

Date

Note: The time field is special and can be used directly without conversion.

Date type, represented as a millisecond-level timestamp in custom queries, for example: 1442937600000.

If necessary, the EPOCH_TO_TIMESTAMP function can be used to convert it to a Timestamp type, for example:

SELECT EPOCH_TO_TIMESTAMP($signup_time / 1000) FROM users LIMIT 100;

Below is an example of a condition filter:

SELECT COUNT(*) AS cnt FROM users WHERE EPOCH_TO_TIMESTAMP($signup_time / 1000) > '2017-01-01';

Datetime

Date and time type, which, like the Date type, also uses a millisecond-level timestamp, for example 1442592138000. Conversion can also be done using the EPOCH_TO_TIMESTAMP function.

Bool

Boolean type, represented as 0/1 for False/True respectively.

List

List type, which supports filtering operations using the CONTAINS or LIKE functions in the WHERE condition. For example:

SELECT FavoriteFruits  from users where  CONTAINS('橘子', FavoriteFruits);

It can also be used by /*EXPLODE_LIST_COLUMN=${table.columnName}*/ annotation can be used to convert List-type data to multiple rows of string-type data. For example:

SELECT list_property FROM events /*EXPLODE_LIST_COLUMN=events.list_property*/