Gem Data Export Format/Schema

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,
);

-- 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,
);

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