Business Intelligence Connector update – September 6, 2019

We are making the following changes to the Greenhouse Analytics schema on September 23rd, 2019! See below for a description, and see attached for the latest schema.

This update allows customers with the Business Intelligence Connector to view data regarding:
  • Demographic Questions and Answers from Greenhouse Inclusion
  • Prospect Pools and Stages
  • Transitions a prospect makes between different Prospect Pools and Stages. Unlike other data, this table will only include information about new transitions that happen after this is turned on for your organization.
---

CREATE TABLE demographic_answer_option_translations (
id bigint NOT NULL,
demographic_answer_option_id bigint NOT NULL FOREIGN_KEY,
language character varying NOT NULL,
name character varying NOT NULL
);

CREATE TABLE demographic_answer_options (
id bigint NOT NULL,
demographic_question_id bigint NOT NULL,
active boolean NOT NULL,
free_form boolean NOT NULL
);

CREATE TABLE demographic_answers (
id bigint NOT NULL,
application_id bigint NOT NULL,
demographic_question_id bigint NOT NULL,
demographic_answer_option_id bigint,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE demographic_free_form_answers (
id bigint NOT NULL,
demographic_answer_id bigint NOT NULL,
text text NOT NULL,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE demographic_question_translations (
id bigint NOT NULL,
demographic_question_id bigint NOT NULL,
language character varying NOT NULL,
name character varying NOT NULL
);

CREATE TABLE demographic_questions (
id bigint NOT NULL,
organization_id bigint NOT NULL,
active boolean NOT NULL
);

CREATE TABLE prospect_pool_stages (
id bigint NOT NULL,
prospect_pool_id bigint NOT NULL,
name character varying NOT NULL,
priority integer NOT NULL,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE prospect_pool_transitions (
application_id bigint NOT NULL,
user_id bigint NOT NULL,
old_prospect_pool_id bigint,
old_prospect_stage_id bigint,
new_prospect_pool_id bigint,
new_prospect_stage_id bigint,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE prospect_pools (
id bigint NOT NULL,
name character varying NOT NULL,
active boolean NOT NULL,
description character varying,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);