Gem data export format/schema

Audience

External

Displayed Description

Page Type

Article

Data is provided in CSV format with the following schema:

create table event_log(
  timestamp int,
  user_name text,
  user_email text,
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_first_name text,
  candidate_last_name text,
  candidate_email text,
  event_type text, -- see below
  event_subtype text, -- see below
  event_metadata text -- see below
);

create table projects( -- there will be one row per candidate per project
  added_timestamp int, -- when the candidate was added to the project
  project_id int, -- internal Gem ID
  project_name text,
  project_owner_name text,
  project_owner_email text,
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_first_name text,
  candidate_last_name text,
  candidate_email text,
  adding_user_name text,
  adding_user_email text
);

create table custom_field_data(
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_first_name text,
  candidate_last_name text,
  candidate_email text,
  -- There will be a column for each custom field
);

create table notes( -- this includes private notes
  timestamp int, -- unix timestamp of when the note was added
  user_name text, -- the name of the user who added the note
  user_email text, -- the email of the user who added the note
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_first_name text,
  candidate_last_name text,
  candidate_email text,
  note_content text, -- the note content in HTML format
);

create table candidate_info( -- not exported if PII is hidden
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_first_name text,
  candidate_last_name text,
  candidate_email text,
  all_candidate_emails text, -- a JSON formatted list of emails
  candidate_phone_number text,
  candidate_location text,
  candidate_linkedin_url text,
  candidate_company text,
  candidate_title text,
  candidate_school text,
  candidate_source text, -- the location the candidate was sourced from
  candidate_ats_id text,
  sourced_timestamp int,
);

-- only included if the team has requested DEI data in the export
-- and if the team has signed the data agreement contract
create table candidate_dei_data( -- not exported if PII is hidden
  candidate_id int, -- internal Gem ID
  candidate_greenhouse_id text,
  candidate_predicted_gender text,
  candidate_predicted_race_ethnicity text,
  sourced_timestamp int,
);

Possible values for event_log.event_type:

First Outreach
Follow-up
Reply

Possible values for event_log.event_subtype:

Email
Inmail
Phone Call
Text Message
Meeting

Possible values for event_log.event_metadata:

Interested
Not Interested
Later

Note: any new columns added later will be added after existing columns

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.

create table event_log( timestamp int, user_name text, user_email text, candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_first_name text, candidate_last_name text, candidate_email text, event_type text, -- see below event_subtype text, -- see below event_metadata text -- see below ); create table projects( -- there will be one row per candidate per project added_timestamp int, -- when the candidate was added to the project project_id int, -- internal Gem ID project_name text, project_owner_name text, project_owner_email text, candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_first_name text, candidate_last_name text, candidate_email text, adding_user_name text, adding_user_email text ); create table custom_field_data( candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_first_name text, candidate_last_name text, candidate_email text, -- There will be a column for each custom field ); create table notes( -- this includes private notes timestamp int, -- unix timestamp of when the note was added user_name text, -- the name of the user who added the note user_email text, -- the email of the user who added the note candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_first_name text, candidate_last_name text, candidate_email text, note_content text, -- the note content in HTML format ); create table candidate_info( -- not exported if PII is hidden candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_first_name text, candidate_last_name text, candidate_email text, all_candidate_emails text, -- a JSON formatted list of emails candidate_phone_number text, candidate_location text, candidate_linkedin_url text, candidate_company text, candidate_title text, candidate_school text, candidate_source text, -- the location the candidate was sourced from candidate_ats_id text, sourced_timestamp int, ); -- only included if the team has requested DEI data in the export -- and if the team has signed the data agreement contract create table candidate_dei_data( -- not exported if PII is hidden candidate_id int, -- internal Gem ID candidate_greenhouse_id text, candidate_predicted_gender text, candidate_predicted_race_ethnicity text, sourced_timestamp int, );