Gem ATS data export schema

Audience

External

Displayed Description

Page Type

Article

For any teams that would like to export their data from Gem ATS, the following tables delineate the schema that you can expect within the export.

Application data

The applications table

The applications table represents a snapshot of application data. Each row represents the latest status of a single application.

Column	Description
id	The application ID. This appears as application_id on other tables.
candidate_id	The candidate ID.
coordinator	The coordinator’s name.
coordinator_id	The candidate’s coordinator’s ID.
applied_at	The date and time the candidate applied, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
status	The application’s current status and is one of active, rejected, hired, or dropped_out.
prospect	N/A
stage_id	The stage ID of the application.
source_id	The source ID for the application.
recruiter	The recruiter’s name.
recruiter_id	The candidate’s recruiter’s ID.
referrer_id	The referrer ID for the application.
rejected_at	The date and time the application was rejected, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
rejected_by_id	The user ID of the person who rejected the application, if applicable.
rejection_reason_id	The rejection reason ID for a rejected application, if applicable.
pipeline_percent	N/A
migrated	N/A
rejected_by	The name of the Gem user who rejected the application, if applicable.
stage_name	The application’s current stage name.
job_post_id	The job post ID associated with the application.
prospect_pool	N/A
prospect_pool_stage	N/A
prospect_owner_id	N/A
created_at	The date and time the application was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The last time the application was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
event_id	The event ID associated with a prospect application, if applicable.
converted_prospect_application_id	N/A
sent_demographic_questions	N/A
organization_id	Your organization’s unique ID in Gem.

The application custom fields table

The application_custom_fields table represents custom application fields associated with an application. Each row represents a single value of a single custom field for a single application.

Column	Description
application_id	The application ID.
custom_field	The custom application field name.
float_value	These are number values entered in the custom application field, if applicable.
date_value	The date entered in the custom application field, if applicable.
display_value	How the custom application field value appears on the candidate profile.
unit	The currency type entered in the custom application field, if applicable.
min_value	The maximum value of the range.
max_value	The minimum value of the range.
user_id	The user ID for custom application fields with user field types.
created_at	The date and time the custom application field was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the custom application field was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
key	The immutable field key for the custom application field.
text_value	The text value as it appears on the application’s custom application field.
organization_id	Your organization’s unique ID in Gem.

The application question answers table

The application_question_answers table represents questions and answers that candidates submitted in response to job posts with their applications. Each row represents a single job post application question and answer from a single application.

Column	Description
job_post_id	The job post ID.
application_id	The application ID.
question	The application question from the job post.
answer	The applicant’s answer to the application question.
created_at	The date the application was received, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date the application was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The application stages table

The application_stages table represents the historical activity of all stages an application has been on, as well as each stage that an application could be on, taken from the job stage setup. Each row represents a single stage that an application can be in and the timestamp that the application entered and exited the stage.

Column	Description
application_id	The application ID.
stage_id	The stage ID.
entered_on	The date the application entered the stage, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
exited_on	The date the application exited the stage, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
stage_name	The job stage name.
organization_id	Your organization’s unique ID in Gem.

The applications jobs table

The applications_jobs table represents applications for jobs. Each row represents one application to one job.

Column	Description
application_id	The application ID.
job_id	The job ID.
organization_id	Your organization’s unique ID in Gem.

Candidate data

The candidates table

The candidates table represents a snapshot of candidate data. Each row represents a single candidate. A candidate is a person.

Column	Description
id	The candidate ID. This appears as candidate_id on other tables.
first_name	The candidate’s first name.
last_name	The candidate’s last name.
preferred_name	The candidate’s preferred name.
migrated	N/A
company	The candidate’s current company, if applicable.
title	The candidate’s current title, if applicable.
created_at	The date and time the candidate was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
private	Boolean. Results are either true or false, indicating whether the candidate is private.
organization_id	Your organization’s unique ID in Gem.

The candidate custom fields table

The candidate_custom_fields table represents custom candidate fields associated with a candidate. Each row represents a single value of a single custom field for a single candidate.

Column	Description
candidate_id	The candidate ID.
custom_field	The custom candidate field.
float_value	These are numbers entered in the custom candidate field, if applicable.
date_value	The date entered in the custom candidate field, if applicable.
display_value	The value entered into the custom candidate field as it appears in Gem.
unit	The currency type entered in the custom candidate field, if applicable.
min_value	The maximum value of the range.
max_value	The minimum value of the range,
user_id	The Gem user ID entered for custom candidate fields with user field types.
created_at	The date and time the custom candidate field value was entered, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the custom candidate field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
key	The immutable field key for the custom candidate field.
text_value	The answer as it appears on the application’s custom candidate field.
organization_id	Your organization’s unique ID in Gem.

The educations table

The educations table represents all structured education fields associated with a candidate. Each row represents a single structured education for a single candidate.

Column	Description
candidate_id	The candidate ID.
school_name	The school name associated with the candidate.
degree	The degree associated with the candidate.
discipline	The school discipline associated with the candidate.
start	The date the candidate started the education, if applicable, like 2024-01-01.
end	The date the candidate ended the education, if applicable, like 2024-01-01.
latest	Boolean. Results are either true or false, indicating whether the education is the latest the candidate has experienced.
created_at	The date and time the education was associated with the candidate profile, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the education for the candidate profile was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The employments table

The employments table represents all structured employment fields associated with a candidate. Each row represents a single structured employment record for a single candidate.

Column	Description
candidate_id	The candidate ID.
company_name	The company name associated with the candidate.
title	The title associated with the candidate.
start	The date the candidate started the employment, if applicable, like2024-01-01.
end	The date the candidate ended the employment, if applicable, like2024-01-01.
latest	Boolean. Results are either true or false, indicating whether the employment is the latest the candidate has experienced.
created_at	The date and time the employment was associated with the candidate profile, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the employment for the candidate profile was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

Candidate PII data

The candidate email addresses table

The candidate_email_addresses table represents a list of email addresses by candidate. Each row represents a single email address and the associated candidate.

Column	Description
candidate_id	The candidate ID.
value	The candidate’s email address.
created_at	The date and time the email address was associated with the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the email address for the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The candidate phone numbers table

The candidate_phone_numbers table represents a list of phone numbers by candidate. Each row represents a single phone number and the associated candidate.

Column	Description
candidate_id	The candidate ID.
value	The candidate’s phone number.
created_at	The date and time the phone number was associated with the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the phone number for the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

Demographic data

The EEOC responses table

The eeoc_responses table represents responses to EEOC questions. Each row represents a single application’s responses to EEOC questions.

Column	Description
application_id	The application ID.
status	The EEOC response status and is one of responded or pending response.
race	The application response to the EEOC questions Are you Hispanic/Latino? and Please identify your race.
gender	The application response to the EEOC question Gender.
disability_status	The application response to the EEOC question Disability Status.
veteran_status	The application response to the EEOC question Veteran Status.
submitted_at	The date and time the EEOC response was submitted, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

Job data

The jobs table

The jobs table represents a list of jobs and their statuses in Gem. Each row represents the status of a single job.

Column	Description
id	The job ID.
organization_id	Your organization’s unique ID in Gem.
name	The job’s internal name.
requisition_id	The job’s requisition ID.
status	The job status and is one of open, closed, or draft.
opened_at	The date and time the job was opened, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
closed_at	The date and time the job was closed, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
department_id	The job’s department ID.
level	The job’s level.
confidential	Boolean. Results are either true or false, indicating whether the job is confidential.
created_at	The date and time the job was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
notes	These are notes entered on the job.
updated_at	The date and time the job was updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
is_template	Whether or not the associated job is a template job.

The job custom fields table

The job_custom_fields table represents custom job fields associated with a job. Each row represents a custom job field associated with a single job.

Column	Description
job_id	The job ID.
custom_field	The custom job field name as it appears in Gem.
float_value	These are numbers entered in the custom job field, if applicable.
date_value	The date entered in the custom job field, if applicable.
display_value	The value entered into the custom job field as it appears in Gem.
unit	The currency type entered in the custom job field, if applicable.
min_value	The minimum value of the range.
max_value	The maximum value of the range.
user_id	The user ID entered for custom job fields with user field types.
created_at	The date and time the custom job field value was associated with the job, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the custom job field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
key	The immutable field key for the custom job field.
text_value	The value entered on the custom job field as it appears in Gem.
organization_id	Your organization’s unique ID in Gem.

The job snapshots table

The job_snapshots table represents a daily snapshot of job activity. Each row represents job-level metrics for a single day.

Column	Description
job_id	The job ID.
date	The date of the snapshot, like 2024-01-31.
hired_count	The count of new hires in the day.
prospect_count	The count of new prospects in the day.
new_today	The count of new applications in the day.
rejected_today	The count of rejected applications in the day.
advanced_today	The count of applications advanced in the day.
interviews_today	The count of interviews in the day.
organization_id	Your organization’s unique ID in Gem.

The jobs departments table

The jobs_departments table represents a list of departments by job. Each row represents a single department on a single job.

Column	Description
id	The primary key to this table.
job_id	The job ID.
department_id	The department ID associated with the job.
created_at	The date and time the department was associated with the job, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the department on the job was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The jobs interviews table

The jobs_interviews table represents a list of interviews by job. Each row represents a single interview on a single stage for a single job.

Column	Description
id	The interview ID.
job_id	The job ID.
stage_id	The stage ID.
interview_id	The interview ID.
order	The order of the interview on a stage with multiple interviews.
estimated_duration	The estimated duration of the interview in minutes.
created_at	The date and time the interview was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the interview was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
interview_name	The interview name as it appears in Gem.
stage_name	The stage name as it appears in Gem.
organization_id	Your organization’s unique ID in Gem.

The jobs offices table

The jobs_offices table represents a list of offices by job. Each row represents a single office on a single job.

Column	Description
id	The primary key to this table.
job_id	The job ID.
office_id	The office ID associated with the job.
created_at	The date and time the job was associated with the office, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the job’s office was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The jobs posts table

The job_posts table represents a list of job posts by job. Each row represents details of a job post associated with a job.

Column	Description
id	The job post ID.
job_id	The job ID.
title	The job post title.
live	Boolean. Results are either true or false, indicating whether the job post is live on your job board.
job_board_name	N/A
language	The language the job post was published in, like en for English.
created_at	The date and time the job post was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the job post was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
eeoc_enabled	N/A
indeed_feed	N/A
linked_in_feed	N/A
glassdoor_feed	N/A
first_published	The date and time the job post was first published, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The job post locations table

The job_post_locations table represents a list of job locations and their statuses in Gem. Each row represents the status of a single job.

Column	Description
id	The job location ID.
name	The job location name as it appears in Gem.
type	The type of job location.
job_post_id	The job post ID.
created_at	The date and time the job post location was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the job post location was updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The job post questions table

The job_post_questions table represents a list of job post questions by job post. Each row represents a single job post question associated with a single job post.

Column	Description
job_post_id	The job post ID.
question	The job post question.
created_at	The date and time the job post question was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the job post question was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The jobs stages table

The jobs_stages table represents a list of stages by job. Each row represents a single stage on a single job.

Column	Description
job_id	The job ID.
stage_id	The stage ID.
order	The order the stage appears on the job.
name	The name of the stage as it appears in Gem.
stage_alert_setting	N/A
created_at	The date and time the job stage was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the job stage was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
milestones	N/A
organization_id	Your organization’s unique ID in Gem.

The hiring team table

The hiring_team table represents a list of hiring teams by job. Each row represents a single Gem user on a hiring team role on a single job.

Column	Description
job_id	The job ID.
user_id	The user ID of the person associated with the hiring team role.
role	The hiring team role associated with the person.
responsible	Boolean. Results are either true or false, indicating whether the person holds a responsible role on the hiring team.
created_at	The date and time the hiring team role was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the hiring team role was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The openings table

The openings table represents a list of openings by job. Each row represents a single opening for a single job.

Column	Description
id	The primary key to this table.
job_id	The job ID.
opening_id	The opening ID.
opened_at	The date and time the opening was opened, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
closed_at	The date and time the opening was closed, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
hired_application_id	The hired application’s ID, if applicable.
close_reason	The close reason selected, if applicable.
created_at	The date and time the opening was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the opening was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
target_start_date	The opening’s target start date.
organization_id	Your organization’s unique ID in Gem.
status	Whether or not the job opening is currently active.

The opening custom fields table

The opening_custom_fields table represents a snapshot of custom opening fields. Each row represents a single value of a single custom opening field for a single opening.

Column	Description
opening_id	The opening ID.
custom_field	The custom opening field.
float_value	These are numbers entered in the custom opening field, if applicable.
date_value	The date entered in the custom opening field, if applicable.
display_value	The value entered into the custom opening field as it appears in Gem.
unit	The currency type entered in the custom opening field, if applicable.
min_value	The minimum value of the range.
max_value	The maximum value of the range.
user_id	The Gem user ID entered for custom opening fields with user field types.
created_at	The date and time the custom opening field value was associated with the opening, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the custom opening field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
key	The immutable field key for the custom opening field.
text_value	The value entered on the custom opening field as it appears in Gem.
organization_id	Your organization’s unique ID in Gem.

Offer data

The offers table

The offers table represents all offers. Each row represents the details of a single version of an offer for a single application. Each new version of an offer generates a new row of data.

Column	Description
id	The offer ID.
application_id	The application ID.
status	The status of the offer and is one of created, accepted, rejected, or deprecated.
created_at	The date and time the offer was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
sent_at	The date and time the offer was sent to the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
resolved_at	The date and time the offer was accepted or rejected, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
start_date	The date and time of the new hire’s start date, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
created_by	The Gem user who created the offer.
updated_at	The date and time the offer was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

The offer custom fields table

The offer_custom_fields table represents all custom offer fields associated with an offer. Each row represents a single value of a single custom field for a single offer.

Column	Description
custom_field	The custom offer field.
float_value	These are numbers entered in the custom offer field, if applicable.
date_value	The date entered in the custom offer field, if applicable.
display_value	The value entered into the custom offer field as it appears in Gem.
unit	The currency type entered in the custom offer field, if applicable.
min_value	The minimum value of the range.
max_value	The maximum value of the range.
user_id	The Gem user ID entered for custom offer fields with user field types.
created_at	The date and time the custom offer field value was associated with the offer, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the custom offer field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
key	The immutable field key for the custom offer field.
text_value	The value entered on the custom offer field as it appears in Gem.
organization_id	Your organization’s unique ID in Gem.
offer_id	The unique identifier for the offer.

Organization data

The departments table

The departments table represents a list of all departments. Each row represents a single department. This table can be joined to any table that has a department_id to get the name of the department.

Column	Description
id	The department ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the department as it appears in Gem.
parent_id	The department’s parent department ID, if applicable.
created_at	The date and time the department was created, like 2024-01-31 06:00:00.000.
updated_at	The date and time the department was last updated, like 2024-01-31 06:00:00.000.

The interviews table

The interviews table represents a list of all your organization’s interviews. Each row represents a single interview.

Column	Description
id	The interview ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the interview as it appears in Gem.
created_at	The date and time the interview was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the interview was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The offices table

The offices table represents a list of all offices. Each row represents a single office location. This table can be joined to any table that has a office_id to get the name of the office location.

Column	Description
id	The office ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the office as it appears in Gem.
parent_id	The office’s parent office ID, if applicable.
created_at	The date and time the office was created, like 2024-01-31 06:00:00.000.
updated_at	The date and time the office was last updated, like 2024-01-31 06:00:00.000.

The referrers table

The referrers table represents a list of all referrers. Each row represents a single referrer. This table can be joined to any table that has a referrer_id to get the name of the referrer.

Column	Description
id	The referrer ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the referrer as it appears in Gem.
user_id	The user ID associated with the referrer, if applicable.
created_at	The date and time the referrer was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the referrer was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The rejection reasons table

The rejection_reasons table represents a list of all rejection reasons. Each row represents a single rejection reason.

Column	Description
id	The rejection reason ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the rejection reason as it appears in Gem.
type	The type of the rejection reason.
created_at	The date and time the rejection reason was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the rejection reason was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The sources table

The sources table represents a list of all sources. Each row represents a single source. This table can be joined to any table with a source_id to get the name of the source.

Column	Description
id	The source ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the source as it appears in Gem.
type	The type of the source.
created_at	The date and time the source was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the source was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The stages table

The stages table represents a list of stages by job, whether they are active or not.

Column	Description
id	The stage ID.
organization_id	Your organization’s unique ID in Gem.
name	The name of the stage as it appears in Gem.
order	The order the stage appears on the job.
active	Boolean. Results are either true or false, indicating whether the stage is active.
created_at	The date and time the stage was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the stage was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The users table

The users table represents a list of all users. Each row represents a single Gem user. This table can be joined to any other table that has a user_id to get the name of a user.

Column	Description
id	The user ID.
organization_id	Your organization’s unique ID in Gem.
first_name	The user’s first name.
last_name	The user’s last name.
email	The user’s email address.
status	The user’s current status in Gem and is one of active or disabled.
full_name	The user’s full name.
employee_id	N/A
created_at	The date and time the user was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the user was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).

The user candidate links table

The user_candidate_links table represents a list of all user candidate links. Each row represents a single user linked to a single candidate.

Column	Description
user_id	The user ID.
candidate_id	The candidate ID linked to the user.
created_at	The date and time the user candidate link was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the user candidate link was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

Process data

The scheduled interviews table

The scheduled_interviews table represents a list of scheduled interviews. Each row represents a single scheduled interview.

Column	Description
id	The interviewer ID associated with the scheduled interview.
application_id	The application ID associated with the scheduled interview.
interview_id	The interview ID.
status	The interview’s feedback status.
scheduled_by_id	The original scheduler or organizer of the interview. If an interview is rescheduled, this field doesn’t update.
scheduled_at	The date and time the interview was scheduled at, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). If an interview is rescheduled, this timestamp doesn’t update.
starts_at	The date and time the interview was scheduled to start, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
ends_at	The date and time the interview was scheduled to end, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
all_day_start_date	The start date for all-day interviews, like 2024-01-31.
all_day_end_date	The end date for all-day interviews, like 2024-01-31.
stage_name	The stage name associated with the scheduled interview.
interview_name	The interview name.
organization_id	Your organization’s unique ID in Gem.
location	The location of the interview.

The interviewers table

The interviewers table represents a list of interviewers per scheduled interview. Each row represents a single interviewer for a scheduled interview.

Column	Description
interview_id	The interview ID.
user_id	The user ID associated with the interview.
scorecard_id	The scorecard associated with the interview.
user	The user name associated with the interview.
organization_id	Your organization’s unique ID in Gem.

The scorecards table

The scorecards table represents data on submitted scorecards. Each row represents a single submitted scorecard. Connect the scheduled_interviews table to the interviewers table, then connect the scorecards table to the interviewers table.

Column	Description
id	The scorecard ID.
application_id	The application ID.
stage_id	The stage ID.
interview_id	The interview ID.
interviewer_id	The interviewer ID.
submitter_id	The user ID of the person who submitted the scorecard.
overall_recommendation	The scorecard’s overall recommendation and is one of Yes, Strong Yes, No, Strong No or No Decision.
submitted_at	The date and time the scorecard was submitted, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
scheduled_interview_ended_at	The date and time the scheduled interview ended, like 2024-01-31 06:00:00.000.
total_focus_attributes	N/A
completed_focus_attributes	N/A
interviewer	The interviewer’s name.
submitter	The scorecard submitter’s name.
stage_name	The stage associated with the scorecard.
created_at	The date and time the scorecard was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the scorecard was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
interview_name	The interview name.
organization_id	Your organization’s unique ID in Gem.
key_takeaways	The text included in the key takeaways field on the scorecard.

The scorecard questions table

The scorecard_questions table represents a snapshot of your organization’s scorecard questions. Each row represents a single scorecard question from a single job.

Column	Description
id	The scorecard question ID.
interview_kit_id	N/A
question	The scorecard question.
answer_type	The scorecard question answer type.
active	Boolean. Results are either true or false, indicating whether the scorecard question is active.
priority	The priority order of the question on the scorecard.
required	Boolean. Results are either true or false, indicating whether the scorecard question is required.
created_at	The date and time the scorecard question was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the scorecard question was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
focus_attributes	N/A
organization_id	Your organization’s unique ID in Gem.

The scorecard question answers table

The scorecard_question_answers table represents submitted scorecard questions and answers. Each row represents a single scorecard question and a single submitted scorecard answer.

Column	Description
scorecard_id	The scorecard ID.
application_id	The application ID.
question	The scorecard question.
answer	The scorecard answer.
created_at	The date and time the scorecard was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the scorecard was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
scorecard_question_id	The scorecard question ID.
organization_id	Your organization’s unique ID in Gem.

The stage snapshots table

The stage_snapshots table represents a daily snapshot of stage-level activity. Each row represents the number of active applications in the stage at 4am ET on a given date for jobs that were open at that time.

Column	Description
stage_id	The stage ID.
date	The date of the stage snapshot.
active_count	The number of active applications on the stage.
job_id	The job ID.
stage_name	The stage name as it appears in Gem.
organization_id	Your organization’s unique ID in Gem.

The approvals table

The approvals table represents a list of approvals. Each row with an offer_id represents a specific job or offer approval associated with each approver.

Column	Description
offer_id	The offer ID.
application_id	The application ID.
job_id	The job ID.
job_name	The job name as it appears in Gem.
candidate_id	The candidate ID.
candidate_name	The candidate’s name.
approver_id	The user ID of the approver.
approver_name	The approver’s name.
approval_type	The approval type. Responses are either Offer Candidate for candidate offers, Open Job for one-stage approvals, and Offer Job for two-stage approvals.
status	The approval status and is one of Due, Waiting, Approved, or Rejected.
version	N/A
version_final	N/A
group_id	N/A
group_order	N/A
group_quorum	N/A
assigned_at	The date and time the approval was assigned, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
completed_at	The date and time the approval was completed, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
created_at	The date and time the approval was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
updated_at	The date and time the approval was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC).
organization_id	Your organization’s unique ID in Gem.

Have any issues or questions on this topic? Please feel free to contact your dedicated Gem Customer Success Manager directly or our Support team at support@gem.com.

Application data
Candidate data
Candidate PII data
Demographic data
Job data
Offer data
Organization data
Process data