BI Tool: Data Schema


users

The users table contains details of each user within the company, including deactivated, and not yet active users.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

The unique user identifier

sign_in_count

Integer

Count of the total number of times the user has logged in all-time

current_sign_in_at

Timestamp

Timestamp (UTC) of the user’s most recent log in

created_at

Timestamp

Timestamp (UTC) when the user was originally created

first_name

String

First name of the user. (aka given name)

last_name

String

Last name of the user. (aka surname)

job_title

String

Job title of the user. (e.g. Marketing Assistant). Can be blank.

language

String

Primary (selected) language for the user in ISO 653-1 format

manager

Boolean

True/false flag to identify whether the user is a manager. Includes both primary team managers and override managers.

time_zone

String

Time zone string for the users selected time zone (if any).

coach_id

Integer

User id of the user’s coach (if any).

is_coach

Boolean

True/false flag to identify if the user is designated as a coach

manager_id

Integer

User is of the user’s override manager, or primary team manager if user has no override manager set.

secondary_manager

Boolean

True/false flag to identify if the user is the secondary manager of any team.

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

Boolean

True/false false to identify if the user is designated as the override manager of another user.

deactivated_at

Timestamp

Timestamp (UTC) for when the user was deactivated

deactivated

Boolean

True/false flag it identify whether or not the user have been deactivated.

system_role

String

System role of the user on Learn Amp.

location_id

Integer

ID for the user’s primary location. See id in locations table.

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

Date

Employment termination / leave date, if available/applicable.

custom fields

The custom_fields table contains each custom field value for each user.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique id of the user’s custom field. Can be ignored

user_id

Integer

ID of the user that the custom field relates to

custom_field_id

Integer

ID of custom field. This value will be repeated for each occurrence of this custom field

name

String

name of the custom field, e.g. “Employee Number”

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the group.

created_at

Timestamp

Timestamp (UTC) for when the group was first created.

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.

Field name

Data type

Description

Field name

Data type

Description

group_id

integer

ID of the group, see id in groups table

id

Integer

Unique ID of the group_user record.

user_id

integer

ID of the user, see id in users table

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the team.

name

String

Name of the team

created_at

Timestamp

Timestamp (UTC) for when the team was first created.

manager_id

Integer

User id of the manager of the team. See id in users table

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

Integer

Count of users belonging directly to this team. (Excludes managers)

team_user_caches_count

Integer

Count of users belonging directly to this team AND any of the child teams beneath this team in the hieracy. (Excludes team managers).

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.

Field name

Data type

Description

Field name

Data type

Description

team_id

integer

ID of the team, see id in teams table

id

Integer

Unique ID of the team_user record.

user_id

integer

ID of the user, see id in users table

created_at

Timestamp

Timestamp (UTC) for when the given user was added to the given team.

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the location.

name

String

Name of the location

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.

Field name

Data type

Description

Field name

Data type

Description

created_at

Timestamp

Timestamp (UTC) when the content was first created

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.

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

String

Concatenation of the content’s primary id with it’s type, in the form: item-123

type

String

The entity type of the content on Learn Amp. Will be one of: Item, Channel, Learnlist, Event, Quiz, Survey

channels

The channels table contains basic metadata of all channels. Channels may include those belonging directly to the company, and those shared from a library account. channels is a subset of the content table, for reporting/listing only channels. Please note archived content is included in the data.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the channel.

created_at

Timestamp

Timestamp (UTC) for when the channel was first created.

name

String

Name of the channel

events

This events table contains basic metadata of all events. events is a subset of the content table, for reporting/listing only events. Please note archived events are included in the data.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the event.

created_at

Timestamp

Timestamp (UTC) for when the event was first created.

name

String

Name of the event

items

The items table contains basic metadata of all items. items is a subset of the content table, for reporting/listing only items. In Learn Amp, an item is a single learning object, such as a SCORM package, a downloable document, a video etc. Please note archived content is included in the data.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the item.

guid

String

Globally unique identifier. Not used. Please ingore.

source_id

String

If the item came from an external integration or import of content from another system, the source_id may be used to store the unique identifier of the item in that system

source_type

String

If the item can from an external integration or import of content, source_type may be used to store a label/name of that system.

created_at

Timestamp

Timestamp (UTC) for when the item was first created.

name

String

Name of the item

learnlists

The learnlists table contains basic metadata of all learnlists. Learnlists may include those belonging directly to the company, and those shared from a library account. learnlists is a subset of the content table, for reporting/listing only learnlists. Please note archived content is included in the data.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the learnlist.

created_at

Timestamp

Timestamp (UTC) for when the learnlist was first created.

name

String

Name of the learnlist

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the quiz.

created_at

Timestamp

Timestamp (UTC) for when the quiz was first created.

name

String

Name of the quiz

quiz_attempts

Field Name

Data Type

Description

Field Name

Data Type

Description

id

bigint (not null)

Unique ID of the quiz attempt

quiz_id

bigint (not null)

Foreign key referencing the quizzes table

user_id

bigint (not null)

Foreign key referencing the users table

correct_answer_count

integer

Number of correct answers in the quiz attempt

incorrect_answer_count

integer

Number of incorrect answers in the quiz attempt

submitted_at

timestamp

Timestamp indicating when the quiz attempt was submitted

score

integer

Overall score achieved in the quiz attempt

passed

boolean

Indicates whether the user passed the quiz

expired_at

timestamp

Timestamp indicating when the quiz attempt expired

expired

boolean

Indicates whether the quiz attempt has expired

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.

Field Name

Data Type

Description

Field Name

Data Type

Description

question_id

bigint (not null)

Unique ID of the question in the quiz

correct

boolean

Indicates whether the submitted answer is correct

submitted_at

timestamp

Timestamp indicating when the answer was submitted

quiz_attempt_id

bigint (not null)

Foreign key referencing the quiz_attempts table

quiz_id

bigint (not null)

Foreign key referencing the quizzes table

user_id

bigint (not null)

Foreign key referencing the users table

most_recent

boolean

Indicates whether the answer is the most recent one

question

varchar(255)

Text of the question being answered

question_type

varchar(255)

Type of the question (e.g., multiple choice, essay)

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the survey.

created_at

Timestamp

Timestamp (UTC) for when the survey was first created.

name

String

Name of the survey

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.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the survey occurrence

survey_id

Integer

ID of the survey that was issued. (Foreign ID reference to surveys table)

issued_at

Timestamp

The precise time of issuance of the survey

deadline

Date

The date that this survey occurrence is, or was due by

attempts_issued

Integer

A count of attempts that were created as a result of the survey being issued - indicating the number of users the survey was issued to.

attempts_completed

Integer

A count of the attempts relating to this survey that have been submitted - the number of users who have responded.

response_rate

Integer

The response rate given as a percentage.

survey_attempts

The survey_attempts table holds top level information about survey attempts. Attempts model the user’s attempt at a given Survey as relating to a specific occurrence thereof. The attempt is created prior to the user submitting any answers, so gives information about unsubmitted user attempts also.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the survey occurrence

user_id

Integer

ID of the user that the attempt belongs to. (Foreign ID reference to users table)

survey_id

Integer

ID of the survey that was issued. (Foreign ID reference to surveys table)

deadline

Date

The date that this survey occurrence is, or was due by

submitted_at

Timestamp

The specific date and time that this attempt was submitted at. This will be null if the user is yet to submit the attempt.

currently_overdue

Boolean

Returns whether the attempt is currently overdue. This will only be true if the attempt is unsubmitted and past the deadline.

overdue_when_submitted

Boolean

Returns whether, at the time of submission (submitted_at) the attempt was overdue. This will return false prior to submission.

survey_type

String

Returns the type of survey the attempt was for, denormalized from surveys

observation_subject_id

Integer

If the survey is an observation of another user, this returns the ID of htis user (Foreign id reference to users table)

completed

Boolean

Returns true if the survey attempt has been submitted.

started_at

Timestamp

Returns the specific time that the attempt was started.

time_taken

Integer

Returns the number of seconds taken between starting and submitting the attempt.

survey_occurrence_id

Integer

ID of the survey occurrence this attempt relates to. (Foreign id reference to survey_occurrences table)

survey_attempt_answers

The survey_attempt_answers table contains one record per answer as relates to a user’s survey attmempt. This table contains the question information denormalized for convenience. In the case of multi-dimensional questions, or Polls, the question will be given in the form “Question - poll option”. Weightings & values are given by the value column, whilst the answer always contains the text representation if relevant.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique ID of the survey occurrence

user_id

Integer

ID of the user that gave the answer (Foreign ID reference to users table)

survey_attempt_id

Integer

ID of the survey attempt that this answer belongs to (Foreign id reference to survey_attempts table).

survey_id

Integer

ID of the survey that was issued. (Foreign ID reference to surveys table)

question

String

The question text. In the case of multi-dimensional questions, or Polls, the question will be given in the form “Question - poll option”

question_type

String

The type of question this answer relates to.

answer

String

The string representation of the answer, where applicable

value

Integer

The integer representation of the answer, where applicable (eg weighting or sliding scale type questions)

deadline

Date

The deadline for this particular survey attempt/occurrence.

submitted_at

Timestamp

Returns the specific time that the answer and its parent attempt were submitted.

survey_occurrence_id

Integer

ID of the survey occurrence this attempt relates to. (Foreign id reference to survey_occurrences table)

activities

The activities table contains all the activity logged by users within the company. For reporting on content completion, we recommend using the completions table, described below. Likewise views and starts have their own table. Activities is the all encompassing table, which is used to generate the Activity Log on the Learn Amp platform.

Field name

Data type

Description

Field name

Data type

Description

id

Integer

Unique id of the activity record. For most use cases this can be ignored

user_id

Integer

User id of the user who performed the activity. (Foreign id reference to users table)

happened_at

Timestamp

Timestamp (UTC) for when the activity occurred

completed

Boolean

True/false flag for whether this activity was a “content was completed” activity.

result

String

Result value reported by SCORM (or similar) elearning package. Typically this will be PASS, FAIL, FAILED, UNKNOWN, COMPELETED. Most activity will have a null value for result.

score

Integer

Score value reported by SCORM (or similar) elearning package. Generally this is a percentage value that the user achieved. Most activity will have a null value for score.

total_time

Integer

Total time in seconds that the user took to complete the content, as reported by SCORM (or similar) elearning package. Most activity will have a null value for total_time.

expired_at

Timestamp

Timestamp (UTC) for when the activity was marked as expired. Learn Amp will expire, for example, the previous year’s activity when a user has a new recurring task issued to them.

type

String

Type of content this activity relates to. See type description for the content table.

content_id

Integer

Unique id of the content this activity relates to.

verb

String

Verb (label) for this activity. For example: completed, started, failed etc.

expired

Boolean

True/false flag to indicate whether this activity has been marked as expired or not. See expired_at description.

completions

The completions table contains all the content completed activity logged by users within the company. This is a subset of the activities table, and is recommended to use for completion reporting.

Field name