Advanced Analytics: Data Schema

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.

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”

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

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.

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

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.

Id

id

Integer

Unique ID of the location.

Location name

name

String

Name of the location

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

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

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

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

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.

Id

id

Integer

Unique ID of the survey occurrence

Id

survey_id

Integer

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

Issued at

issued_at

Timestamp

The precise time of issuance of the survey

Deadline

deadline

Date

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

Attempts issued

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

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

response_rate

Integer

The response rate given as a percentage.

Id

id

Integer

Unique ID of the survey occurrence

Id

user_id

Integer

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

Id

survey_id

Integer

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

Deadline

deadline

Date

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

Submitted at

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

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

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

survey_type

String

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

Observation subject id

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

completed

Boolean

Returns true if the survey attempt has been submitted.

Started at

started_at

Timestamp

Returns the specific time that the attempt was started.

Time taken

time_taken

Integer

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

Survey occurrence id

survey_occurrence_id

Integer

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

Id

id

Integer

Unique ID of the survey occurrence

Id

user_id

Integer

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

Id

survey_attempt_id

Integer

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

Survey id

survey_id

Integer

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

Question

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

question_type

String

The type of question this answer relates to.

Answer

answer

String

The string representation of the answer, where applicable

Value

value

Integer

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

Deadline

deadline

Date

The deadline for this particular survey attempt/occurrence.

Submitted at

submitted_at

Timestamp

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

Survey Occurrence id

survey_occurrence_id

Integer

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

Id

id

Integer

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

Id

user_id

Integer

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

Happened at

happened_at

Timestamp

Timestamp (UTC) for when the activity occurred

Completed

completed

Boolean

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

Result

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

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

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

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

type

String

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

Content id

content_id

Integer

Unique id of the content this activity relates to.

Verb

verb

String

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

Expired

expired

Boolean

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

Id

id

Integer

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

Id

user_id

Integer

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

Happened at

happened_at

Timestamp

Timestamp (UTC) for when the activity occurred

Completed

completed

Boolean

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

Result

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

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

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

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

type

String

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

Id

content_id

Integer

Unique id of the content this activity relates to.

Expired

expired

Boolean

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

Id

id

Integer

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

Id

user_id

Integer

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

Happened at

happened_at

Timestamp

Timestamp (UTC) for when the activity occurred

Expired at

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

type

String

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

Id

content_id

Integer

Unique id of the content this activity relates to.

Expired

expired

Boolean

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

Id

id

Integer

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

Id

user_id

Integer

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

Happened at

happened_at

Timestamp

Timestamp (UTC) for when the activity occurred

Expired at

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

type

String

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

Id

content_id

Integer

Unique id of the content this activity relates to.

Expired

expired

Boolean

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

Id

user_id

Integer

User id of the user who was assigned the task. (Foreign id reference to users table)

Id

id

Integer

Unique ID of the task

Deadline

deadline

Date

Deadline for when the content must be completed by.

Completed at

completed_at

Datetime

Timestamp (in UTC) for when the user completed the content, triggering the task to be marked as completed. If the task has not yet been completed, this will be null.

Mandatory

mandatory

Booolean

True/false flag denoting whether the task was marked as mandatory.

Expired at

expired_at

Timestamp

Timestamp (in UTC) for when the task was marked as expired. When a recurring task is issued, any previous tasks in the series will be marked as expired.

Assigned at

assigned_at

Timestamp

Timestamp (in UTC) for when the task was issued to the user.

Archived

archived

Boolean

True/false flag denoting that the task has been archived. A task will be archived automatically if the content that it relates to gets archived. This is because the user can no longer access the content once it has been archived.

Type

type

String

Type of content this task is for. See type description for the content table.

Id

content_id

Integer

Unique id of the content this task is for.

Expired

expired

Boolean

True/false flag denoting whether this task has been expired. See expired_at.

Completed

completed

Boolean

True/false flag denoting whether this task has been completed. See completed_at.

Overdue

overdue

Boolean

True/false flag denoting whether the user has not yet completed the task and is past the deadline. Note that overdue relates to today’s date. If a user completed the task after the deadline (ie. it was overdue at the time of completion) this flag will show false, since the task is no longer overdue today.

Id

id

Integer

Unique ID of the event session

Event id

event_id

Integer

Event id of the event this session belongs to. (Foreign id reference to events table)

Starts at

starts_at

Timestamp

Date/time (stored in UTC) for when the event session begins

Ends at

ends_at

Timestamp

Date/time (stored in UTC) for when the event session ends

Location type

location_type

String

Type of location, e.g. Website URL, Map location, MS Teams, Zoom etc.

Attendance type

attendance_type

String

How the user’s attendance is marked, e.g. Marked automatically, Marked by attendee, Marked by admin, Marked by host.

Id

host_id

Integer

User ID of the user who is designated as the Host for the event session. See id in users table.

Event name

name

String

Name of the event. Will be identical to name in the events table for the given event_id

Host name

host_name

String

Full name of the host. Will be identical to full_name in the users table for the given host_id.

Id

content_id

Integer

Foreign ID reference to the content table for this event.

Id

user_id

Integer

User ID of the user who is enrolled in the event session.

Id

id

Integer

Unique ID of the enrollment

Id

event_session_id

Integer

ID of the event session for this enrollement.

Status

status

String

The status of the enrollment. Will be one of: pending, approved, rejected, unenrolled

Attendance

attendance

String

Denotes whether or not the user attended the event session or not, if available. Will be one of: not yet recorded, attended, did not attend.

Host

host

Boolean

True/false flag denoting if this is the host’s enrollment record. When a user is made the host of an event session, a host enrollment is automatically created for them, since they will likely attend the event session for which they are the host.

Enrolled at

enrolled_at

Date

The date a user was enrolled in an event session.

Id

id

Integer

Unique ID of the skill/topic

Skill name

name

String

Name of the skill/topic. If the skill has multiple translations, this field will contain English or the first available translation of the name.

Id

id

Integer

Unique ID of the skill/topic category

Skill category name

name

String

Name of the skill/topic category. If the category has multiple translations, this field will contain English or the first available translation of the name.

Id

skill_category_id

Integer

Skill Category ID, foreign id reference to skill_categories table

Id

id

Integer

Unique ID of the skill/topic category

Id

skill_id

Integer

Skill ID, foreign id reference to skills table

Id

content_id

Integer

ID of the content, see type_with_id in the content table

Id

skill_id

Integer

Skill ID, foreign id reference to skills table

Id

user_id

Integer

User ID of the user who has expressed interest in or been rated for the skill.

Id

id

Integer

Unique id of this user_skill record.

Id

skill_id

Integer

Skill ID, foreign id reference to skills table

Own rating

own_rating

Integer

Rating that the user has given themselves in the skill. Rating range will depend on how your skill ratings have been configured in your company.

Target rating

target_rating

Integer

Target rating that the user or their manager has set for this user to achieve in this skill

Manager rating

manager_rating

Integer

Rating that the user’s manager has given them in the skill.

Preferred rating

preferred_rating

Integer

If present this will be the manager_rating value. If not present, this will be the own_rating value.

Id

id

Integer

Unique id for the session. Note this is not the browser session ID value.

Id

user_id

Integer

ID of the user who this session relates to.

Duration

duration

Integer

Length of the session in seconds

Starts at

starts_at

Timestamp

Timestamp (in UTC) for when this session started.

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

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

1-to-1 Id

id

bigint

Unique identifier for the one-to-one meeting. Primary key.

1-to-1 Cycle name

cycle_name

varchar(255)

Name of the cycle to which this one-to-one belongs.

1-to-1 Created at

created_at

timestamp

Timestamp when the one-to-one was created.

Updated at

updated_at

timestamp

Timestamp when the one-to-one was last updated.

1-to-1 Deadline

deadline

date

Deadline for the one-to-one.

Finalized at

finalized_at

timestamp

Timestamp when the one-to-one was finalized.

1-to-1 Name

name

varchar(255)

Name of the one-to-one.

1-to-1 Description

description

varchar(2000)

Description of the one-to-one.

1-to-1 Reviewer name

reviewer_name

varchar(255)

Name of the reviewer.

1-to-1 Reviewee name

reviewee_name

varchar(255)

Name of the reviewee.

Reviewee Total Time

reviewee_total_time

integer

Total time spent by the reviewee.

Reviewer Total Time

reviewer_total_time

integer

Total time spent by the reviewer.

Time To Finalize

time_to_finalize

integer

Time taken to finalize the one-to-one.

1-to-1 Status

status

varchar(255)

Status of the one-to-one. Cannot be null.

1-to-1 attempt Id

id

bigint

Unique identifier for the attempt. Primary key.

1-to-1 attempt User is reviewee

user_is_reviewee

boolean

Indicates if the user is the reviewee in this attempt.

1-to-1 Question

question

varchar(255)

Question being answered.

1-to-1 Value

value

integer

Value of the answer.

1-to-1 Answer

answer

varchar(2000)

Text of the answer.

1-to-1 Question type label

question_type_label

varchar(255)

Label indicating the type of question.

Objective id

id

Integer

Unique identifier for the objective.

Objective name

name

Varchar(255)

Name of the objective. This corresponds to the information entered in the field: “What is the overall objective?”

Objective level

level

Varchar(64)

Level assigned to the objective. Will be one of: Self-development, Individual, Team, Company.

Objective set for (user id)

assigned_to_id

Integer

Id of the user the objective has been assigned to (“owner” of the objective).

Objective team id

team_id

Integer

Id of the team the objective has been assigned to.

Objective set by (user id)

assigned_by_id

Integer

Id of the user who has created the objective and assigned it to a user.

Objective start date

start_date

Date

The start date the objective has been set for.

Objective end date

end_date

Date

The end date the objective has been set for.

Objective progress

current_progress

Integer

The progress against the objective given as a percentage.

Objective completion status

completion_status

Varchar(64)

The completion status of the objective. Will be one of: Not started, In Progress, Completed.

Objective parent objective id

parent_objective_id

Integer

Id of the parent objective (for objectives placed under other objectives via the “Align to other objective” feature).

Objectives archived

archived

Boolean

True/false flag denoting that the objective has been archived.

Objectives private

private

Boolean

True/false flag denoting that an objective has been made Private (only visible to the owner of the objective and key results. Their manager, account owner and admins can view the objective via the user's profile. No other users can see the objective).

Key result id

id

Integer

Unique identifier for the key result.

Objective id

objective_id

Integer

Unique identifier for the objective the key result belongs to.

Key result name

name

Varchar(4000)

Name of the key result. This corresponds to the information entered in the field: “Key result description”

Key result completion type

completion_type

Varchar(64)

Type of completion required for the key result - one of ‘self_certify’, ‘pass_score’ or ‘maintain’

Key result progress

progress

integer

Progress against the key result expressed as a percentage

Key result status

status

Varchar(64)

Status of the key result - one of ‘not_started’, ‘on_track’, ‘behind’, ‘at_risk’

Key result owner id

owner_id

Integer

Unique ID of the owning user.

Key result contribution to objective progress

contribution_to_objective_progress

Boolean

Whether or not the key result contributes to the objective’s progress.

Key result target from value

target_from_value

Numeric(14,2)

The “from” value of the target range, expressed as a decimal

Key result target to value

target_to_value

Numeric(14,2)

The “to” value of the target range, expressed as a decimal

Key result unit

unit

Varchar(100)

The unit for the target values and result

Key result value

result

Numeric(14,2)

The actual result of the key result