OpenField Data Dictionary

Jump to:

• Overview
• Tables
  • redshift_people_attempts
  • redshift_people_{question_type}_conversations
  • knock_conversation_code
  • knock_people

Overview

• Tenancy: Schema - each customer has their own dedicated schema
• Sync Frequency: Hourly
• Database contents:
  • Read-only access to replicas of real-time production tables
    • Key tables 
  • Read-only access to constructured redshift sync tables:
    • Knock attempts joined to people
    • All survey question responses joined to people
• The conversation_time column in OpenField data tables displays as a UNIX timestamp. To convert it to a date format in google sheets, see this google support article.

Tables

redshift_people_attempts

Description: People joined to knock attempts.

• Neither people, nor addresses, or conversation IDs are unique.
• There will be one row per person at an attempted house.

Details:

• Address fields are populated for address contacted at, regardless of relationship to person
  • (IE: the address here may be found on Emily’s profile under registered address, provided address, or mailed etc,...)

Column	Type	Description
of_people_id	integer; unique	OpenField person id; unique to each schema.
conversation_id	integer	Conversation id; unique to schema
conversation_time	Timestamp with time zone
contact_type	varchar(2)	Attempt responses:  NH = Not Home WN = Wrong Number LM = Left Message RF = Refused HS = Hostile DC = Deceased MV = Moved IA = Inaccessible OL = Different languages
canvasser	varchar(100)	Name of canvasser
state_file_id	varchar(50)
other_voter_file_id	varchar(50)	VAN or PDI IDs mapped here
van_id	varchar(50)	My Voter / EveryAction / VAN IDs
my_campaign_id	varchar(50)	My Campaign IDs
first_name	varchar(100)
middle_name	varchar(100)
last_name	varchar(100)
suffix	varchar(10)
preferred_name	varchar(100)
sex	varchar(20)
age	integer
phone1	varchar(128)
email1	varchar(258)
email_opt_in1	boolean
address1	varchar(100)
address2	varchar(100)
city	varchar(100)
state	varchar(2)	State abbreviation
county	varchar(100)
zip_5	varchar(5)
contact_script_id	integer	Script Id; unique to schema
conversation_code_id	integer	Conversation Code Id; unique to schema

redshift_people_{question_type}_conversations

Description: 

There are currently six question types with discrete redshift tables:

• Celloptins (Cell Opt Ins)
• Email (Email Opt Ins)
• List (AKA Drop Down Questions)
• Longtext (Long Open Field Questions)
• Shorttext (Short OpenField Questions)
• Truefalse (True / False Questions)

A contact’s people_id + conversation_id + question_id together should be UNIQUE. Each individual column can have duplicate values (i.e., these columns are not unique by themselves and you shouldn’t be alarmed that they aren’t unique). Each row represents the response to one question by one contact, which includes the text of the question AND the recorded response. 

Ex: If we are talking to Jane, and she answers the three script questions to complete the conversation, there will be three rows or one row for each question answered that will make up the conversation ID.

Details:

• Question IDs, a.k.a. template IDs in the Questions table, are unique to a survey question. To pull analytics by survey question, filter on question_id

Column	Type	Description
of_people_id	integer; unique	OpenField person id; unique to each schema.
conversation_id	integer	Conversation id; unique to schema
conversation_time	Timestamp with time zone
contact_type	varchar(2)	Attempt responses:  NH = Not Home WN = Wrong Number LM = Left Message RF = Refused HS = Hostile DC = Deceased MV = Moved IA = Inaccessible OL = Different languages
canvasser	varchar(100)	Name of canvasser
state_file_id	varchar(50)
other_voter_file_id	varchar(50)	PDI IDs mapped here
van_id	varchar(50)	My Voter / EveryAction / VAN IDs
my_campaign_id	varchar(50)	My Campaign IDs
first_name	varchar(100)
middle_name	varchar(100)
last_name	varchar(100)
suffix	varchar(10)
preferred_name	varchar(100)
sex	varchar(20)
age	integer
phone1	varchar(128)
email1	varchar(258)
email_opt_in1	boolean
address1	varchar(100)
address2	varchar(100)
city	varchar(100)
state	varchar(2)	State abbreviation
county	varchar(100)
zip_5	varchar(5)
contact_script_id	integer	Script Id; unique to schema
conversation_code_id	integer	Conversation Code ID; unique to schema
question_id	integer	Template ID for question; unique to each survey question
question_text	text	Survey Question text; IE: “Can Medgar Evers count on your support?”
response	varchar(100)	Survey Question Response
Email or Phone_opt_in	varchar(100)	Only present in Cell Opt-In or Email type question tables

knock_conversation_code

Description: OpenField Conversation Code table.

• Conversation Codes unify target lists of doors or phones and scripts.
• Join to the Conversation Codes table to decipher which set of targets are connected to a set of attempts or responses.

Details:

• Conversation Code IDs are unique to a schema and are the primary unifying factor across the application’s tables. 
• Scripts and targets can be connected to multiple Conversation Codes at a time

Column	Type	Description
id	integer; unique	OpenField Conversation Code ID; unique to each schema.
code	varchar(6)	Conversation Code; unique to schema; input by canvassers in the interface to access turf or calls
created_at	Timestamp with time zone
description	varchar(255)	Specifics about the conversation code itself; generally location and targets, etc,...
org	varchar(255)	Organization leading the code; displays in scripts
expires	Timestamp with time zone	Date code will be automatically disabled
conversation_type	varchar(2)	Type of conversation; determines interface display: VK = Volunteer Door Knock PK = Paid Door Knock VR = Volunteer Registration Door Knock PR = Paid Registration Door Knock VC = Volunteer Call PC = Paid Call VD = Volunteer Registration Call PD = Paid Registration Call ST = Street Canvass
start_time	Time without time zone	Time conversation code is active
end_time	Time without time zone	Time conversation code is inaccessible
status	varchar(100)	Code Status: Processing Active After Hours Expired Deactivated
attempt_goal	integer	Number of attempts to nudge canvassers towards on login
created_by_id	integer
script_id	integer	Script ID; unique to schema
targets_id	integer	Target ID; unique to schema
reg_goal	integer	Voter Registration goal to nudge canvassers toward
vbm_goal	integer	VBM collection goal to nudge canvassers toward

knock_people

Description: The core people table, where all people contacted or imported are stored, in addition to their unique IDs and core identifying information. 

Details:

• ID on this table is "of_people_id" on other tables.
• Address fields are populated for addresses contacted at, regardless of relationship to person (IE: the address here may be found on Emily’s profile under registered address, provided address, or mailed etc,...)
• Mailing address split parts exist to aid with mailers

Column	Type	Description
id	Int unique	Unique ID to OpenField
created_date	timestamp with time zone (timestamptz)	The date and time (with time zone) that an attempt was recorded
state_file_id	varchar(50)	Voter File ID on the state voter file (if provided)
county_file_id	varchar(50)	Voter File ID assigned by the county (if provided)
action_network_id	varchar(50)	ID associated with your Action Network instance (if provided)
of_voter_file_id	varchar(50)	Voter File ID provided by OpenField
other_voter_file_id	varchar(50)	Voter File ID from another source (if provided)
alloy_uuid	varchar(100)	Voter File ID on the alloy voter file (if provided)
voterbase_id	varchar(50)	The voterbase id for the contacted person (from the TargetSmart voter file)
dwid	varchar(50)	The DWID for the contacted person (from the Catalist voter file)
other_crm_id	varchar(50)	An ID for the contacted person sourced from a different CRM
vr_status	varchar(20)	Voter registration status of the contacted person
full_name	varchar(500)	The full name of the contacted person
honorarium	varchar(10)	The honorarium chosen by a contacted person. Examples include: Dr, Mr, Mrs, Ms, Rev, etc.
first_name	varchar(100)	The first name of the contacted person
middle_name	varchar(100)	The middle name of the contacted person
last_name	varchar(100)	The last name of the contacted person
suffix	varchar(20)	The suffix for the contacted person. Examples include: Jr., Sr., III, Esq., etc
preferred_name	varchar(100)	The name the contacted person prefers to use (if different than their government name)
sex	varchar(20)	The sex of the contacted person (if provided)
age	integer	The age of the contacted person
dob	date	The date of birth of the contacted person
gender_identity	varchar(20)	The gender identity of the contacted person
phone1	varchar(128)	The first phone number we have for the contacted person
phone_opt_in1	Boolean	The opt-in status of phone1
phone2	varchar(128)	The second phone number we have for the contacted person
phone_opt_in2	boolean	The opt-in status of phone1
email1	varchar(254)	The first email we have for the contacted person
email_opt_in1	boolean	The opt-in status of email1
email2	varchar(254)	The second email we have for the contacted person
email_opt_in2	boolean	The opt-in status of email2
prov_full_address	varchar(1000)	The full address that is provided for the contacted person
prov_address1	varchar(100)	The first part of the provided address for the contacted person
prov_address2	varchar(100)	The second part of the provided address for the contacted person
prov_city	varchar(100)	The city in the provided address for the contacted person
prov_state	varchar(2)	The state in the provided address for the contacted person
prov_zip_5	varchar(5)	The zip code in the provided address for the contacted person
p_raw_lat_long	varchar(500)	The latitude and longitude of the provided address for the contacted person
p_raw_lat	Double precision	The latitude of the provided address for the contacted person
p_raw_long	Double precision	The longitude of the provided address for the contacted person
p_geom	geometry(point,4326)	The two-dimensional geography for the provided address of the contacted person (for use in mapping applications)
contacted_full_address	varchar(1000)	The full address where the contacted person was contacted
lives_at_contacted	boolean	Does the person live at the address where they were contacted?
reg_full_address	varchar(100)	The full address for the contacted person on the provided voter file
reg_address1	varchar(100)	The first part of the address for the contacted person on the provided voter file
reg_address2	varchar(100)	The second part of the address for the contacted person on the provided voter file
reg_city	varchar(100)	The city of the address for the contacted person on provided the voter file
reg_county	varchar(100)	The county of residence for the contacted person on the provided  voter file
reg_state	varchar(2)	The state of the address for the contacted person on the voter file
reg_zip_5	varchar(5)	The zip code of the address for the contacted person on the voter file
r_raw_lat_long	varchar(500)	The latitude and longitude for the address on the voter file for the contacted person
r_raw_lat	Double precision	The latitude for the address on the provided voter file for the contacted person
r_raw_long	Double precision	The longitude for the address on the provided voter file for the contacted person
r_geom	geometry(Point,4326)	The two-dimensional geography for the address on the provided voter file for the contacted person (for use in mapping applications)
mail_full_address	varchar(1000)	The full mailing address for the contacted person
mail_address1	varchar(100)	The first part of the mailing address for the contacted person
mail_address2	varchar(100)	The second part of the mailing address for the contacted person
mail_city	varchar(100)	The city of the mailing address for the contacted person
mail_state	varchar(2)	The state of the mailing address for the contacted person
mail_zip_5	varchar(5)	The zip code of the mailing address for the contacted person
m_raw_lat_long	varchar(500)	The latitude and longitude of the mailing address for the contacted person
m_raw_lat	Double precision	The latitude of the mailing address for the contacted person
m_raw_long	Double precision	The longitude of the mailing address for the contacted person
m_geom	geometry(Point,4326)	The two-dimensional geography for the mailing address for the contacted person (for use in mapping applications)
early_voted	boolean	If true, the contacted person has early voted; provided by a voter file provider
contacted_national_address_id_id	bigint	The national ID for the contacted person’s address
mailing_national_address_id_id	bigint	The national ID for the contacted person’s mailing address
provided_national_address_id_id	bigint	The national ID for the contacted person’s provided address
registered_national_address_id_id	bigint	The national ID for the contacted person’s registration address (provided on the voter file)
actionkit_id	varchar(50)	The ID of the contacted person in ActionKit (if provided)
mobilize_id	varchar(50)	The ID of the contacted person in Mobilize (if provided)
polling_location	varchar(100)	The polling location for the contacted person (if provided)
precinct_id	varchar(100)	The ID of the precinct assigned to the contacted person (from the voter file)
precinct_name	varchar(50)	The name of the precinct assigned to the contacted person (from the voter file)
van_id	varchar(50)	The VANID for the contacted person (if provided)
mail_street_name	varchar(50)	The name of the mailing address for the contacted person
mail_street_number	varchar(50)	The number of the mailing address for the contacted person
mail_unit_number	varchar(50)	The unit number of the mailing address for the contacted person
prov_street_name	varchar(50)	The name of the provided address for the contacted person
prov_street_number	varchar(50)	The number of the provided address for the contacted person
prov_unit_number	varchar(50)	The unit number of the provided address for the contacted person
reg_street_name	varchar(50)	The street name of the provided address for the contacted person
reg_street_number	varchar(50)	The street number of the address on the voter file of the contacted person
reg_unit_number	varchar(50)	The unit number of the address on the voter file for the contacted person.
political_party	varchar(100)	The political party of the contacted person (if provided)
my_campaign_id	varchar(100)	The MyCampaign ID for the contacted person (if provided)
member	boolean	Value designated to members (if applicable)
mail_apt_type	varchar(20)	The type of unit of the mailing address for the contacted person
mail_street_number_half	varchar(20)	The fractional part of the mailing address for the contacted person pulled out to its numeric form
mail_street_part	varchar(100)	If the mailing address for the contacted person includes a partial, it will be here; Examples include: L St NW
mail_street_prefix	varchar(20)	If the mailing address for the contacted person includes a prefix, it will be here.  Examples include: North Saint Louis Ave
mail_street_suffix	varchar(20)	The suffix of the mailing address for the contacted person. Examples include: Cuthbert Ave
mail_street_type	varchar(20)	The type of street of the mailing address for the contacted person
prov_apt_type	varchar(100)	The unit type of the provided address for the contacted person
prov_street_number_half	varchar(20)	The number of the provided address for the contacted person
prov_street_part	varchar(20)	The name of the street in the provided address for the contacted person
prov_street_prefix	varchar(20)	The prefix of the street in the provided address for the contacted person
prov_street_suffix	varchar(20)	The suffix of the street in the provided address for the contacted person
prov_street_type	varchar(50)	The type of street of the provided address for the contacted person
reg_apt_type	varchar(20)	The unit type of the address on the voter file for the contacted person
reg_street_number_half	varchar(20)	The number of the address on the voter file for the contacted person
reg_street_part	varchar(100)	The street name of the address on the voter file for the contacted person
reg_street_prefix	varchar(20)	The prefix of the address on the voter file for the contacted person
reg_street_suffix	varchar(20)	The suffix of the address on the voter file for the contacted person
reg_street_type	varchar(50)	The type of the address on the voter file for the contacted person
mail_county	varchar(100)	The county of the mailing address of the contacted person
prov_county	varchar(100)	The county of the provided address for the contacted person
phone3	varchar(128)	The third phone number we have for the contacted person
phone_opt_in3	boolean	The opt-in status for phone3
phone4	varchar(128)	The fourth phone number we have for the contacted person
phone_opt_in4	boolean	The opt-in status for phone4

Have more questions? Email us at [e-mail]. 

If you have feature requests or suggestions for how to improve OpenField, stop by our Product Suggestion & Feature Request page!