Advanced Analytics: Data Schema
Here you can find all the data points currently available in our BI tool. You can use these data points to create your own insights and reports.
- 1 People
- 1.1 users
- 1.2 custom fields
- 1.3 groups
- 1.4 group_users
- 1.5 teams
- 1.6 team_users
- 1.7 locations
- 1.8 managers
- 1.9 coaches
- 2 Content
- 2.1 content
- 2.2 quizzes
- 2.3 quiz_attempts
- 2.4 quiz_attempt_answers
- 2.5 surveys
- 2.6 survey_occurrences
- 2.7 survey_attempts
- 2.8 survey_attempt_answers
- 2.9 event_sessions
- 2.10 enrollments
- 2.11 tags
- 2.12 skills (a.k.a Topics)
- 2.13 skill_categories (a.k.a Topic Categories)
- 2.14 skill_category_skills
- 2.15 content_skills
- 2.16 user_skills
- 3 Activity
- 3.1 activities
- 3.2 completions
- 3.3 starts
- 3.4 views
- 3.5 session_tracks
- 4 Compliance
- 4.1 tasks
- 5 Development
- 5.1 one_to_ones
- 5.2 one_to_one_attempts
- 5.3 one_to_one_attempt_answers
- 5.4 objectives
- 5.5 key_results
We are continuously adding new tables and data points.
Please be aware that Advanced Analytics includes data linked to deactivated users and archived content and events, so remember to filter those out if appropriate.
People
users
The users table contains details of each user within the company, including deactivated, and not yet active users.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | The unique user identifier |
Sign in count | sign_in_count | Integer | Count of the total number of times the user has logged in all-time |
Current sign in at | current_sign_in_at | Timestamp | Timestamp (UTC) of the user’s most recent log in |
Created at | created_at | Timestamp | Timestamp (UTC) when the user was originally created |
First name | first_name | String | First name of the user. (aka given name) |
Last name | last_name | String | Last name of the user. (aka surname) |
Job title | job_title | String | Job title of the user. (e.g. Marketing Assistant). Can be blank. |
Language | language | String | Primary (selected) language for the user in ISO 653-1 format |
Manager | manager | Boolean | True/false flag to identify whether the user is a manager. Includes both primary team managers and override managers. |
Time zone | time_zone | String | Time zone string for the users selected time zone (if any). |
Id | coach_id | Integer | User id of the user’s coach (if any). |
Is coach | is_coach | Boolean | True/false flag to identify if the user is designated as a coach |
Id | manager_id | Integer | User is of the user’s override manager, or primary team manager if user has no override manager set. |
Secondary manager | secondary_manager | Boolean | True/false flag to identify if the user is the secondary manager of any team. |
Hire date | hire_date | Date | Hire date of the employee. Only set if this data is available from an HR system integration or similar. |
Is override manager | is_override_manager | Boolean | True/false false to identify if the user is designated as the override manager of another user. |
Deactivated at | deactivated_at | Timestamp | Timestamp (UTC) for when the user was deactivated |
Deactivated | deactivated | Boolean | True/false flag it identify whether or not the user have been deactivated. |
System role | system_role | String | System role of the user on Learn Amp. |
Location id | location_id | Integer | ID for the user’s primary location. See id in locations table. |
Tenure in days | tenure_in_days | Integer | Total length of the user’s tenure in days (Today or Leave Date minus their Hire Date, in days). Will be null if Hire Date has not been set. |
Leave date | leave_date | Date | Employment termination / leave date, if available/applicable. |
custom fields
The custom_fields table contains each custom field value for each user.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique id of the user’s custom field. Can be ignored |
Custom field id | custom_field_id | Integer | ID of custom field. This value will be repeated for each occurrence of this custom field |
Custom field name | name | String | name of the custom field, e.g. “Employee Number” |
Value | value | String | value of the custom field for this particular user, e.g. “ABC123” |
groups
The groups table contains basic metadata of all groups. A group is a collection of users, defined by a set of filtering criteria. Users are added to groups automatically by the system, and are refreshed on a regular basis.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique ID of the group. |
Created at | created_at | Timestamp | Timestamp (UTC) for when the group was first created. |
Group name | name | String | Name of the Group |
group_users
The group_users table is a link table, associating users with groups. This table defines who belongs to which groups.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | group_id | integer | ID of the group, see id in groups table |
Id | id | Integer | Unique ID of the group_user record. |
Id | user_id | integer | ID of the user, see id in users table |
Created at | created_at | Timestamp | Timestamp (UTC) for when the given user was added to the given group. |
teams
The teams table contains basic metadata of all teams. A team may have a manager and secondary managers. Users may belong to a primary, and many secondary teams.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique ID of the team. |
Team name | name | String | Name of the team |
Created at | created_at | Timestamp | Timestamp (UTC) for when the team was first created. |
Id | manager_id | Integer | User id of the manager of the team. See id in users table |
Parent team id | parent_team_id | Integer | ID of this team’s parent team. Teams may exist within a hierachy, and have parent and child teams. |
Team users count | team_users_count | Integer | Count of users belonging directly to this team. (Excludes managers) |
Team users caches count | team_user_caches_count | Integer | Count of users belonging directly to this team AND any of the child teams beneath this team in the hierarchy. (Excludes team managers). |
Manager name | manager_name | String | Full name of the team’s manager. This will be identical to full_name in users table, for the given manager_id |
team_users
The team_users table is a link table, associating users with teams. This table defines who belongs to which teams.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | team_id | integer | ID of the team, see id in teams table |
Id | id | Integer | Unique ID of the team_user record. |
Id | user_id | integer | ID of the user, see id in users table |
Created at | created_at | Timestamp | Timestamp (UTC) for when the given user was added to the given team. |
Primary flag | primary_flag
| Boolean | True/false flag to denote whether this is the user’s primary team. |
locations
The locations table contains basic metadata of all locations.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique ID of the location. |
Location name | name | String | Name of the location |
managers
The managers table contains basic meta data for a given manager. This table includes both override managers and team managers. The full details of each user are stored in users table, but this table is useful for reporting by manager.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | User ID of this manager. Will match the id in the users table |
First name | first_name | String | First name of the manager |
Last name | last_name | String | Last name of the manager |
Manager full name | full_name | String | Full name of the manager |
coaches
The coaches table contains basic meta data for a given coach. The full details of each user are stored in users table, but this table is useful for reporting by coach.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | User ID of this coach. Will match the id in the users table |
First name | first_name | String | First name of the coach |
Last name | last_name | String | Last name of the coach |
Coach full name | full_name | String | Full name of the coach |
Content
content
The content table contains basic metadata for all types of content referenced by activities and tasks. Content may belong directly to the company or is shared with the company from a library account. Please note archived content is included in the data.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Created at | created_at | Timestamp | Timestamp (UTC) when the content was first created |
Content type | content_type | String | Type of the content on the Learn Amp platform. Since Items has multiple types, this field may be different from the type field described below. Example content types: video, article, book etc. |
Content name | name | String | Name (or title) of the piece of content. If the content has a title saved in multiple languages, the name stored here will be the first available translation - beginning with English, then English-USA, and so on. |
Type with id | type_with_id | String | Concatenation of the content’s primary id with it’s type, in the form: item-123 |
Type | type | String | The entity type of the content on Learn Amp. Will be one of: Item, Channel, Learnlist, Event, Quiz, Survey |
quizzes
The quizzes table contains basic metadata of all quizzes. Quizzes may include those belonging directly to the company, and those shared from a library account. quizzes is a subset of the content table, for reporting/listing only quizzes. Please note archived quizzes are included in the data.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique ID of the quiz. |
Created at | created_at | Timestamp | Timestamp (UTC) for when the quiz was first created. |
Name | name | String | Name of the quiz |
Archived | archived | Boolean | True/false flag denoting that the quiz has been archived. |
quiz_attempts
Data point name in BI tool | Field Name | Data Type | Description |
|---|---|---|---|
Id | id | bigint (not null) | Unique ID of the quiz attempt |
Id | quiz_id | bigint (not null) | Foreign key referencing the quizzes table |
Id | user_id | bigint (not null) | Foreign key referencing the users table |
Correct answer count | correct_answer_count | integer | Number of correct answers in the quiz attempt |
Correct answer count | incorrect_answer_count | integer | Number of incorrect answers in the quiz attempt |
Submitted at | submitted_at | timestamp | Timestamp indicating when the quiz attempt was submitted |
Score | score | integer | Overall score achieved in the quiz attempt |
Passed | passed | boolean | Indicates whether the user passed the quiz |
Expired at | expired_at | timestamp | Timestamp indicating when the quiz attempt expired |
Expired | expired | boolean | Indicates whether the quiz attempt has expired |
Most recent | most_recent | boolean | Indicates whether the attempt is the most recent one |
quiz_attempt_answers
The "quiz_attempt_answers" table is designed to store information about individual answers submitted by users during quiz attempts.
Data point name in BI tool | Field Name | Data Type | Description |
|---|---|---|---|
Question id | question_id | bigint (not null) | Unique ID of the question in the quiz |
Correct | correct | boolean | Indicates whether the submitted answer is correct |
Submitted at | submitted_at | timestamp | Timestamp indicating when the answer was submitted |
Id | quiz_attempt_id | bigint (not null) | Foreign key referencing the quiz_attempts table |
Id | quiz_id | bigint (not null) | Foreign key referencing the quizzes table |
Id | user_id | bigint (not null) | Foreign key referencing the users table |
Most recent | most_recent | boolean | Indicates whether the answer is the most recent one |
Question | question | varchar(255) | Text of the question being answered |
Question type | question_type | varchar(255) | Type of the question (e.g., multiple choice, essay) |
Answer | answer | varchar(2000) | Text of the submitted answer |
surveys
The surveys table contains basic metadata of all surveys. Surveys may include those belonging directly to the company, and those shared from a library account. surveys is a subset of the content table, for reporting/listing only surveys. Please note archived surveys are included in the data.
Data point name in BI tool | Field name | Data type | Description |
|---|---|---|---|
Id | id | Integer | Unique ID of the survey. |
Created at | created_at | Timestamp | Timestamp (UTC) for when the survey was first created. |
Survey name | name | String | Name of the survey |
Survey type | survey_type | String | Returns the type of survey. |
survey_occurrences
The survey_occurrences table contains specific point in time issuances of surveys. Metadata around attempts (and therefore responses) is denormalized into this table for convenience.
Data point name in BI tool | Field name | Data type |
|---|