Export Civis Data to NGPVAN

The Export Civis Data to NGPVAN templated script allows users to export data to both NGP and VAN. The resources applicable to both NGP and VAN can be utilized by inputting the respective credential (NGP or VAN) in the NGPVAN Credential parameter. (Click here for credential creation info)

You can create your script using this link, or in Platform you can navigate to Code → Scripts → More Script Templates and searching for "Export Civis Data to NGPVAN" or “19204”.  If you are not shared on this template, please email support@civisanalytics.com.

Parameters

• Cluster (required): Name of the cluster where data to be exported is stored.
• Database Credential (required): A valid database credential for the cluster
• Export Mode (required):What data to export to NGPVAN. See below for documentation on each mode. Choose one: people, update people, add notes to people, add codes to people, add relationships to people, locations, events, update events, shifts, signups, edit signups, survey responses, activist codes, codes, contact history, scores*, voter registrations, create supporter groups, and add supporters to groups, contributions (ngp only), disbursements (ngp only) *scores: DNC clients must export scores using a different process documented here 
• Queue View (optional): The full table name (including schema) of a Civis table to export. Each row will be uploaded as a unique record in NGP or VAN. Required fields for each export mode are defined below.
• One of Queue View or SQL Query are required.

• SQL Query (optional):A complete SQL query defining the data to export to NGP or VAN. Each row of the resulting query will be uploaded as a unique record in NGP or VAN. Required fields for each export mode are defined below.
• One of Queue View or SQL Query are required.
• Response Table (optional): Table to output API responses and errors.
• Response Table Setting (optional): Drop or append API records to the response table.
• Database Mode (required): The NGPVAN database to upload data - One of MyVoterfile or MyCampaign. Always use MyCampaign for NGP exports.
• VAN Score ID (optional): The unique ID of a score to upload to NGPVAN. Required for scores export mode.
• Callback Email Address (optional): Email address for receiving updates about score loading job from VAN.
• NGPVAN (required): A custom Civis credential with a username and password for NGP or VAN. Note for resources supported in both NGP and VAN (ex: people), the credential determines the destination for the export.
• Script Mode: The NGPVAN export uses a paradigm of script “modes”, where different methods are executed by running the script in a different mode. The modes available for the NGPVAN Export script are:
• “help” - Get help 
• “test_connection” - Test connectivity to NGPVAN API. (Note: Check the logs to see if your connection succeeded or failed; the job will succeed so long as the test is executed successfully. For example, if the test succeeded in determining that the connection to NGPVAN API failed, the job will succeed.)
• “list_objects” - List export modes available
• “run” - Run sync (default)
• “view_config” - Output current configuration as a file output
• Log Level
• “DEBUG” - Log details of all API calls.
• “INFO” - Log informational messages about program execution.
• “WARNING” - Log potential errors and other warnings.
• “ERROR” - Logs only unrecoverable errors in program execution.

 

NGPVAN Export Modes

Below describes the valid export modes for the script, the NGPVAN API endpoints leveraged, and documentation on table columns for each mode.

Friendly/UI Name	Template API string	NGPVAN Endpoint
people	people	people/findOrCreate
update people	people_update	people/{vanId}
add codes to people	people_codes	people/{vanId}/codes
add notes to people	people_notes	people/{vanId}/notes
add relationships to people	people_relationships	people/{vanId}/relationships
locations	locations	locations/findOrCreate
event	events	events
update event	event_update	events/{eventId}
shifts	shifts	events/{eventId}/shifts
survey responses	surveys	people/{vanId}/canvassResponses
activist codes	activists	people/{vanId}/canvassResponses
contact history	contact_history	people/{vanId}/canvassResponses
signups	signups	signups
edit signups	edit_signups	signups/{eventSignupId}
scores	scores	fileLoadingJobs
contributions (NGP Only)	contributions	contributions
disbursements (NGP Only)	disbursements	disbursements
codes	codes	codes
create supporter groups	supporter_groups	supporterGroups
add supporters to groups	add_supporters_to_groups	supporterGroups/{supporterGroupId}/people/{vanId}
voter registration	voter_registration	voterRegistration/{batchId}/people
merge people	merge_people	people/{vanId}/mergeInto

 

Properties marked with a * are required.

People Export

To create a person record  in NGP or VAN use the “people” export mode. To successfully create people records the following fields are accepted either from a table view or as output of a SQL query:

 

Property	Type
first_name	String
middle_name	String
last_name	String
address_line_1	String
address_line_2	String
city	String
state_or_province	String
zip_or_postal_code	String
address_is_preferred	Boolean
email	String
email_is_preferred	Boolean
email_is_subscribed	Boolean From the NGPVAN documentation: “Deprecated if Targeted Email is available. Optional; indicates whether the email address may be used in a broadcast email. Default value is true. If it’s explicitly set to false, it is treated as an opt-out and you will not be able to resubscribe the email address.” Note: Targeted Email is a feature from NGPVAN. If you have questions about it or enabling it for your committee, please reach out to NGPVAN support.
email_subscription_status	String  From the NGPVAN documentation: “Optional; indicates the email address subscription status for Targeted Email. One of U → unsubscribed, N → neutral, S → subscribed. If unspecified, assumed value is S. Only email addresses with status S will receive messages from Targeted Email. Once an email address has been unsubscribed, it may not be subscribed or set to neutral. This property is only available if Targeted Email is available; it is not valid to set this property and isSubscribed at the same time.”
home_phone	String
home_phone_is_preferred	Boolean
home_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
cell_phone	String
cell_phone_is_preferred	Boolean
cell_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
work_phone	String
work_phone_is_preferred	Boolean
work_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
main_phone	String
main_phone_is_preferred	Boolean
main_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
fax_phone	String
fax_phone_is_preferred	Boolean
fax_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
external_id	Int
external_id_type	String - Required if using external_id, identifies specific ID slot (must be requested and created by VAN first)
civis_external_id	Int - does not export to NGP or VAN, but will be returned in response tables. Useful for building ID crosswalks in Platform.
custom_field_id	Int - custom_field_group_id and assigned_value are required if you want to export custom fields
custom_field_group_id	Int - required if exporting custom fields
assigned_value	String - The assigned value of the custom field to export, required if exporting custom fields.

 

 

People Update Export

 

To update a person record in NGP or VAN use the “update people” export mode. To successfully edit people records in VAN the following fields are accepted either from a table view or as output of a SQL query:

 

Property	Type
*van_id	Int
first_name	String
middle_name	String
last_name	String
address_line_1	String
address_line_2	String
city	String
state_or_province	String
zip_or_postal_code	String
address_is_preferred	Boolean
email	String
email_is_preferred	Boolean
email_is_subscribed	Boolean From the NGPVAN documentation: “Deprecated if Targeted Email is available. Optional; indicates whether the email address may be used in a broadcast email. Default value is true. If it’s explicitly set to false, it is treated as an opt-out and you will not be able to resubscribe the email address.” Note: Targeted Email is a feature from NGPVAN. If you have questions about it or enabling it for your committee, please reach out to NGPVAN support.
email_subscription_status	String  From the NGPVAN documentation: “Optional; indicates the email address subscription status for Targeted Email. One of U → unsubscribed, N → neutral, S → subscribed. If unspecified, assumed value is S. Only email addresses with status S will receive messages from Targeted Email. Once an email address has been unsubscribed, it may not be subscribed or set to neutral. This property is only available if Targeted Email is available; it is not valid to set this property and isSubscribed at the same time.”
home_phone	String
home_phone_is_preferred	Boolean
home_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
cell_phone	String
cell_phone_is_preferred	Boolean
cell_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
work_phone	String
work_phone_is_preferred	Boolean
work_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
main_phone	String
main_phone_is_preferred	Boolean
main_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
fax_phone	String
fax_phone_is_preferred	Boolean
fax_phone_opt_in_status	String, one of I → opt in, U → unknown, O → opt out. Default, if not supplied, is U
external_id	Int
external_id_type	String - Required if using external_id, identifies specific ID slot (must be requested and created by VAN first)
civis_external_id	Int - does not export to NGP or VAN, but will be returned in response tables. Useful for building ID crosswalks in Platform.
custom_field_id	Int - custom_field_group_id and assigned_value are required if you want to export custom fields
custom_field_group_id	Int - required if exporting custom fields
assigned_value	String - The assigned value of the custom field to export, required if exporting custom fields.

 

Add Notes to People

To add notes to a person record in VAN use the “add notes to people” export mode. The following fields are accepted from either a table view or a SQL query

 

Property	Type
*van_id	Integer
*text	String
*is_view_restricted	Boolean
note_category_id	Integer

 

 

Add Codes to People

To add codes to people records in VAN use the “add codes to people” export mode - this enables you to add a SourceCode or a tag to a van_id. The following fields are accepted from either a table view or SQL query

 

Property	Type
*van_id	Integer
*code_id	Integer

 

 

Add Relationships to People

To add relationships to people records in VAN use the “add relationships to people” export mode. The following fields are accepted from either a table view or SQL query. Make sure you enter the relationship destination as van_id, and the source as van_id_2. For example, if applying the “parent” relationship, van_id will be marked as the parent and van_id_2 the child.

 

Property	Type
*van_id	Integer - the VAN ID of the destination of the relationship
*van_id_2	Integer - the VAN ID of the source of the relationship
*relationship_id	Integer

 

Location Export

To create a location object in NGP or VAN use the “locations” export mode. To create locations the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*name	String
address_line_1	String
address_line_2	String
city	String
state_or_province	String
zip_or_postal_code	String

 

Event Export

To create an event object in NGP or VAN use the “events” export mode. 

Note that to create an event, an event type ID, as well as event host and attendee role IDs from VAN are required. The typical workflow involves creating a location for the event first, then using the assigned NGP or VAN location ID to create an event with pre-defined event type and role IDs. 

To create an event, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*name	String
*short_name	String
description	String
*start_date	datetime
*end_date	datetime
*event_type_id	Int
location_id	Int
*attendee_role_id	Int
*host_role_id	Int

 

Events Update Export

To update an event object in NGP or VAN use the “update events” export mode. Note that when using the event update export, all existing event properties will be replaced or set to NULL based on the content of the request. For example, if you do not provide an event description in the update events  export, any existing event description will be replaced with NULL. Additionally, any scheduled signups will be removed from the event. For this reason, we've created a script to record existing signups before editing events so that you can re-assign to new shifts after event edits occur: “NGPVAN: Get and Store all Event Signups”. If you are trying to edit event details and replace the shifts scheduled once the event is updated, here’s what we’d recommend:

1. Use the script “NGPVAN: Get and Store all Event Signups” to pull existing event signups into Platform
2. Use the Update Events mode of the “Export Civis Data to NGPVAN” to edit event details 
3. Use the Shifts mode to add shift times back to the events edited in step 2
4. Replace the shift ids in the output table of step 1 with the new shift ids created in step 3
5. Use the Event Signups export to re-assign people to shift times

Property	Type
*event_id	Int
*name	String
*short_name	String
description	String
*start_date	datetime
*end_date	datetime
*event_type_id	Int - required but cannot change
location_id	Int
*attendee_role_id	Int
*host_role_id	Int

 

Shift Export

To create an event shift object in NGP or VAN use the “shifts” export mode. To create an event shift, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*event_id	Int
*name	String
*start_time	datetime
*end_time	datetime

 

Event Signup Export

To create an event signup object in NGP or VAN use the “signups” export mode. To create a signup, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*van_id	Int
*event_id	Int
*shift_id	Int
*role_id	Int
*status_id	Int
*location_id	Int

 

Edit Event Signup Export

To edit an Event Signup object in NGP or VAN use the “edit signups” export mode. Note: you may only update the following fields for an Event Signup: Shift, Role, Status, or Location. A Signup’s Event and Person properties are immutable after creation, and are only included to identify the Event Signup. If you want to sign up a Person for a different Event, first delete the existing Signup and create a new one.

 

Property	Type
*event_signup_id	Int
*van_id	Int
*event_id	Int
*shift_id	Int
*role_id	Int
*status_id	Int
*location_id	Int

 

Survey Response Export

To apply a survey response to a person record in NGP or VAN use the “survey responses” export mode. To apply a survey response, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*van_id	Int
*survey_question_id	Int
*survey_response_id	Int
input_type_id	Int
contact_type_id	Int
date_canvassed (defaults to current timestamp)	datetime

 

Activist Code Export

To apply or remove  an activist code to a person record in NGP or VAN use the “activist codes” export mode. To add or remove an activist code, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*van_id	Int
*activist_code_id	Int
*action	String - one of ‘Apply’ or ‘Remove’
input_type_id	Int
contact_type_id	Int
date_canvassed (defaults to current timestamp)	Datetime

 

Contact History Export

To apply a contact history record to a person record in NGP or VAN use the “contact history” export mode. To create a contact history, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*van_id	Int
*result_code_id	Int
input_type_id	Int
contact_type_id	Int
phone_id	Int
date_canvassed (defaults to current timestamp)	Datetime

 

People Score Export

Note: If you are a Votebuilder user, please use this process as set by the DNC instead.

 

To upload scores to NGP or VAN  use the “scores” export mode. When exporting scores to NGP or VAN a file-loading job is initialized. The file will be automatically created within Platform, and the export script will post the file to NGP or VAN. To export scores to NGP or VAN, the following fields are accepted from either a table view or the output of a SQL query:

Property	Type
*van_id	Int
*score	Int

 

Codes Export

To create a code object in NGP or VAN use the “codes” export mode. To create a code in NGP or VAN, the following fields are accepted either from a table view or as output of a SQL query:

Property	Type
*name	String - Must be no longer than 20 characters.
description	String
parent_id	String
code_type	String - One of 'Tag' or 'SourceCode'

 

Create Supporter Groups

To supporter groups in VAN use the “create supporter groups” export mode - this enables you to create one or more supporter groups. The following fields are accepted from either a table view or SQL query:

 

Property	Type
*name	String
description	String

 

Add Supporters to Groups

To add supporters to supporter groups in VAN use the “add supporters to groups” export mode - this enables you to add van_ids to supporter groups in VAN. The following fields are accepted from either a table view or SQL query

 

Property	Type
*supporter_group_id	Int
*van_id	Int

 

Contributions Export (NGP only)

To create a contribution object in NGP use the “contributions” export mode. To create contribution records in NGP the following fields are accepted either from a table view or as output of a SQL query:

Property	Type
*van_id	String
*designation_id	Int
*date_received	String
*amount	String - Values that have more than 2 digits after decimal point will not be accepted.
*status	String - One of 'Pending', 'Declined', or 'Settled'
*payment_type	String - One of 'Check', 'MoneyOrder', 'Cash', 'CreditCard', 'InKind', 'Unknown', 'ElectronicFundsTransfer', 'ElectronicPaySystem', 'PayPal', 'Stock', or 'ApplePay'
financial_batch_id	Int - If specified, the Designation of the Financial Batch must match the Designation of this contribution.
bank_account	Int
contribution_bank_account_id	Int
contribution_bank_account_name	String
check_date	String - A date string between 1901-01-01and 2075-12-31.
check_number	String
online_reference_number	String
deposit_date	String - A date string between1901-01-01and2075-12-31.
deposit_number	String
pledge_id	String
code_id	Int - Contributions may not have more than one Code applied, and if a Code is applied it must be a Source Code.
code_name	String
direct_marketing_code	String
date_thanked	Date
notes	String
linked_joint_fundraising_contribution_id	Int
linked_partnership_contribution_id	Int

 

Disbursements Export  (NGP only)

To create a contribution object in NGP use the “disbursements” export mode. To create disbursement records in NGP the following fields are accepted either from a table view or as output of a SQL query:

Property	Type
*van_id	String
*designation_id	Int
*date_issued	String
*amount	String - Value that has more than 2 digits after decimal point will not be accepted.
batch_code	String
check_date	String
check_number	String
code_id	Int
notes	String
disclosure_field_id	Int
disclosure_designation_id	Int
disclosure_field_value	String

 

Voter Registration Export

To create a voter registration record in VAN use the “voter registration” export mode. This mode requires a batch_id and any of the fields for a Person (see Person Export, above), as either a table view or a SQL query:

 

Property	Type
*batch_id	Int.  Note: You will need to have previously created a Voter Registration Batch in the VAN UI or through this API call. From the documentation: “A Voter Registration Batch must be state specific and can only include records entered from the same form.”
*Person	A Person is represented by any of the fields from the Person Export (above).

 

Response Table Fields

People

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• van_id

• van_status

 

Locations

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• location_id

 

Events

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• event_id

• location_id

 

Shifts 

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• shift_id

• location_id

• event_id

 

Signups

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• event_signup_id

 

Edit Signups

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

 

Surveys

• external_id

• attempt_id

• raw_payload

• raw_erro

• error_code

• content_hash

• contact_survey_response_id

• contacts_contact_id

 

Activist Codes

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• contact_activist_code_id

• contacts_contact_id

 

Contact History

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

 

Scores

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• job_id

 

Supporter Groups

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• supporter_group_id

 

Add Supporters to Groups

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

 

Voter Registration

• external_id

• attempt_id

• raw_payload

• raw_error

• error_code

• content_hash

• van_id

Merge People Export

To merge two contact records in your database, use the “merge people” export mode. With this export mode, a source contact record is merged into a target contact record, retaining the target's information when source and target conflict. Note that the merges are permanent and cannot be undone, so use this export mode with caution. To merge records, the following fields are accepted either from a table view or as output of a SQL query: