Business Intelligence Connector data model

There are three main data tables / objects in the Business Intelligence Connector data set:

1. Candidates
2. Applications
3. Jobs

A candidate (person) can submit any number of applications onto any number of jobs (i.e. can apply to any number of jobs, or apply many times to the same job)

Only one application can be submitted for a single job. This information is summarized in the application_jobs table.

An individual can also have a prospect application which is a separate record that can be assigned to multiple jobs, departments, or offices. When the prospect is turned into a candidate,  a unique candidate record is created. Prospect applications can also be assigned to no jobs, departments, or offices without breaking. 

There is a boolean dimension named prospect on the applications table which indicates whether an application is an actual application on a job, or is a prospect application.

Candidate-level data

The candidates in tbl_candidates can be associated with the following candidate-level data tables using the Candidate ID.

One-to-many relationships

• candidate_custom_fields
• candidate_email_addresses
• candidate_mailing_addresses
• candidate_phone_numbers
• candidate_tags
• educations
• employments
• referral_question_custom_fields

Application-level data

The applications in the applications data table can be associated with the following application-level data tables using the Application ID.

One-to-one relationships

• eeoc_responses

One-to-many relationships

• application_custom_fields
• application_question_answers
• rejection_question_custom_fields

Job-level data

The jobs in the jobs data table can be associated with the following job-level data tables using the Job ID.

One-to-many relationships

• hiring_team
• job_custom_fields
• job_posts
• job_snapshots
• jobs_attributes
• jobs_departments
• jobs_interviews
• jobs_offices
• jobs_stages
• openings
• stage_snapshots

Process and Applicant Tracking Data

As applications progress through the recruiting process, the following process and applicant tracking data is generated for each Application ID.

Process	Data table
Moving through stages	The 'application_stages' data table represents each stage of the job that the application can be in, and the enter and exit times for each stage; null data exists for stages that applications have not entered yet.
Interviewing	The scheduled_interviews data table represents each interview that is scheduled in Greenhouse The interviewers and interviewer_tags data tables represent the Greenhouse user(s) assigned to the interview The scorecards, scorecard_question_answers, and scorecards_attributes data tables represent each scorecard that each interviewer submits Though it is best practice, it is not required to schedule an interview in order to submit a scorecard (thus you can have more scorecards than scheduled interviews).
Offers	The offers and offer_custom_fields data tables represent the details of any offer that is generated for an application

Other Tables

There are a few stand-alone tables not associated with candidates, applications, or jobs:

User-level data tables

• users
• user_actions
• greenhouse_usages

Standalone tables

• Organizations
• Candidate_surveys (responses are anonymous)

Every other data table is a simple mapping table that maps an ID number to the name of the item represented (e.g., the 'attributes' data table maps an Attribute ID to the name of the attribute).